|
Problem IC75014
|
Status: Closed |
DB2 MAY NOT APPLY LOCAL PREDICATE WITH BIGINT, INTEGER, SMALLINT IN AN OPTIMAL WAY MAY RETURN INCORRECT RESULT ON LENGTH FUNCTION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
DB2 may not apply local predicates with BIGINT, INTEGER,
SMALLINT on varying-length char strings in an optimal way or
may return incorrect results with LENGTH function on
varying-length char strings
There are two problems:
(1) DB2 may not push down local predicates containing an
expression of the form <function>(<expr>) where
<function> is BIGINT, INTEGER or SMALLINT and
<expr> is an expression of the varying-length character strings
(such as VARCHAR, VARGRAPHIC, CLOB, etc.)
to lower operations. This may result in performance degradation
when upgrading DB2 from earlier releases to Version 9.7.
(2) DB2 may return incorrect results when running the SQL
statement containing predicates containing an expression of the
form
LENGTH(<expr>) where
<expr> is an expression of the varying-length character strings
of type VARCHAR, VARGRAPHIC, CLOB, NCLOB.
and the predicates are applied above Group By operation.
For example:
db2 => create table t1(c1 varchar(10), c2 int, c3 int);
db2 => insert into t1 values ('C1', 1, -1), ('C1 ', 2, -2), ('C1
', -1, 1);
db2 => select * from t1;
C1 C2 C3
---------- ----------- -----------
C1 1 -1
C1 2 -2
C1 -1 1
3 record(s) selected.
-- Correct result
db2 => select c1,sum(c2),sum(c3) from t1 group by c1 having
length(c1) = 2;
C1 2 3
---------- ----------- -----------
C1 2 -2
1 record(s) selected.
-- Incorrect result because LENGTH(c1) is applied before Group
By
db2 => select * from (select c1,sum(c2),sum(c3) from t1 group by
c1 ) as q where length(c1)=2;
C1 2 3
---------- ----------- -----------
C1 1 -1
1 record(s) selected.
This problem occurs in DB2 Version 9.7 GA and Fix Packs 1,2, and
3 |
| Problem Summary: |
Users Effected:
All user using V9.7
Problem Description:
DB2 may not apply local predicates with BIGINT, INTEGER,
SMALLINT on varying-length char strings in an optimal way or
may return incorrect results with LENGTH function on
varying-length char strings
There are two problems:
(1) DB2 may not push down local predicates containing an
expression of the form <function>(<expr>) where
<function> is BIGINT, INTEGER or SMALLINT and
<expr> is an expression of the varying-length character strings
(such as VARCHAR, VARGRAPHIC, CLOB, etc.)
to lower operations. This may result in performance degradation
when upgrading DB2 from earlier releases to Version 9.7.
(2) DB2 may return incorrect results when running the SQL
statement containing predicates containing an expression of the
form
LENGTH(<expr>) where
<expr> is an expression of the varying-length character strings
of type VARCHAR, VARGRAPHIC, CLOB, NCLOB.
and the predicates are applied above Group By operation.
For example:
db2 => create table t1(c1 varchar(10), c2 int, c3 int);
db2 => insert into t1 values ('C1', 1, -1), ('C1 ', 2, -2), ('C1
', -1, 1);
db2 => select * from t1;
C1 C2 C3
---------- ----------- -----------
C1 1 -1
C1 2 -2
C1 -1 1
3 record(s) selected.
-- Correct result
db2 => select c1,sum(c2),sum(c3) from t1 group by c1 having
length(c1) = 2;
C1 2 3
---------- ----------- -----------
C1 2 -2
1 record(s) selected.
-- Incorrect result because LENGTH(c1) is applied before Group
By
db2 => select * from (select c1,sum(c2),sum(c3) from t1 group by
c1 ) as q where length(c1)=2;
C1 2 3
---------- ----------- -----------
C1 1 -1
1 record(s) selected.
This problem occurs in DB2 Version 9.7 GA and Fix Packs 1,2, and
3
Problem Summary:
DB2 MAY NOT APPLY LOCAL PREDICATE WITH BIGINT, INTEGER, SMALLINT
IN AN OPTIMAL WAY MAY RETURN INCORRECT RESULT ON LENGTH FUNCTION |
| Local Fix: |
Upgraded to DB2 Version 9.7 Fix Pack 4 |
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
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
|
| Solution |
defect wsdbu00823817
CSD First fixed in DB2 UDB Version 9.7, FixPak 4
module engn_sqnr |
| Workaround |
Upgrade to DB2 Version 9.7, FixPak 4 |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 15.03.2011
02.05.2011
02.05.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
