PH12133: ECLIPSELINK RETURNS THE WRONG RESULT FOR LEFT JOINS WITH EMPTY RESULTS FROM THE RIGHT

9.0.5.3: WebSphere Application Server traditional Version 9.0.5 Fix Pack 3

APAR status

• Closed as program error.

Error description

• Given a JPQL query that contains a LEFT JOIN expression,
  EclipseLink's JPQL processing generates a query that does
  not
  account for possible empty (NULL) results from the right
  side of
  the join.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:  All users of IBM WebSphere Application      *
  *                  Server - Java Persistence API - JPA 2.1 &   *
  *                  EclipseLink                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: EclipseLink generates an incorrect      *
  *                      LEFT                                    *
  *                      JOIN statement that returns incorrect   *
  *                      results when the right side of the      *
  *                      join                                    *
  *                      is emtpy                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When creating a JPQL query that contains a LEFT JOIN,
  EclipseLink will generate SQL that incorrectly accounts for the
  right side of the join
  For example, consider the following query:
  Query q = em.createQuery("SELECT sub1, sub2 from SubClass
  sub1 LEFT OUTER JOIN SubClass sub2 ON sub2.name = 'unknown'");
  *Note that there are no results in the table for name='unknown'
  EclipseLink will generate a query similar to this:
  "SELECT t0.ID, t0.DTYPE, t0.NAME, t1.ID, t1.DTYPE, t1.NAME
  FROM SUPERCLASS t0 LEFT OUTER JOIN SUPERCLASS t1 ON (t1.NAME =
  'unknown') WHERE (((t0.DTYPE = 'Sub') AND (t1.DTYPE = 'Sub'))
  AND (t1.DTYPE = 'Sub'))"
  With this query, EclipseLink contains too many DTYPE checks and
  fails to account for DTYPE==NULL that will occur when the right
  side of the LEFT JOIN is empty.
  

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=493804
  
  This fix is currently targeted for WebSphere Application Server
  9.0.5.3 and WebSphere Liberty 19.0.0.6.  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:

Fix information

• Fixed component name

  WAS LIBERTY COR

• Fixed component ID

  5725L2900

Applicable component levels

• R855 PSY

     UP