Latest versionsfixlist
11.1.0.6 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
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC82163 Status: Closed

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

product:
DB2 FOR LUW / DB2FORLUW / 950 - 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 DB2 v9.5 Fix Pack 10                               * 
****************************************************************
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
Solution
Update to DB2 v9.5 Fix Pack 10
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
21.03.2012
28.08.2012
28.08.2012
Problem solved at the following versions (IBM BugInfos)
9.5.FP10
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.10 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.