|
Problem IC98361
|
Status: Geschlossen |
QUERY USING ZIGZAG IN THE ACCESS PLAN MIGHT PRODUCE WRONG RESULTS WHEN IN2JOIN IS IN THE ACCESS PLAN AS WELL. |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* 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 |
| verfügbare FixPacks: |
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Lösung |
First fixed in Version 10.1 Fix Pack 4 |
| Workaround |
Disable Zig Zag join using :-
db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF" -IM |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 17.12.2013
16.06.2014
02.09.2016 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
|
| Problem behoben lt. FixList in der Version |
| 10.1.0.4
|
 |
