|
Problem IC62688
|
Status: Closed |
DB2 MAY ISSUE SQL0901N EXECUTING A QUERY WITH CORRELATED REFERENCES TO A TABLE FROM AN OUTER JOIN |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
An SQL0901N may be issued when executing a query containing a
LEFT or RIGHT OUTER JOIN (LOJ) if correlation exists to the
table from the row-preserving (RP) side of the LOJ.
The following conditions must be satisfied for the scenario to
occur:
- The RP table has a unique key associated with it
- 2 or more levels of correlation exist to the RP table
- All correlated constructs are inner-joined after the LOJ
(either directly or via transitivity) on the correlated column
An example scenario follows:
-- Table DDL
CREATE TABLE RP (PK_C CHAR(2) NOT NULL PRIMARY KEY);
CREATE TABLE NP (J_C CHAR(2));
-- Table Function DDL
CREATE FUNCTION F1(IN_C CHAR(2))
RETURNS TABLE (OUT_C CHAR(2))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DUMMY CHAR(2);--
SET (DUMMY) = (IN_C);--
RETURN VALUES(IN_C);--
END;
CREATE FUNCTION F2(IN_C CHAR(2))
RETURNS TABLE (OUT_C CHAR(2))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
RETURN
SELECT A.J_C
FROM (VALUES(IN_C)) AS A(J_C)
LEFT OUTER JOIN NP AS B
ON B.J_C = A.J_C;
-- Query that will issue the SQL0901N
SELECT A.PK_C
FROM RP AS A,
TABLE(F1(A.PK_C)) AS B, -- First level of correlation
TABLE(F2(B.OUT_C)) AS C -- Second level of correlation
WHERE A.PK_C = B.OUT_C -- Explicit inner-join
AND B.OUT_C = C.OUT_C -- Inner-join via transitivity
;
-- SQLCODE issued
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.5 Fix Pack 5. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Use a query containing a LEFT or RIGHT OUTER JOIN *
****************************************************************
* PROBLEM DESCRIPTION: *
* An SQL0901N may be issued when executing a query containing *
* a LEFT or RIGHT OUTER JOIN (LOJ) if correlation exists to *
* the *
* table from the row-preserving (RP) side of the LOJ. *
* *
* The following conditions must be satisfied for the scenario *
* to occur: *
* - The RP table has a unique key associated with it *
* - 2 or more levels of correlation exist to the RP table *
* - All correlated constructs are inner-joined after the LOJ *
* (either directly or via transitivity) on the correlated *
* column *
* *
* An example scenario follows: *
* *
* -- Table DDL *
* CREATE TABLE RP (PK_C CHAR(2) NOT NULL PRIMARY KEY); *
* CREATE TABLE NP (J_C CHAR(2)); *
* *
* -- Table Function DDL *
* CREATE FUNCTION F1(IN_C CHAR(2)) *
* RETURNS TABLE (OUT_C CHAR(2)) *
* LANGUAGE SQL *
* DETERMINISTIC *
* NO EXTERNAL ACTION *
* BEGIN ATOMIC *
* DECLARE DUMMY CHAR(2);-- *
* SET (DUMMY) = (IN_C);-- *
* RETURN VALUES(IN_C);-- *
* END; *
* *
* CREATE FUNCTION F2(IN_C CHAR(2)) *
* RETURNS TABLE (OUT_C CHAR(2)) *
* LANGUAGE SQL *
* DETERMINISTIC *
* NO EXTERNAL ACTION *
* READS SQL DATA *
* RETURN *
* SELECT A.J_C *
* FROM (VALUES(IN_C)) AS A(J_C) *
* LEFT OUTER JOIN NP AS B *
* ON B.J_C = A.J_C; *
* *
* -- Query that will issue the SQL0901N *
* SELECT A.PK_C *
* FROM RP AS A, *
* TABLE(F1(A.PK_C)) AS B, -- First level of correlation *
* TABLE(F2(B.OUT_C)) AS C -- Second level of *
* correlation *
* WHERE A.PK_C = B.OUT_C -- Explicit inner-join *
* AND B.OUT_C = C.OUT_C -- Inner-join via transitivity *
* ; *
* *
* -- SQLCODE issued *
* 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 workaround exists aside from manually rewriting the query in
question. |
| 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
|
 |
