JPQL SELECT query returns incorrect results when running back to back on DB2

[ajaypaul-ibm]

EclipseLink is returning an empty result list intermittently when running on DB2.

For example, the JPQL Query:
SELECT this.numFullTimeWorkers FROM DemographicInfo WHERE this.collectedOn=:when

When executed on DB2 this query will have one of the two behaviors:

1. The first time the query is executed an empty result list will be returned, all subsequent executions of this query will return
   the correct result
2. The first time the query is executed the correct result will be returned, all subsequent executions of this query will return an empty result list.

[lukasj]

DB2 specific support: https://github.com/eclipse-ee4j/eclipselink/blob/master/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/DB2Platform.java

[ajaypaul-ibm]

Hi @rfelcman / @lukasj ,
We've been investigating issue 2243, and based on our findings, it appears to be related to EclipseLink. We tested the same scenario using a standalone DB2 JDBC application, which returned the correct result, suggesting that the issue might not be database related.Could you please take a closer look at this from the EclipseLink perspective?

[rfelcman]

Hi @rfelcman / @lukasj , We've been investigating issue 2243, and based on our findings, it appears to be related to EclipseLink. We tested the same scenario using a standalone DB2 JDBC application, which returned the correct result, suggesting that the issue might not be database related.Could you please take a closer look at this from the EclipseLink perspective?

Hello,
Yes we can do it, but please deliver there test case which contains:

• persistence.xml
• Entity DemographicInfo
• SQL script which will create and populate DB table which is behind DemographicInfo
• Test where SELECT this.numFullTimeWorkers FROM DemographicInfo WHERE this.collectedOn=:when is called.

NOTE: We don't have DB2 we should test it against MySQL, Derby, Oracle RDBMS and PostgreSQL (limited scope).

[ajaypaul-ibm]

Hi @rfelcman
Please find the details below :

• persistence.xml

<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_2.xsd"
        version="3.2">
    <persistence-unit name="JPAPU">
        <qualifier>java.lang.String</qualifier>
        <scope>java.lang.String</scope>
        <properties>
            <!-- EclipseLink should create the database schema automatically -->
            <property name="jakarta.persistence.schema-generation.database.action" value="drop-and-create" />
            <!-- SQL logging -->
            <property name="eclipselink.logging.level.sql" value="FINE"/>
            <property name="eclipselink.logging.parameters" value="true"/>
            <property name="eclipselink.logging.level.transaction" value="FINE"/>
            <property name="eclipselink.logging.level.cache" value="FINE"/>
        </properties>
    </persistence-unit>
</persistence>

DemographicInfo Model:

@Entity
public class DemographicInfo {

    @Column
    public Instant collectedOn;

    @GeneratedValue
    @Id
    public BigInteger id;

    @Column
    public BigDecimal publicDebt;

    @Column
    public BigDecimal intragovernmentalDebt;

    @Column
    public BigInteger numFullTimeWorkers;

    public static DemographicInfo of(int year, int month, int day,
                                     long numFullTimeWorkers,
                                     double intragovernmentalDebt, double publicDebt) {
        DemographicInfo inst = new DemographicInfo();
        inst.collectedOn = ZonedDateTime.of(year, month, day, 12, 0, 0, 0, ZoneId.of("America/New_York")).toInstant();
        inst.numFullTimeWorkers = BigInteger.valueOf(numFullTimeWorkers);
        inst.intragovernmentalDebt = BigDecimal.valueOf(intragovernmentalDebt);
        inst.publicDebt = BigDecimal.valueOf(publicDebt);
        return inst;
    }

    @Override
    public String toString() {
        return "DemographicInfo from " + collectedOn;
    }
}

Test case :

@Test
    // @SkipIfSysProp(DB_DB2) // Reference issue: https://github.com/OpenLiberty/open-liberty/issues/29443
    public void testOLGH29443() throws Exception {
        deleteAllEntities(DemographicInfo.class);

        ZoneId ET = ZoneId.of("America/New_York");
        Instant when = ZonedDateTime.of(2022, 4, 29, 12, 0, 0, 0, ET)
                .toInstant();

        DemographicInfo US2022 = DemographicInfo.of(2022, 4, 29, 132250000, 6526909395140.41, 23847245116757.60);
        DemographicInfo US2007 = DemographicInfo.of(2007, 4, 30, 121090000, 3833110332444.19, 5007058051986.64);

        List<BigInteger> results;

        tx.begin();
        em.persist(US2022);
        em.persist(US2007);
        tx.commit();

        List<Error> errors = new ArrayList<>();

        Thread.sleep(Duration.ofSeconds(1).toMillis());

        for (int i = 0; i < 10; i++) {
            System.out.println("Executing SELECT query, iteration: " + i);

            tx.begin();
            results = em
                    .createQuery("SELECT this.numFullTimeWorkers FROM DemographicInfo WHERE this.collectedOn=:when",
                            BigInteger.class)
                    .setParameter("when", when)
                    .getResultList();
            tx.commit();

            try {
                assertNotNull("Query should not have returned null after iteration " + i, results);
                assertFalse("Query should not have returned an empty list after iteration " + i, results.isEmpty()); // Recreate
                                                                                                                     // -
                                                                                                                     // an
                                                                                                                     // empty
                                                                                                                     // list
                                                                                                                     // is
                                                                                                                     // returned
                assertEquals("Query should not have returned more than one result after iteration " + i, 1,
                        results.size());
                assertEquals(US2022.numFullTimeWorkers, results.get(0));
            } catch (AssertionError e) {
                errors.add(e);
            }
        }

        if (!errors.isEmpty()) {
            throw new AssertionError(
                    "Executing the same query returned incorrect results " + errors.size() + " out of 10 executions",
                    errors.get(0));
        }
    }

[rfelcman]

In the attachment is my local test case which is passing against all specified DBs mentioned in the persistence.xml. I tried to call it multiple times without any error.
In the test case I had to add/modify some missing and problematic code which was or not provided by code above:

• missing pom.xml -> it's not clear which dependencies are used
• persistence.xml what does mean?

           <qualifier>java.lang.String</qualifier>
        <scope>java.lang.String</scope>

• added connection properties to persistence.xml
• added missing import in classes
• added deleteAllEntities( implementation
• added missing EntityManagerFactory emf, EntityManager em, EntityTransaction tx
  Feel free to modify attached test case to demonstrate bug and upload there modified version there.
  jpa-bug-2243-JPQLSELECTQueryReturnsIncorrectResults.tar.gz