DB2 - Problembeschreibung
Problem IC62692 | Status: Geschlossen |
DB2 MAY ISSUE AN SQL0901N COMPILING A QUERY CONTAINING AN OUTER JOIN ON A TABLE WITH GENERATED COLUMNS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
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-Zusammenfassung: | |
**************************************************************** * 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. | |
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 |