List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:October 18 2010 7:55am
Subject:bzr push into mysql-next-mr-bugfixing branch (roy.lyseng:3262 to 3263)
Bug#31480
View as plain text  
 3263 Roy Lyseng	2010-10-15
      Bug#31480: Incorrect result for nested subquery when executed via semijoin
      
      This problem may occur when we have a query containing at least two nested
      subqueries; one subquery is transformed into a semijoin, and a subquery that
      is inner to the transformed subquery (and is not itself transformed away)
      contains outer references either to the transformed subquery or to it's
      immediate outer query block.
      
      When this situation happens, the transformed query and it's immediate
      outer query block is consolidated into one query block. In this process,
      involved tables may be renumbered, and resolved information for the inner
      expressions may change.
      
      Example with a query:
      
      select t1.a from t1
      where t1.a in (select t2.c from t2
                     where t2.d >= some(select t3.e from t3
                                        where t1.b=t3.e));
      
      Graph of select_lex objects and table objects for this query, before transformation:
      
       A - t1
        \
         B - t2
          \
           C - t3
      
      For shorthand, we denote the IN predicate the "outer subquery predicate"
      and the quantified comparison predicate the "inner subquery predicate".
      The outer subquery predicate contains select_lex B and the inner subquery
      predicate contains select_lex C.
      
      Now, semijoin transformation is applied to the outer subquery predicate. 
      Here is the query graph after transformation:
      
       A - t1, t2
        \
         C - t3
      
      After the transformation, used_tables information is wrong for the column t1.b.
      
      The problem is fixed by adding fix_after_pullout() functions for the
      subquery objects (Item_subselect, Item_in_subselect, Item_in_optimizer),
      that can propagate re-resolution down to subqueries that have expressions
      that are affected by the semijoin transformation.
      
      In addition, fix_after_pullout() is extended with another argument that
      identifies the select_lex object that is being removed. With the current use
      of fix_after_pullout(), this information is implicit, but it has to be explicit
      when also handling fields that are referenced within inner subqueries.
      
      The new fix_after_pullout() interface then becomes:
      
      fix_after_pullout(st_select_lex *parent_select, st_select_lex *removed_select,
                        Item **ref);
      
      fix_after_pullout() applied to an Item_field object only needs to handle cases
      where the depended_from or context->select_lex is equal to either parent_select
      or removed_select. If the field belongs to a table that is "inner" or "outer"
      compared to the select_lex level we are transforming, then no information needs
      to be updated.
      
      Note also that information about correlated outer columns must be recorded
      directly in a subquery predicate object. Suppose we have two select_lex objects
      A and B. B is part of a subquery that is evaluated on level A. The subquery
      has a predicate that contains a reference to a column that is resolved to a
      table t1 that belongs to select_lex A. In order to properly evaluate the
      subquery predicate, we need to make sure that a row from t1 is present.
      
      Thus, the fix_after_pullout() function for Item_field needs to propagate
      information about the table it is resolved within to an outer subquery
      predicate, if that predicate is evaluated on the same level as the table t1
      is resolved on.
      
      mysql-test/r/optimizer_switch.result
        Added result for test case for Bug#31480
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
        Minor plan change (WHERE clause moved from first to second table).
        The reason for this is that the IN subquery predicate object is updated
        with correct used tables information.
      
      mysql-test/t/optimizer_switch.test
        Added test case for Bug#31480
        Notice that the test case with an inner grouped subquery is wrong
        when semijoin optimization is turned on.
      
      sql/item.cc
        Item_field::resolved_used_tables() is implemented.
        Item_field::fix_after_pullout() is changed so that it behaves properly
        also for fields that are used in a scope that is inner to the select_lex
        being removed. Also update used_tables information for subqueries that
        are outer-correlated with respect to this field.
        Updated fix_after_pullout() in conformance with new interface.
      
      sql/item.h
        Changed interface of fix_after_pullout().
        Added interface for resolved_used_tables(). This function returns
        used table information for the level on which a field's table is resolved,
        in contrast to used_tables() which returns OUTER_REF_TABLE_BIT if
        the field is an outer reference.
      
      sql/item_cmpfunc.cc
        Necessary changes to fix_after_pullout().
        Item_in_optimizer::fix_after_pullout() resolves itself by dispatching
        to the wrapped Item_in_subselect object.
      
      sql/item_cmpfunc.h
        Added fix_after_pullout() to class Item_in_optimizer.
      
      sql/item_func.cc
        Necessary changes to fix_after_pullout().
      
      sql/item_func.h
        Changed interface of fix_after_pullout().
      
      sql/item_row.cc
        Necessary changes to fix_after_pullout().
      
      sql/item_row.h
        Changed interface of fix_after_pullout().
      
      sql/item_subselect.cc
        Item_subselect::fix_after_pullout() resolves all expressions referred
        from the inner query specification objects (select_lex objects).
        Item_in_subselect::fix_after_pullout() dispatches the query expression
        resolution to Item_subselect::fix_after_pullout(), but needs to resolve
        its left expression explicitly.
      
      sql/item_subselect.h
        Added fix_after_pullout() to classes Item_subselect and Item_in_subselect.
      
      sql/sql_select.cc
        Interface change to fix_list_after_tbl_changes().
        Also make sure that transformed-away select_lex object is removed before
        calling fix_after_pullout(), so that the function operates on a chain
        of select_lex objects that is valid after semijoin transformation.

    modified:
      mysql-test/r/optimizer_switch.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/t/optimizer_switch.test
      sql/item.cc
      sql/item.h
      sql/item_cmpfunc.cc
      sql/item_cmpfunc.h
      sql/item_func.cc
      sql/item_func.h
      sql/item_row.cc
      sql/item_row.h
      sql/item_subselect.cc
      sql/item_subselect.h
      sql/sql_select.cc
 3262 Jorgen Loland	2010-10-13
      Bug#52329: Wrong result: subquery materialization, IN, non-null 
                 field followed by nullable
      
      Consider a query 
      
      SELECT * FROM t1
      WHERE (a1, a2) IN (
      SELECT b1, b2 FROM t2 ...)
      
      When solved using materialized subselect, we check if a record
      from t1 is part of the result set by constructing a key from the
      a1 and a2 values and perform a lookup based on that key. If a1
      and a2 are CHAR(3) with values foo and bar, the KEY is "foobar".
      If a2 is NULLable, the null-byte should be the first byte for this
      field's part of the KEY ("foo<nullbyte_a2>bar").
      
      Before, the null-byte pointer for each key field wrongly pointed
      to the first byte of the KEY instead of the first byte of the key
      part. Thus, when setting the null byte for a2 above, the first byte
      of a1 was wrongly updated: "0oo bar" ('f' in foo replaced
      with null-byte of a2, and the null-byte of a2 not set)
      
      This patch sets the null-pointer for each key part to the first
      bit of that field's part of KEY, not the first bit of KEY.
      
      The bug was masked if the first field was NOT NULL. The bug also 
      applied to semi-join materialization since the code has been 
      copied (also fixed by this patch).
     @ mysql-test/include/subquery_sj.inc
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_all.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_dupsweed.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_firstmatch.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_loosescan.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_mat.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_none.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Add test for BUG52329
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Add test for BUG52329
     @ sql/item_subselect.cc
        subselect_hash_sj_engine::init_permanent():
        Make the null-pointer for each key part point to the first
        byte of that field's part of KEY, not the first byte of KEY.
     @ sql/sql_select.cc
        setup_sj_materialization():
        Make the null-pointer for each key part point to the first
        byte of that field's part of KEY, not the first byte of KEY.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      sql/item_subselect.cc
      sql/sql_select.cc
