Latest versionsfixlist
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
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IT07875 Status: Closed

WRONG RESULT IN ORA MODE WHEN GENERATED TWIN PRED CONTAINS CHAR WITH HEX
VALUES LOWER THEN SPACE

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
CREATE TABLE T(c1 CHAR(3)) 
INSERT INTO T VALUES('abc'); 
SELECT * FROM T WHERE c2 < CAST('abc' || CHR(10) AS 
VARCHAR(10)); 
=> Returns 'abc' which is correct because in ORA mode we compare 
non padded as VARCHAR 
Now add an index: 
CREATE INDEX I ON T(c1); 
SELECT * FROM T WHERE c2 < CAST('abc' || CHR(10) AS 
VARCHAR(10)); 
=> NOW ROWS FOUND 
 
The problem is caused by our CHAR padding semantics, when twin 
predicate  is generated we add a CHAR cast function 
 
3) Stop Key Predicate, 
   Comparison Operator:   Less Than or Equal (<=) 
   Subquery Input Required:  No 
   Filter Factor:    0.333333 
 
   Predicate Text: 
   -------------- 
   (Q1.C1 <= CHAR(ORA_VARCHAR(('abc' || "SYSFUN "."CHR"(10))))) 
 
what happens is the expression CHAR(ORA_VARCHAR(('abc' || 
"SYSFUN "."CHR"(10))))  produces hex value 6162630A  a four 
character CHAR string so our semantics tell us 
that we should pad other expression Q1.C1 which has 'abc' with 
empty space 20 , so we getting 61626320 < 6162630A which is 
false. Without index we do not generate twin predicates and 
comparing varchars hence no problem. 
 
Predicate Text: 
   -------------- 
   (ORA_VARCHAR(Q1.C1) < ORA_VARCHAR(('abc' || "SYSFUN 
"."CHR"(10))))
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ORA compatibility users                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 fix pack 11                       * 
****************************************************************
Local Fix:
Solution
First fixed in DB2 version 9.7 fix pack 11
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
23.03.2015
06.10.2015
06.10.2015
Problem solved at the following versions (IBM BugInfos)
9.7.FP11
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.11 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.