| Bug #52068 | Optimizer generates invalid semijoin materialization plan | ||
|---|---|---|---|
| Submitted: | 15 Mar 2010 15:49 | Modified: | 22 Nov 2010 1:26 |
| Reporter: | Roy Lyseng | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Roy Lyseng | CPU Architecture: | Any |
| Tags: | materialization, optimizer_switch, semijoin, subquery | ||
[15 Mar 2010 17:05]
MySQL Verification Team
Thank you for the bug report.
[17 Mar 2010 10:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/103552 3827 Roy Lyseng 2010-03-17 Bug#52068: Optimizer generates invalid semijoin materialization plan The semijoin materialization strategies require that all outer correlated tables are either in the prefix (for MaterializeLookup) or in the suffix (for MaterializeScan). The notation above describes the semijoin plan as prefix - handled inner tables - suffix. Hence, it is not allowed that outer correlated tables are both in the prefix and in the suffix. A simple check for this requirement was added to at_sjmat_pos(). Descriptions of allowed table patterns for MaterializeLookup and MaterializeScan were added to the function header of setup_semijoin_dups_elimination(). Renamed at_sjmat_pos() to allows_semijoin_materialization(), simplified arguments to this function, and simplified the code slightly. Added Doxygen header. mysql-test/r/subselect_sj.result Test result for Bug#52068 mysql-test/r/subselect_sj_jcl6.result Test result for Bug#52068 mysql-test/t/subselect_sj.test Test case for Bug#52068 sql/sql_select.cc Added check for correlated tables not being both in prefix and suffix. Added description about use pattern for materialization strategies. Rewritten at_sjmat_pos() slightly, new name allows_semijoin_materialization.
[17 Mar 2010 14:28]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/103605 3830 Tor Didriksen 2010-03-17 comments/questions on patch for bug#52068
[18 Mar 2010 8:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/103650 3832 Roy Lyseng 2010-03-18 Bug#52068: Optimizer generates invalid semijoin materialization plan The semijoin materialization strategies require that all outer correlated tables are either in the prefix (for MaterializeLookup) or in the suffix (for MaterializeScan). The notation above describes the semijoin plan as prefix - handled inner tables - suffix. Hence, it is not allowed that outer correlated tables are both in the prefix and in the suffix. A simple check for this requirement was added to at_sjmat_pos(). Descriptions of allowed table patterns for MaterializeLookup and MaterializeScan were added to the function header of setup_semijoin_dups_elimination(). Renamed at_sjmat_pos() to allows_semijoin_materialization(), simplified arguments to this function, and simplified the code slightly. Added Doxygen header. mysql-test/r/subselect_sj.result Test result for Bug#52068 mysql-test/r/subselect_sj_jcl6.result Test result for Bug#52068 mysql-test/t/subselect_sj.test Test case for Bug#52068 sql/sql_select.cc Added check for correlated tables not being both in prefix and suffix. Added description about use pattern for materialization strategies. Rewritten at_sjmat_pos() slightly, new name allows_semijoin_materialization.
[24 Mar 2010 9:19]
Øystein Grøvlen
From looking at the code, it does not seem to be an intended restriction that MaterializeScan should only work when the materialized table comes first in the join sequence. However, after some debugging, it seems to me that there is currently to issues that needs to be solved for this to work: 1. Errors in equality propagation for semijoin materialization. This is addressed by Bug#45174/Bug#50019. 2. If a materialized table is not first in the join sequence, it will be scanned several times. Currently, the read cursor is not reset on subsequent scans. I suggest awaiting the fix for Bug#45174, and then use this bug (Bug#52068) for addressing item 2 above. The comments and refactoring of the current patch for this bug, is very good, and I suggest submitting them in a separate patch (without the restriction on join sequence for MaterializeScan).
[28 Apr 2010 13:09]
Øystein Grøvlen
Bug#53172 has been marked as a duplicate since the fixes mentioned above will also fix the query in that report.
[7 Oct 2010 15:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/120276 3260 Roy Lyseng 2010-10-07 Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Bug#50019: Wrong result for IN-query with materialization. Bug#52068: Optimizer generates invalid semijoin materialization plan When a subquery is subject to a semijoin optimization, it's tables are merged to the outer query and later are treated as regular tables. This allows a bunch of optimizations to be applied, equality propagation is among them. Equality propagation is done after query execution plan is chosen. It substitutes fields from tables being retrieved later for fields from tables being retrieved earlier. However, it can't be applied as is to any semijoin table. The semijoin materialization strategy differs from other semijoin strategies that the data from materialized semijoin tables isn't used directly but saved to a temporary table first. The materialization isn't isolated in a separate step, it is done inline within the nested loop execution. When it comes to fetching rows from the first table in the block of materialized semijoin tables, sub_select() function is called to materialize the result of the subquery and save it in the materialized table. Later, data from the materialized table is used as they were regular table rows. Due to this we can't substitute fields that belong to the semi-join for fields from outer query and vice versa. Example: suppose we have a join order: ot1 ot2 SJ-Mat(it1 it2 it3) ot3 and equality ot2.col = it1.col = it2.col If we're looking for best substitute for 'it2.col', we should pick it1.col and not ot2.col. For a field that is not in a materialized semijoin we can use any field, even those that are embedded in a materialized semijoin. This is because such fields are "copied back" to their original join-tab structures when the materialized temporary table is being read. Now we have added another Item_equal::get_first() function that accepts as a parameter a field being substituted and checks whether it belongs to a materialized semijoin. The field to substitute will be from the same materialized semijoin nest (if supplied field is within such nest), otherwise it will be the first field in the multiple equality. The new checks rely on the first_sj_inner_tab and first_sj_inner_tab fields of the join-tab. These fields are therefore set as soon as possible after the join strategy is fixed. Also fixed problem appearing in Bug#52068: When MaterializeScan semijoin strategy was used and there were one or more outer dependent tables before the semijoin tables, the scan over the materialized table was not properly reset for each row of the prefix outer tables. Also fixed problems with pushdown of SJM-aware predicates during make_join_select(): wrong predicates were sometimes generated, make_cond_after_sjm() was called at the wrong position, and make_cond_after_sjm() was never actually considering the pushed-down SJM predicates. mysql-test/include/subquery_sj.inc Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Bug#50019: Wrong result for IN-query with materialization. Bug#52068: Optimizer generates invalid semijoin materialization plan Added new tests. mysql-test/r/subquery_mat_all.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Bug#50019: Wrong result for IN-query with materialization. Bug#52068: Optimizer generates invalid semijoin materialization plan Results for three new tests added. Some tests using semijoin materialization show that where clause has moved from the outer query into the materialized inner query. This is caused by the changed call to get_first() in eliminate_item_equal(). Ex: select * from ot where a in(select b from it where b>0); The clause "b>0" is now evaluated on the inner query materialization. Performance-wise this is never worse when using MaterializeScan and usually better for MaterializeLookup. For the latter strategy, the best possible solution is probably to evaluate the clause in both queries, this can be subject for a later feature development. Another test that applies the same condition to both the outer and the inner query is added, to show the plan for such types of queries. Earlier wrong semijoin materialization test results are corrected. sql/item.cc Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Calling new get_first() function instead of old. sql/item_cmpfunc.cc Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. New function Item_equal::get_first() that accepts as argument a field being substituted. sql/item_cmpfunc.h Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. New function Item_equal::get_first() that accepts as argument a field being substituted. sql/sql_select.cc Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Bug#50019: Wrong result for IN-query with materialization. Bug#52068: Optimizer generates invalid semijoin materialization plan Setting fields first_sj_inner_tab and last_sj_inner_tab moved from setup_semijoin_dups_elimination() to get_best_combination(), so they are set as early as possible after join order optimization. In make_join_select(), the test that determined when to pushdown SJM-specific predicates was wrong, in addition to approving the comments. The logic of eliminate_item_equal() has been simplified and adjusted so that it generates equalities that are useful also when the semijoin materialization strategy is being used. Some simplification was possible by taking advantage of the new Item_equal::get_first() function. In sub_select_sjm(), moved code that initializes the scan over the materialized table so that it is now performed for each scan of table, instead of only for the first scan. In make_cond_for_table_from_pred(), a number of comments has been added, and TAB characters are replaced by spaces. In make_cond_after_sjm(), make sure that it handles equalities generated for semijoin materialization (with marker=3). Otherwise, removed marker optimizations for this function, as it will only be called once per materialized semijoin nest in a query. Added comments and removed TAB characters.
[22 Oct 2010 7:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/121634 3265 Roy Lyseng 2010-10-22 Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Bug#50019: Wrong result for IN-query with materialization. Bug#52068: Optimizer generates invalid semijoin materialization plan When a subquery is subject to a semijoin optimization, its tables are merged to the outer query and later are treated as regular tables. This allows a bunch of optimizations to be applied, equality propagation is among them. Equality propagation is done after query execution plan is chosen. It substitutes fields from tables being retrieved later for fields from tables being retrieved earlier. However, it can't be applied as is to any semijoin table. The semijoin materialization strategy differs from other semijoin strategies that the data from materialized semijoin tables isn't used directly but saved to a temporary table first. The materialization isn't isolated in a separate step, it is done inline within the nested loop execution. When it comes to fetching rows from the first table in the block of materialized semijoin tables, sub_select() function is called to materialize the result of the subquery and save it in the materialized table. Later, data from the materialized table is used as if they were regular table rows. Due to this we can't substitute fields that belong to the semi-join for fields from outer query and vice versa. Example: suppose we have a join order: ot1 ot2 SJ-Mat(it1 it2 it3) ot3 and equality ot2.col = it1.col = it2.col If we're looking for best substitute for 'it2.col', we should pick it1.col and not ot2.col. For a field that is not in a materialized semijoin we can use any field, even those that are embedded in a materialized semijoin. This is because such fields are "copied back" to their original join-tab structures when the materialized temporary table is being read. Now we have added another Item_equal::get_first() function that accepts as a parameter a field being substituted and checks whether it belongs to a materialized semijoin. The field to substitute will be from the same materialized semijoin nest (if supplied field is within such nest), otherwise it will be the first field in the multiple equality. The new checks rely on the first_sj_inner_tab and first_sj_inner_tab fields of the join-tab. These fields are therefore set as soon as possible after the join strategy is fixed. Also fixed problem appearing in Bug#52068: When MaterializeScan semijoin strategy was used and there were one or more outer dependent tables before the semijoin tables, the scan over the materialized table was not properly reset for each row of the prefix outer tables. Also fixed problems with pushdown of SJM-aware predicates during make_join_select(): wrong predicates were sometimes generated, make_cond_after_sjm() was called at the wrong position, and make_cond_after_sjm() was never actually considering the pushed-down SJM predicates. mysql-test/include/subquery_sj.inc Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Bug#50019: Wrong result for IN-query with materialization. Bug#52068: Optimizer generates invalid semijoin materialization plan Added new tests. mysql-test/r/subquery_mat_all.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Bug#50019: Wrong result for IN-query with materialization. Bug#52068: Optimizer generates invalid semijoin materialization plan Results for three new tests added. Some tests using semijoin materialization show that where clause has moved from the outer query into the materialized inner query. This is caused by the changed call to get_first() in eliminate_item_equal(). Ex: select * from ot where a in(select b from it where b>0); The clause "b>0" is now evaluated on the inner query materialization. Performance-wise this is never worse when using MaterializeScan and usually better for MaterializeLookup. For the latter strategy, the best possible solution is probably to evaluate the clause in both queries, this can be subject for a later feature development. Another test that applies the same condition to both the outer and the inner query is added, to show the plan for such types of queries. Earlier wrong semijoin materialization test results are corrected. sql/item.cc Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Calling new get_first() function instead of old. sql/item_cmpfunc.cc Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. New function Item_equal::get_first() that accepts as argument a field being substituted. sql/item_cmpfunc.h Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. New function Item_equal::get_first() that accepts as argument a field being substituted. sql/sql_select.cc Bug#45174: Incorrectly applied equality propagation caused wrong result on a query with a materialized semi-join. Bug#50019: Wrong result for IN-query with materialization. Bug#52068: Optimizer generates invalid semijoin materialization plan Setting fields first_sj_inner_tab and last_sj_inner_tab moved from setup_semijoin_dups_elimination() to get_best_combination(), so they are set as early as possible after join order optimization. In make_join_select(), the test that determined when to pushdown SJM-specific predicates was wrong, in addition to improving the comments. The logic of eliminate_item_equal() has been simplified and adjusted so that it generates equalities that are useful also when the semijoin materialization strategy is being used. Some simplification was possible by taking advantage of the new Item_equal::get_first() function. In sub_select_sjm(), moved code that initializes the scan over the materialized table so that it is now performed for each scan of table, instead of only for the first scan. In make_cond_for_table_from_pred(), a number of comments has been added, and TAB characters are replaced by spaces. In make_cond_after_sjm(), make sure that it handles equalities generated for semijoin materialization (with marker=3). Added comments and removed TAB characters.
[26 Oct 2010 14:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/121934 3270 Roy Lyseng 2010-10-26 Bug#52068: Optimizer generates invalid semijoin materialization plan When MaterializeScan semijoin strategy was used and there were one or more outer dependent tables before the semijoin tables, the scan over the materialized table was not properly reset for each row of the prefix outer tables. Example: suppose we have a join order: ot1 SJ-Mat-Scan(it2 it3) ot4 Notice that this is called a MaterializeScan, even though there is an outer table ahead of the materialized tables. Usually a MaterializeScan has the outer tables after the materialized table, but this is a special (but legal) case with outer dependent tables both before and after the materialized table. For each qualifying row from ot1, a new scan over the materialized table must be set up. The code failed to do that, so all scans after the first one returned zero rows from the materialized table. mysql-test/include/subquery_sj.inc Bug#52068: Optimizer generates invalid semijoin materialization plan Added new test. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result Bug#52068: Optimizer generates invalid semijoin materialization plan Earlier wrong semijoin materialization test results are corrected. sql/sql_select.cc Bug#52068: Optimizer generates invalid semijoin materialization plan In sub_select_sjm(), moved code that initializes the scan over the materialized table so that it is now performed for each scan of table, instead of only for the first scan.
[29 Oct 2010 14:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/122315 3270 Roy Lyseng 2010-10-29 Bug#52068: Optimizer generates invalid semijoin materialization plan When MaterializeScan semijoin strategy was used and there were one or more outer dependent tables before the semijoin tables, the scan over the materialized table was not properly reset for each row of the prefix outer tables. Example: suppose we have a join order: ot1 SJ-Mat-Scan(it2 it3) ot4 Notice that this is called a MaterializeScan, even though there is an outer table ahead of the materialized tables. Usually a MaterializeScan has the outer tables after the materialized table, but this is a special (but legal) case with outer dependent tables both before and after the materialized table. For each qualifying row from ot1, a new scan over the materialized table must be set up. The code failed to do that, so all scans after the first one returned zero rows from the materialized table. mysql-test/include/subquery_sj.inc Bug#52068: Optimizer generates invalid semijoin materialization plan Added new test. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result Bug#52068: Optimizer generates invalid semijoin materialization plan Earlier wrong semijoin materialization test results are corrected. sql/sql_select.cc Bug#52068: Optimizer generates invalid semijoin materialization plan In sub_select_sjm(), added code that resets the cursor over the materialized table when more than one scan is needed.
[1 Nov 2010 15:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/122443 3272 Roy Lyseng 2010-11-01 Bug#52068: Optimizer generates invalid semijoin materialization plan When MaterializeScan semijoin strategy was used and there were one or more outer dependent tables before the semijoin tables, the scan over the materialized table was not properly reset for each row of the prefix outer tables. Example: suppose we have a join order: ot1 SJ-Mat-Scan(it2 it3) ot4 Notice that this is called a MaterializeScan, even though there is an outer table ahead of the materialized tables. Usually a MaterializeScan has the outer tables after the materialized table, but this is a special (but legal) case with outer dependent tables both before and after the materialized table. For each qualifying row from ot1, a new scan over the materialized table must be set up. The code failed to do that, so all scans after the first one returned zero rows from the materialized table. mysql-test/include/subquery_sj.inc Bug#52068: Optimizer generates invalid semijoin materialization plan Added new test. mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result Bug#52068: Optimizer generates invalid semijoin materialization plan Earlier wrong semijoin materialization test results are corrected. sql/sql_select.cc Bug#52068: Optimizer generates invalid semijoin materialization plan In sub_select_sjm(), added code that resets the cursor over the materialized table when more than one scan is needed.
[13 Nov 2010 16:16]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (merge vers: 5.6.99-m5) (pib:21)
[13 Nov 2010 16:28]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)
[22 Nov 2010 1:26]
Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.
Description: The semijoin strategy MaterializeLookup requires that all outer correlated tables are before all inner tables in the join plan. The semijoin strategy MaterializeScan requires that all outer correlated tables are after all inner tables in the join plan. However, it is possible to generate a plan that has outer correlated tables both before and after the inner tables. This join plan causes wrong result (20 rows are output from a cross product of 2 by 8 rows). How to repeat: DROP TABLE IF EXISTS it2, it3; DROP TABLE IF EXISTS ot1, ot4; CREATE TABLE ot1(a INTEGER); INSERT INTO ot1 VALUES(5), (8); CREATE TABLE it2(a INTEGER); INSERT INTO it2 VALUES(9), (5), (1), (8); CREATE TABLE it3(a INTEGER); INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4); CREATE TABLE ot4(a INTEGER); INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1); SELECT * FROM ot1,ot4 WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); EXPLAIN SELECT * FROM ot1,ot4 WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); SET optimizer_switch='materialization=off'; SELECT * FROM ot1,ot4 WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); EXPLAIN SELECT * FROM ot1,ot4 WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); Suggested fix: Make sure that all outer correlated tables either precede or follow the inner tables in the join order, when a semijoin materialization strategy is considered.