home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
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
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IT07875 Status: Geschlossen

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

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
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-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ORA compatibility users                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 fix pack 11                       * 
****************************************************************
Local-Fix:
Lösung
First fixed in DB2 version 9.7 fix pack 11
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
23.03.2015
06.10.2015
06.10.2015
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP11
Problem behoben lt. FixList in der Version
9.7.0.11 FixList