List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 10 2012 8:52am
Subject:bzr push into mysql-trunk branch (roy.lyseng:3510 to 3511) Bug#13545215
View as plain text  
 3511 Roy Lyseng	2012-01-10
      Bug#13545215: Missing rows on nested in-subquery with materialization 
      
      This problem occurs with a semi-join materialization that is executed
      multiple times. In this case, the problem is that some outer-join data
      item (the last_inner field of the join_tab) is not reset after the
      first materialization. Effectively, this means that the first
      materialization is performed as an outer join, and all subsequent
      materializations are performed as inner joins.
      The problem occurs only with the MaterializeScan semi-join strategy.
      
      Another demonstration of why reusing data structures for another purpose
      is a really stupid idea.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13545215.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
        Added test case results for bug#13545215.
        Notice also the correction in some previous test runs for
        the test case for bug#12603183.
      
      sql/sql_executor.cc
        In sub_select_sjm(), save the value of last_inner before
        performing scan over materialized table, and restore the value
        after the scan.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_executor.cc
 3510 Roy Lyseng	2012-01-09
      Bug#13552968: Extra row with materialization on join + subquery in where + 
      
      The problem here occurs with an outer join between two "system" tables
      (tables with exactly one row each), and with an "expensive" join
      condition between the two tables. User-defined functions and materialized
      subqueries are considered expensive in this context.
      
      The fix for bug#52344 is related to this problem, as it moved subquery
      materialization out of the optimizer phase and into the execution phase.
      However, when marking the inner table of the outer join as "const",
      it is not possible to later evaluate the join condition and subsequently
      mark the fields from the inner tables as NULL. This is contrary to the
      condition attached to the JOIN object: If there are only const tables
      in the JOIN, do_select() will evaluate this condition and correctly
      return an empty result if the condition evaluates to FALSE.
      
      The cases for where the bug hit were quite interesting:
      - With semijoin and materialization ON, the subquery is first marked
        as semi-join candidate, but because the outer query is OUTER JOIN,
        it is instead reverted to IN-TO-EXISTS. This is marked non-expensive
        and thus does not pose a problem.
      - With semijoin OFF, materialization is selected. The condition is then
        marked as expensive and the problem occurs.
      - With STRAIGHT JOIN applied to the outer query, materialization is
        always selected, and the problem occurs.
      
      The chosen solution is to stop marking the inner table of an outer join
      that has an expensive join condition as a const table.
      Fixing the executor to handle this particular case does not seem viable.
       
      mysql-test/include/subquery_mat.inc
        Added test case for bug#13552968.
        Modified test case for bug#52344 so that problem is visible here as well.
      
      mysql-test/r/subquery_mat.result
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_mat_none.result
        Added test case results for bug#13552968.
      
      sql/sql_executor.cc
        Ensure that join_read_const_table() is not called with an outer-joined
        table with an expensive condition.
      
      sql/sql_optimizer.cc
        For the inner table of an outer join that has an expensive condition,
        do not mark a table containing one row as const.

    modified:
      mysql-test/include/subquery_mat.inc
      mysql-test/r/subquery_mat.result
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_mat_none.result
      sql/sql_executor.cc
      sql/sql_optimizer.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-12-13 08:49:11 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-01-10 08:49:18 +0000
@@ -3910,9 +3910,6 @@ WHERE (grandparent1.col_varchar_key) IN
 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 test for bug#12603183.
@@ -4575,4 +4572,58 @@ DROP TABLE t1, t2;
 
 --echo # End of test for bug#13414014.
 
