In MPP and SMP environment, query with multiple IN list to join
(GENROW) plans generated via transivity on SMP and MPP
environment might have incorrect results.
Query example: select * from a, b where a1 = b1 and a1 in (1, 2,
3, 4, 5, 6, 7, 8, 9, 10);
The optimized query would generate second IN list via
transitivity: a1 = b1 and a1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
and b1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
If query plan result with two GENROW plans (both MGJN in this
example), due to unexpected interaction of MPP and SMP planning
for the GENROW plans, incorrect result produced.
HSJN
/ \
MGJN(1) MGJN(2)
/ \ / \
SORT ISCAN SORT ISCAN
| |
GENROW GENROW
This can happen for a variety of combination of GENROW plans,
not just MGJN plans. The problem is specific to release v9.5,
and would be fixed in v9.5 FP5 and laster release. |
****************************************************************
* USERS AFFECTED: *
* n/a *
****************************************************************
* PROBLEM DESCRIPTION: *
* In MPP and SMP environment, query with multiple IN list to *
* join *
* (GENROW) plans generated via transivity on SMP and MPP *
* *
* environment might have incorrect results. *
* *
* *
* *
* Query example: select * from a, b where a1 = b1 and a1 in *
* (1, 2, *
* 3, 4, 5, 6, 7, 8, 9, 10); *
* *
* The optimized query would generate second IN list via *
* *
* transitivity: a1 = b1 and a1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, *
* 10) *
* and b1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) *
* *
* *
* *
* If query plan result with two GENROW plans (both MGJN in *
* this *
* example), due to unexpected interaction of MPP and SMP *
* planning *
* for the GENROW plans, incorrect result produced. *
* *
* *
* *
* HSJN *
* *
* / \ *
* *
* MGJN(1) MGJN(2) *
* *
* / \ / \ *
* *
* SORT ISCAN SORT ISCAN *
* *
* | | *
* *
* GENROW GENROW *
* *
* *
* *
* This can happen for a variety of combination of GENROW *
* plans, *
* not just MGJN plans. The problem is specific to release *
* v9.5, *
* and would be fixed in v9.5 FP5 and laster release. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fixpack 1 *
**************************************************************** |
