search

eclipse-ee4j / eclipselink

Eclipselink project
https://eclipse.dev/eclipselink/
Other
202 stars 173 forks source link

JPQL SELECT NEW not joining nor recognising ElementCollection as a collection type #2264

Open Riva-Tholoor-Philip opened 2 months ago

Riva-Tholoor-Philip commented 2 months ago

EclipseLink is rejecting JPQL SELECT NEW clauses where an entity contains an attribute that is an ElementCollection.

For example, SELECT o.comments FROM Rating o WHERE o.id = :id where the column comments is an ElementCollection is correctly returned as a result set. We can see from the SQL generated by EclipseLink that a JOIN is required to get the data from the Rating_COMMENTS table:

SELECT t1.COMMENTS 
FROM RATING t0 
  LEFT OUTER JOIN Rating_COMMENTS t1 
  ON (t1.Rating_ID = t0.ID) 
WHERE (t0.ID = ?)

However, when we try to use o.comments in a SELECT NEW query like the one below:

SELECT NEW 
  io.openliberty.jpa.data.tests.models.Rating( o.id, o.item, o.numStars, o.reviewer, o.comments )
FROM Rating o 
WHERE o.id = :id

The following error is thrown: 

Exception [EclipseLink-0] (Eclipse Persistence Services - 5.0.0.v202408071314-43356e84b79e71022b1656a5462b0a72d70787a4): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT NEW io.openliberty.jpa.data.tests.models.Rating( o.id, o.item, o.numStars, o.reviewer, o.comments ) FROM Rating o WHERE o.id = :id].
[93, 103] The state field path 'o.comments' cannot be resolved to a collection type. (SELECT NEW io.openliberty.jpa.data.tests.models.Rating(o.id, o.item, o.numStars, o.reviewer, [ o.comments ] ...
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:175)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:364)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:298)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:180)

I would have expected EclipseLink to be able to satisfy this JPQL query with a SQL Query something like:

SELECT t0.ID, t0.NAME, t0.PRICE, t0.NUMSTARS, t0.EMAIL, t0.FIRSTNAME, t0.LASTNAME, t1.COMMENTS 
FROM RATING t0
  LEFT OUTER JOIN Rating_COMMENTS t1 
  ON (t1.Rating_ID = t0.ID) 
WHERE (t0ID = ?)
rfelcman commented 18 hours ago

Please attach there entities code.