DB2 - Problembeschreibung
Problem IC80899 | Status: Geschlossen |
COALESCE EXPRESSION IN THE OUTER JOIN OPERATOR (+) MAY RETURN INCORRECT RESULTS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
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. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 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; | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows | |
Lösung | |
Upgrade to DB2 version 9.7 Fix Pack 6. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC84320 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 19.01.2012 30.10.2012 25.09.2013 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP6 | |
Problem behoben lt. FixList in der Version | |
9.7.0.6 |