LI73864: UNEXPECTED RESULTS WHEN SELECTING FROM A VIEW WITH OUTER JOIN AFTER UPGRADE TO FP6

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  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.
  
  The local fix is to swap the place of local predicate. Change "2
  = t2_c2" to "t2_c2 = 2".
  

Local fix

• N/A
  

Problem summary

• UNEXPECTED RESULTS WHEN SELECTING FROM A VIEW WITH OUTER JOIN
  AFTER UPGRADE TO FP6
  

Problem conclusion

• APAR is fixed in db2 v9.1 fp7
  

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

• R910 PSY

     UP