|
Problem IC63210
|
Status: Closed |
Two or more outer joins containing f(NULL) that returns non-null value may return incorrect results |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
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 Summary: |
USERS AFFECTED: ALL
PROBLEM DESCRIPTION:
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 SUMMARY:
see PROBLEM DESCRIPTION |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
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 |
The complete fix for this problem first appears in DB2 UDB
Version 9.5 FixPak 5. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC63329 IC63330
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.09.2009
15.12.2009
15.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.5
|
 |
