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

[KyleAure]

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.

[ajaypaul-ibm]

Raised Eclipselink issue here : eclipse-ee4j/eclipselink#2243

[njr-11]

I was asked to verify this is fixed in the latest code from integration which brings in beta 7 of EclipseLink.
It is not fixed. When I attempted to enable the tests to run on DB2, I still see the same broken behavior where multiple attempts to read the data produce different results, one finding it and another not.

Here is an example,

Finds the data:

[3/7/25, 9:59:49:425 CST] 00000065 id=457474f8 io.openliberty.data.internal.persistence.QueryInfo           3 createQuery 
                                                                                                               SELECT this.numFullTimeWorkers FROM DemographicInfo WHERE this.collectedOn=:when
                                                                                                               test.jakarta.data.jpa.web.DemographicInfo
[3/7/25, 9:59:49:427 CST] 00000065 id=457474f8 io.openliberty.data.internal.persistence.QueryInfo           3 set :when 2024-04-30T16:00:00Z
[3/7/25, 9:59:49:427 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 client acquired: 2089677666
[3/7/25, 9:59:49:427 CST] 00000065 id=00000000 eclipselink.ps.transaction                                   3 acquire unit of work: 107165726
[3/7/25, 9:59:49:427 CST] 00000065 id=00000000 eclipselink.ps.query                                         3 Execute query ReportQuery(referenceClass=DemographicInfo sql="SELECT NUMFULLTIMEWORKERS FROM DemographicInfo WHERE (COLLECTEDON = ?)")
[3/7/25, 9:59:49:428 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 Connection acquired from connection pool [read].
[3/7/25, 9:59:49:428 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 reconnecting to external connection pool
[3/7/25, 9:59:49:428 CST] 00000065 id=00000000 eclipselink.ps.sql                                           3 SELECT NUMFULLTIMEWORKERS FROM DemographicInfo WHERE (COLLECTEDON = ?)
	bind => [1 parameter bound]
[3/7/25, 9:59:49:696 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 Connection released to connection pool [read].
[3/7/25, 9:59:49:697 CST] 00000065 id=457474f8 io.openliberty.data.internal.persistence.QueryInfo           3 result list type: public class java.util.Vector<E>
[3/7/25, 9:59:49:697 CST] 00000065 id=457474f8 io.openliberty.data.internal.persistence.QueryInfo           3 type of first result: public class java.math.BigInteger
[3/7/25, 9:59:49:697 CST] 00000065 id=457474f8 io.openliberty.data.internal.persistence.QueryInfo           3 convert BigInteger (133809000) to BigDecimal
[3/7/25, 9:59:49:697 CST] 00000065 id=457474f8 io.openliberty.data.internal.persistence.QueryInfo           < find Exit  
                                                                                                               133809000

Does not find it:

[3/7/25, 9:59:49:698 CST] 00000065 id=23ee6d9f io.openliberty.data.internal.persistence.QueryInfo           3 createQuery 
                                                                                                               SELECT numFullTimeWorkers FROM DemographicInfo WHERE collectedOn=:when
                                                                                                               test.jakarta.data.jpa.web.DemographicInfo
[3/7/25, 9:59:49:701 CST] 00000065 id=23ee6d9f io.openliberty.data.internal.persistence.QueryInfo           3 set :when 2024-04-30T16:00:00Z
[3/7/25, 9:59:49:701 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 client acquired: 301113392
[3/7/25, 9:59:49:701 CST] 00000065 id=00000000 eclipselink.ps.transaction                                   3 acquire unit of work: 2111142582
[3/7/25, 9:59:49:701 CST] 00000065 id=00000000 eclipselink.ps.query                                         3 Execute query ReportQuery(referenceClass=DemographicInfo sql="SELECT NUMFULLTIMEWORKERS FROM DemographicInfo WHERE (COLLECTEDON = ?)")
[3/7/25, 9:59:49:701 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 Connection acquired from connection pool [read].
[3/7/25, 9:59:49:701 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 reconnecting to external connection pool
[3/7/25, 9:59:49:701 CST] 00000065 id=00000000 eclipselink.ps.sql                                           3 SELECT NUMFULLTIMEWORKERS FROM DemographicInfo WHERE (COLLECTEDON = ?)
	bind => [1 parameter bound]
[3/7/25, 9:59:49:981 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 Connection released to connection pool [read].
[3/7/25, 9:59:49:981 CST] 00000065 id=23ee6d9f io.openliberty.data.internal.persistence.QueryInfo           3 result list type: public class java.util.Vector<E>
[3/7/25, 9:59:49:982 CST] 00000065 id=00000000 eclipselink.ps.transaction                                   3 release unit of work
[3/7/25, 9:59:49:982 CST] 00000065 id=00000000 eclipselink.ps.connection                                    3 client released
[3/7/25, 9:59:49:982 CST] 00000065 id=00000000 io.openliberty.data.internal.persistence.RepositoryImpl      3 Failure occurred: jakarta.data.exceptions.EmptyResultException

Here is another example for a different query,

Finds the data:

[3/7/25, 9:59:48:896 CST] 0000003e id=2d304990 io.openliberty.data.internal.persistence.QueryInfo           3 createQuery 
                                                                                                               SELECT publicDebt FROM DemographicInfo WHERE collectedOn=:when
                                                                                                               test.jakarta.data.jpa.web.DemographicInfo
[3/7/25, 9:59:48:898 CST] 0000003e id=2d304990 io.openliberty.data.internal.persistence.QueryInfo           3 set :when 2024-04-30T16:00:00Z
[3/7/25, 9:59:48:898 CST] 0000003e id=00000000 eclipselink.ps.connection                                    3 client acquired: 914902329
[3/7/25, 9:59:48:898 CST] 0000003e id=00000000 eclipselink.ps.transaction                                   3 acquire unit of work: 2090484971
[3/7/25, 9:59:48:898 CST] 0000003e id=00000000 eclipselink.ps.query                                         3 Execute query ReportQuery(referenceClass=DemographicInfo sql="SELECT PUBLICDEBT FROM DemographicInfo WHERE (COLLECTEDON = ?)")
[3/7/25, 9:59:48:898 CST] 0000003e id=00000000 eclipselink.ps.connection                                    3 Connection acquired from connection pool [read].
[3/7/25, 9:59:48:898 CST] 0000003e id=00000000 eclipselink.ps.connection                                    3 reconnecting to external connection pool
[3/7/25, 9:59:48:898 CST] 0000003e id=00000000 eclipselink.ps.sql                                           3 SELECT PUBLICDEBT FROM DemographicInfo WHERE (COLLECTEDON = ?)
	bind => [1 parameter bound]
[3/7/25, 9:59:49:143 CST] 0000003e id=00000000 eclipselink.ps.connection                                    3 Connection released to connection pool [read].
[3/7/25, 9:59:49:143 CST] 0000003e id=2d304990 io.openliberty.data.internal.persistence.QueryInfo           3 result list type: public class java.util.Vector<E>
[3/7/25, 9:59:49:143 CST] 0000003e id=2d304990 io.openliberty.data.internal.persistence.QueryInfo           3 type of first result: public class java.math.BigDecimal
[3/7/25, 9:59:49:144 CST] 0000003e id=2d304990 io.openliberty.data.internal.persistence.QueryInfo           < find Exit  
                                                                                                               27480960216618

Does not find it:

[3/7/25, 9:59:49:145 CST] 0000003e id=3e05d621 io.openliberty.data.internal.persistence.QueryInfo           3 createQuery 
                                                                                                               SELECT publicDebt FROM DemographicInfo WHERE collectedOn=:when
                                                                                                               test.jakarta.data.jpa.web.DemographicInfo
[3/7/25, 9:59:49:145 CST] 0000003e id=3e05d621 io.openliberty.data.internal.persistence.QueryInfo           3 set :when 2024-04-30T16:00:00Z
[3/7/25, 9:59:49:145 CST] 0000003e id=00000000 eclipselink.ps.connection                                    3 client acquired: 273114507
[3/7/25, 9:59:49:145 CST] 0000003e id=00000000 eclipselink.ps.transaction                                   3 acquire unit of work: 1045747316
[3/7/25, 9:59:49:145 CST] 0000003e id=00000000 eclipselink.ps.query                                         3 Execute query ReportQuery(referenceClass=DemographicInfo sql="SELECT PUBLICDEBT FROM DemographicInfo WHERE (COLLECTEDON = ?)")
[3/7/25, 9:59:49:145 CST] 0000003e id=00000000 eclipselink.ps.connection                                    3 Connection acquired from connection pool [read].
[3/7/25, 9:59:49:145 CST] 0000003e id=00000000 eclipselink.ps.connection                                    3 reconnecting to external connection pool
[3/7/25, 9:59:49:146 CST] 0000003e id=00000000 eclipselink.ps.sql                                           3 SELECT PUBLICDEBT FROM DemographicInfo WHERE (COLLECTEDON = ?)
	bind => [1 parameter bound]
[3/7/25, 9:59:49:407 CST] 0000003e id=00000000 eclipselink.ps.connection                                    3 Connection released to connection pool [read].
[3/7/25, 9:59:49:408 CST] 0000003e id=3e05d621 io.openliberty.data.internal.persistence.QueryInfo           3 result list type: public class java.util.Vector<E>
[3/7/25, 9:59:49:408 CST] 0000003e id=3e05d621 io.openliberty.data.internal.persistence.QueryInfo           < find Exit  
                                                                                                               null