DB2 FOR LUW / DB2FORLUW / 980 - DB2

DB2 may return an incorrect result set if the query has all of 
the following patterns: 
 
1. A group of multiple LEFT OUTER JOIN and INNER JOIN. 
2. There is an INNER JOIN as a right side table of another LEFT 
OUTER JOIN. 
3. There is a COALESCE expression in the ON predicate and this 
ON predicate is not the last join of the group. 
4. The first non-null column of the COALESCE expression is from 
the tables of the INNER JOIN in (2). 
 
Example: 
 
CREATE TABLE A1 ( 
          A1_ID BIGINT NOT NULL) 
         ; 
CREATE TABLE A2 ( 
          A2_ID BIGINT NOT NULL) 
         ; 
CREATE TABLE A3 ( 
          A3_ID BIGINT NOT NULL) 
         ; 
 
CREATE TABLE B1  ( 
                  B1_SRC BIGINT NOT NULL , 
                  B1_DEST BIGINT NOT NULL ) 
                 ; 
CREATE TABLE B2   ( 
                  B2_SRC BIGINT NOT NULL , 
                  B2_DEST BIGINT NOT NULL ) 
                 ; 
CREATE TABLE B3   ( 
                  B3_SRC BIGINT NOT NULL , 
                  B3_DEST BIGINT NOT NULL ) 
                 ; 
 
INSERT INTO A1(A1_ID) VALUES 1,2; 
INSERT INTO A2(A2_ID) VALUES 1; 
INSERT INTO A3(A3_ID) VALUES 1; 
INSERT INTO B1(B1_SRC, B1_DEST) VALUES (1,1),(2,2); 
INSERT INTO B2(B2_SRC, B2_DEST) VALUES (1,1),(2,2); 
INSERT INTO B3(B3_SRC, B3_DEST) VALUES (1,1),(2,2); 
 
-- Pattern 1: A group of LEFT JOINs and INNER JOINs 
SELECT B1.B1_SRC 
FROM 
    B1 
INNER JOIN 
    A1 
ON B1.B1_DEST=A1.A1_ID 
LEFT JOIN 
    (B2 
     INNER JOIN                   -- Pattern 2: An INNER JOIN on 
the right side table of a LEFT JOIN 
     A2 
     ON B2.B2_DEST=A2.A2_ID) 
ON A1.A1_ID=B2.B2_SRC 
INNER JOIN 
    B3 
ON COALESCE(A2.A2_ID, A1.A1_ID)=B3.B3_SRC -- Pattern 3: COALESCE 
expression but not the last ON predicate 
 
        -- Pattern 4: The first non-null column A2_ID is from 
the table in (2) 
INNER JOIN 
    A3 
ON 1=1 
; 
 
The correct result set is: 
 
B1_SRC 
-------------------- 
                   1 
                   2 
 
  2 record(s) selected. 
 
To observe that a query has hit this problem, inspect the 
Optimized Statement section of the db2exfmt output of the query. 
If there is no COALESCE in the statement text, it is possible 
that the query has hit this problem.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 9.8 Fix Pack 5                                * 
****************************************************************

You can obtain the correct result by manually reordering the 
LEFT JOIN/INNER JOIN in the query. 
To ensure that the rewrite is 
correct, observe that the COALESCE is in the statement text of 
the Optimized Statement section of the db2exfmt output.

First fixed in DB2 9.8 Fix Pack 5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

28.05.2012
08.03.2017
08.03.2017

9.8.FP5