DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

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.

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
 

module engn_sqnr 
fixed >= v97 fpk1

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

21.09.2009
11.02.2010
11.02.2010

9.7.FPk1