JPQL queries that select ElementCollection attributes have incorrect results

[ajaypaul-ibm]

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

[Riva-Tholoor-Philip]

I am looking into this issue

[rfelcman]

Please attach there entities code.

[ajaypaul-ibm]

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;
    }
}

[ajaypaul-ibm]

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 : OpenLiberty/open-liberty#30578

[ajaypaul-ibm]

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)