DB2 query in Stored Procedure may produce wrong result if the
following conditions are met:
1)There is a table having inequality/IN/OR check constraint
predicate
2)The stored procedure uses that table in a query
3)After compilation of Stored Procedure, the check constraint is
dropped and/or added new constraint
To reproduce the issue:
db2 -v "create db testdb"
db2 -v "connect to testdb"
db2 -v "create table t1 (x int)"
db2 -v "alter table t1 add constraint c1 check (x between 0 and
100)"
db2 -v "create procedure sp1 ( IN x1 INTEGER, IN x2 INTEGER )
language sql begin declare curs CURSOR WITH RETURN FOR select *
from t1 where x between x1 and x2 order by x; open curs; end"
db2 -v "alter table t1 drop constraint c1"
db2 -v "alter table t1 add constraint c1 check (x between 101
and 200)"
db2 -v "insert into t1 values (150)"
db2 -v "call sp1 (140, 160)"
db2 -v "disconnect testdb"
The call to sp1 is expected to return the newly inserted row
with value 150, but instead returns no results. An Explain shows
that the dropped constraint is still present in the optimized
statement:
Original Statement:
------------------
DECLARE CURS CURSOR
WITH RETURN
FOR
select *
from T1
where X between :HV00008 :HI00008 and :HV00009 :HI00009
order by X
Optimized Statement:
-------------------
SELECT Q3.X
FROM
(SELECT Q1.$C0
FROM (VALUES 0) AS Q1
WHERE (:HV00008 <= :HV00009 SELECTIVITY 1.000000) AND
(:HV00008 <= 100
SELECTIVITY 1.000000) AND (0 <= :HV00009 SELECTIVITY
1.000000)) AS
Q2, ALEXCHEU.T1 AS Q3
WHERE (Q3.X <= :HV00009) AND (:HV00008 <= Q3.X)
ORDER BY Q3.X
Checking the catalog, it appears that the procedure and package
are not invalidated as part of the drop constraint, since
following "alter table t1 drop constraint c1" we have:
> db2 "select substr(ROUTINENAME,1,11) as ROUTINENAME,
substr(VALID,1,1) as VALID from SYSIBM.SYSROUTINES where
ROUTINENAME = 'SP1'"
ROUTINENAME VALID
----------- -----
SP1 Y
1 record(s) selected.
> db2 "select substr(NAME,1,11) as NAME, substr(VALID,1,1) as
VALID from SYSIBM.SYSPLAN where NAME = 'P1362264217'"
NAME VALID
----------- -----
P1362264217 Y
1 record(s) selected. |
Recompiling the procedure will resolve this.
Steps for the example given in APAR text:
db2 -v "drop procedure sp1"
db2 -v "create procedure sp1 ( IN x1 INTEGER, IN x2 INTEGER )
language sql begin declare curs CURSOR WITH RETURN FOR
select *
from t1 where x between x1 and x2 order by x; open
curs; end"
db2 -v "call sp1 (140, 160)" |
