|
Problem IC77837
|
Status: Closed |
INCORRECT RESULT WHEN OUTER JOIN HAS GENERATED COLUMN'S DEFINITION COLUMN IN THE JOIN AND OTHER LOCAL PREDICATES |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
DB2 might produce wrong results under the following scenario:
1. The query is an outer join.
2. The NULL producing side table has a generated column
3. The generate column's definition column is used in the join
4. The row preserving side table has a join and a local
predicate on the same column.
5. The local predicate is such that it causes the generated
column to have same value as the column it is derived from.
Here's an example.
CREATE TABLE RP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ');
CREATE TABLE NP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ' ,
GC CHAR(13) GENERATED
ALWAYS AS (SUBSTR(C1, 1, 10)));
INSERT INTO RP VALUES '123', '123456', '2';
INSERT INTO NP (C1) VALUES '123', '123456', '2';
SELECT * FROM RP LEFT OUTER JOIN NP ON RP.C1 = NP.C1 AND RP.C1 =
'123';
C1 C1 GC
-------------------- -------------------- -------------
123 123 123
123456 123456 123456
2 2 2
The correct result should be
C1 C1
-------------------- --------------------
123 123
123456 -
2 - |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See APAR description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to db2 version 970 fixpack 5 *
**************************************************************** |
| Local Fix: |
|
| 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 |
The problem is first fixed in db2 version 970 fixpack 5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 29.07.2011
17.01.2012
17.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.0
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
