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 IC63329 Status: Geschlossen

Two or more outer joins containing f(NULL) that returns non-null value may
return incorrect results

Produkt:
DB2 FOR LUW / DB2FORLUW / 910 - DB2
Problembeschreibung:
SQL statement that contains two or more outer joins with the 
predicate of the first ON clause containing expressions that 
could change the null value of the left side of the LEFT outer 
join to a non-null value may return incorrect results. 
 
Example: 
 
WITH A(A1) as (values 1), 
        B(B1) as (values cast(NULL as INTEGER)), 
        C(C1) as (values 1) 
SELECT A1,B1,C1 
FROM   A LEFT JOIN (B LEFT JOIN C ON COALESCE(B1,1)=C1) ON 
A1=B1; 
 
The above statement hits this APAR because 
1. There are two outer joins. 
2. The expression in the first ON clause, COALESCE(B1,1)=C1 
could change the value of NULL of B1 to a non-null value. 
 
The correct result for the above statement is 
 
A1          B1          C1 
----------- ----------- ----------- 
          1           -           - 
 
An example of a SQL statement that does NOT hit this APAR is 
 
WITH A(A1) as (values 1), 
        B(B1) as (values cast(NULL as INTEGER)), 
        C(C1) as (values 1) 
SELECT A1,B1,C1 
FROM   A LEFT JOIN (B LEFT JOIN C ON COALESCE(B1,1)=C1) AND 
B1=C1 ON A1=B1; 
 
Even though, the COALESCE expression exists, the addition 
expression B1=C1, which return NULL if B1 is NULL, will safe 
guard this statement to not return incorrect results.
Problem-Zusammenfassung:
USERS AFFECTED 
 
All 
 
PROBLEM DESCRIPTION 
 
see ERROR DESCRIPTION 
 
PROBLEM SUMMARY 
 
see ERROR DESCRIPTION
Local-Fix:
verfügbare FixPacks:
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

Lösung
The complete fix for this problem first appears in DB2 UDB 
Version 9.1 FixPak 9.
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
22.09.2009
14.04.2010
14.04.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.0.,
9.1.
Problem behoben lt. FixList in der Version
9.1.0.9 FixList