|
Problem IC77837
|
Status: Geschlossen |
INCORRECT RESULT WHEN OUTER JOIN HAS GENERATED COLUMN'S DEFINITION COLUMN IN THE JOIN AND OTHER LOCAL PREDICATES |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See APAR description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to db2 version 970 fixpack 5 *
**************************************************************** |
| Local-Fix: |
|
| verfügbare FixPacks: |
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
|
| Lösung |
The problem is first fixed in db2 version 970 fixpack 5 |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 29.07.2011
17.01.2012
17.01.2012 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.0
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.5
|
 |
