Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC76722 Status: Closed

COALESCE expression in the OUTER JOIN operator (+) may return incorrect
results

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem 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. 
 
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; 
 
The correct result should return 2 rows 
 
T1.C1        T2.C1 
-----------  ----------- 
          1            1 
          -            2 
 
This problem is fixed in DB2 Version 9.7 Fix Pack 5.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2V97_FP4 with DB2_COMPATIBILITY_VECTOR=ORA                 * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Problem Description Above                                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to DB2 Version 9.7 Fix Pack 5                        * 
****************************************************************
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; 
 
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;
available fix packs:
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Solution
First Fix in Version 9.7 Fix Pack 5
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
01.06.2011
23.12.2011
23.12.2011
Problem solved at the following versions (IBM BugInfos)
9.7.FP5
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.5 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.