PH29720: ECLIPSELINK JPQL COALESCE FUNCTION USES IMPROPER WHITESPACE

APAR status

• Closed as program error.

Error description

• Description: EclipseLink generates SQL for the coalesce function
  with incorrect whitespace. This can cause certain SQL parsers
  to throw a DatabaseException when attempting to parse the SQL.
  
  Incorrect SQL: SELECT foo FROM Bar WHERE (foo = COALESCE(foo,?,?
  ))
  
  Correct SQL: SQL: SELECT foo FROM Bar WHERE (foo = COALESCE(foo,
  ?, ?))
  
  Possible SQL exception:
  
  Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException:
  [jcc][50053][12311][4.26.16] T2zOS exception:
  [jcc][T2zos]T2zosPreparedStatement.readPrepareDescribeOutput_:na
  tivePrepareInto:1583: DB2 engine SQL error, SQLCODE = -104,
  SQLSTATE = 42601, error tokens = ,0;AT YEAR YEARS MONTH MONTHS
  DAY DAYS HOUR HOURS MINUTE MINUTES ERRORCODE=-104,
  SQLSTATE=42601
  Error Code: -104
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:  All users of IBM WebSphere Application      *
  *                  Server - DB2 zOS - Java Persistence API -   *
  *                  JPA 2.1 & EclipseLink                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: EclipseLink creates SQL for COALESCE    *
  *                      that doesnt properly format the         *
  *                      whitespace. DB2 zOS SQL parsers         *
  *                      can throw an exception.                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  EclipseLink generates SQL for the coalesce function
  with incorrect whitespace. This can cause certain SQL parsers,
  like DB2 zOS, to throw a DatabaseException when attempting to
  parse the SQL.
  Incorrect SQL: COALESCE(foo,?,? ))
  Correct SQL: COALESCE(foo, ?, ?))
  Exception:
  ´´´
  Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException:
  [jcc][50053][12311][4.26.16] T2zOS exception:
  [jcc][T2zos]T2zosPreparedStatement.readPrepareDescribeOutput_:na
  tivePrepareInto:1583: DB2 engine SQL error, SQLCODE = -104,
  SQLSTATE = 42601, error tokens = ,0;AT YEAR YEARS MONTH MONTHS
  DAY DAYS HOUR HOURS MINUTE MINUTES ERRORCODE=-104,
  SQLSTATE=42601
  Error Code: -104
  ´´´
  

Problem conclusion

• The fix for this APAR changes the EclipseLink implementation
  and involves an update to the thirdparty source. See
  https://bugs.eclipse.org/bugs/show_bug.cgi?id=567087
  
  This fix is currently targeted for WebSphere Application
  Server 9.0.5.7 and WebSphere Liberty 20.0.0.12. For more
  information, see 'Recommended Updates for
  WebSphere Application Server':
  https://www.ibm.com/support/pages/node/715553
  

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:

Fix information

• Fixed component name

  WEBSPHERE FOR Z

• Fixed component ID

  5655I3500

Applicable component levels