List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 27 2011 6:46am
Subject:bzr push into mysql-trunk branch (roy.lyseng:3392 to 3393) Bug#12603183
View as plain text  
 3393 Roy Lyseng	2011-06-27
      Bug#12603183: Segfault in hp_movelink
      
        The bug manifests itself sometimes as a segmentation fault,
        sometimes as a Valgrind warning. Consistent faulting has been
        difficult to achieve.
      
        The problem is with the Materialization scan semi-join strategy.
        When materialization is done, the read_first_record function
        pointer is replaced with a function to read from the materialized
        table instead. This strategy works when the materialization is
        performed once per query, even when the materialized table is
        read multiple times. However, if the materialization is performed
        multiple times, such as when called from within another subquery,
        the original function pointer is never restored, and the wrong
        function is used to read from the subquery tables when materializing.
      
        The solution for the problem is to save the original function pointer
        in the save_read_first_record field of the join_tab and restore
        it for every new materialization.
      
        save_read_first_record is now used both from materialize-scan and
        from subqueries that toggle between ref access and full table scan.
        We could imagine a query that used both strategies at the same time:
      
        SELECT ... FROM ot...
        WHERE ocol NOT IN (SELECT ... FROM mt...
                                      WHERE mcol IN (SELECT ... FROM it ...))
      
        Where the inner-most subquery is transformed into a semi-join and
        materialize-scan strategy is selected, and the NOT IN alternates
        betwen a ref access and a full table scan on its inner table.
        However, the ref access would have to be against table "mt", but the
        table being materialized in this case is "it".
      
        Hence, I think this scenario is not possible.
      
        Notice that there are still some result differences, which would
        not be seen with the original "LIMIT 1" specification.
        It seems that Materialization scan is still slightly broken when
        used together with an outer join. This problem will be looked at
        in the context of WL#5561. Notice also that when semi-join
        transformation for outer joins is enabled, both subqueries of this
        query will be converted, and the materialization will be performed
        only once, avoiding the entire problem. Hence, to reproduce this
        problem in context of WL#5561, make sure that the outer subquery
        is not transformed.
      
        mysql-test/include/subquery_sj.inc
          Added test case for bug#12603183.
      
        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
          Updated with test results for bug#12603183.
      
      sql/sql_select.cc
        sub_select_sjm():
        Save read function pointer into save_read_first_record on first
        materialization, and restore it on subsequent materializations.
        Deleted a DBUG_ASSERT that seemed redundant, and moved setting
        of sjm->materialized to a better place.
      
      sql/sql_select.h
        Updated comment for join_tab field save_read_first_record.

    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/sql_select.cc
      sql/sql_select.h
 3392 Guilhem Bichot	2011-06-24
      Fix for BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
      Don't mark MIN() as constant when optimizing correlated subquery.
     @ mysql-test/r/subquery_all.result
        Before the code fix:
        the third and fifth rows of the testcase had NULL in last column.
     @ sql/opt_sum.cc
        Scenario of the bug, using the simplified testcase.
        
        Subq is executed for each row of "t1f JOIN t3f".
        First subq execution also optimizes the subquery. So we have in total,
        for the subquery, one optimization and four executions.
        The optimization goes into opt_sum_query() for
         SELECT MIN(t3s.col_int_key)
         FROM t3 AS t3s JOIN
         t1 AS t1s ON t1s.col_int_key = 9 and
         t1s.col_varchar_key = 'e'
         WHERE 'e' <> t1f.col_varchar_nokey.
        t1s is irrelevant here.
        opt_sum_query() sees that the WHERE clause does not mention t3s, so it
        reads the minimum of t3s.col_int_key (found through the index)
        (value:4) and marks the MIN() item as being constant with value 4.
        At second execution, row of t1f has t1f.col_varchar_nokey='e',
        so WHERE condition is false, end_send_group() calls
        item->no_rows_in_result() which sets the const item above to NULL.
        At third execution, the MIN() should be set back 4, but it's not,
        because opt_sum_query() marked it constant. So it stays at how the
        second execution left it: NULL. It stays NULL for all further
        executions.
        The explanation above assumed optimizer_join_cache_level=0;
        with higher levels, the order of rows is different (rows
        with value 4 come first); as we run the testsuite with higher levels,
        the testcase has been changed (forcing the join order) so that
        BNL row shuffling put rows in the "right order" to trigger the bug.
        
        The transformation done in opt_sum_query() is ok as long as there
        is a single execution: this single execution will either find rows in
        the JOIN (then the constant value will not be touched, and be
        returned), or find no rows, and end_send_group() will set to NULL.
        But for multiple executions, the transformation done at subquery's
        optimization is wrong, as it offers no way to get "from NULL back to
        4" for a next outer row.
        Fix: don't set MIN() to "constant" if the WHERE depends on outer rows.
        I ran the full testsuite, to see if the fix above makes
        opt_sum_query() not do "constant marking" for some test, and the
        answer is "no" (except for this test's testcase). So it does not look
        too intrusive.
        
        FYI here is the backtrace where t1f.col_varchar_nokey gets the
        OUTER_REF_TABLE_BIT bit, in the subquery:
        
        (top-gdb) p depended_from
        $37 = (st_select_lex *) 0x1e6c580
        (top-gdb) p depended_from->select_number
        $38 = 1 # this is the top-query
        (top-gdb) p *this
        $44 = (Item_field) "t1f.col_varchar_nokey" # field from outer table
        (top-gdb) f
        #2  0x00000000005e8b20 in Item_func::fix_fields at sql/item_func.cc:218
        (top-gdb) bt
        #1  Item_field::used_tables at sql/item.cc:2367
        #2  Item_func::fix_fields at sql/item_func.cc:218
        #3  setup_conds at sql/sql_base.cc:8565
        #4  setup_without_group at sql/sql_select.cc:570
        #5  JOIN::prepare of subquery

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_jcl6.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_jcl6.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_jcl6.result
      sql/opt_sum.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-06-21 09:29:01 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-06-27 06:45:51 +0000
@@ -3748,3 +3748,58 @@ eval $query;
 DROP TABLE t1, t2;
 
 --echo # End of bug#12603200
+
+--echo #
+--echo # Bug#12603183: Segfault in hp_movelink
+--echo #
+
+CREATE TABLE t1 (
+  col_varchar_key varchar(1) ,
+  col_varchar_nokey varchar(1) ,
+  KEY col_varchar_key(col_varchar_key)
+);
+
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+
+CREATE TABLE t2 (
+  col_varchar_nokey varchar(1)
+);
+
+INSERT INTO t2 VALUES
+('b');
+
+let $query=
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+   (SELECT parent1.col_varchar_nokey
+    FROM t1 AS parent1
+    WHERE parent1.col_varchar_key IN
+       (SELECT child1.col_varchar_nokey AS c1
+        FROM t1 AS child1 LEFT JOIN t2 AS child2
+                ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+
+eval EXPLAIN $query;
+eval $query;
+
+--echo -- Notice that Materialize-scan algorithm reports wrong result for this query.
+--echo -- This problem will be filed as a separate bug and dealt with in WL#5561.
+
+DROP TABLE t1, t2;
+
+--echo # End of the test for bug#12603183.

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-06-27 06:45:51 +0000
@@ -5644,4 +5644,76 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,77 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where; Using join buffer (BKA, incremental buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,77 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where; Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-06-27 06:45:51 +0000
@@ -5643,4 +5643,77 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2011-06-27 06:45:51 +0000
@@ -5647,5 +5647,78 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2011-06-27 06:45:51 +0000
@@ -5647,5 +5647,78 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-06-27 06:45:51 +0000
@@ -5645,6 +5645,79 @@ c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
 #
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	15	Using where
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(parent1)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-06-27 06:45:51 +0000
@@ -5649,6 +5649,79 @@ c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
 #
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	15	Using where
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(parent1)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-06-27 06:45:51 +0000
@@ -5649,6 +5649,79 @@ c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
 #
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	15	Using where
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(parent1)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-06-27 06:45:51 +0000
@@ -5644,4 +5644,77 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,78 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,78 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-06-27 06:45:51 +0000
@@ -5644,4 +5644,76 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,77 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2011-06-27 06:45:51 +0000
@@ -5648,5 +5648,77 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where; Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize
+2	DEPENDENT SUBQUERY	parent1	ref	col_varchar_key	col_varchar_key	4	test.child1.col_varchar_nokey	2	Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-06-27 06:45:51 +0000
@@ -5866,4 +5866,77 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	
+3	SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-06-27 06:45:51 +0000
@@ -5789,4 +5789,77 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2011-06-27 06:45:51 +0000
@@ -5793,5 +5793,78 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2011-06-21 09:29:01 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2011-06-27 06:45:51 +0000
@@ -5793,5 +5793,78 @@ c	3
 c	13
 DROP TABLE t1, t2;
 # End of bug#12603200
+#
+# Bug#12603183: Segfault in hp_movelink
+#
+CREATE TABLE t1 (
+col_varchar_key varchar(1) ,
+col_varchar_nokey varchar(1) ,
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('i','i'),
+('h','h'),
+('q','q'),
+('a','a'),
+('v','v'),
+('u','u'),
+('s','s'),
+('y','y'),
+('z','z'),
+('h','h'),
+('p','p'),
+('e','e'),
+('i','i'),
+('y','y'),
+('w','w');
+CREATE TABLE t2 (
+col_varchar_nokey varchar(1)
+);
+INSERT INTO t2 VALUES
+('b');
+EXPLAIN SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+1	PRIMARY	grandparent2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	15	Using where
+3	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 LEFT JOIN t2 AS grandparent2 USING (col_varchar_nokey)
+WHERE (grandparent1.col_varchar_key) IN
+(SELECT parent1.col_varchar_nokey
+FROM t1 AS parent1
+WHERE parent1.col_varchar_key IN
+(SELECT child1.col_varchar_nokey AS c1
+FROM t1 AS child1 LEFT JOIN t2 AS child2
+ON (child1.col_varchar_key > child2.col_varchar_nokey)));
+col_varchar_nokey
+i
+h
+q
+a
+v
+u
+s
+y
+z
+h
+p
+e
+i
+y
+w
+-- Notice that Materialize-scan algorithm reports wrong result for this query.
+-- This problem will be filed as a separate bug and dealt with in WL#5561.
+DROP TABLE t1, t2;
+# End of the test for bug#12603183.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-06-24 13:09:12 +0000
+++ b/sql/sql_select.cc	2011-06-27 06:45:51 +0000
@@ -17366,6 +17366,20 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
     Next_select_func next_func= join_tab[sjm->table_count - 1].next_select;
     join_tab[sjm->table_count - 1].next_select= end_sj_materialize;
 
+    if (sjm->is_scan)
+    {
+      JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
+      if (last_tab->save_read_first_record == NULL)
+      {
+        /* Save a copy of the read first function before substituting it */
+        last_tab->save_read_first_record= last_tab->read_first_record;
+      }
+      else
+      {
+        /* Restore read function saved in previous materialization round */
+        last_tab->read_first_record= last_tab->save_read_first_record;
+      }
+    }
     /*
       Now run the join for the inner tables. The first call is to run the
       join, the second one is to signal EOF (this is essential for some
@@ -17382,7 +17396,6 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
     /*
       Ok, materialization finished. Initialize the access to the temptable
     */
-    sjm->materialized= TRUE;
     join_tab->read_record.read_record= join_no_more_records;
     if (sjm->is_scan)
     {
@@ -17391,7 +17404,6 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
       init_read_record(&last_tab->read_record, join->thd,
                        sjm->table, NULL, TRUE, TRUE, FALSE);
 
-      DBUG_ASSERT(last_tab->read_record.read_record == rr_sequential);
       last_tab->read_first_record= join_read_record_no_init;
       last_tab->read_record.copy_field= sjm->copy_field;
       last_tab->read_record.copy_field_end= sjm->copy_field +
@@ -17402,6 +17414,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
       last_tab->last_inner= NULL;
       last_tab->first_unmatched= NULL;
     }
+
+    sjm->materialized= true;
   }
   else
   {

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-06-24 13:22:37 +0000
+++ b/sql/sql_select.h	2011-06-27 06:45:51 +0000
@@ -308,9 +308,10 @@ public:
   Next_select_func next_select;
   READ_RECORD	read_record;
   /* 
-    Currently the following two fields are used only for a [NOT] IN subquery
-    if it is executed by an alternative full table scan when the left operand of
+    The following two fields are used for a [NOT] IN subquery if it is
+    executed by an alternative full table scan when the left operand of
     the subquery predicate is evaluated to NULL.
+    save_read_first_record is also used by semi-join materialization strategy.
   */  
   READ_RECORD::Setup_func save_read_first_record;/* to save read_first_record */
   READ_RECORD::Read_func save_read_record;/* to save read_record.read_record */

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3392 to 3393) Bug#12603183Roy Lyseng27 Jun