DB2 FOR LUW / DB2FORLUW / 950 - 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 db2 version 9.5 fixpack 10.                        * 
****************************************************************

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'). 
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.

This problem is first fixed in db2 version 9.5 fixpack 10.

See Local Fix.

forerunner  : APAR is sysrouted TO one or more of the following: IC82158 
follow-up : 

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

19.03.2012
20.10.2012
20.10.2012

9.5.FP10