DB2 FOR LUW / DB2FORLUW / A10 - DB2

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.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 Fix Pack 4                       * 
****************************************************************

Rewrite the query to modify the join predicate to form: b.c1+0 = 
1

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Upgrade to DB2 Version 10.1 Fix Pack 4

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC97617 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

21.10.2013
25.08.2014
25.08.2014