|
Problem IC62692
|
Status: Closed |
DB2 MAY ISSUE AN SQL0901N COMPILING A QUERY CONTAINING AN OUTER JOIN ON A TABLE WITH GENERATED COLUMNS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
During compilation of a query containing a LEFT or RIGHT OUTER
JOIN, DB2 may issue an SQL0901N if the following conditions are
satisfied:
1) The table from the NULL-producing (NP) side has a generated
column (GC)
2) The parent column (PC) of the GC is used in the ON clause as
part of the JOIN predicate
3) The column from the row-preserving (RP) side has a local
predicate
4) The GC from the NP table has derivable equivalence to the RP
column via its PC and the local predicate literal
An example scenario that may generate (depending on the
Optimizer plan picked) such an SQL0901N follows:
-- Table DDL
CREATE TABLE RP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ');
CREATE TABLE NP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ' ,
DUMMY CHAR(9) GENERATED ALWAYS AS (SUBSTR(C1,
1, 9)),
GC CHAR(13) GENERATED ALWAYS AS (SUBSTR(C1, 1,
13)));
-- Query that may generate the -901
SELECT B.C1
FROM RP AS A
LEFT OUTER JOIN NP AS B
ON A.C1 = B.C1
AND A.C1 = '1234567890'
;
-- -901 text
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "Bad Plan;
Unresolved QNC
found".) SQLSTATE=58004
This problem will be first fixed in DB2 Version 9.1 Fix Pack 8
and DB2 Version 9.5 Fix Pack 5. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Use a query containing a LEFT or RIGHT OUTER JOIN *
****************************************************************
* PROBLEM DESCRIPTION: *
* During compilation of a query containing a LEFT or RIGHT *
* OUTER JOIN, DB2 may issue an SQL0901N if the following *
* conditions *
* are satisfied: *
* *
* 1) The table from the NULL-producing (NP) side has a *
* generated column (GC) *
* 2) The parent column (PC) of the GC is used in the ON clause *
* as part of the JOIN predicate *
* 3) The column from the row-preserving (RP) side has a local *
* predicate *
* 4) The GC from the NP table has derivable equivalence to the *
* RP column via its PC and the local predicate literal *
* *
* An example scenario that may generate (depending on the *
* Optimizer plan picked) such an SQL0901N follows: *
* *
* -- Table DDL *
* CREATE TABLE RP (C1 CHAR(20) NOT NULL WITH DEFAULT ' '); *
* CREATE TABLE NP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ' , *
* DUMMY CHAR(9) GENERATED ALWAYS AS *
* (SUBSTR(C1,1, 9)), *
* GC CHAR(13) GENERATED ALWAYS AS (SUBSTR(C1,1,13))); *
* *
* -- Query that may generate the -901 *
* SELECT B.C1 *
* FROM RP AS A *
* LEFT OUTER JOIN NP AS B *
* ON A.C1 = B.C1 *
* AND A.C1 = '1234567890' *
* ; *
* *
* -- -901 text *
* SQL0901N The SQL statement failed because of a non-severe *
* system error. *
* Subsequent SQL statements can be processed. (Reason "Bad *
* Plan; Unresolved QNC found".) SQLSTATE=58004 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 1 *
**************************************************************** |
| Local Fix: |
No feasible workaround exists aside from altering the query or
the table(s) in question.
Additionally, if a suitable Optimizer plan is picked, the
problem may not manifest even if all conditions required to
reproduce the problem are satisfied. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
|
| Solution |
Problem is first fixed in DB2 Version 9.7 Fix Pack 1. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.08.2009
13.01.2010
13.01.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