=== modified file 'mysql-test/r/optimizer_switch.result'
--- a/mysql-test/r/optimizer_switch.result	2010-08-19 07:10:58 +0000
+++ b/mysql-test/r/optimizer_switch.result	2010-10-15 10:32:50 +0000
@@ -204,3 +204,345 @@ SET optimizer_switch="default";
 call run_n_times(1);
 DROP PROCEDURE run_n_times;
 DROP TABLE it, ot;
+# 
+# BUG#31480: Incorrect result for nested subquery when executed via semijoin
+# 
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL);
+CREATE TABLE t3 (e INT NOT NULL);
+CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL);
+INSERT INTO t1 VALUES (1,10);
+INSERT INTO t1 VALUES (2,10);
+INSERT INTO t1 VALUES (1,20);
+INSERT INTO t1 VALUES (2,20);
+INSERT INTO t1 VALUES (3,20);
+INSERT INTO t1 VALUES (2,30);
+INSERT INTO t1 VALUES (4,40);
+INSERT INTO t2 VALUES (2,10);
+INSERT INTO t2 VALUES (2,20);
+INSERT INTO t2 VALUES (4,10);
+INSERT INTO t2 VALUES (5,10);
+INSERT INTO t2 VALUES (3,20);
+INSERT INTO t2 VALUES (2,40);
+INSERT INTO t3 VALUES (10);
+INSERT INTO t3 VALUES (30);
+INSERT INTO t3 VALUES (10);
+INSERT INTO t3 VALUES (20);
+INSERT INTO t4 VALUES (2,10);
+INSERT INTO t4 VALUES (2,10);
+INSERT INTO t4 VALUES (3,10);
+INSERT INTO t4 VALUES (4,10);
+INSERT INTO t4 VALUES (4,20);
+INSERT INTO t4 VALUES (4,20);
+# Reference to the parent query block (used tables was wrong)
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+# Subquery with GROUP BY and HAVING
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+GROUP BY f
+HAVING ta.a=tc.f));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+GROUP BY f
+HAVING ta.a=tc.f));
+a	b
+2	10
+2	20
+3	20
+2	30
+set @@optimizer_switch='materialization=off,semijoin=on';
+# The query result with semijoin is WRONG
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+GROUP BY f
+HAVING ta.a=tc.f));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+GROUP BY f
+HAVING ta.a=tc.f));
+a	b
+# Subquery with ORDER BY and LIMIT
+set @@optimizer_switch='materialization=off,semijoin=off';
+# NOTE: The ordered subquery should have a LIMIT clause to make sense
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d IN (SELECT g FROM t4 as tc
+WHERE ta.a=tc.f
+ORDER BY tc.f));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d IN (SELECT g FROM t4 as tc
+WHERE ta.a=tc.f
+ORDER BY tc.f));
+a	b
+2	10
+2	20
+2	30
+4	40
+set @@optimizer_switch='materialization=off,semijoin=on';
+# The query result with semijoin is WRONG
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d IN (SELECT g FROM t4 as tc
+WHERE ta.a=tc.f
+ORDER BY tc.f));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d IN (SELECT g FROM t4 as tc
+WHERE ta.a=tc.f
+ORDER BY tc.f));
+a	b
+2	10
+2	20
+2	30
+4	40
+# Reference to the transformed-away query block (dependency was wrong)
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE tb.d=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE tb.d=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+4	40
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE tb.d=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE tb.d=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+4	40
+# Reference above the parent query block (should not be affected)
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE t.b=tc.e)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+3	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE t.b=tc.e)));
+a	b
+1	10
+1	20
+4	40
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE t.b=tc.e)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
+2	DEPENDENT SUBQUERY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+WHERE t.b=tc.e)));
+a	b
+1	10
+1	20
+4	40
+# EXISTS with reference to the parent query block
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE EXISTS (SELECT * FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE EXISTS (SELECT * FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE EXISTS (SELECT * FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE EXISTS (SELECT * FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+2	30
+# Scalar subquery with reference to the parent query block
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	10
+2	20
+3	20
+# Combine scalar subquery with quantified comparison subquery
+set @@optimizer_switch='materialization=off,semijoin=off';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE (SELECT MIN(e) FROM t3 as tc
+WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
+2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
+4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE (SELECT MIN(e) FROM t3 as tc
+WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	20
+2	30
+set @@optimizer_switch='materialization=off,semijoin=on';
+EXPLAIN SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE (SELECT MIN(e) FROM t3 as tc
+WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
+1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (BNL, regular buffers)
+4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+WHERE (SELECT MIN(e) FROM t3 as tc
+WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+WHERE ta.b=tc.e));
+a	b
+2	20
+2	30
+DROP TABLE t1, t2, t3, t4;
+set @@optimizer_switch='default';
+# End of BUG#31480

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2010-10-15 10:32:50 +0000
@@ -3784,8 +3784,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2010-10-15 10:32:50 +0000
@@ -3783,8 +3783,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3787,8 +3787,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3787,8 +3787,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2010-10-15 10:32:50 +0000
@@ -3784,8 +3784,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2010-10-15 10:32:50 +0000
@@ -3788,8 +3788,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3792,8 +3792,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3792,8 +3792,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2010-10-15 10:32:50 +0000
@@ -3794,8 +3794,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-10-15 10:32:50 +0000
@@ -3798,8 +3798,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2010-10-13 13:27:36 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-10-15 10:32:50 +0000
@@ -3798,8 +3798,8 @@ SELECT t1field
 FROM t1
 WHERE t1field IN (SELECT v1field FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
-1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using where; Using index
 4	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 CREATE PROCEDURE p1() 
 BEGIN 

=== modified file 'mysql-test/t/optimizer_switch.test'
--- a/mysql-test/t/optimizer_switch.test	2010-08-19 07:10:58 +0000
+++ b/mysql-test/t/optimizer_switch.test	2010-10-15 10:32:50 +0000
@@ -209,3 +209,199 @@ DROP PROCEDURE run_n_times;
 DROP TABLE it, ot;
 
 # End of Bug#50489
+
+--echo # 
+--echo # BUG#31480: Incorrect result for nested subquery when executed via semijoin
+--echo # 
+
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL);
+CREATE TABLE t3 (e INT NOT NULL);
+CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL);
+
+INSERT INTO t1 VALUES (1,10);
+INSERT INTO t1 VALUES (2,10);
+INSERT INTO t1 VALUES (1,20);
+INSERT INTO t1 VALUES (2,20);
+INSERT INTO t1 VALUES (3,20);
+INSERT INTO t1 VALUES (2,30);
+INSERT INTO t1 VALUES (4,40);
+
+INSERT INTO t2 VALUES (2,10);
+INSERT INTO t2 VALUES (2,20);
+INSERT INTO t2 VALUES (4,10);
+INSERT INTO t2 VALUES (5,10);
+INSERT INTO t2 VALUES (3,20);
+INSERT INTO t2 VALUES (2,40);
+
+INSERT INTO t3 VALUES (10);
+INSERT INTO t3 VALUES (30);
+INSERT INTO t3 VALUES (10);
+INSERT INTO t3 VALUES (20);
+
+INSERT INTO t4 VALUES (2,10);
+INSERT INTO t4 VALUES (2,10);
+INSERT INTO t4 VALUES (3,10);
+INSERT INTO t4 VALUES (4,10);
+INSERT INTO t4 VALUES (4,20);
+INSERT INTO t4 VALUES (4,20);
+
+--echo # Reference to the parent query block (used tables was wrong)
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+                                  WHERE ta.b=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Subquery with GROUP BY and HAVING
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
+                                  GROUP BY f
+                                  HAVING ta.a=tc.f));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+--echo # The query result with semijoin is WRONG
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Subquery with ORDER BY and LIMIT
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+--echo # NOTE: The ordered subquery should have a LIMIT clause to make sense
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d IN (SELECT g FROM t4 as tc
+                                  WHERE ta.a=tc.f
+                                  ORDER BY tc.f));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+--echo # The query result with semijoin is WRONG
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Reference to the transformed-away query block (dependency was wrong)
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+                                  WHERE tb.d=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Reference above the parent query block (should not be affected)
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS t
+WHERE t.a NOT IN (SELECT a FROM t1 AS ta
+                  WHERE ta.a IN (SELECT c FROM t2 AS tb
+                                 WHERE tb.d >= SOME(SELECT e FROM t3 as tc
+                                                    WHERE t.b=tc.e)));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # EXISTS with reference to the parent query block
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE EXISTS (SELECT * FROM t3 as tc
+                             WHERE ta.b=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Scalar subquery with reference to the parent query block
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
+                             WHERE ta.b=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo # Combine scalar subquery with quantified comparison subquery
+
+set @@optimizer_switch='materialization=off,semijoin=off';
+
+let query=
+SELECT * FROM t1 AS ta
+WHERE ta.a IN (SELECT c FROM t2 AS tb
+               WHERE (SELECT MIN(e) FROM t3 as tc
+                      WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
+                                              WHERE ta.b=tc.e));
+
+eval EXPLAIN $query;
+eval $query;
+
+set @@optimizer_switch='materialization=off,semijoin=on';
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2, t3, t4;
+
+set @@optimizer_switch='default';
+
+--echo # End of BUG#31480

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-09-28 15:17:29 +0000
+++ b/sql/item.cc	2010-10-15 10:32:50 +0000
@@ -2314,21 +2314,75 @@ table_map Item_field::used_tables() cons
 }
 
 
-void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+table_map Item_field::resolved_used_tables() const
 {
-  if (new_parent == depended_from)
-    depended_from= NULL;
-  Name_resolution_context *ctx= new Name_resolution_context();
-  ctx->outer_context= NULL; // We don't build a complete name resolver
-  ctx->table_list= NULL;    // We rely on first_name_resolution_table instead
-  ctx->select_lex= new_parent;
-  ctx->first_name_resolution_table= context->first_name_resolution_table;
-  ctx->last_name_resolution_table=  context->last_name_resolution_table;
-  ctx->error_processor=             context->error_processor;
-  ctx->error_processor_data=        context->error_processor_data;
-  ctx->resolve_in_select_list=      context->resolve_in_select_list;
-  ctx->security_ctx=                context->security_ctx;
-  this->context=ctx;
+  if (field->table->const_table)
+    return 0;					// const item
+  return field->table->map;
+}
+
+void Item_field::fix_after_pullout(st_select_lex *parent_select,
+                                   st_select_lex *removed_select,
+                                   Item **ref)
+{
+  if (context->select_lex == removed_select ||
+      context->select_lex == parent_select)
+  {
+    if (parent_select == depended_from)
+      depended_from= NULL;
+    Name_resolution_context *ctx= new Name_resolution_context();
+    ctx->outer_context= NULL; // We don't build a complete name resolver
+    ctx->table_list= NULL;    // We rely on first_name_resolution_table instead
+    ctx->select_lex= parent_select;
+    ctx->first_name_resolution_table= context->first_name_resolution_table;
+    ctx->last_name_resolution_table=  context->last_name_resolution_table;
+    ctx->error_processor=             context->error_processor;
+    ctx->error_processor_data=        context->error_processor_data;
+    ctx->resolve_in_select_list=      context->resolve_in_select_list;
+    ctx->security_ctx=                context->security_ctx;
+    this->context=ctx;
+  }
+  else
+  {
+    /*
+      The definition scope of this field item reference is inner to the removed
+      select_lex object.
+      No new resolution is needed, but we may need to update the dependency.
+    */
+    if (removed_select == depended_from)
+      depended_from= parent_select;
+  }
+
+  if (depended_from)
+  {
+    /*
+      Refresh used_tables information for subqueries between the definition
+      scope and resolution scope of the field item reference.
+    */
+    st_select_lex *child_select= context->select_lex;
+
+    if (child_select->outer_select() != depended_from)
+    {
+      /*
+        The subquery on this level is outer-correlated with respect to the field
+      */
+      Item_subselect *subq_predicate= child_select->master_unit()->item;
+      subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT;
+    }
+
+    while (child_select->outer_select() != depended_from)
+      child_select= child_select->outer_select();
+
+    /*
+      child_select is select_lex immediately inner to the depended_from level.
+      Now, locate the subquery predicate that contains this select_lex and
+      update used tables information.
+    */
+    Item_subselect *subq_predicate= child_select->master_unit()->item;
+
+    subq_predicate->used_tables_cache|= this->resolved_used_tables();
+    subq_predicate->const_item_cache&= this->const_item();
+  }
 }
 
 
@@ -6804,26 +6858,31 @@ bool Item_outer_ref::fix_fields(THD *thd
   return err;
 }
 
-void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_outer_ref::fix_after_pullout(st_select_lex *parent_select,
+                                       st_select_lex *removed_select,
+                                       Item **ref_arg)
 {
-  if (depended_from == new_parent)
+  if (depended_from == parent_select)
   {
-    *ref= outer_ref;
-    outer_ref->fix_after_pullout(new_parent, ref);
+    *ref_arg= outer_ref;
+    outer_ref->fix_after_pullout(parent_select, removed_select, ref_arg);
   }
   // @todo: Find an actual test case for this funcion.
   DBUG_ASSERT(false);
 }
 
-void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
+void Item_ref::fix_after_pullout(st_select_lex *parent_select,
+                                 st_select_lex *removed_select,
+                                 Item **ref_arg)
 {
   // @todo: Find an actual test case where depended_from == new_parent.
-  DBUG_ASSERT(depended_from != new_parent);
-  if (depended_from == new_parent)
+  DBUG_ASSERT(depended_from != parent_select);
+  if (depended_from == parent_select)
     depended_from= NULL;
 }
 
-void Item_direct_view_ref::fix_after_pullout(st_select_lex *new_parent,
+void Item_direct_view_ref::fix_after_pullout(st_select_lex *parent_select,
+                                             st_select_lex *removed_select,
                                              Item **refptr)
 {
   DBUG_EXECUTE("where",
@@ -6831,11 +6890,11 @@ void Item_direct_view_ref::fix_after_pul
                            "Item_direct_view_ref::fix_after_pullout",
                            QT_ORDINARY););
 
-  (*ref)->fix_after_pullout(new_parent, ref);
+  (*ref)->fix_after_pullout(parent_select, removed_select, ref);
 
-  // @todo: Find an actual test case where depended_from == new_parent.
-  DBUG_ASSERT(depended_from != new_parent);
-  if (depended_from == new_parent)
+  // @todo: Find an actual test case where depended_from == parent_select.
+  DBUG_ASSERT(depended_from != parent_select);
+  if (depended_from == parent_select)
     depended_from= NULL;
 }
 

=== modified file 'sql/item.h'
--- a/sql/item.h	2010-09-07 19:07:18 +0000
+++ b/sql/item.h	2010-10-15 10:32:50 +0000
@@ -592,12 +592,20 @@ public:
   virtual void make_field(Send_field *field);
   Field *make_string_field(TABLE *table);
   virtual bool fix_fields(THD *, Item **);
-  /*
-    Fix after some tables has been pulled out. Basically re-calculate all
-    attributes that are dependent on the tables.
-  */
-  virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {};
-
+  /**
+    Fix after tables have been moved from one select_lex level to the parent
+    level, e.g by semijoin conversion.
+    Basically re-calculate all attributes dependent on the tables.
+
+    @param parent_select  select_lex that tables are moved to.
+    @param removed_select select_lex that tables are moved away from,
+                          child of parent_select.
+    @param ref            updated with new ref whenever the function substitutes
+                          this item with another.
+  */
+  virtual void fix_after_pullout(st_select_lex *parent_select,
+                                 st_select_lex *removed_select,
+                                 Item **ref) {};
   /*
     should be used in case where we are sure that we do not need
     complete fix_fields() procedure.
@@ -1753,11 +1761,16 @@ public:
   bool send(Protocol *protocol, String *str_arg);
   void reset_field(Field *f);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   void make_field(Send_field *tmp_field);
   int save_in_field(Field *field,bool no_conversions);
   void save_org_in_field(Field *field);
   table_map used_tables() const;
+  /*
+    Return used table information for the level on which this table is resolved.
+  */
+  table_map resolved_used_tables() const;
   enum Item_result result_type () const
   {
     return field->result_type();
@@ -2573,7 +2586,8 @@ public:
   bool send(Protocol *prot, String *tmp);
   void make_field(Send_field *field);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   int save_in_field(Field *field, bool no_conversions);
   void save_org_in_field(Field *field);
   enum Item_result result_type () const { return (*ref)->result_type(); }
@@ -2712,7 +2726,8 @@ public:
   {}
 
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   bool eq(const Item *item, bool binary_cmp) const;
   Item *get_tmp_table_item(THD *thd)
   {
@@ -2769,7 +2784,8 @@ public:
     outer_ref->save_org_in_field(result_field);
   }
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   table_map used_tables() const
   {
     return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-09-28 15:17:29 +0000
+++ b/sql/item_cmpfunc.cc	2010-10-15 10:32:50 +0000
@@ -1770,6 +1770,27 @@ bool Item_in_optimizer::fix_fields(THD *
 }
 
 
+void Item_in_optimizer::fix_after_pullout(st_select_lex *parent_select,
+                                          st_select_lex *removed_select,
+                                          Item **ref)
+{
+  used_tables_cache=0;
+  not_null_tables_cache= 0;
+  const_item_cache= 1;
+
+  /*
+    No need to call fix_after_pullout() on args[0] (ie left expression),
+    as Item_in_subselect::fix_after_pullout() will do this.
+    So, just forward the call to the Item_in_subselect object.
+  */
+
+  args[1]->fix_after_pullout(parent_select, removed_select, &args[1]);
+
+  used_tables_cache|= args[1]->used_tables();
+  not_null_tables_cache|= args[1]->not_null_tables();
+  const_item_cache&= args[1]->const_item();
+}
+
 /**
    The implementation of optimized \<outer expression\> [NOT] IN \<subquery\>
    predicates. The implementation works as follows.
@@ -1840,6 +1861,7 @@ bool Item_in_optimizer::fix_fields(THD *
      @see Item_in_subselect::val_bool()
      @see Item_is_not_null_test::val_int()
  */
+
 longlong Item_in_optimizer::val_int()
 {
   bool tmp;
@@ -4337,7 +4359,9 @@ Item_cond::fix_fields(THD *thd, Item **r
 }
 
 
-void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_cond::fix_after_pullout(st_select_lex *parent_select,
+                                  st_select_lex *removed_select,
+                                  Item **ref)
 {
   List_iterator<Item> li(list);
   Item *item;
@@ -4351,7 +4375,7 @@ void Item_cond::fix_after_pullout(st_sel
   while ((item=li++))
   {
     table_map tmp_table_map;
-    item->fix_after_pullout(new_parent, li.ref());
+    item->fix_after_pullout(parent_select, removed_select, li.ref());
     item= *li.ref();
     used_tables_cache|= item->used_tables();
     const_item_cache&= item->const_item();

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2010-08-12 00:26:10 +0000
+++ b/sql/item_cmpfunc.h	2010-10-15 10:32:50 +0000
@@ -274,6 +274,8 @@ public:
   { with_subselect= TRUE; }
   bool fix_fields(THD *, Item **);
   bool fix_left(THD *thd, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   bool is_null();
   longlong val_int();
   void cleanup();
@@ -1513,7 +1515,8 @@ public:
     list.prepand(nlist);
   }
   bool fix_fields(THD *, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
 
   enum Type type() const { return COND_ITEM; }
   List<Item>* argument_list() { return &list; }

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2010-08-30 08:40:42 +0000
+++ b/sql/item_func.cc	2010-10-15 10:32:50 +0000
@@ -226,7 +226,9 @@ Item_func::fix_fields(THD *thd, Item **r
 }
 
 
-void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func::fix_after_pullout(st_select_lex *parent_select,
+                                  st_select_lex *removed_select,
+                                  Item **ref)
 {
   Item **arg,**arg_end;
 
@@ -237,7 +239,7 @@ void Item_func::fix_after_pullout(st_sel
   {
     for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
     {
-      (*arg)->fix_after_pullout(new_parent, arg);
+      (*arg)->fix_after_pullout(parent_select, removed_select, arg);
       Item *item= *arg;
 
       used_tables_cache|=     item->used_tables();

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2010-07-13 17:29:44 +0000
+++ b/sql/item_func.h	2010-10-15 10:32:50 +0000
@@ -120,7 +120,8 @@ public:
   // Constructor used for Item_cond_and/or (see Item comment)
   Item_func(THD *thd, Item_func *item);
   bool fix_fields(THD *, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   table_map used_tables() const;
   table_map not_null_tables() const;
   void update_used_tables();

=== modified file 'sql/item_row.cc'
--- a/sql/item_row.cc	2010-07-13 17:29:44 +0000
+++ b/sql/item_row.cc	2010-10-15 10:32:50 +0000
@@ -138,13 +138,15 @@ void Item_row::update_used_tables()
   }
 }
 
-void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_row::fix_after_pullout(st_select_lex *parent_select,
+                                 st_select_lex *removed_select,
+                                 Item **ref)
 {
   used_tables_cache= 0;
   const_item_cache= 1;
   for (uint i= 0; i < arg_count; i++)
   {
-    items[i]->fix_after_pullout(new_parent, &items[i]);
+    items[i]->fix_after_pullout(parent_select, removed_select, &items[i]);
     used_tables_cache|= items[i]->used_tables();
     const_item_cache&= items[i]->const_item();
   }

=== modified file 'sql/item_row.h'
--- a/sql/item_row.h	2010-07-13 17:29:44 +0000
+++ b/sql/item_row.h	2010-10-15 10:32:50 +0000
@@ -63,7 +63,8 @@ public:
     return 0;
   };
   bool fix_fields(THD *thd, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   void cleanup();
   void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields);
   table_map used_tables() const { return used_tables_cache; };

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-10-13 13:27:36 +0000
+++ b/sql/item_subselect.cc	2010-10-15 10:32:50 +0000
@@ -319,6 +319,69 @@ bool Item_subselect::exec()
 }
 
 
+/**
+  Fix used tables information for a subquery after query transformations.
+  Common actions for all predicates involving subqueries.
+  Most actions here involve re-resolving information for conditions
+  and items belonging to the subquery.
+  Notice that the usage information from underlying expressions is not
+  propagated to the subquery predicate, as it belongs to inner layers
+  of the query operator structure.
+  However, when underlying expressions contain outer references into
+  a select_lex on this level, the relevant information must be updated
+  when these expressions are resolved.
+*/
+
+void Item_subselect::fix_after_pullout(st_select_lex *parent_select,
+                                       st_select_lex *removed_select,
+                                       Item **ref)
+
+{
+  /* Clear usage information for this subquery predicate object */
+  used_tables_cache= 0;
+  const_item_cache= 1;
+
+  /*
+    Go through all query specification objects of the subquery and re-resolve
+    all relevant expressions belonging to them.
+  */
+  for (SELECT_LEX *sel= unit->first_select(); sel; sel= sel->next_select())
+  {
+    if (sel->where)
+      sel->where->fix_after_pullout(parent_select, removed_select,
+                                    &sel->where);
+
+    if (sel->having)
+      sel->having->fix_after_pullout(parent_select, removed_select,
+                                     &sel->having);
+
+    List_iterator<Item> li(sel->item_list);
+    Item *item;
+    while ((item=li++))
+      item->fix_after_pullout(parent_select, removed_select, li.ref());
+
+    /*
+      No need to call fix_after_pullout() for outer-join conditions, as these
+      cannot have outer references.
+    */
+
+    /* Re-resolve ORDER BY and GROUP BY fields */
+
+    for (ORDER *order= (ORDER*) sel->order_list.first;
+         order;
+         order= order->next)
+      (*order->item)->fix_after_pullout(parent_select, removed_select,
+                                        order->item);
+
+    for (ORDER *group= (ORDER*) sel->group_list.first;
+         group;
+         group= group->next)
+      (*group->item)->fix_after_pullout(parent_select, removed_select,
+                                        group->item);
+  }
+}
+
+
 /*
   Compute the IN predicate if the left operand's cache changed.
 */
@@ -1820,6 +1883,19 @@ bool Item_in_subselect::fix_fields(THD *
 }
 
 
+void Item_in_subselect::fix_after_pullout(st_select_lex *parent_select,
+                                          st_select_lex *removed_select,
+                                          Item **ref)
+{
+  Item_subselect::fix_after_pullout(parent_select, removed_select, ref);
+
+  left_expr->fix_after_pullout(parent_select, removed_select, &left_expr);
+
+  used_tables_cache|= left_expr->used_tables();
+  const_item_cache&= left_expr->const_item();
+}
+
+
 /**
   Try to create an engine to compute the subselect via materialization,
   and if this fails, revert to execution via the IN=>EXISTS transformation.

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2010-07-26 11:34:07 +0000
+++ b/sql/item_subselect.h	2010-10-15 10:32:50 +0000
@@ -124,6 +124,8 @@ public:
     return null_value;
   }
   bool fix_fields(THD *thd, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   virtual bool exec();
   virtual void fix_length_and_dec();
   table_map used_tables() const;
@@ -170,6 +172,9 @@ public:
   friend bool Item_field::fix_fields(THD *, Item **);
   friend int  Item_field::fix_outer_field(THD *, Field **, Item **);
   friend bool Item_ref::fix_fields(THD *, Item **);
+  friend void Item_field::fix_after_pullout(st_select_lex *parent_select,
+                                            st_select_lex *removed_select,
+                                            Item **ref);
   friend void mark_select_range_as_dependent(THD*,
                                              st_select_lex*, st_select_lex*,
                                              Field*, Item*, Item_ident*);
@@ -403,6 +408,8 @@ public:
   bool test_limit(st_select_lex_unit *unit);
   virtual void print(String *str, enum_query_type query_type);
   bool fix_fields(THD *thd, Item **ref);
+  void fix_after_pullout(st_select_lex *parent_select,
+                         st_select_lex *removed_select, Item **ref);
   bool setup_engine();
   bool init_left_expr_cache();
   bool is_expensive_processor(uchar *arg);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-10-13 13:27:36 +0000
+++ b/sql/sql_select.cc	2010-10-15 10:32:50 +0000
@@ -3548,16 +3548,20 @@ static TABLE_LIST *alloc_join_nest(THD *
 }
 
 
-void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist)
+void fix_list_after_tbl_changes(st_select_lex *parent_select,
+                                st_select_lex *removed_select,
+                                List<TABLE_LIST> *tlist)
 {
   List_iterator<TABLE_LIST> it(*tlist);
   TABLE_LIST *table;
   while ((table= it++))
   {
     if (table->on_expr)
-      table->on_expr->fix_after_pullout(new_parent, &table->on_expr);
+      table->on_expr->fix_after_pullout(parent_select, removed_select,
+                                        &table->on_expr);
     if (table->nested_join)
-      fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list);
+      fix_list_after_tbl_changes(parent_select, removed_select,
+                                 &table->nested_join->join_list);
   }
 }
 
@@ -3870,15 +3874,16 @@ bool convert_subquery_to_semijoin(JOIN *
     sj_nest->sj_on_expr->fix_fields(thd, &sj_nest->sj_on_expr);
   }
 
+  /* Unlink the child select_lex: */
+  subq_lex->master_unit()->exclude_level();
   /*
     Walk through sj nest's WHERE and ON expressions and call
     item->fix_table_changes() for all items.
   */
-  sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
-  fix_list_after_tbl_changes(parent_lex, &nested_join->join_list);
-
-  /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */
-  subq_lex->master_unit()->exclude_level();
+  sj_nest->sj_on_expr->fix_after_pullout(parent_lex, subq_lex,
+                                         &sj_nest->sj_on_expr);
+  fix_list_after_tbl_changes(parent_lex, subq_lex,
+                             &sj_nest->nested_join->join_list);
 
   //TODO fix QT_
   DBUG_EXECUTE("where",


Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20101015103250-f4nvvliykeb9f3qs.bundle
Thread
bzr push into mysql-next-mr-bugfixing branch (roy.lyseng:3262 to 3263)Bug#31480Roy Lyseng18 Oct