Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC81689 Status: Closed

QUERY WITH CORRELATED SUBQUERY PREDICATE AND GROUP BY CLAUSE REFERENCING
THE SAME COLUMNS MIGHT RETURN INCORRECT RESULTS

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
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.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to Version 9.7 Fix Pack 6                             * 
****************************************************************
Local Fix:
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
available fix packs:
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

Solution
Problem was first fixed in Version 9.7 Fix Pack 6
Workaround
See Local Fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC82163 IC84531 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
28.02.2012
02.08.2012
02.08.2012
Problem solved at the following versions (IBM BugInfos)
9.7.FP6
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.6 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.