DB2 - Problembeschreibung
Problem IC97072 | Status: Geschlossen |
DB2 MIGHT PRODUCE INCORRECT RESULTS FOR QUERIES CONTAINING UNION ALL AND CTE OR VIEWS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
Under rare scenarios, DB2 might produce incorrect results if the following conditions are true. 1. Query also contains a UNION ALL and one or more of its branches refer to a View or common table expression (CTE) 2. The same view/CTE is also joined to the Union All through join predicates The problem can be verified through the explain plan by checking if any of the join predicate has been incorrectly dropped or not. eg. create table t1(c1 int not null , c2 int not null); insert into1 t1 values (1,1),(1,2); create table t2 (c1 int not null , c2 int not null); insert into t2 values (1,1),(1,2); create view v1 (c1) as (select t1.c1 from t1,t2 where t1.c1 = t2.c1 and t1.c2 = t2.c2); Current result: select count(1) from (select * from v1 union all select * from v1) b inner join v1 c on b.c1 = 1 and c.c1 = 1" 1 ----------- 16 1 record(s) selected. Expected result: select count(1) from (select * from v1 union all select * from v1) b inner join v1 c on b.c1+0 = 1 and c.c1 = 1" 1 ----------- 8 1 record(s) selected. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.1 Fix Pack 4 * **************************************************************** | |
Local-Fix: | |
Rewrite the query to modify the join predicate to form: b.c1+0 = 1 | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows | |
Lösung | |
Upgrade to DB2 Version 10.1 Fix Pack 4 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC97617 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 21.10.2013 25.08.2014 25.08.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.4 |