IC84320: COALESCE EXPRESSION IN THE OUTER JOIN OPERATOR (+) MAY RETURN INCORRECT RESULTS

DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• DB2 may return incorrect result when DB2_COMPATIBILITY_VECTOR is
  set to support the OUTER JOIN (+) syntax, and all the conditions
  below are met:
  
  1. WHERE clause contains a predicate with COALESCE function.
  2. The COALESCE function contains a column from the table used
  in the (+) operator.
  3. The column in 2) is defined NOT NULL.
  4. Another predicate AND to the predicate with COALESCE function
  in (1).
  
  For example:
  CREATE TABLE T1(C1 INTEGER, C2 INTEGER NOT NULL);
  CREATE TABLE T2(C1 INTEGER);
  
  INSERT INTO T1 VALUES (1,1);
  INSERT INTO T2 VALUES 1,2;
  
  SELECT T1.C1,T2.C1 FROM T1,T2 WHERE T1.C1 (+) = T2.C1 AND
  COALESCE(T1.C2,1) = 1 AND T2.C1=2;
  
  The correct result should return 1 row.
  
  T1.C1        T2.C1
  -----------  -----------
            -            2
  
  This problem is fixed in DB2 Version 9.7 Fix Pack 6.
  

Local fix

• There are two possible workarounds,
  1) Use the ANSI syntax of the OUTER JOIN
  
  The example above can be written as
  SELECT T1.C1,T2.C1 FROM T1 RIGHT OUTER JOIN T2 ON T1.C1=T2.C1
  WHERE COALESCE(T1.C2,1) = 1 AND T2.C1=2;
  
  2) Separate the COALESCE predicate into a subselect after the
  OUTER JOIN (+) operator predicate
  
  The example above can be written as
  SELECT T1C1, T2C1
  FROM (SELECT T1.C1 as T1C1, T1.C2 as T1C2, T2.C1 as T2C1 FROM
  T1,T2 WHERE T1.C1(+) = T2.C1) AS Q
  WHERE COALESCE(T1C2, 1) = 1 AND T2C1=2;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 version 10.1 Fix Pack 1                       *
  ****************************************************************
  

Problem conclusion

• Upgrade to DB2 version 10.1 Fix Pack 1
  

Temporary fix

Comments

APAR Information

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

  IC80899

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

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP

• RA10 PSY

     UP