DB2 - Problembeschreibung
Problem IT05059 | Status: Geschlossen |
Query may get wrong result in VARCHAR2 enabled database when using multi-row, multi-column VALUES clause in an IN predicate | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
In a VARCHAR2 enabled database, a multiple row, multiple column VALUES clause may return CHAR instead of the expected VARCHAR. For example: describe VALUES ('abc', 1),('longer', 2) Column Information Number of columns: 2 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 448 CHAR 6 1 1 496 INTEGER 4 2 1 The result type of the first column should be VARCHAR(6) since the two CHAR literals 'abc' and 'longer' have different lengths. When a multiple row VALUES clause is used in an IN predicate, wrong results may be returned, e.g.: create table T ( c1 varchar(10), c2 int ) insert into T values ('abc', 1) select * from T where (c1,c2) IN ( VALUES ('abc',1),('longer',2) ) C1 C2 ---------- ----------- 0 record(s) selected. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 10.1 fix pack 5 * **************************************************************** | |
Local-Fix: | |
Use UNION ALL instead of a multiple row VALUES clase, e.g.: select 'abc', 1 from dual union all select 'longer', 2 from dual | |
Lösung | |
First fixed in DB2 version 10.1 fix pack 5 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 21.10.2014 24.07.2015 24.07.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.5 |