List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:March 21 2012 11:24am
Subject:bzr push into mysql-trunk branch (roy.lyseng:3889 to 3890) WL#5561
View as plain text  
 3890 Roy Lyseng	2012-03-21
      WL#5561 - Enable semi-join transformation with outer join queries
      
      Second worklog commit, after Guilhem's review.
      
      mysql-test/include/subquery_sj.inc
        Added a test case to demonstrate impact of table extension when
        setting up duplicate weedout for tables involved in outer join.
      
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
        Changed plan due to modifications to nested join advance and backout.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
        New results for the added test case.
      
      sql/item_func.cc
        Change a return to an old-style assert in Item_func::fix_fields.
      
      sql/sql_executor.cc
        Added a const modifier in do_sj_dups_weedout().
        Changed a comment in evaluate_null_complemented_join_record().
      
      sql/sql_optimizer.cc
        In optimize_semijoin_nests_for_materialization(), updated code
        according to review comments.
      
      sql/sql_planner.cc
        Optimize_table_order::check_interleaving_with_nj() and
        Optimize_table_order::backout_nj_sj_state() are updated and simplified
        according to review comments.
      
      sql/sql_select.cc
        Table extension code in duplicate weedout changed according to
        review comments (setup_semijoin_dups_elimination()).
        Comment added in setup_join_buffering(), according to review
        comments.
      
      sql/table.h
        Changed comments of new members according to review comments.
        Removed is_fully_covered() function, as it was barely used.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/item_func.cc
      sql/sql_executor.cc
      sql/sql_optimizer.cc
      sql/sql_planner.cc
      sql/sql_select.cc
      sql/table.h
 3889 Guilhem Bichot	2012-03-19
      Fix for BUG#13685026 ASSERTION CUR_SJ_INNER_TABLES == 0 IN
      OPTIMIZE_TABLE_ORDER::CHOOSE_TABLE_ORDER:
      cur_sj_inner_tables was not properly computed when
      the search depth is less than the number of tables.
     @ sql/sql_planner.cc
        Scenario of the bug follows.
        Search depth is 4, we have 5 tables (one outer "outr", and 4 sj-inner
        "aliasN").
        
        When search depth is >=5, we explore plans by traversing a tree of
        plans (of depth 5, because we have 5 tables). As we go from the root (no
        tables in the plan) to nodes (add more tables), we modify
        cur_sj_inner_tables, to represent the open semijoin nests in this
        partial plan; when we reach leaves, we have no open nests. When we go
        back towards the root, we enter and leave open nests, so we modify
        cur_sj_inner_tables; when we're back to the root (have explored all
        plans), we have no open nests, cur_sj_inner_tables is back to 0.
        
        With search depth =4, things work differently.
        We walk down the tree with depth 4. We select the best 4-table plan,
        which is alias1/4/5/3. We thus decide that alias1 will be the first
        table in the complete 5-table not-yet-determined plan.
        So we put alias1 as first table (search code for comment "select the
        first table in the optimal extension as most promising").
        And we then explore, with a depth of 4, the sub-tree rooted at
        alias1. We explore plan alias1/alias4/5/3/outr and choose
        it, then we explore plan alias1/alias5 and reject it for cost
        reasons. This has the following problems:
        - cur_sj_inner_tables is 0 when we start exploration of the sub-tree
        (this is the first oddity: alias1 is part of the plan, so the semijoin
        nest is open, but cur_sj_inner_tables is 0!)
        - we add alias5 ("push" it): this adds bits to cur_sj_inner_tables
        - we discard the plan, so we "pop" alias5: backout_nj_sj_state()
        does *not* rollback cur_sj_inner_tables to 0 because it does so only
        when popping the *first* table of the nest (here, alias1); as alias1
        is not popped in this algorithm (as it's "forever glued to the plan's
        first cell"), cur_sj_inner_tables remains non-zero (second oddity)
        - we come to the end of choose_plan(), and the assertion fails.
        
        The chosen solution is:
        - notice similarity between POSITION::dups_producing_tables and
        cur_sj_inner_tables
        - notice that POSITION::dups_producing_tables has no problem because
        it is attached to POSITION, and thus needs no "backing out" and is
        preserved when we stick alias1 into the first cell of the final plan:
        both oddities disappear.
        - thus, delete cur_sj_inner_tables and use dups_producing_tables
        instead. It makes one less state member to maintain.
        
        This required a few small changes:
        - dups_producing_tables was left uninitialized (i.e. random) if the
        query had no semijoin; as this patch uses this variable more (for
        example in the call to Loose_scan_opt.init(), which runs even if we
        have no semijoin nest), we need to have it always set, so we
        initialize it to 0 in the constructor of POSITION.
        - we now set sj_strategy to SJ_OPT_NONE in this same constructor,
        which allows removing three "else join->positions[idx].sj_strategy=
        SJ_OPT_NONE" branches from sql_planner.cc
        - the assertion on cur_sj_inner_tables==0 is now on
        dups_producing_tables of the last POSITION of the plan; it shouldn't
        be tested if the plan is partial (where it's legal to have inner
        tables not yet handled with some strategy as we haven't seen all
        tables).
        
        I wondered if cur_embedding_map has the same bug. The answer is that
        it used to have it, but that was fixed, see BUG#38795 (search for "Update
        the interleaving state after extending the current partial plan with a
        new table" in code). Thus, an assertion on this variable is added to
        consider_complete_plan().
        
        Now that cur_sj_inner_tables is gone, backout_nj_sj_state() does not
        do anything related to semijoin, so is renamed to backout_nj_state().
        And Loose_scan_opt::init(), which does not really need the map of inner
        tables but only needs to know if there are any, has his
        cur_sj_inner_tables argument changed to bool.
        
        Actually, dups_producing_tables and cur_sj_inner_tables are not
        exactly the same thing. When we have an inside-out plan (possibly
        suitable for loosescan, semijoin materialization scan, duplicates
        weedout), have passed the inner tables but not yet the outer tables,
        cur_sj_inner_tables is 0 (we're in no nest) but
        dups_producing_tables!=0 (because the passed inner tables are not yet
        handled with some strategy, as we need to see outer tables before
        making a decision).
        In advance_sj_state(), Firstmatch or Loosescan start a potential range
        if they see that cur_sj_inner_tables==0. Let's consider the following
        example of inside-out scenario: a query with two nests:
        select * from ot1,ot2 where ot1 SJ it1 and (ot1,ot2) SJ it2,
        and this order of tables:
        it2
        ot1
        it1
        ot2
        it2 is before ot1 and ot2 so this plan starts "inside-out".
        Let's assume that advance_sj_state() is at it1.
        Firstmatch is chosen for it1 (cur_sj_inner_tables==0). Then
        advance_sj_state() comes to ot2, and, in order to handle it2, chooses
        some inside-out strategy, for example duplicates weedout. So we have a
        dups-weedout range from it2 to ot2, and inside it, a firstmatch range
        (it1).
        Then fix_semijoin_strategies(), taking
        tables from last to first, will see that ot2 has dups-weedout, and
        will clear any strategy in the range [it2,ot2]: firstmatch will be
        wiped out (see end of fix_semijoin_strategies()) (I have verified this
        with a debugger and a real query). So the end result is
        that this firstmatch choice in advance_sj_state() was useless:
        implementation is not ready for overlapping ranges of 
        tables. The same is true of setup_semijoin_dups_elimination() (see how
        it "leaps" by lengths of pos->n_sj_tables, i.e. leaps from range's start
        to range's end, not setting up any strategy for any contained
        subrange).
        After this patch, Firstmatch will not be chosen for it1 in
        advance_sj_state(), because firstmatch will test
        dups_producing_tables==0 instead of cur_sj_inner_tables==0, and
        dups_producing_tables!=0 between inner and outer tables. The useless
        choice will not be made. It is a very good thing, because before this
        patch, the fact that firstmatch declared that it would handle it1,
        whereas it would be cancelled later in fix_semijoin_strategies(), was
        sort of "lying" to the further calls to advance_sj_state() for ot2.
        
        During debugging, it was observed that the optimizer trace gives
        confusing output when search depth is not infinite. Indeed, searching
        is done like this:
        a) explore 4-table plans (shown in trace)
        b) pick first table of best 4-table plan (not shown in trace)
        c) explore 4-table plans rooted at this first table: shown in trace,
        but the fact that they start with the first table is not shown.
        Thus the output gets confusing: (a) could show plan t1-t2-t3-t4, then
        (c) could show plan t2-t3-t4-t5, giving the impression that this plan
        was chosen over t1-t2-t3-t4, while in fact it's t1-t2-t3-t4-t5 which
        is chosen. This confusion is fortunately removed by printing the "plan
        prefix" when we add a table to the partial plan; this will be done in
        a separate patch (bug 13799348).
     @ sql/sql_planner.h
        removed cur_sj_inner_tables

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_planner.cc
      sql/sql_planner.h
      sql/sql_select.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-03-19 10:20:57 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-03-21 11:22:13 +0000
