PK68982: EJB2 QUERY USING "LEFT JOIN" SYNTAX MAY RESULT IN INCORRECT SQL GENERATION WITH SYNTAX ERROR.

Rational Application Developer for WebSphere Software, Version 7.0.0.8
6.1.1.8: WebSphere Application Server Toolkit Version 6.1.1 Fix Pack 8
6.1.0.29: Java SDK 1.5 SR11 Cumulative Fix for WebSphere Application Server
6.1.0.31: Java SDK 1.5 SR11 FP1 Cumulative Fix for WebSphere Application Server
6.1.0.33: Java SDK 1.5 SR12 FP1 Cumulative Fix for WebSphere
6.1.0.35: Java SDK 1.5 SR12 FP2 Cumulative Fix for WebSphere
6.1.0.37: Java SDK 1.5 SR12 FP3 Cumulative Fix for WebSphere
6.1.0.47: WebSphere Application Server V6.1 Fix Pack 47
6.1.0.39: Java SDK 1.5 SR12 FP4 Cumulative Fix for WebSphere Application Server
6.1.0.41: Java SDK 1.5 SR12 FP5 Cumulative Fix for WebSphere Application Server
6.1.0.43: Java SDK 1.5 SR13 Cumulative Fix for WebSphere Application Server
6.1.0.45: Java SDK 1.5 SR14 Cumulative Fix for WebSphere Application Server
6.1.0.47: Java SDK 1.5 SR16 Cumulative Fix for WebSphere Application Server

APAR status

• Closed as program error.

Error description

• Deploy code generated from EJB-QL has syntax error.
  
  Affects all EJB2 applications in 6.1.0.x using "LEFT JOIN" query
  syntax. EJB2 query using "LEFT JOIN" syntax may result in
  incorrect SQL generation with syntax error.
  
  EJB-QL: select o from BusinessGeo o left join o.subCountries k
  where o.
  regionType.regionType = ?1 and o.isoCountryCode = ?2 and k.
  subCountryCode = ?3
  SQL generated has incorrect syntax:
  " select  q1.\"ID\",  q1.\"NAME\",  q1.\"ISOCOUNTRYCODE\",
  q1.\"
  REGIONTYPE_REGIONTYPE\" from BUSINESSGEO q1 left outer join
  SUBCOUNTRY
  q3 on  ( q3.\"BUSINESSGEO_ID\" =  q1.\"ID\")  inner join
  REGIONTYPE q2
  on  where  ( q2.\"REGIONTYPE\" = ?)  and  (
  q1.\"ISOCOUNTRYCODE\" = ?)
  and  ( q3.\"SUBCOUNTRYCODE\" = ?)  and  ( q2.\"REGIONTYPE\" =
  q1.\"
  REGIONTYPE_REGIONTYPE\") ";
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:  All users of WebSphere Application Server   *
  *                  Toolkit v6.1.1 and Rational Application     *
  *                  Developer v7.0                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: An enterprise bean containing an        *
  *                      EJB-QL statement contains a 'left       *
  *                      join' statement will generate           *
  *                      incorrect SQL.                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  If an v2.x enterprise bean is constructed with a EJB-QL
  statement containing a 'left join' clause then the underlying
  SQL statement will be incorrect and cause runtime application
  errors.
  

Problem conclusion

• The generated SQL for an EJB-QL statement containing a 'left
  join' clause will now produce valid SQL.
  
  The fix for this APAR is currently targeted for inclusion in
  the WebSphere Application Server Toolkit fixpack 6.1.1.8. and
  Rational Application Developer fixpack 7.0.0.8.  Please refer
  to the following Recommended Updates page for WebSphere
  Application Server Toolkit delivery information:
  http://www.ibm.com/support/docview.wss?uid=swg27004980
  and the following Recommended Updates page for Rational
  Application Developer delivery information:
  http://www.ibm.com/support/docview.wss?uid=swg27007823
  

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

  WEBAPP SERVER A

• Fixed component ID

  5724J0820

Applicable component levels

• R610 PSY

     UP