Fixes are available
8.5.0.2: WebSphere Application Server V8.5 Fix Pack 2
8.0.0.6: WebSphere Application Server V8.0 Fix Pack 6
8.0.0.7: WebSphere Application Server V8.0 Fix Pack 7
8.0.0.8: WebSphere Application Server V8.0 Fix Pack 8
8.0.0.9: WebSphere Application Server V8.0 Fix Pack 9
8.0.0.10: WebSphere Application Server V8.0 Fix Pack 10
8.0.0.11: WebSphere Application Server V8.0 Fix Pack 11
8.0.0.12: WebSphere Application Server V8.0 Fix Pack 12
8.0.0.13: WebSphere Application Server V8.0 Fix Pack 13
8.0.0.14: WebSphere Application Server V8.0 Fix Pack 14
8.0.0.15: WebSphere Application Server V8.0 Fix Pack 15
APAR status
Closed as program error.
Error description
We have opened this APAR for the following OpenJPA JIRA for the issue: https://issues.apache.org/jira/browse/OPENJPA-2289 For a given query, which includes sub queries, OpenJPA generates SQL which yields an incorrect number of database rows being returned. This issue occurs only when using an Oracle Database.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All users of IBM WebSphere Application * * Server V8.0.0 and V8.5.0 who make * * use of multiple sub queries in * * an OpenJPA query. * **************************************************************** * PROBLEM DESCRIPTION: An additional SQL alias generated for * * a query with a subquery causes an * * incorrect number of rows to be * * returned on Oracle. * **************************************************************** * RECOMMENDATION: * **************************************************************** Take the following JPA code snippet which creates a query: createQuery("SELECT e FROM MaxQueryEntity e, MaxQueryMapEntity " + "map WHERE map.selectCriteria = 'B3' AND map.refEntity = e " + "AND e.revision = ( SELECT MAX(e_.revision) " + "FROM MaxQueryEntity e_ WHERE e_.domainId = e.domainId ) " + "AND map.revision = ( SELECT MAX(map_.revision) " + "FROM MaxQueryMapEntity map_ WHERE map_.refEntity = " + "map.refEntity)"); On Oracle OpenJPA generates SQL like this: SELECT t1.id, t1.domainId, t1.revision FROM OPENJPA_MAXQUERY_MAPENTITY t0, OPENJPA_MAXQUERY_ENTITY t1, OPENJPA_MAXQUERY_MAPENTITY t4 WHERE (t0.selectCriteria = ? AND t0.refEntity = t1.id AND t1.revision = (SELECT MAX(t2.revision) FROM OPENJPA_MAXQUERY_ENTITY t2 WHERE (t2.domainId = t1.domainId)) AND t0.revision = (SELECT MAX(t3.revision) FROM OPENJPA_MAXQUERY_MAPENTITY t3 WHERE (t3.refEntity = t4.refEntity))) [params=(String) B3] Notice that there is an additional alias "OPENJPA_MAXQUERY_MAPENTITY t4" which caused an unexpected number of rows to be returned.
Problem conclusion
With this fix, code has been added to OpenJPA to ensure the extraneous SQL alias does not occur when sub queries are used. The fix for this APAR is currently targeted for inclusion in Service Levels (Fix Packs) 8.0.0.6 and 8.5.0.2 of WebSphere Application Server versions 8.0.0 and 8.5.0. Please refer to the recommended updates page for delivery information: http://www.ibm.com/support/docview.wss?rs=180&uid=swg27004980
Temporary fix
Comments
APAR Information
APAR number
PM76292
Reported component name
WEBS APP SERV N
Reported component ID
5724H8800
Reported release
700
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2012-11-01
Closed date
2012-12-31
Last modified date
2012-12-31
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
PM77940 PM77942 PM80451
Fix information
Fixed component name
WEBS APP SERV N
Fixed component ID
5724H8800
Applicable component levels
R800 PSY
UP
R850 PSY
UP
Document Information
Modified date:
29 October 2021