@@ -1486,6 +1486,43 @@ FROM   ot1
 
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
 
+CREATE TABLE t (
+  a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+
+CREATE TABLE t2 (
+  a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+
+CREATE TABLE t4 (
+  a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+
+CREATE TABLE v (
+  a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+
+let $query=
+SELECT *
+FROM t AS t1
+     LEFT JOIN
+      (t2
+       LEFT JOIN t AS t3
+       ON t3.a IN (SELECT a FROM t AS it)
+       JOIN t4
+       ON t4.a=100
+      )
+     ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+eval explain $query;
+eval $query;
+
+DROP TABLE t,t2,t4,v;
+
 --echo # End of WL#5561
 
 --echo #

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2012-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_all.result	2012-03-21 11:22:13 +0000
@@ -6864,14 +6864,14 @@ AND sq2_alias1.col_varchar_key > alias1.
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	80.00	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
 SELECT alias2.col_varchar_nokey
 FROM v1 AS alias1
 RIGHT JOIN t1 AS alias2 ON 1
@@ -6908,14 +6908,14 @@ AND sq2_alias1.col_varchar_key > alias1.
 ;
 EXPLAIN EXTENDED SELECT * FROM v2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	80.00	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #3 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
 SELECT * FROM v2;
 col_varchar_nokey
 d
@@ -6939,14 +6939,14 @@ WHERE sq2_alias1.pk != alias1.col_int_ke
 AND sq2_alias1.col_varchar_key > alias1.col_varchar_key
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	80.00	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
 SELECT alias2.col_varchar_nokey
 FROM t1 AS alias2
 LEFT JOIN v1 AS alias1 ON 1

=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result	2012-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_all_bka.result	2012-03-21 11:22:13 +0000
@@ -6865,14 +6865,14 @@ AND sq2_alias1.col_varchar_key > alias1.
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	80.00	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
 SELECT alias2.col_varchar_nokey
 FROM v1 AS alias1
 RIGHT JOIN t1 AS alias2 ON 1
@@ -6909,14 +6909,14 @@ AND sq2_alias1.col_varchar_key > alias1.
 ;
 EXPLAIN EXTENDED SELECT * FROM v2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	80.00	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #3 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
 SELECT * FROM v2;
 col_varchar_nokey
 d
@@ -6940,14 +6940,14 @@ WHERE sq2_alias1.pk != alias1.col_int_ke
 AND sq2_alias1.col_varchar_key > alias1.col_varchar_key
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	80.00	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	sq2_alias2	index	col_int_key	col_int_key	4	NULL	5	80.00	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
 SELECT alias2.col_varchar_nokey
 FROM t1 AS alias2
 LEFT JOIN v1 AS alias1 ON 1

=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result	2012-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result	2012-03-21 11:22:13 +0000
@@ -6865,14 +6865,14 @@ AND sq2_alias1.col_varchar_key > alias1.
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	ref	col_int_key	col_int_key	4	func	2	100.00	Using where; Using index; FirstMatch(t1)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_nokey	1	100.00	Using join buffer (Batched Key Access)
+1	PRIMARY	sq2_alias2	ref	col_int_key	col_int_key	4	func	2	100.00	Using where; Using index
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
 SELECT alias2.col_varchar_nokey
 FROM v1 AS alias1
 RIGHT JOIN t1 AS alias2 ON 1
@@ -6909,14 +6909,14 @@ AND sq2_alias1.col_varchar_key > alias1.
 ;
 EXPLAIN EXTENDED SELECT * FROM v2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	ref	col_int_key	col_int_key	4	func	2	100.00	Using where; Using index; FirstMatch(t1)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_nokey	1	100.00	Using join buffer (Batched Key Access)
+1	PRIMARY	sq2_alias2	ref	col_int_key	col_int_key	4	func	2	100.00	Using where; Using index
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #3 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
 SELECT * FROM v2;
 col_varchar_nokey
 d
@@ -6940,14 +6940,14 @@ WHERE sq2_alias1.pk != alias1.col_int_ke
 AND sq2_alias1.col_varchar_key > alias1.col_varchar_key
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	alias2	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	
-1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index
-1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x4)
-1	PRIMARY	sq2_alias2	ref	col_int_key	col_int_key	4	func	2	100.00	Using where; Using index; FirstMatch(t1)
+1	PRIMARY	t1	ALL	col_varchar_key	NULL	NULL	NULL	5	100.00	Start temporary
+1	PRIMARY	alias2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_nokey	1	100.00	Using join buffer (Batched Key Access)
+1	PRIMARY	sq2_alias2	ref	col_int_key	col_int_key	4	func	2	100.00	Using where; Using index
+1	PRIMARY	t1	index	NULL	col_varchar_key	7	NULL	5	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1276	Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
 Note	1276	Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note	1003	/* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
 SELECT alias2.col_varchar_nokey
 FROM t1 AS alias2
 LEFT JOIN v1 AS alias1 ON 1

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-03-21 11:22:13 +0000
@@ -4555,6 +4555,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-03-21 11:22:13 +0000
@@ -4552,6 +4552,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-03-21 11:22:13 +0000
@@ -4555,6 +4555,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-03-21 11:22:13 +0000
@@ -4555,6 +4555,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-03-21 11:22:13 +0000
@@ -4555,6 +4555,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-03-21 11:22:13 +0000
@@ -4622,6 +4622,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+3	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	1	
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-03-21 11:22:13 +0000
@@ -4564,6 +4564,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-03-21 11:22:13 +0000
@@ -4565,6 +4565,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-03-21 11:22:13 +0000
@@ -4565,6 +4565,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result	2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-03-21 11:22:13 +0000
@@ -4566,6 +4566,54 @@ a	a	a	a
 6	6	6	6
 7	NULL	NULL	NULL
 DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a	a	a	a
+1	NULL	NULL	NULL
+DROP TABLE t,t2,t4,v;
 # End of WL#5561
 #
 # Bug#48868: Left outer join in subquery causes segmentation fault in

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2012-02-13 11:57:09 +0000
+++ b/sql/item_func.cc	2012-03-21 11:22:13 +0000
@@ -172,8 +172,7 @@ Item_func::Item_func(THD *thd, Item_func
 bool
 Item_func::fix_fields(THD *thd, Item **ref)
 {
-  if (fixed)
-    return false;
+  DBUG_ASSERT(fixed == 0 || basic_const_item());
 
   Item **arg,**arg_end;
   uchar buff[STACK_BUFF_ALLOC];			// Max argument in function

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2012-02-13 11:57:09 +0000
+++ b/sql/sql_executor.cc	2012-03-21 11:22:13 +0000
@@ -2155,7 +2155,7 @@ int do_sj_dups_weedout(THD *thd, SJ_TMP_
   }
 
   // 2. Zero the null bytes 
-  uchar *nulls_ptr= ptr;
+  uchar *const nulls_ptr= ptr;
   if (sjtbl->null_bytes)
   {
     memset(ptr, 0, sjtbl->null_bytes);
@@ -2482,7 +2482,7 @@ evaluate_null_complemented_join_record(J
     mark_as_null_row(join_tab->table);       // For group by without error
     if (join_tab->flush_weedout_table && join_tab > first_inner_tab)
     {
-      // This qualifies as a new scan over the table
+      // sub_select() has not performed a reset for this table.
       do_sj_reset(join_tab->flush_weedout_table);
     }
     /* Check all attached conditions for inner table rows. */

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-03-14 12:03:54 +0000
+++ b/sql/sql_optimizer.cc	2012-03-21 11:22:13 +0000
@@ -2608,18 +2608,17 @@ static bool record_semijoin_nests(st_sel
 /**
   Assign each nested join structure a bit in nested_join_map.
 
-    Assign each nested join structure (except "confluent" ones - those that
-    embed only one element) a bit in nested_join_map.
-
-  @param join          Join being processed
   @param join_list     List of tables
   @param first_unused  Number of first unused bit in nested_join_map before the
                        call
 
   @note
     This function is called after simplify_joins(), when there are no
-    redundant nested joins, #non_confluent_nested_joins <= #tables_in_join so
-    we will not run out of bits in nested_join_map.
+    redundant nested joins.
+    We cannot have more nested joins in a query block than there are tables,
+    so as long as the number of bits in nested_join_map is not less than the
+    maximum number of tables in a query block, nested_join_map can never
+    overflow.
 
   @return
     First unused bit in nested_join_map after the call.
@@ -2645,16 +2644,25 @@ static uint build_bitmap_for_nested_join
       */
       if (table->join_cond())
       {
+        DBUG_ASSERT(first_unused < sizeof(nested_join_map)*8);
         nested_join->nj_map= (nested_join_map) 1 << first_unused++;
         nested_join->nj_total= nested_join->join_list.elements;
       }
       else if (table->sj_on_expr)
       {
-        NESTED_JOIN* outer_nest= table->embedding ?
-                                 table->embedding->nested_join : NULL;
+        NESTED_JOIN *const outer_nest=
+          table->embedding ? table->embedding->nested_join : NULL;
+        /*
+          The semi-join nest has already been counted into the table count
+          for the outer join nest as one table, so subtract 1 from the
+          table count.
+        */
         if (outer_nest)
-          outer_nest->nj_total+= (nested_join->join_list.elements-1);
+          outer_nest->nj_total+= (nested_join->join_list.elements - 1);
       }
+      else
+        DBUG_ASSERT(false);
+
       first_unused= build_bitmap_for_nested_joins(&nested_join->join_list,
                                                   first_unused);
     }
@@ -3411,9 +3419,8 @@ error:
 
   @param[inout] sj_nest Semi-join nest containing information about correlated
          expressions. Set nested_join->sjm.scan_allowed to TRUE if
-         MaterializeScan strategy allowed.
-
-  @return TRUE if materialization is allowed, FALSE otherwise
+         MaterializeScan strategy allowed. Set nested_join->sjm.lookup_allowed
+         to TRUE if MaterializeLookup strategy allowed
 
   @details
     This is a temporary fix for BUG#36752.
@@ -3488,6 +3495,43 @@ void semijoin_types_allow_materializatio
   sj_nest->nested_join->sjm.scan_allowed= all_are_fields;
   sj_nest->nested_join->sjm.lookup_allowed= !blobs_involved;
   DBUG_PRINT("info",("semijoin_types_allow_materialization: ok, allowed"));
+
+#if defined(UNUSED)
+  // @todo: When sub_select_sjm() can handle outer joins,
+  //        enable the lines marked SJM-DISABLED and disable the lines
+  //        marked SJM-ENABLED.
+  //        Note also next todo a few lines below.
+  //if (sj_nest->nested_join->sjm.scan_allowed)                   SJM-DISABLED
+  if (sj_nest->nested_join->sjm.scan_allowed ||                // SJM-ENABLED
+      sj_nest->nested_join->sjm.lookup_allowed)                // SJM-ENABLED
+  {
+    /*
+      If the semi-join contains dependencies to outer tables,
+      materialize-scan strategy cannot be used.
+    */
+    JOIN_TAB *jt;
+    for (jt= join->join_tab + join->const_tables;
+         jt < join->join_tab + join->tables;
+         jt++)
+    {
+      if ((jt->table->map & sj_nest->sj_inner_tables) &&
+          (jt->dependent & ~sj_nest->sj_inner_tables))
+        break;
+    }
+    //if (jt < join->join_tab + join->tables)                     SJM-DISABLED
+    //  sj_nest->nested_join->sjm.scan_allowed= false;            SJM-DISABLED
+    if (jt < join->join_tab + join->tables)                    // SJM-ENABLED
+    {                                                          // SJM-ENABLED
+      sj_nest->nested_join->sjm.scan_allowed= false;           // SJM-ENABLED
+      sj_nest->nested_join->sjm.lookup_allowed= false;         // SJM-ENABLED
+    }                                                          // SJM-ENABLED
+  }
+
+  DBUG_PRINT("info",("semijoin_types_allow_materialization: %s",
+                     sj_nest->nested_join->sjm.scan_allowed ||
+                     sj_nest->nested_join->sjm.lookup_allowed ?
+                     "ok, allowed" : "not allowed"));
+#endif
   DBUG_VOID_RETURN;
 }
 
@@ -3902,35 +3946,24 @@ static bool optimize_semijoin_nests_for_
       */
       semijoin_types_allow_materialization(sj_nest);
 
-      // @todo: When sub_select_sjm() can handle outer joins,
-      //        enable the lines marked SJM-DISABLED and disable the lines
-      //        marked SJM-ENABLED.
-      //        Note also next todo a few lines below.
-      //if (sj_nest->nested_join->sjm.scan_allowed)               SJM-DISABLED
-      if (sj_nest->nested_join->sjm.scan_allowed ||            // SJM-ENABLED
-          sj_nest->nested_join->sjm.lookup_allowed)            // SJM-ENABLED
+      if (sj_nest->embedding)
       {
+        DBUG_ASSERT(sj_nest->embedding->join_cond());
         /*
-          If the semi-join contains dependencies to outer tables,
-          materialize-scan strategy cannot be used.
+          There are two issues that prevent materialization strategy
+          from being used when a semi-join nest is on the inner side of
+          an outer join:
+          1. If the semi-join contains dependencies to outer tables,
+             materialize-scan strategy cannot be used.
+          2. sub_select_sjm() lacks support for setup of first_unmatched
+             and evaluation of triggered conditions, as in
+             evaluate_null_complemented_join_record().
+          Handle this by disabling materialization strategies:
         */
-        JOIN_TAB *jt;
-        for (jt= join->join_tab + join->const_tables;
-             jt < join->join_tab + join->tables;
-             jt++)
-        {
-          if ((jt->table->map & sj_nest->sj_inner_tables) &&
-              (jt->dependent & ~sj_nest->sj_inner_tables))
-            break;
-        }
-        //if (jt < join->join_tab + join->tables)                 SJM-DISABLED
-        //  sj_nest->nested_join->sjm.scan_allowed= false;        SJM-DISABLED
-        if (jt < join->join_tab + join->tables)                // SJM-ENABLED
-        {                                                      // SJM-ENABLED
-          sj_nest->nested_join->sjm.scan_allowed= false;       // SJM-ENABLED
-          sj_nest->nested_join->sjm.lookup_allowed= false;     // SJM-ENABLED
-        }                                                      // SJM-ENABLED
+        sj_nest->nested_join->sjm.scan_allowed= false;
+        sj_nest->nested_join->sjm.lookup_allowed= false;
       }
+
       if (sj_nest->nested_join->sjm.scan_allowed || 
           sj_nest->nested_join->sjm.lookup_allowed)
       {

=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc	2012-03-19 10:20:57 +0000
+++ b/sql/sql_planner.cc	2012-03-21 11:22:13 +0000
@@ -2753,20 +2753,12 @@ bool Optimize_table_order::check_interle
   */
   for (;next_emb; next_emb= next_emb->embedding)
   {
+    // Ignore join nests that are not outer joins.
     if (!next_emb->nested_join->nj_map)
       continue;
-    DBUG_ASSERT(next_emb->nested_join->nj_counter <
-                next_emb->nested_join->nj_total);
+
     next_emb->nested_join->nj_counter++;
-    if (next_emb->nested_join->nj_counter == 1)
-    {
-      /* 
-        next_emb is the first table inside a nested join we've "entered". In
-        the picture above, we're looking at the 'X' bracket. Don't exit yet as
-        X bracket might have Y pair bracket.
-      */
-      cur_embedding_map |= next_emb->nested_join->nj_map;
-    }
+    cur_embedding_map |= next_emb->nested_join->nj_map;
     
     if (next_emb->nested_join->nj_total != next_emb->nested_join->nj_counter)
       break;
@@ -3727,21 +3719,22 @@ void Optimize_table_order::backout_nj_st
 
   for (; last_emb != emb_sjm_nest; last_emb= last_emb->embedding)
   {
-    if (last_emb->nested_join->nj_map)
-    {
-      NESTED_JOIN *nest= last_emb->nested_join;
-      DBUG_ASSERT(nest->nj_counter > 0);
+    NESTED_JOIN *const nest= last_emb->nested_join;
 
-      bool was_fully_covered= nest->is_fully_covered();
+    // Ignore join nests that are not outer joins.
+    if (!nest->nj_map)
+      continue;
 
-      if (--nest->nj_counter == 0)
-        cur_embedding_map&= ~nest->nj_map;
+    DBUG_ASSERT(nest->nj_counter > 0);
 
-      if (!was_fully_covered)
-        break;
+    cur_embedding_map|= nest->nj_map;
+    bool was_fully_covered= nest->nj_total == nest->nj_counter;
 
-      cur_embedding_map|= nest->nj_map;
-    }
+    if (--nest->nj_counter == 0)
+      cur_embedding_map&= ~nest->nj_map;
+
+    if (!was_fully_covered)
+      break;
   }
 }
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-02-13 11:57:09 +0000
+++ b/sql/sql_select.cc	2012-03-21 11:22:13 +0000
@@ -534,15 +534,8 @@ static bool setup_semijoin_dups_eliminat
           }
         }
 
-        TABLE_LIST *first_embedding=
-          (join->join_tab+first_table)->table->pos_in_table_list->embedding;
-        if (first_embedding && first_embedding->sj_on_expr)
-          first_embedding= first_embedding->embedding;
-        TABLE_LIST *last_embedding=
-          last_sj_tab->table->pos_in_table_list->embedding;
-        if (last_embedding && last_embedding->sj_on_expr)
-          last_embedding= last_embedding->embedding;
-        if (first_embedding != last_embedding)
+        JOIN_TAB *const first_sj_tab= join->join_tab+first_table;
+        if (first_sj_tab->first_inner != last_sj_tab->first_inner)
         {
           /*
             The first duplicate weedout table is an outer table of an outer join
@@ -550,26 +543,22 @@ static bool setup_semijoin_dups_eliminat
             the outer join.
             In this case, we must assure that all the inner tables of the
             outer join is part of the duplicate weedout operation.
+            This is to assure that NULL-extension for inner tables of an
+            outer join is performed before duplicate elimination is performed,
+            otherwise we will have extra NULL-extended rows being output, which
+            should have been eliminated as duplicates.
           */
-          for (JOIN_TAB *tab= last_sj_tab + 1;
-               tab < join->join_tab + join->tables; tab++)
-          {
-            TABLE_LIST *embedding= tab->table->pos_in_table_list->embedding;
-            if (embedding && embedding->sj_on_expr)
-              embedding= embedding->embedding;
-            // We are past the range if the join nest immediately containing
-            // this table contains the first table as well:
-            if (embedding == first_embedding)
-              break;
-            while (embedding && embedding != first_embedding)
-              embedding= embedding->embedding;
-            // We are past the range if we never reached the join nest
-            // containing the first table:
-            if (embedding != first_embedding)
-              break;
-            // Extend the duplicate weedout range with this table:
-            last_sj_tab= tab;
-          }
+          JOIN_TAB *tab= last_sj_tab->first_inner;
+          /*
+            First, locate the table that is the first inner table of the
+            outer join operation that first_sj_tab is outer for.
+          */
+          while (tab->first_upper != NULL &&
+                 tab->first_upper != first_sj_tab->first_inner)
+            tab= tab->first_upper;
+          // Then, extend the range with all inner tables of the join nest:
+          if (tab->first_inner->last_inner > last_sj_tab)
+            last_sj_tab= tab->first_inner->last_inner;
         }
 
         SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
@@ -2288,8 +2277,22 @@ static bool setup_join_buffering(JOIN_TA
       tab->first_sj_inner_tab != (tab-1)->first_sj_inner_tab)     // 3
     prev_cache= NULL;
 
-  // @todo The following code eliminates use of join buffering when there is an
-  //       outer join operation and first match semi-join strategy is used.
+  /*
+    The following code eliminates use of join buffering when there is an
+    outer join operation and first match semi-join strategy is used, because:
+
+    Outer join needs a "match flag" to track what a row should be
+    NULL-complemented, such flag being attached to first inner table's cache
+    (tracks whether the cached row from outer table got a match, in which case
+    no NULL-complemented row is needed).
+
+    FirstMatch also needs a "match flag", such flag is attached to sj inner
+    table's cache (tracks whether the cached row from outer table already got
+    a first match in the sj-inner table, in which case we don't need to join
+    this cached row again)
+     - but a row in a cache has only one "match flag"
+     - so if "sj inner table"=="first inner", there is a problem. 
+  */
   if (tab_sj_strategy == SJ_OPT_FIRST_MATCH &&
       tab->is_inner_table_of_outer_join())
     goto no_join_cache;

=== modified file 'sql/table.h'
--- a/sql/table.h	2012-02-13 11:57:09 +0000
+++ b/sql/table.h	2012-03-21 11:22:13 +0000
@@ -2099,6 +2099,14 @@ struct Semijoin_mat_optimize
   Cost_estimate scan_cost;
 };
 
