|
Problem IC77489
|
Status: Closed |
POSSIBLE INCORRECT RESULTS FROM A GROUP OF LEFT JOIN, INNER JOIN, AND COALESCE EXPRESSION IN AN ON PREDICATE |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* 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. *
****************************************************************
* RECOMMENDATION: *
* Upgraded to DB2 version 9.5 Fix Pack 9. *
**************************************************************** |
| Local Fix: |
You can obtain the correct result set 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. |
| available fix packs: |
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Upgraded to DB2 version 9.5 Fix Pack 9. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 12.07.2011
05.03.2012
06.03.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP9
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.9
|
 |
