DB2 FOR LUW / DB2FORLUW / 970 - DB2

Incorrect result will be returned if the query contains 
predicate which has a deeply nested (OR / AND) and a part can be 
proven TRUE or FALSE 
 
Here are the trigger conditions: 
 
 -- predicate contains one or more nested conjuncts (nested AND) 
 -- Nested AND has one or more OR sub-terms (nested OR sub-term) 
 -- All sub-terms of Nested OR can be proven TRUE or FALSE at 
compile-time. 
 -- Last sub-terms of Nested OR can be proven TRUE. 
 
The incorrect result is caused by incorrect optimized statement 
generated during compile time.(db2 explain and db2exfmt can be 
used to check optimized statement) 
 
For example, here is a predicate having nested AND and OR: 
 
This example should not return any row. But, a row will be 
returned incorrectly. 
select 
  * 
from 
  (select 
       1 as c1 
     , 2 as c2 
     , current date as c3 
     from sysibm.sysdummy1 
  ) 
where 
 ( 
    (c1=2 or c2=2)                  -- Nested OR. It is TRUE. 
    and c3=date('0001-01-01')  -- Nested AND. 
  ) 
  or c2=4; 
 
Here is the incorrect result: 
 
C1          C2          C3 
----------- ----------- ---------- 
          1           2 03/16/2012 
 
  1 record(s) selected. 
 
 
Here are the original statement and optimized statement got from 
db2exfmt: 
 
Original Statement: 
------------------ 
select * 
from 
   (select 1 as c1 , 2 as c2 , current date as c3 
   from sysibm.sysdummy1) 
where ((c1=2 or c2=2) and c3=date('0001-01-01')) or c2=4; 
 
 
Optimized Statement: 
------------------- 
SELECT 1 AS "C1", 2 AS "C2", CURRENT DATE AS "C3" 
FROM 
   (SELECT 'Y' 
   FROM (VALUES 1) AS Q1) AS Q2

**************************************************************** 
* USERS AFFECTED:                                              * 
* All.                                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to version 9.7 fixpack 6 or newer fixpacks.           * 
****************************************************************

There might be many local fixes. But, some workarounds may work 
in a scenario, but may not work in another scenario. 
 
Here are some possible local fixes: 
  - Change the sequence of sub-predicates in the nested OR, eg. 
(c2=2 or c1=2) and c3=date('0001-01-01'). 
  - Change the sequence of sub-predicates in the nested AND, eg. 
c3=date('0001-01-01') and (c1=2 or c2=2). 
  - Add function onto the column name, eg. (c1=2 or 
rtrim(char(c2))='2'). 
  - Enable statement concentrator, i.e.  STMT_CONC=LITERALS. 
Statement concentrator will replace the constant value to 
variable first. So, it makes query rewriter can not judge the 
predicate as TRUE or FALSE. 
 
The key point is to try modifying the statement, and use 
db2exfmt to verify whether the optimized statement is identical 
to the original statement semantically.

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

This problem is first fixed in db2 version 9.7 fixpack 6.

See Local Fix.

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

21.03.2012
05.06.2012
24.08.2012

9.7.FP6