home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC82125 Status: Geschlossen

INCORRECT RESULT WHEN PREDICATE CONTAINS DEEPLY NESTED (OR / AND) AND A
PART CAN BE PROVEN TRUE OR FALSE.

Produkt:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problembeschreibung:
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
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL.                                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to db2 version 9.5 fixpack 10.                        * 
****************************************************************
Local-Fix:
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.
Lösung
This problem is first fixed in db2 version 9.5 fixpack 10.
Workaround
See Local Fix.
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC82158 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
19.03.2012
20.10.2012
20.10.2012
Problem behoben ab folgender Versionen (IBM BugInfos)
9.5.FP10
Problem behoben lt. FixList in der Version
9.5.0.10 FixList