DB2 - Problem description
Problem IT21760 | Status: Closed |
DB2 MAY RETURN WRONG RESULTS WITH ORACLE COMPATIBILITY AND SUBSTR | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
DB2 may return wrong results when all the following conditions are met: -The database has oracle compatibility (DB2_COMPATIBILITY_VECTOR=ORA) mode enabled. -The SELECT query being issued contains a relational operator predicate with SUBSTR. -The string argument to SUBSTR is from a table that has an index on that column. -The other operand of the relational operator is a literal. This forces the optimizer to perform an index scan using only the start/stop predicates. The index scan LOLEPOP does not contain the SUBSTR predicate, only the start/stop preds. Example: create table t (v VARCHAR(128)); create index i on t(v); insert into t values 'PT1', 'PT10'; select v from t where substr(v, 1, 20) = cast('PT1' as VARCHAR(20)); The above query returns two rows ('PT1', 'PT10') when we should only be returning one ('PT1'). The expected result (one row) is returned by the following: select v from t where v = cast('PT1' as VARCHAR(20)); | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Compatibility ORA users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * See Error Description. Please, install fix. * **************************************************************** | |
Local Fix: | |
Workaround: Replace the literal with a table column or variable. Continuing with the example above: create variable myvar char(3) default 'PT1'; select * from test where substr(v, 1, 20) = myvar; | |
Solution | |
See Error Description. Please, install fix. | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 03.08.2017 27.09.2017 12.10.2017 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |