DB2 FOR LUW / DB2FORLUW / A50 - 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 value(1,1),(1,2); 
create table t2 (c1 int not null , c2 int not null); 
insert into1 t2 value(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:                                              * 
* Update to 10.5.0.3                                           * 
****************************************************************

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

Problem Fixed In 10.5.0.3

not known / see Local fix

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

12.11.2013
28.07.2014
28.07.2014