DB2 - Problem description
Problem IT21060 | Status: Closed |
SQL QUERIES WITH IN OR NOT IN CLAUSE MAY PRODUCE INCORRECT RESULTS FOR A COLUMN-ORGANIZED TABLE | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
Additional space is added in rewritten query to string predicate when we use "NOT IN" clause, thus we get wrong results from query. This only happens when we have table organized by columns where IN or NOT IN clause uses CHAR literal, but base table datatype is variable type. Example: create table t(c varchar(5)) organize by column; insert into t values('x'); select substr(c,1) from t where substr(c,1) not in (CHAR('abc'),CHAR('x')); 1 ----- x 1 record(s) selected. ==>incorrect result | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 10.5 Fix Pack 9 or higher * **************************************************************** | |
Local Fix: | |
Rewrite the query to avoid this condition. | |
Solution | |
First fixed in Db2 10.5 Fix Pack 9 | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 16.06.2017 29.09.2017 12.10.2017 |
Problem solved at the following versions (IBM BugInfos) | |
9.0. | |
Problem solved according to the fixlist(s) of the following version(s) |