PK77658; 6.1.0.21: jpa issues with named query (@namedquery)

Download Description

*Note: This Interim Fix supersedes PK69000.

PK77658 resolves the following problem:

ERROR DESCRIPTION:
Incorrect SQL generated for Java Persistence Language Queries which operate on multiple tables on Oracle.

LOCAL FIX:
None.

PROBLEM SUMMARY:

USERS AFFECTED:
All users of the Java™ Persistence API (JPA) with Oracle databases on WebSphere Application Server

PROBLEM DESCRIPTION:
Incorrect SQL is generated for Java Persistence Query Language (JPQL) queries which operate on multiple tables..

RECOMMENDATION:
None

When a JPQL query is executed on multiple entities and contains a sub query the generated SQL might not include the correct table aliases.

For example consider this JPQL query :
SELECT e,d from Employee e, Dependent d
WHERE e.empId = :empid
AND d.id.empid = (SELECT MAX (e2.empId) FROM Employee e2)
AND d.id.effDate > :minDate
AND d.id.effDate < :maxDate

The generated SQL might look like this :
ELECT t0.empId, t3.effDate, t3.empid, t3.name
FROM SUBQ_EMPLOYEE t0, SUBQ_DEPENDENT t3
WHERE (t0.empId = ?
AND t1.empid = (SELECT MAX(t2.empId) FROM SUBQ_DEPENDENT
t1, SUBQ_EMPLOYEE t2)
AND t3.effDate > ?
AND t3.effDate < ?

The SQL is missing the definition for table alias t1 and t2. When this occurs a SQLException is thrown by the JDBC provider. For example:

openjpa-1.0.4-SNAPSHOT-r420667:740105 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException:
ORA-00904: "T1"."EMPID": invalid identifier
{prepstmnt 23206283 SELECT t0.empId, t3.effDate, t3.empid,
t3.name FROM SUBQ_EMPLOYEE t0, SUBQ_DEPENDENT t3 WHERE
(t0.empId = ? AND t1.empid = (SELECT MAX(t2.empId) FROM
SUBQ_DEPENDENT t1, SUBQ_EMPLOYEE t2) AND t3.effDate > ? AND
t3.effDate < ?) [params=(long) 101, (Timestamp) 1969-12-31
18:00:00.1, (Timestamp) 1969-12-31 18:01:40.0]} [code=904,
state=42000]
at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3951)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:97
<snip>

NestedThrowables:
java.sql.SQLException: ORA-00904: "T1"."EMPID": invalid identifier

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

PROBLEM CONCLUSION:
The JPA Provider product code was updated to include the correct table alias definitions.

The fix for this APAR is currently targeted for inclusion in fixpack 6.1.0.25. Please refer to the Recommended Updates page for delivery information:
http://www.ibm.com/support/docview.wss?rs=180&uid=swg27004980