PH36841: ECLIPSELINK CRITERIABUILDER TRIM FUNCTION CREATES INCORRECT SQL

APAR status

• Closed as program error.

Error description

• EclipseLink generates incorrect SQL from CriteriaBuilder for
  the &#180;CriteriaBuilder.trim(char t, Expression<String>
  x)&#180; function. It is important to note that this may not be
  failing as some databases will not fail attempting to execute
  this SQL, but the query is very wrong and cannot return the
  expected result.
  
  Example:
  &#180;&#180;&#180;
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Object[]> cquery =
  cb.createQuery(Object[].class);
      Root<SimpleEntity> root = cquery.from(SimpleEntity.class);
      cquery.multiselect(cb.trim(cb.literal('A'),
  cb.literal("AAHELLO WORDAAAAA")));
      cquery.where(cb.equal(root.get(SimpleEntity_.strVal1),
  cb.literal("HELLO")));
  
      Query query = em.createQuery(cquery).getResultList();
  &#180;&#180;&#180;
  
  Example SQL:
  &#180;&#180;&#180;
      SELECT TRIM('AAHELLO WORDAAAAA' FROM 'AAHELLO WORDAAAAA')
  FROM SIMPLEENTITY WHERE (STRVAL1 = 'HELLO')
  &#180;&#180;&#180;
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:  All users of IBM WebSphere Application      *
  *                  Server - Java Persistence API - JPA 2.1 &   *
  *                  EclipseLink                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: EclipseLink generates incorrect SQL     *
  *                      from CriteriaBuilder                    *
  *                      for ´CriteriaBuilder.trim()´ functions  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Example:
  ´´´
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Object[]> cquery =
  cb.createQuery(Object[].class);
  Root<SimpleEntity> root = cquery.from(SimpleEntity.class);
  cquery.multiselect(cb.trim(cb.literal('A'),
  cb.literal("AAHELLO WORDAAAAA")));
  cquery.where(cb.equal(root.get(SimpleEntity_.strVal1),
  cb.literal("HELLO")));
  Query query = em.createQuery(cquery).getResultList();
  &#180;&#180;&#180;
  Example SQL:
  &#180;&#180;&#180;
  SELECT TRIM('AAHELLO WORDAAAAA' FROM 'AAHELLO WORDAAAAA')
  FROM SIMPLEENTITY WHERE (STRVAL1 = 'HELLO')
  &#180;&#180;&#180;
  

Problem conclusion

• The fix for this APAR changes the EclipseLink implementation
  and involves an update to the third-party source. See
  https://bugs.eclipse.org/bugs/show_bug.cgi?id=573094
  
  This fix is currently targeted for WebSphere Application
  Server 9.0.5.9. 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 APP S

• Fixed component ID

  5724J0800

Applicable component levels

• R900 PSY

     UP