|
Problem IT16779
|
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 / A50 - 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 *
****************************************************************
* RECOMMENDATION: *
* Update to 10.5.0.4 *
**************************************************************** |
| Local Fix: |
Disable Zig Zag join using :-
db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF" -IM |
| Solution |
|
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : IC98361
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 25.08.2016
22.12.2016
22.12.2016 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
