Incorrect SQL queries generated for Oracle

[KyleAure]

Describe the bug

The entity manager produces incorrect queries for Oracle when given valid JPQL.
Below I have code and logs of 3 different examples of this behavior.

To reproduce

Steps/resources to reproduce the behavior:

• EclipseLink version: 4.0.2
• Java/JDK version:

openjdk 17.0.7 2023-04-18
IBM Semeru Runtime Open Edition 17.0.7.0 (build 17.0.7+7)
Eclipse OpenJ9 VM 17.0.7.0 (build openj9-0.38.0, JRE 17 Mac OS X aarch64-64-Bit 20230418_329 (JIT enabled, AOT enabled)
OpenJ9   - d57d05932
OMR      - 855813495
JCL      - 9d7a231edbc based on jdk-17.0.7+7)

• Entity source

public class Package {
    public String description;
    public int id;
    public float height;
    public float length;
    public float width;

    public Package() {
    }
}

public class Prime {
    public String binaryDigits;
    public boolean even;
    public String hex;
    public String name;
    public long numberId;
    public String romanNumeral;
    public ArrayList<String> romanNumeralSymbols;
    public int sumOfBits;
    public Prime() {
    }
}

@Entity
@IdClass(CityId.class)
public class City {
    public Set<Integer> areaCodes;
    @Version
    long changeCount;
    @Id
    public String name;
    public int population;
    @Id
    public String stateName;

    public City() {
    }
}

public class CityId implements Serializable {
    private static final long serialVersionUID = 1L;
    public String name;
    public String stateName;

    public CityId(String name, String state) {
        this.name = name;
        this.stateName = state;
    }
}

1. For problem in persistence (JPA)
   • JPA context like persistence.xml settings or related system properties (in case of JPA)

<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd"                 version="2.1">
 <entity class="test.jakarta.data.web.Package">
  <table name="WLPPackage"/>
  <attributes>
   <basic name="description">
   </basic>
   <basic name="height">
    <column nullable="false"/>
   </basic>
   <id name="id">
    <column nullable="false"/>
   </id>
   <basic name="length">
    <column nullable="false"/>
   </basic>
   <basic name="width">
    <column nullable="false"/>
   </basic>
  </attributes>
 </entity>
 <entity class="test.jakarta.data.web.Prime">
  <table name="WLPPrime"/>
  <attributes>
   <basic name="binaryDigits">
   </basic>
   <basic name="even">
    <column nullable="false"/>
   </basic>
   <basic name="hex">
   </basic>
   <basic name="name">
   </basic>
   <id name="numberId">
    <column nullable="false"/>
   </id>
   <basic name="romanNumeral">
   </basic>
   <element-collection name="romanNumeralSymbols">
   </element-collection>
   <basic name="sumOfBits">
    <column nullable="false"/>
   </basic>
  </attributes>
 </entity>
</entity-mappings>

• Database provider/version

[5/22/24, 11:17:15:985 CDT] 00000035 id=00000000 eclipselink.ps.connection                                    3 Detected database platform: org.eclipse.persistence.platform.database.Oracle23Platform

• JDBC driver provider/version (it should be useful if bug is related with some "specific" datatype e.g. JSON)

[5/22/24, 11:17:07:367 CDT] 00000035 id=00000000 com.ibm.ws.rsadapter.impl.DatabaseHelper                     I DSRA8203I: Database product name : Oracle
[5/22/24, 11:17:07:368 CDT] 00000035 id=00000000 com.ibm.ws.rsadapter.impl.DatabaseHelper                     I DSRA8204I: Database product version : Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
[5/22/24, 11:17:07:368 CDT] 00000035 id=00000000 com.ibm.ws.rsadapter.impl.DatabaseHelper                     I DSRA8205I: JDBC driver name  : Oracle JDBC driver
[5/22/24, 11:17:07:368 CDT] 00000035 id=00000000 com.ibm.ws.rsadapter.impl.DatabaseHelper                     I DSRA8206I: JDBC driver version  : 21.8.0.0.0

Issue 1

Application code:

...
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

TypedQuery<?> query = em.createQuery("SELECT o FROM Package o ORDER BY o.width DESC", test.jakarta.data.web.Package.class);
query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
query.setMaxResults(1);

List<?> results = query.getResultList();
... Use results

em.getTransaction().commit();
...

Trace:

[5/22/24, 11:17:36:144 CDT] 0000003a id=00000000 eclipselink.ps.query                                         3 Execute query ReadAllQuery(referenceClass=Package sql="SELECT ID, DESCRIPTION, HEIGHT, LENGTH, WIDTH FROM WLPPackage ORDER BY WIDTH DESC")
[5/22/24, 11:17:36:296 CDT] 0000003a id=00000000 eclipselink.ps.sql                                           3 SELECT ID AS a1, DESCRIPTION AS a2, HEIGHT AS a3, LENGTH AS a4, WIDTH AS a5 FROM WLPPackage WHERE (ID) IN (SELECT a1 FROM (SELECT a1, ROWNUM rnum  FROM (SELECT ID AS a1, DESCRIPTION AS a2, HEIGHT AS a3, LENGTH AS a4, WIDTH AS a5 FROM WLPPackage ORDER BY a1) WHERE ROWNUM <= ?) WHERE rnum > ? )  ORDER BY WIDTH DESC FOR UPDATE
	bind => [1, 0]

Incorrect result

SELECT ID AS a1,
       DESCRIPTION AS a2,
       HEIGHT AS a3,
       LENGTH AS a4,
       WIDTH AS a5
