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:
None 
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
Triage: Triaged: D2 (Serious)

[15 Mar 2010 15:49] Roy Lyseng
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.
[15 Mar 2010 17:05] Miguel Solorzano
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.