PM80421: JPA SQL QUERY CACHE GENERATES INCORRECT SQL

8.5.0.2: WebSphere Application Server V8.5 Fix Pack 2

APAR status

• Closed as program error.

Error description

• Running WebSphere Application Server V8.0, using JPA sql query
  cache. If a JPA prepared cached query is called once with a
  non-null parameter, then the next time with a null parameter,
  then the subsequently generated SQL query is generated
  incorrectly. The incorrect syntax is
  
  SELECT A FROM B WHERE C=NULL
  
  This doesn't work in Oracle. The expected result is
  
  SELECT A FROM B WHERE C IS NULL
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:  All users of IBM WebSphere Application      *
  *                  Server                                      *
  *                  Liberty Profile                             *
  ****************************************************************
  * PROBLEM DESCRIPTION: The Query SQL Cache in OpenJPA is       *
  *                      unable                                  *
  *                      to properly cope with NULL parameters.  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The Query SQL Cache matches previously run SQL for a given
  JPQL by using the JPQL string itself for the key. As long as
  none of the predicate parameters are NULL, this is fine, as the
  prepared statement's SQL remains unchanged. Since SQL requires
  "column IS NULL" statements and not "column = NULL", the
  approach used by OpenJPA's Query SQL Cache breaks and results
  with bad getResultList() returns.
  In the future, the Query SQL Cache needs more intelligence to
  handle this situation, but for service releases the best way
  to handle this issue is to disqualify queries with NULL
  parameters from the query cache.
  

Problem conclusion

• The Query SQL Cache will no longer cache queries that contain
  one or more NULL parameters.  Queries with no NULL parameter
  will continue to be cached as normal.
  
  The fix for this APAR is currently targeted for inclusion in fix
  pack 8.5.0.2. 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 is sysrouted FROM one or more of the following:

  PM67897

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

Fix information

• Fixed component name

  LIBERTY PROFILE

• Fixed component ID

  5724J0814

Applicable component levels

• R850 PSY

     UP