Sorting not honored with JPA and Oracle #2301
Description
When running a Jakarta Data test against Oracle there is an issue with sorting not always being honored.
Note: Similar issue was found in DB2 database which is solved by the PR : #2296 cause was problem in insertion of values in DB2)
Recreation steps below :
Test case :
@Test
// @SkipIfSysProp({DB_Oracle })
// Reference issue: https://github.com/OpenLiberty/open-liberty/issues/28289
public void testOLGH28289() throws Exception {
deleteAllEntities(Package.class);
Package p1 = Package.of(70071, 17.0f, 17.1f, 7.7f, "testOLGH28289#70071"); // tallest and smallest length
Package p2 = Package.of(70077, 77.0f, 17.7f, 7.7f, "testOLGH28289#70077"); // tallest and largest length
Package p3 = Package.of(70007, 70.0f, 10.7f, 0.7f, "testOLGH28289#70007");
List<Package> tallToShort;
tx.begin();
em.persist(p1);
em.persist(p2);
em.persist(p3);
tx.commit();
tx.begin();
try {
tallToShort = em
.createQuery("SELECT o FROM Package o WHERE (o.height<?1) ORDER BY o.height DESC, o.length",
Package.class)
.setParameter(1, 8.0)
.setLockMode(LockModeType.PESSIMISTIC_WRITE) // Cause of issue
.setMaxResults(2)
.getResultList();
} catch (Exception e) {
tx.rollback();
throw e;
}
System.out.println(tallToShort);
assertEquals(2, tallToShort.size());
/**
* Recreate
*
* <pre>
* SELECT * FROM (
* SELECT * FROM (
* SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM
* FROM (
* SELECT ID AS a1, DESCRIPTION AS a2, HEIGHT AS a3, LENGTH AS a4, WIDTH AS a5
* FROM PACKAGE
* WHERE (HEIGHT < ?)
* ORDER BY HEIGHT DESC, LENGTH
* ) AS EL_TEMP
* ) AS EL_TEMP2 WHERE EL_ROWNM <= ?
* ) AS EL_TEMP3 WHERE EL_ROWNM > ?
* FOR READ ONLY WITH RS USE AND KEEP UPDATE LOCKS // When this is added to query the ordering is ignored
*
* bind => [8.0, 2, 0]
* </pre>
*/
System.out.println("expected 70071 , real "+tallToShort.get(0).id);
System.out.println("expected 70077 , real "+tallToShort.get(1).id);
assertEquals(70071, tallToShort.get(0).id);
assertEquals(70077, tallToShort.get(1).id);
}
Package 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;
}
}