FROM WLPPackage
WHERE (ID) IN
    (SELECT a1
     FROM
       (SELECT a1, ROWNUM rnum
        FROM
          (SELECT ID AS a1,
                  DESCRIPTION AS a2,
                  HEIGHT AS a3,
                  LENGTH AS a4,
                  WIDTH AS a5
           FROM WLPPackage
           ORDER BY a1)
        WHERE ROWNUM <= ?)
     WHERE rnum > ? )
ORDER BY WIDTH DESC
FOR
UPDATE

bind => [1, 0]

The resulting SQL is incorrect because of the nested select statements that are produced to limit the output prescribe an ORDER BY ID instead of ORDER BY WIDTH DESC.
Therefore, the result will always be the one element with the lowest ID, instead of the largest width.

Expected behavior
I would expect a query like this:

SELECT ID AS a1,
       DESCRIPTION AS a2,
       HEIGHT AS a3,
       LENGTH AS a4,
       WIDTH AS a5
FROM
  (SELECT a1, ROWNUM rnum
   FROM
     (SELECT ID AS a1,
             DESCRIPTION AS a2,
             HEIGHT AS a3,
             LENGTH AS a4,
             WIDTH AS a5
      FROM WLPPackage
      ORDER BY WIDTH DESC)
   WHERE ROWNUM <= ?)
WHERE rnum > ?
FOR
UPDATE

bind => [1, 0]

Issue 2

Application code:

...
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

TypedQuery<?> query = em.createQuery("SELECT o.id FROM Package o ORDER BY o.width DESC
", test.jakarta.data.web.Package.class);
query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
query.setMaxResults(1);

List<?> results = query.getResultList();
... Use results

em.getTransaction().commit();
...

Trace:

[5/23/24, 17:24:50:275 CDT] 00000034 id=00000000 eclipselink.ps.query                                         3 Execute query ReportQuery(referenceClass=Package sql="SELECT ID FROM WLPPackage ORDER BY WIDTH DESC")
[5/23/24, 17:24:50:437 CDT] 00000034 id=00000000 eclipselink.ps.sql                                           3 SELECT ID AS a1 FROM WLPPackage WHERE (ID) IN (SELECT null FROM (SELECT null, ROWNUM rnum  FROM (SELECT ID AS a1 FROM WLPPackage ORDER BY null) WHERE ROWNUM <= ?) WHERE rnum > ? )  ORDER BY WIDTH DESC FOR UPDATE
	bind => [1, 0]

Incorrect result

SELECT ID AS a1
FROM WLPPackage
WHERE (ID) IN
    (SELECT NULL
     FROM
       (SELECT NULL, ROWNUM rnum
        FROM
          (SELECT ID AS a1
           FROM WLPPackage
           ORDER BY NULL)
        WHERE ROWNUM <= ?)
     WHERE rnum > ? )
ORDER BY WIDTH DESC
FOR
UPDATE

bind => [1, 0]

The resulting SQL is incorrect the ID will never be IN the result of a SELECT NULL statement.

Expected behavior

SELECT ID
FROM
  (SELECT ID, ROWNUM rnum
   FROM
     (SELECT ID
      FROM WLPPackage
      ORDER BY WIDTH DESC)
   WHERE ROWNUM <= ?)
WHERE rnum > ?
FOR
UPDATE

bind => [1, 0]

Issue 3

Application code:

...
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

TypedQuery<?> query = em.createQuery("SELECT DISTINCT LENGTH(p.romanNumeral)  FROM Prime p WHERE p.numberId <= ?1 ORDER BY LENGTH(p.romanNumeral) DESC
", test.jakarta.data.web.Prime.class);
query.setParameter(41);
query.setMaxResults(4)

List<?> results = query.getResultList();
... Use results

em.getTransaction().commit();
...

Trace:

[5/23/24, 17:27:04:760 CDT] 00000034 id=00000000 eclipselink.ps.query                                         3 Execute query ReportQuery(referenceClass=Prime sql="SELECT DISTINCT LENGTH(ROMANNUMERAL), LENGTH(ROMANNUMERAL) FROM WLPPrime WHERE (NUMBERID <= ?) ORDER BY LENGTH(ROMANNUMERAL) DESC")
[5/23/24, 17:27:04:761 CDT] 00000034 id=00000000 eclipselink.ps.sql                                           3 SELECT * FROM (SELECT a.*, ROWNUM rnum  FROM (SELECT DISTINCT LENGTH(ROMANNUMERAL), LENGTH(ROMANNUMERAL) FROM WLPPrime WHERE (NUMBERID <= ?) ORDER BY LENGTH(ROMANNUMERAL) DESC) a WHERE ROWNUM <= ?) WHERE rnum > ?
	bind => [41, 5, 0]
java.sql.SQLSyntaxErrorException: ORA-00918: LENGTH(ROMANNUMERAL): column ambiguously specified - appears in  and 

Incorrect result

SELECT *
FROM
  (SELECT a.*, ROWNUM rnum
   FROM
     (SELECT DISTINCT LENGTH(ROMANNUMERAL),
                      LENGTH(ROMANNUMERAL)
      FROM WLPPrime
      WHERE (NUMBERID <= ?)
      ORDER BY LENGTH(ROMANNUMERAL) DESC) a
   WHERE ROWNUM <= ?)
WHERE rnum > ?

	bind => [41, 5, 0]

The resulting SQL has a syntax error, LENGTH(ROMANNUMERAL) appears twice on the same projection

Expected behavior

SELECT *
FROM
  (SELECT a.*, ROWNUM rnum
   FROM
     (SELECT DISTINCT LENGTH(ROMANNUMERAL)
      FROM WLPPrime
      WHERE (NUMBERID <= ?)
      ORDER BY LENGTH(ROMANNUMERAL) DESC) a
   WHERE ROWNUM <= ?)
WHERE rnum > ?

	bind => [41, 5, 0]

[rfelcman]

Duplicated with #2301. Fixed in #2309 .