IBM Support

PM76292: ADDITIONAL SQL ALIAS GENERATED FOR QUERY WITH SUBQUERY CAUSES INCORRECT # OF ROWS RETURNED - ORACLE ONLY

Subscribe

You can track all active APARs for this component.

 

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

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEQTP","label":"WebSphere Application Server"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
29 October 2021