DB2 FOR LUW / DB2FORLUW / 970 - DB2

If your query satisfies the following conditions, then the 
results returned might be incorrect: 
 
1.  There is a correlated subquery predicate that references the 
same column as the one correlated predicate within the subquery 
2.  There is a GROUP BY clause in the query that references the 
same column as in 1 
 
For example, the following query could return incorrect results 
as it satisfies both conditions with respect to column T1.A 
 
select t1.a, count(*) from t1,t3 
where t1.a=t3.a and t1.a = (select distinct t2.a from t2 where 
t2.a=t1.a) 
group by t1.a 
 
You can identify if the results are incorrect if the expected 
groups are collapsed into less groups than expected, or there 
are duplicates in the key columns in the result. For example, if 
the three tables from the example query above contained the 
following integer data: 
 
insert into t1 values (1,1),(2,2), (3,3), (4,4), (5,5), (6,6); 
insert into t2 values (1,1),(2,2), (3,3), (4,4), (5,5), (6,6); 
insert into t3 values (1,1),(2,2), (3,3), (4,4), (5,5), (6,6); 
 
the expected, correct result is to return 6 rows, each with a 
count of 1; however if you encounter this defect, executing the 
query will produce a single row with any one of the 6 integer 
values and a corresponding count of 6.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to Version 9.7 Fix Pack 6                             * 
****************************************************************

You can workaround this defect by modifying the query to change 
the subquery predicate to an EXISTS predicate, under the 
assumption that the correlated subquery returns a single value 
for each correlation value. Using the example in the 
description, the following rewrite to an EXISTS predicate will 
produce correct results: 
 
select t1.a, count(*) from t1,t3 
where t1.a=t3.a and exists (select 1 from t2 where t2.a=t1.a) 
group by t1.a

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.7 Fix Pack 6

See Local Fix

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

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

28.02.2012
02.08.2012
02.08.2012

9.7.FP6