IBM Support

PM12153: SUB-SUB-QUERY GENERATES SQL WITH SYNTAX ERROR

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • For a query as follows:
    
    SELECT p, m
    FROM Publisher p
    LEFT OUTER JOIN p.magazineCollection m
    WHERE m.id = (SELECT MAX(m2.id)
    FROM Magazine m2
    WHERE m2.idPublisher.id = p.id
    AND m2.datePublished =
    (SELECT MAX(m3.datePublished)
    FROM Magazine m3
    WHERE m3.idPublisher.id = p.id))
    
    OpenJPA translates this into native (MySQL) query:
    
    SELECT t0.id, t1.id, t1.date_published, t1.id_publisher, t1.name
    FROM Publisher t0
    LEFT OUTER JOIN Magazine t1
    ON t0.id = t1.id_publisher
    WHERE (t1.id = (SELECT MAX(t4.id)
    FROM Magazine t4
    WHERE (t2.id_publisher = t0.id
    AND t2.date_published =
    (SELECT MAX(t3.date_published)
    FROM Magazine t2, Magazine t3
    WHERE (t3.id_publisher = t0.id)))))
    
    and that query throws an exception:
    
    NestedThrowables:
    com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown
    column 't2.id_publisher' in 'where clause'
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:  All users of IBM ‚ ® WebSphere ‚ ® Applicatio
    *                  Server V6.1.0 Feature Pack for Enterprise   *
    *                  JavaBeans 3.0 who generate sub-sub-queries  *
    *                  against their JPA Entities.                 *
    ****************************************************************
    * PROBLEM DESCRIPTION: Incorrect native SQL generated for a    *
    *                      given JPA query which contains          *
    *                      multiple sub-queries.                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    When a JPA query is generated which itself contains
    multiple sub-queries, JPA generates an incorrect native SQL
    string for the given JPA query.
    

Problem conclusion

  • With this fix, code has been added to ensure multiple
    sub-queries result in the correct native SQL.
    
    The fix for this APAR is currently targeted for inclusion
    in Enterprise JavaBeans 3.0 Feature Pack Fix Pack 33
    (6.1.0.33) of WebSphere Application Server version 6.1.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

    PM12153

  • Reported component name

    WEBSPHERE APP S

  • Reported component ID

    5724J0800

  • Reported release

    610

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-04-14

  • Closed date

    2010-05-25

  • Last modified date

    2010-05-25

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    WAS EJB3 FEATUR

  • Fixed component ID

    5724J0851

Applicable component levels

  • R610 PSY

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEQTP","label":"WebSphere Application Server"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"6.1","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
24 October 2021