An OR predicate of the form
(C1 > :HV1
OR
C1 = :HV1 AND C2 > :HV2)
may be incorrectly removed if the two conditions are met
1. we have an index on columns (C1,C2), AND
2. another predicate of the following form
(C1 < :HV4)
-or-
(C1 <= :HV4)
Note that the predicate (C1 <= :HV4) may be transformed from (C1
BETWEEN :HV3 AND :HV4).
If users replace each host variable with a literal, this problem
does not occur. Likewise, if parameter markers replace those
host variables, this problem does not occur because the
parameter markers replacing the two :HV1 are treated as two
different parameter markers.
For usages of parameter markers that hit this problem, please
see the example below.
This problem is first found in DB2 Version 9.5 Fix Pack 4 and
Version 9.7 GA. It is fixed in DB2 Version 9.5 Fix Pack 5 and
Version 9.7 Fix Pack 1.
Example:
create table t1(c1 int,c2 int,c3 int);
create index i1 on t1(c1,c2,c3);
with parm(p1,p2,p3,p4,p5) as (values (cast(? as int),cast(? as
int),cast(? as int),cast(? as int),cast(? as int)))
select *
from t1,parm p
where t1.c1 between p.p4 and p.p5
and ((t1.c1 > p.p1)
or
(t1.c1=p.p1 and t1.c2>p.p2)
or
(t1.c1=p.p1 and t1.c2=p.p2 and t1.c3>p.p3)
)
; |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* OR PREDICATE INCLUDING HOST VARIABLES OR PARAMETER *
* MARKERSMAY BE INCORRECTLY REMOVEDAn OR predicate of the *
* form(C1 > :HV1ORC1 = :HV1 AND C2 > :HV2)may be incorrectly *
* removed if the two conditions are met1. we have an index on *
* columns (C1,C2), AND2. another predicate of the following *
* form(C1 < :HV4)-or-(C1 <= :HV4)Note that the predicate (C1 *
* <= :HV4) may be transformed from(C1BETWEEN :HV3 AND :HV4). *
****************************************************************
* RECOMMENDATION: *
* If users replace each host variable with a literal, *
* thisproblem does not occur. Likewise, if parameter *
* markersreplace those host variables, this problem does not *
* occur. *
**************************************************************** |
