DB2 - Problembeschreibung
Problem IC62688 | Status: Geschlossen |
DB2 MAY ISSUE SQL0901N EXECUTING A QUERY WITH CORRELATED REFERENCES TO A TABLE FROM AN OUTER JOIN | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
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-Zusammenfassung: | |
**************************************************************** * 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. | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows | |
Lösung | |
Problem is first fixed in DB2 Version 9.7 Fix Pack 1. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 21.08.2009 13.01.2010 13.01.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP1 | |
Problem behoben lt. FixList in der Version | |
9.7.0.1 |