|
Problem IC98361
|
Status: Closed |
QUERY USING ZIGZAG IN THE ACCESS PLAN MIGHT PRODUCE WRONG RESULTS WHEN IN2JOIN IS IN THE ACCESS PLAN AS WELL. |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Queries logically similar can produce wrong result. The given
scenario is one example
Expected result from all the queries:-
Acct Acct 3
2012 2012 71
2013 2013 20
Example of incorrect results are as follows:-
select test.acct, head.acct, count(*)
from db2inst1.employee test
join db2inst1.salary test1 on test1.rid = test.id
where test.acct =test1.acct and test.acct in ( 2013,
2012
) and test1.code = '102228'
group by test.acct,test1.acct
Acct Acct 3
2012 2012 71
select test.acct,test1.acct, count(*)
from db2inst1.employee test
join db2inst1.salary test1 on test1.rid = test.id
where test.acct =test1.acct and test.acct in ( 2012,
2013
) and test1.code = '102228'
group by test.acct,test1.acct
;
Acct Acct 3
2013 2013 20
Access plans will show zig zag join used with IN2JOIN in bad
case. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Queries logically similar can produce wrong result. The *
* given *
* scenario is one example *
* Expected result from all the queries:- *
* *
* Acct Acct 3 *
* 2012 2012 71 *
* 2013 2013 20 *
* *
* *
* *
* Example of incorrect results are as follows:- *
* *
* select test.acct, head.acct, count(*) *
* from db2inst1.employee test *
* join db2inst1.salary test1 on test1.rid = test.id *
* where test.acct =test1.acct and test.acct in ( 2013, *
* 2012 *
* ) and test1.code = '102228' *
* group by test.acct,test1.acct *
* Acct Acct 3 *
* 2012 2012 71 *
* *
* *
* *
* *
* select test.acct,test1.acct, count(*) *
* from db2inst1.employee test *
* join db2inst1.salary test1 on test1.rid = test.id *
* where test.acct =test1.acct and test.acct in ( 2012, *
* 2013 *
* ) and test1.code = '102228' *
* group by test.acct,test1.acct *
* ; *
* *
* Acct Acct 3 *
* 2013 2013 20 *
* *
* Access plans will show zig zag join used with IN2JOIN in bad *
* case. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 10.1 Fix Pack 4 *
**************************************************************** |
| Local Fix: |
Disable Zig Zag join using :-
db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF" -IM |
| available fix packs: |
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
First fixed in Version 10.1 Fix Pack 4 |
| Workaround |
Disable Zig Zag join using :-
db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF" -IM |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.12.2013
16.06.2014
02.09.2016 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.4
|
 |
