PM67897: JPA SQL QUERY CACHE GENERATES INCORRECT SQL

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

• In 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 V8.0 and V8.5                        *
  ****************************************************************
  * 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 packs 8.0.0.6 and 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:

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

  PM80421

Fix information

• Fixed component name

  WEBSPHERE APP S

• Fixed component ID

  5724J0800

Applicable component levels

• R800 PSY

     UP

• R850 PSY

     UP