LI73865: UNEXPECTED RESULTS WHEN SELECTING FROM A VIEW WITH OUTER JOIN AFTER UPGRADE

DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• This problem happens only to outer join query. The outer join
  operator has local equality predicate on the null producing
  side. In this local predicate, the literal is at the left hand
  side and null producing column is at the right hand side. On top
  of this outer join result, the query has multiple local
  predicates. One sample query is:
  
  SELECT * from (select * from t1 left outer join t2 on t1_c1 =
  t2_c1 and 2 = t2_c2) t where t1_c2 = 100 and t1_c3 = 200;
  
  The problem can be identified by checking if the access plan has
  predicate "2 = t2_c2". If that predicate is gone, the query
  result may be incorrect.
  

Local fix

• The local fix is to swap the place of local predicate.
  
  
  Change "2 = t2_c2" to "t2_c2 = 2".
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
       All DB2 v9.5 users performing an outer join query.
  ****************************************************************
  PROBLEM DESCRIPTION:
       Unexpected results when performing outer join.
       Workaround is to swap the local predicate in the query.
  ****************************************************************
  RECOMMENDATION:
       Upgrade to DB2 Version 9.5 fix pack 4 or higher.
  ****************************************************************
  

Problem conclusion

• First Fixed in DB2 Version 9.5 Fix Pack 4
  

Temporary fix

Comments

APAR Information

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

  LI73863

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

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R950 PSY

     UP