+--echo #
+--echo # Bug#13545215: Missing rows on nested in-subquery with materialization
+--echo #
+
+CREATE TABLE t1 (
+  col_int_key int,
+  col_varchar_key varchar(1),
+  col_varchar_nokey varchar(1),
+  KEY col_int_key (col_int_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+
+INSERT INTO t1 VALUES
+ (8,'x','x'), (0,'p','p'), (8,'c','c');
+
+CREATE TABLE t2 (
+  pk int NOT NULL,
+  col_varchar_key varchar(1),
+  col_varchar_nokey varchar(1),
+  PRIMARY KEY (pk),
+  KEY col_varchar_key (col_varchar_key)
+);
+
+INSERT INTO t2 VALUES
+ (1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+ (5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+
+CREATE TABLE t3 (
+ col_int_nokey int
+);
+
+INSERT INTO t3 VALUES (7);
+
+let $query=
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+    SELECT col_varchar_nokey
+    FROM t2 AS parent1
+    WHERE col_varchar_key IN (
+        SELECT child1.col_varchar_nokey
+        FROM t2 AS child1 LEFT JOIN t3 AS child2
+             ON child1.pk < child2.col_int_nokey
+        )
+    );
+
+eval explain $query;
+--sorted_result
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of test for bug#13545215.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-01-10 08:49:18 +0000
@@ -6595,6 +6595,7 @@ col_varchar_nokey
 i
 h
 q
+a
 v
 u
 s
@@ -6606,8 +6607,6 @@ 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 test for bug#12603183.
 #
@@ -7270,5 +7269,67 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-01-10 08:49:18 +0000
@@ -6596,6 +6596,7 @@ col_varchar_nokey
 i
 h
 q
+a
 v
 u
 s
@@ -6607,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7271,6 +7270,68 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-01-10 08:49:18 +0000
@@ -6596,6 +6596,7 @@ col_varchar_nokey
 i
 h
 q
+a
 v
 u
 s
@@ -6607,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7271,6 +7270,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	ref	col_int_key	col_int_key	5	test.grandparent1.col_int_key	2	Using index
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	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 (Batched Key Access)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-01-10 08:49:18 +0000
@@ -6597,6 +6597,7 @@ col_varchar_nokey
 i
 h
 q
+a
 v
 u
 s
@@ -6608,8 +6609,6 @@ 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 test for bug#12603183.
 #
@@ -7272,6 +7271,68 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-01-10 08:49:18 +0000
@@ -6606,8 +6606,6 @@ 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 test for bug#12603183.
 #
@@ -7270,5 +7268,67 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-01-10 08:49:18 +0000
@@ -6607,8 +6607,6 @@ 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 test for bug#12603183.
 #
@@ -7271,6 +7269,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-01-10 08:49:18 +0000
@@ -6607,8 +6607,6 @@ 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 test for bug#12603183.
 #
@@ -7271,6 +7269,68 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	ref	col_int_key	col_int_key	5	test.grandparent1.col_int_key	2	Using index
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where
+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 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7272,6 +7270,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-01-10 08:49:18 +0000
@@ -6607,8 +6607,6 @@ 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 test for bug#12603183.
 #
@@ -7271,6 +7269,68 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7272,6 +7270,68 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7272,6 +7270,68 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	ref	col_int_key	col_int_key	5	test.grandparent1.col_int_key	2	Using index
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where
+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 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-01-10 08:49:18 +0000
@@ -6609,8 +6609,6 @@ 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 test for bug#12603183.
 #
@@ -7273,6 +7271,68 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-01-10 08:49:18 +0000
@@ -6607,8 +6607,6 @@ 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 test for bug#12603183.
 #
@@ -7271,5 +7269,67 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7272,6 +7270,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7272,6 +7270,68 @@ x
 q
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	ref	col_int_key	col_int_key	5	test.grandparent1.col_int_key	2	Using index
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Using where; Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where
+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 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-01-10 08:49:18 +0000
@@ -6609,8 +6609,6 @@ 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 test for bug#12603183.
 #
@@ -7273,6 +7271,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start temporary
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-01-10 08:49:18 +0000
@@ -6595,6 +6595,7 @@ col_varchar_nokey
 i
 h
 q
+a
 v
 u
 s
@@ -6606,8 +6607,6 @@ 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 test for bug#12603183.
 #
@@ -7270,5 +7269,67 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-01-10 08:49:18 +0000
@@ -6596,6 +6596,7 @@ col_varchar_nokey
 i
 h
 q
+a
 v
 u
 s
@@ -6607,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7271,6 +7270,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-01-10 08:49:18 +0000
@@ -6596,6 +6596,7 @@ col_varchar_nokey
 i
 h
 q
+a
 v
 u
 s
@@ -6607,8 +6608,6 @@ 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 test for bug#12603183.
 #
@@ -7271,6 +7270,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	ref	col_int_key	col_int_key	5	test.grandparent1.col_int_key	2	Using index
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	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 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-01-10 08:49:18 +0000
@@ -6597,6 +6597,7 @@ col_varchar_nokey
 i
 h
 q
+a
 v
 u
 s
@@ -6608,8 +6609,6 @@ 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 test for bug#12603183.
 #
@@ -7272,6 +7271,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Start materialize; Scan
+2	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	col_varchar_key	NULL	NULL	NULL	8	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-01-10 08:49:18 +0000
@@ -6683,8 +6683,6 @@ 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 test for bug#12603183.
 #
@@ -7347,5 +7345,67 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	
+3	SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-01-10 08:49:18 +0000
@@ -6618,8 +6618,6 @@ 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 test for bug#12603183.
 #
@@ -7282,5 +7280,67 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-01-10 08:49:18 +0000
@@ -6619,8 +6619,6 @@ 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 test for bug#12603183.
 #
@@ -7283,6 +7281,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-01-10 08:49:18 +0000
@@ -6619,8 +6619,6 @@ 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 test for bug#12603183.
 #
@@ -7283,6 +7281,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	ref	col_int_key	col_int_key	5	test.grandparent1.col_int_key	2	Using index
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result	2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-01-10 08:49:18 +0000
@@ -6620,8 +6620,6 @@ 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 test for bug#12603183.
 #
@@ -7284,6 +7282,68 @@ q
 x
 DROP TABLE t1, t2;
 # End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	grandparent1	ALL	col_int_key	NULL	NULL	NULL	3	Using where
+1	PRIMARY	grandparent2	index	col_int_key	col_int_key	5	NULL	3	Using where; Using index; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	DEPENDENT SUBQUERY	child1	ALL	NULL	NULL	NULL	NULL	8	Using where
+3	DEPENDENT SUBQUERY	child2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2012-01-09 14:19:49 +0000
+++ b/sql/sql_executor.cc	2012-01-10 08:49:18 +0000
@@ -1751,6 +1751,7 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
     // Save contents of join tab for possible repeated materializations:
     const READ_RECORD saved_access= last_tab->read_record;
     const READ_RECORD::Setup_func saved_rfr= last_tab->read_first_record;
+    st_join_table *const saved_last_inner= last_tab->last_inner;
 
     // Initialize full scan
     if (init_read_record(&last_tab->read_record, join->thd,
@@ -1777,6 +1778,7 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
     last_tab->set_condition(save_cond, __LINE__);
     last_tab->read_record= saved_access;
     last_tab->read_first_record= saved_rfr;
+    last_tab->last_inner= saved_last_inner;
   }
   else
   {

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3510 to 3511) Bug#13545215Roy Lyseng10 Jan