JPQL query for all entities in descending order returns no results on Oracle

[anija-anil]

A Jakarta Data test fails with,

java.util.NoSuchElementException: No value present at java.base/java.util.Optional.orElseThrow(Optional.java:377) at test.jakarta.data.web.DataTestServlet.testFindAndDeleteReturnsIds(DataTestServlet.java:1822)

After persisting a number of entities, Jakarta Data sent the following JPQL:

SELECT ID FROM Package ORDER BY WIDTH DESC

EclipseLink turned this into a SQL query that didn't produce any results:

[12/19/24, 13:27:49:861 CST] 00000039 id=00000000 eclipselink.ps.query                                         3 Execute query ReportQuery(referenceClass=Package sql="SELECT ID FROM Package ORDER BY WIDTH DESC")
[12/19/24, 13:27:49:861 CST] 00000039 id=00000000 eclipselink.ps.transaction                                   3 TX beginTransaction, status=STATUS_ACTIVE
[12/19/24, 13:27:49:861 CST] 00000039 id=00000000 eclipselink.ps.connection                                    3 Connection acquired from connection pool [default].
[12/19/24, 13:27:49:861 CST] 00000039 id=00000000 eclipselink.ps.connection                                    3 reconnecting to external connection pool
[12/19/24, 13:27:49:954 CST] 00000039 id=00000000 eclipselink.ps.sql                                           3 SELECT ID AS a1 FROM Package WHERE (ID) IN (SELECT null FROM (SELECT null, ROWNUM rnum  FROM (SELECT ID AS a1 FROM Package ORDER BY WIDTH DESC) WHERE ROWNUM <= ?) WHERE rnum > ? )  ORDER BY WIDTH DESC FOR UPDATE
	bind => [2 parameters bound]

Also, a second Jakarta Data test fails similarly,

java.util.NoSuchElementException: No value present
at java.base/java.util.Optional.orElseThrow(Optional.java:377)
at test.jakarta.data.web.DataTestServlet.testFindAndDeleteReturnsIds(DataTestServlet.java:1822) 

In this test, Jakarta Data persists entities and then sends the following JPQL:

SELECT NEW test.jakarta.data.jpa.web.CityId(o.name, o.stateName) FROM City o WHERE (o.stateName=?1) ORDER BY o.name

EclipseLink turns this into a SQL query and finds no results:

[12/19/24, 13:18:58:006 CST] 0000003a id=00000000 eclipselink.ps.query                                         3 Execute query ReportQuery(referenceClass=City sql="SELECT NAME, STATENAME FROM City WHERE (STATENAME = ?) ORDER BY NAME")
[12/19/24, 13:18:58:006 CST] 0000003a id=00000000 eclipselink.ps.transaction                                   3 TX beginTransaction, status=STATUS_ACTIVE
[12/19/24, 13:18:58:006 CST] 0000003a id=00000000 eclipselink.ps.connection                                    3 Connection acquired from connection pool [default].
[12/19/24, 13:18:58:006 CST] 0000003a id=00000000 eclipselink.ps.connection                                    3 reconnecting to external connection pool
[12/19/24, 13:18:58:082 CST] 0000003a id=00000000 eclipselink.ps.sql                                           3 SELECT NAME AS a1, STATENAME AS a2 FROM City WHERE (STATENAME = ?) AND (STATENAME,NAME) IN (SELECT null,null FROM (SELECT null,null, ROWNUM rnum  FROM (SELECT NAME AS a1, STATENAME AS a2 FROM City WHERE (STATENAME = ?) ORDER BY NAME) WHERE ROWNUM <= ?) WHERE rnum > ? )  ORDER BY NAME FOR UPDATE
	bind => [4 parameters bound]

It should be noted that the City entity has an @IdClass(CityId.class) where CityId has String fields: name, stateName corresponding to fields of the City entity:

    @Id
    public String name;

    @Id
    public String stateName;

[Tomas-Kraus]

I see no issue on Eclipselink side. Here is small application with few tests to verify your scenario:
https://github.com/Tomas-Kraus/eclipselink-bug/tree/bug-2339
Tested with 5.0.0-B05 version of Eclipselink which is JPA 3.2 compliant.

[ajaypaul-ibm]

Hi @Tomas-Kraus I tried in JPA , but still getting the issue. Please check recreate details in this PR OpenLiberty/open-liberty#30595

[ajaypaul-ibm]

Entity:

@Entity
public class Package {

    public String description;

    @Id
    public int id;

    public float height;

    public float length;

    public float width;

    public static Package of(int id, float length, float width, float height, String description) {
        Package inst = new Package();
        inst.id = id;
        inst.length = length;
        inst.width = width;
        inst.height = height;
        inst.description = description;
        return inst;
    }

    @Override
    public String toString() {
        return "Package id=" + id + "; L=" + length + "; W=" + width + "; H=" + height + " " + description;
    }
}

Test case :

 @Test
    @Ignore
    //Reference issue : https://github.com/OpenLiberty/open-liberty/issues/30444
    public void testOLGH30444() throws Exception {
        deleteAllEntities(Package.class); 

        Package p1 = Package.of(1, 1.0f, 1.0f, 1.0f, "testOLGH28545-1");
        Package p2 = Package.of(2, 1.0f, 2.0f, 1.0f, "testOLGH28545-2");

        List<Integer> results;

        tx.begin();
        em.persist(p1);
        em.persist(p2);
        tx.commit();

        tx.begin();
        try {
            results = em.createQuery("SELECT ID FROM Package ORDER BY WIDTH DESC", Integer.class)
                            .setLockMode(LockModeType.PESSIMISTIC_WRITE)
                            .setMaxResults(1)
                            .getResultList();

            tx.commit();
        } catch (Exception e) {
            tx.rollback();
            throw e;
        }
        assertEquals(1, results.size());
        assertEquals(2, results.get(0).intValue());

    }