+/**
+  Struct st_nested_join is used to represent how tables are connected through
+  outer join operations and semi-join operations to form a query block.
+  Out of the parser, inner joins are also represented by st_nested_join
+  structs, but these are later flattened out by simplify_joins().
+  Some outer join nests are also flattened, when it can be determined that
+  they can be processed as inner joins instead of outer joins.
+*/
 typedef struct st_nested_join
 {
   List<TABLE_LIST>  join_list;       /* list of elements in the nested join */
@@ -2109,13 +2117,14 @@ typedef struct st_nested_join
     join nest. It is used exclusively within make_outerjoin_info().
    */
   struct st_join_table *first_nested;
-  /*
-    Number of tables administered by this nested join object for the sake
-    of cost analysis. Includes direct member tables as well as tables
-    included through semi-join nests.
+  /**
+    Number of tables and outer join nests administered by this nested join
+    object for the sake of cost analysis. Includes direct member tables as
+    well as tables included through semi-join nests, but notice that semi-join
+    nests themselves are not counted.
   */
   uint              nj_total;
-  /* 
+  /**
     Used to count tables in the nested join in 2 isolated places:
     1. In make_outerjoin_info(). 
     2. check_interleaving_with_nj/restore_prev_nj_state (these are called
@@ -2123,9 +2132,9 @@ typedef struct st_nested_join
     Before each use the counters are zeroed by reset_nj_counters.
   */
   uint              nj_counter;
-  /*
+  /**
     Bit identifying this nested join. Only nested joins representing the
-    outer join structure needs this, other nests have bit set to zero.
+    outer join structure need this, other nests have bit set to zero.
   */
   nested_join_map   nj_map;
   /*
@@ -2141,15 +2150,6 @@ typedef struct st_nested_join
   */
   List<Item>        sj_outer_exprs, sj_inner_exprs;
   Semijoin_mat_optimize sjm;
-  /**
-     True if this join nest node is completely covered by the query execution
-     plan. This means two things.
-
-     1. All tables on its @c join_list are covered by the plan.
-
-     2. All child join nest nodes are fully covered.
-   */
-  bool is_fully_covered() const { return nj_total == nj_counter; }
 } NESTED_JOIN;
 
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3889 to 3890) WL#5561Roy Lyseng21 Mar