home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
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
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

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
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

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 FixList