DB2 - Problembeschreibung
Problem IC63303 | Status: Geschlossen |
POSSIBLE UNEXPECTED RESULTS IN SQL STATEMENT CONTAINING OR PREDICATE(S) WITH IN SUBQUERIES. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
This problem could occur when all the following conditions are met: . 1) The SQL statement has an OR predicate with subquery of the form . <col-1> IN (select <col-sq> from....) OR <other-subterm> . -OR- <col-1> NOT IN (select <col-sq> from....) OR <other-subterm> 2) The statement also has an OR predicate with subquery of the form <col-2> IN (select <col-sq> from....) OR <other-subterm> -OR- <col-2> NOT IN (select <col-sq> from....) OR <other-subterm> Note that the OR predicate in 1) and 2) could be the same OR predicate or two different OR predicates 3) The <col-sq> in 1) and 2) must be the same value of a constant, a host variable, a parameter marker, or a special register such as CURRENT DATE. In addition, <col-sq> could be a normal column in the user written SQL statement but is substituted by DB2 Query Rewrite to one of those aforementioned types, which could be observed in the Optimized Statement of an output of db2exfmt. 4) There are join predicates in other part of the same SQL statement such that DB2 Optimizer could derive the following relationship <col-1> = <col-2> = <col-3> An example of such join predicates is <col-1> = <col-3> AND <col-2> = <col-3> 5) When all the above conditions are met, DB2 incorrectly derives that <col-1> = <col-2> = <col-3> = <col-sq> In some certain choices of access plan where DB2 Optimizer chooses to apply the two join predicates <col-1> = <col-3> and <col-2> = <col-3> in the same join operator, the Optimizer may not apply one of the join predicates in 4). As a result, DB2 will produce possibly more rows in the result set. | |
Problem-Zusammenfassung: | |
This problem could occur when all the following conditions are met: . 1) The SQL statement has an OR predicate with subquery of the form . <col-1> IN (select <col-sq> from....) OR <other-subterm> . -OR- <col-1> NOT IN (select <col-sq> from....) OR <other-subterm> 2) The statement also has an OR predicate with subquery of the form <col-2> IN (select <col-sq> from....) OR <other-subterm> -OR- <col-2> NOT IN (select <col-sq> from....) OR <other-subterm> Note that the OR predicate in 1) and 2) could be the same OR predicate or two different OR predicates 3) The <col-sq> in 1) and 2) must be the same value of a constant, a host variable, a parameter marker, or a special register such as CURRENT DATE. In addition, <col-sq> could be a normal column in the user written SQL statement but is substituted by DB2 Query Rewrite to one of those aforementioned types, which could be observed in the Optimized Statement of an output of db2exfmt. 4) There are join predicates in other part of the same SQL statement such that DB2 Optimizer could derive the following relationship <col-1> = <col-2> = <col-3> An example of such join predicates is <col-1> = <col-3> AND <col-2> = <col-3> 5) When all the above conditions are met, DB2 incorrectly derives that <col-1> = <col-2> = <col-3> = <col-sq> In some certain choices of access plan where DB2 Optimizer chooses to apply the two join predicates <col-1> = <col-3> and <col-2> = <col-3> in the same join operator, the Optimizer may not apply one of the join predicates in 4). As a result, DB2 will produce possibly more rows in the result set. | |
Local-Fix: | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows | |
Lösung | |
module engn_sqnr fixed >= v97 fpk1 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 21.09.2009 11.02.2010 11.02.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FPk1 | |
Problem behoben lt. FixList in der Version | |
9.7.0.1 |