IT17458: IN DB2 DPF, POSSIBLE WRONG RESULT WHEN OUTER JOIN PREDICATE COL1=COL2 AND BOTH COLUMNS ARE FROM THE OUTER TABLE

DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• DB2 can return wrong query result when all of the following
  conditions are satisfied
  
  1. DPF (Data Partitioning Feature) environment.
  2. The query has a outer join, and the outer join has a
  predicate of the form COLUMN1=COLUMN2, where both columns are
  from the outer table.
  3. Either DB2COMPOPT=OJLOCALPRDPLANNING is set, or the
  NULL-producing side of the outer join is a CSE (Common
  SubExpression), UNION, GROUP-BY, or another outer join.
  4. The query has a key requirement or order requirement on one
  of the columns in the COLUMN1=COLUMN2 predicate.
  
  For example:
  
  The following query can possibly produce a wrong result in DPF
  environment, because the query has an outer join predicate of
  TT.T1_C1 = TT.T1_C2, the NULL-producing side of the outer join
  is another outer join, and the query requires "DISTINCT
  TT.T1_C2"
  
  SELECT DISTINCT TT.T1_C2 FROM T1 TT LEFT OUTER JOIN
  (SELECT T2.T2_C4 FROM T2 FULL OUTER JOIN T1 ON T1.T1_C1 =
  T2.T2_C2) TT2 on TT.T1_C1 = TT.T1_C2
  
  The following query can possibly produce a wrong result in DPF
  environment and when DB2COMPOPT=OJLOCALPRDPLANNING is set,
  because the query has an outer join predicate of
  T1.T1_C1=T1.T1_C2, and the query requires "ORDER BY TT.T1_C2"
  
  SELECT * FROM T2 TT1, (SELECT * FROM T1 LEFT JOIN T2 on
  T1.T1_C1=T1.T1_C2 ) TT
  WHERE TT1.T2_C4 = TT.T1_C1
  ORDER BY TT.T1_C2
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * DB2 DPF                                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 Version 11.1.1.1                              *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 Version 11.1.1.1
  

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:

  IT17505 IT17506 IT17507 IT17512

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels