IBM Support

PI28257: JPQL QUERY USING OPENJPA.HINT.USELITERALINSQL=TRUE DOES NOT HANDLE BOOLEAN CONSTANTS (<PARAMETER> =TRUE) CORRECTLY

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Due to a complex entity design some JPQL queries gets complex
    and its SQL runs xx to xxx seconds.  In order to improve the
    performance IBM DB2support suggested passing some parameter by
    literal and no longer by parameter. And client changed DB2
    runstats to detailed distribution statistics.  Now the pure SQL
    shows the huge performance benefits but client do not get
    openjpa to execute that JPQL with Boolean constants (TRUE and
    FALSE).
    
    OPENJPA Version:  openjpa-2.1.2-SNAPSHOT-r422266:1569528
    
    Following following SQL to be should be generated:
    SELECT t0.pk, t0.code, t0.code2, t0.flag FROM MyEntity t0 WHERE
    (t0.flag = '1')
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:  All users of IBM WebSphere Application      *
    *                  Server V8.0.0, V8.5.0, and V8.5.5 who make  *
    *                  use of the OpenJPA hint 'UseLiteralInSQL'.  *
    ****************************************************************
    * PROBLEM DESCRIPTION: A boolean is not converted correctly    *
    *                      when using the OpenJPA hint             *
    *                      'UseLiteralInSQL'.                      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The hint, 'openjpa.hint.UseLiteralInSQL', is not properly
    converting a boolean to an int. To illustrate this, take
    this entity:
    public class MyEntity implements Serializable {
    @Id
    private Integer pk;
    private boolean flag;
    ..........
    With this entity, take this test:
    Query q = em.createQuery("SELECT f FROM MyEntity f WHERE
    f.flag = true");
    //false is the default:
    q.setHint("openjpa.hint.UseLiteralInSQL", "false");
    q.getResultList();
    With this code, the following SQL will be generated:
    SELECT t0.pk, t0.code, t0.code2, t0.flag FROM MyEntity t0
    WHERE (t0.flag = ?) [params=(int) 1]
    Next, take this code:
    Query q = em.createQuery("SELECT f FROM MyEntity f WHERE
    f.flag = true");
    q.setHint("openjpa.hint.UseLiteralInSQL", "true");
    q.getResultList();
    With this code, the following SQL will be generated:
    SELECT t0.pk, t0.code, t0.code2, t0.flag FROM MyEntity t0
    WHERE (t0.flag = true)
    However, in the latter case where UseLiteralInSQL=true, the
    following SQL should be generated:
    SELECT t0.pk, t0.code, t0.code2, t0.flag FROM MyEntity t0
    WHERE (t0.flag = '1')
    

Problem conclusion

  • With this fix, code has been added to OpenJPA to properly
    handle booleans when using the 'UseLiteralInSQL' property.
    
    The fix for this APAR is currently targeted for
    inclusion in Service Levels (Fix Packs) 8.0.0.10,
    8.5.5.5 of WebSphere Application Server versions 8.0.0 and
    8.5.5.
    
    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

    PI28257

  • Reported component name

    WEBS APP SERV N

  • Reported component ID

    5724H8800

  • Reported release

    800

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2014-10-23

  • Closed date

    2015-01-30

  • Last modified date

    2015-01-30

  • 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

    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":"8.0","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
28 April 2022