DB2 FOR LUW / DB2FORLUW / 970 - DB2

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))))

**************************************************************** 
* USERS AFFECTED:                                              * 
* ORA compatibility users                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 fix pack 11                       * 
****************************************************************

First fixed in DB2 version 9.7 fix pack 11

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

23.03.2015
06.10.2015
06.10.2015

9.7.FP11