eclipse-ee4j
JPQL queries that select ElementCollection attributes have incorrect results
Describe the bug
When JPQL queries select an attribute that is a ElementCollection (if using annotations) or element-collection (if using XML) and getResultList is used to obtain the result, instead of returning a List of one or more Collection(s), EclipseLink appears to be combining all of the collection values into a single list of values and returning it to the caller.
This can be surfaced to the application in a variety of ways, such as:
DataJPATestServlet.testCollectionAttribute: expected:<[507]> but was:<507>
at test.jakarta.data.jpa.web.DataJPATestServlet.testCollectionAttribute(DataJPATestServlet.java:439)
In the above case, the result list contained an Integer rather than a Set<Integer>, despite issuing a query SELECT o.areaCodes FROM City o WHERE (o.name=?1 AND o.stateName=?2) on o.areaCodes which is a collection.
Exception [EclipseLink-0] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT NEW test.jakarta.data.jpa.web.AreaInfo(o.name, o.stateName, o.areaCodes) FROM City o WHERE (o.stateName=?1) ORDER BY o.name].
[67, 78] The state field path 'o.areaCodes' cannot be resolved to a collection type.
In the above case, it complains about the value being used for o.areaCodes not being a collection, when it ought to be a collection because o.areaCodes is defined as a collection on the entity.
Steps to Reproduce This fails a variety of test cases so there are various places where you can reproduce it.
One way: uncomment the line @ElementCollection(fetch = FetchType.EAGER) on test.jakarta.data.jpa.web.City and run the io.openliberty.data.internal_fat_jpa test bucket. The test testCollectionAttribute will start failing.
Another way: uncomment the lines of testRecordWithEmbeddables in test.jakarta.data.web.DataTestServlet and run the io.openliberty.data.internal_fat test bucket.
Another way: uncomment the lines of testEmbeddableCollection in test.jakarta.data.jpa.web.DataJPATestServlet and run the io.openliberty.data.internal_fat_jpa test bucket.
Expected behavior
JPQL queries for an attribute that is a Set<Integer> should return a result list that contains Set<Integer>, not Integer.
JPQL queries that construct a new class instance with an attribute that is a Set<Integer> as a parameter should receive a Set<Integer> and successfully construct an instance, not be rejected saying that the collection attribute is not a collection attribute.
Diagnostic information:
OpenLiberty Version: latest
Affected feature(s) persistence-3.1
Java Version:
java.home = /Users/njr/drivers/jdk-21.jdk/Contents/Home
java.version = 21
java.runtime = OpenJDK Runtime Environment (21+35-2513)
os = Mac OS X (14.5; aarch64) (en_US)
server.xml configuration (WITHOUT sensitive information like passwords) see test buckets
Hi @rfelcman Please find the entity below :
@Entity
@IdClass(CityId.class)
public class City {
@ElementCollection(fetch = FetchType.EAGER)
public Set<Integer> areaCodes;
@Version
long changeCount;
@Id
public String name;
public int population;
@Id
public String stateName;
public static City of(String name, String state, int population, Set<Integer> areaCodes) {
City inst = new City();
inst.name = name;
inst.stateName = state;
inst.population = population;
inst.areaCodes = areaCodes;
return inst;
}
@Override
public String toString() {
return "City of " + name + ", " + stateName + " pop " + population + " in " + areaCodes + " v" + changeCount;
}
}
Hi @rfelcman Please find the recreate details :
@Test
public void testElementCollection() throws Exception {
ECEntity e1 = new ECEntity();
e1.setId("EC1");
e1.setIntArray(new int[] { 14, 12, 1 });
e1.setLongList(new ArrayList<>(List.of(14L, 12L, 1L)));
e1.setLongListEC(new ArrayList<>(List.of(14L, 12L, 1L)));
e1.setStringSet(Set.of("fourteen", "twelve", "one"));
e1.setStringSetEC(Set.of("fourteen", "twelve", "one"));
ECEntity e2 = new ECEntity();
e2.setId("EC2");
e2.setIntArray(new int[] { 14, 12, 2 });
e2.setLongList(new ArrayList<>(List.of(14L, 12L, 2L)));
e2.setLongListEC(new ArrayList<>(List.of(14L, 12L, 2L)));
e2.setStringSet(Set.of("fourteen", "twelve", "two"));
e2.setStringSetEC(Set.of("fourteen", "twelve", "two"));
tx.begin();
em.persist(e1);
em.persist(e2);
tx.commit();
// Test JPQL queries
String jpql;
List<?> results;
// Query for intArray
tx.begin();
try {
jpql = "SELECT intArray FROM ECEntity WHERE id=?1";
results = em.createQuery(jpql)
.setParameter(1, "EC1")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
// Query for longList
tx.begin();
try {
jpql = "SELECT longList FROM ECEntity WHERE id=?1";
results = em.createQuery(jpql)
.setParameter(1, "EC1")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
// Query for stringSet
tx.begin();
try {
jpql = "SELECT stringSet FROM ECEntity WHERE id=?1";
results = em.createQuery(jpql)
.setParameter(1, "EC1")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
tx.begin();
try {
jpql = "SELECT longListEC FROM ECEntity WHERE id=?1";
results = em.createQuery(jpql)
.setParameter(1, "EC1")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
// Query for longListEC
tx.begin();
try {
jpql = "SELECT longListEC FROM ECEntity WHERE id LIKE ?1";
results = em.createQuery(jpql)
.setParameter(1, "EC%")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
tx.begin();
try {
jpql = "SELECT longList FROM ECEntity WHERE id LIKE ?1";
results = em.createQuery(jpql)
.setParameter(1, "EC%")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
// Query for stringSetEC
tx.begin();
try {
jpql = "SELECT stringSetEC FROM ECEntity WHERE id LIKE ?1";
results = em.createQuery(jpql)
.setParameter(1, "EC%")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
tx.begin();
try {
jpql = "SELECT stringSet FROM ECEntity WHERE id LIKE ?1";
results = em.createQuery(jpql)
.setParameter(1, "EC%")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
tx.begin();
try {
jpql = "SELECT stringSetEC FROM ECEntity WHERE id=?1";
results = em.createQuery(jpql)
.setParameter(1, "EC1")
.getResultList();
logQueryResults(jpql,results);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
}
public void logQueryResults(String jpql, Collection<?> results) {
System.out.println();
System.out.println(jpql);
System.out.println("getResultList returned a " + results.getClass().getTypeName());
if (!results.isEmpty()) {
System.out.println(" elements are of type " + results.iterator().next().getClass().getTypeName());
} else {
System.out.println(" elements are of type <empty>");
}
StringBuilder s = new StringBuilder();
boolean first = true;
for (Object element : results) {
if (first)
first = false;
else
s.append(", ");
if (element instanceof int[])
s.append(Arrays.toString((int[]) element));
else
s.append(element);
}
System.out.println(" contents are [" + s.toString() + "]");
}
ECEntity.java is like below :
package io.openliberty.jpa.data.tests.models;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;
import jakarta.persistence.ElementCollection;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
/**
* Entity with and without ElementCollection attributes.
*/
@Entity
public class ECEntity {
@Id
String id;
int[] intArray = new int[] {};
ArrayList<Long> longList = new ArrayList<>();
@ElementCollection(fetch = FetchType.EAGER)
ArrayList<Long> longListEC = new ArrayList<>();
Set<String> stringSet = new HashSet<>();
@ElementCollection(fetch = FetchType.EAGER)
Set<String> stringSetEC = new HashSet<>();
public String getId() {
return id;
}
public int[] getIntArray() {
return intArray;
}
public ArrayList<Long> getLongList() {
return longList;
}
public ArrayList<Long> getLongListEC() {
return longListEC;
}
public Set<String> getStringSet() {
return stringSet;
}
public Set<String> getStringSetEC() {
return stringSetEC;
}
public void setId(String id) {
this.id = id;
}
public void setIntArray(int[] intArray) {
this.intArray = intArray;
}
public void setLongList(ArrayList<Long> longList) {
this.longList = longList;
}
public void setLongListEC(ArrayList<Long> longListEC) {
this.longListEC = longListEC;
}
public void setStringSet(Set<String> stringSet) {
this.stringSet = stringSet;
}
public void setStringSetEC(Set<String> stringSetEC) {
this.stringSetEC = stringSetEC;
}
}
This demonstrates that EclipseLink is allowing ElementCollection attributes to be returned by JPQL queries, but with results that are both inconsistent with non-ElementCollection attributes of the same type, and are also incorrect. Especially when returning multiple results which get erroneously combined together into a single collection!
Here is the output of this test. Entity attribute names ending in "EC" use ElementCollection. The others have the same types but don't use ElementCollection:
SELECT intArray FROM ECEntity WHERE id=?1
getResultList returned a java.util.Vector
elements are of type int[]
contents are [[14, 12, 1]]
SELECT longList FROM ECEntity WHERE id=?1
getResultList returned a java.util.Vector
elements are of type java.util.ArrayList
contents are [[14, 12, 1]]
SELECT stringSet FROM ECEntity WHERE id=?1
getResultList returned a java.util.Vector
elements are of type java.util.ImmutableCollections$SetN
contents are [[fourteen, twelve, one]]
SELECT longListEC FROM ECEntity WHERE id=?1
getResultList returned a java.util.Vector
elements are of type java.lang.Long
contents are [14, 12, 1]
SELECT longListEC FROM ECEntity WHERE id LIKE ?1
getResultList returned a java.util.Vector
elements are of type java.lang.Long
contents are [14, 12, 1, 14, 12, 2]
SELECT longList FROM ECEntity WHERE id LIKE ?1
getResultList returned a java.util.Vector
elements are of type java.util.ArrayList
contents are [[14, 12, 1], [14, 12, 2]]
SELECT stringSetEC FROM ECEntity WHERE id LIKE ?1
getResultList returned a java.util.Vector
elements are of type java.lang.String
contents are [fourteen, twelve, one, fourteen, twelve, two]
SELECT stringSet FROM ECEntity WHERE id LIKE ?1
getResultList returned a java.util.Vector
elements are of type java.util.ImmutableCollections$SetN
contents are [[fourteen, twelve, one], [fourteen, twelve, two]]
SELECT stringSetEC FROM ECEntity WHERE id=?1
getResultList returned a java.util.Vector
elements are of type java.lang.String
contents are [fourteen, twelve, one]
<<< END: testElementCollection
If EclipseLink wants to reject this outright because the Jakarta Persistence specification does not require element collections to be selected by JPQL queries, that would be perfectly fine. But if EclipseLink doesn't reject it, it at least needs to get the behavior correct and return valid results. PR for reference : https://github.com/OpenLiberty/open-liberty/pull/30578
Hi @rfelcman Please find the DDL statements below as you mentioned :
- CREATE TABLE ECENTITY (ID VARCHAR(255) NOT NULL, INTARRAY BLOB(2147483647), LONGLIST BLOB(2147483647), STRINGSET BLOB(2147483647), PRIMARY KEY (ID))
- CREATE TABLE ECEntity_LONGLISTEC (ECEntity_ID VARCHAR(255) /* */ , LONGLISTEC BIGINT)
- CREATE TABLE ECEntity_STRINGSETEC (ECEntity_ID VARCHAR(255) /* */ , STRINGSETEC VARCHAR(255))
- ALTER TABLE ECEntity_LONGLISTEC ADD CONSTRAINT CnttyLNGLSTCCnttyD FOREIGN KEY (ECEntity_ID) REFERENCES ECENTITY (ID)
- ALTER TABLE ECEntity_STRINGSETEC ADD CONSTRAINT CnttySTRNGSTCnttyD FOREIGN KEY (ECEntity_ID) REFERENCES ECENTITY (ID)
I don't think that test case is correct. There are compared:
- BLOBs
int[] intArray - INTARRAY BLOB(2147483647)where Java array is (de)serialized into DB and is part of the Entity - Collections where each item is mapped to cell in the table but mapped by
@ElementCollection. @ElementCollection marks a collection. This does not necessarily mean that this collection references a 1-n join. This is not same thing like@OneToMany. JPQL Query likeSELECT longListEC FROM ECEntity WHERE id LIKE ?1is transformed into SQLSELECT t1.STRINGSETEC FROM ECENTITY t0 LEFT OUTER JOIN ECEntity_STRINGSETEC t1 ON (t1.ECEntity_ID = t0.ID) WHERE t0.ID LIKE ?and EclipseLink doesn't have info about Ids. Don't forget, that You will get collection of simple types (Strings, etc.) or a collection of embeddable elements but not a collection of entities.
Hi @rfelcman We already know that the test case is doing something that is not permitted by EclipseLink/JPA in querying for an ElementCollection value in JPQL. That isn't the point. The point is that EclipseLink didn't reject it and instead ran a query and returned wrong data to the application.
TCK https://github.com/jakartaee/platform-tck/tree/main/tcks/apis/persistence/persistence-outside-container