From: Roy Lyseng
Date: April 30 2012 12:17pm
Subject: bzr push into mysql-trunk branch (roy.lyseng:3900 to 3901) Bug#13971022
List-Archive: http://lists.mysql.com/commits/143691
X-Bug: 13971022
Message-Id: <201204301217.q3UCHQpb016501@khepri07.no.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3901 Roy Lyseng	2012-04-30
      Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
      
      This bug has its cause in two problems in the code base.
      
      First, there is a bug in Optimize_table_order::fix_semijoin_strategies()
      that does not properly clear semi-join plan data that has been rejected.
      Second, there is a bug in Optimize_table_order::advance_sj_state() when
      keeping track of potential LooseScan table orders.
      
      For the first bug, when fix_semijoin_strategies() is invoked, there
      are two potential semi-join strategies in join->best_positions:
      
      Positions     0       1       2       3
                   <-- DupsWeedout -->
                   <-------- LooseScan ------>
      
      fix_semijoin_strategies() scans best_positions in reverse order,
      finding (correctly) the LooseScan strategy when looking at
      best_positions[3]. (After greedy_search() procedure, semi-join
      strategy choices are recorded in the last element.) The strategy
      information is then re-arranged so that semi-join strategy selection
      is recorded in the first element, and all other elements should be
      cleared. However, on doing this, only one element is actually cleared,
      because the number of elements to clear is taken from n_sj_tables.
      Thus, on the next round, the DupsWeedout strategy is found in
      best_positions[2], and then we have overlapping semi-join strategies,
      which is not supported by the optimizer.
      This problem is fixed by clearing semi-join information for all
      elements of best_positions that are covered by the semi-join strategy.
      
      For the second bug, look at the proposed table order:
      
        subquery3_t1 - t3 - table1 - table2
      
      subquery3_t1 is a semi-join inner table, for which a LooseScan strategy
      is possible. When looking at the next table (t3), which is another
      semi-join inner table from another semi-join nest, advance_sj_state()
      adds this table to the LooseScan prefix, even though this table has
      forward references (to table table1), and it belongs to another
      semi-join nest. Other semi-join inner tables must be semi-joined to
      the LooseScan driving table by application of FirstMatch strategy, and
      they must be in the same semi-join nest for this strategy to be possible.
      Thus, the criteria for setting up table order for LooseScan are wrong.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13971022.
      
      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#13971022.
      
      sql/sql_planner.cc
        In Optimize_table_order::fix_semijoin_strategies(), made sure that
        semi-join strategy information is properly cleared after fixing
        each strategy.
        In Optimize_table_order::advance_sj_state(), changed the LooseScan
        table order check to make sure that only tables from one semi-join
        nest take part in LooseScan operation.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_planner.cc
 3900 Roy Lyseng	2012-04-30
      Bug#13974177: Assert !(tab->table->regginfo.not_exists_optimize...
      
      The problem query in this bug report can be written as:
      
      SELECT ot1.* FROM ot1 LEFT JOIN ot2 ON ot1.c=ot2.c
      WHERE ot2.c IN
         (SELECT it2.c FROM it1 LEFT JOIN it2 ON it1.c=it2.c
         ) AND ot2.c IS NULL
      ;
      
      (The columns are all not nullable.)
      
      We see that there is a left outer join in both the inner and the outer
      query. What happens is that the optimizer after semi-join transformation
      detects a multiple equality between it2.c and ot2.c. When it sees
      "ot2.c IS NULL" it tries to apply outer join NOT EXISTS optimization, and
      due to the multiple equality, it is applied to both outer join operations.
      However, the NOT EXISTS optimization requires an accompanying table
      condition (with the IS NULL predicate), and only one of the outer joins
      will have that predicate. Hence, when executing the second outer join,
      there is a NOT EXISTS optimization flag, but no associated table
      condition, and the assertion hits.
      
      The root cause for this problem is lack of nullability propagation in
      semi-join transformation. Even though we can safely assume that conditions
      generated as part of semi-join transformation treat UNKNOWN results as
      FALSE (because we are transforming an =ANY operator and not an ALL
      operator), the generated conditions do not have the abort_on_null flag set.
      This means that not_null_tables() is zero (an empty table map) for 
      the resulting condition, and simplify_joins() is unable to convert the
      outer joins to inner joins (We see that NULL values for it2.c and ot2.c
      can safely be ignored).
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13974177.
      
      mysql-test/r/derived.result
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
        Some changed explain results, because outer join is converted to inner.
      
      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#13974177.
        There are also some changed explain results, because sometimes
        outer join is converted to inner join.
      
      sql/sql_optimizer.cc
        In convert_subquery_to_semijoin(), apply the top_level_item() function
        to all generated conditions.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/derived.result
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_optimizer.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-04-30 06:56:15 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-04-30 12:15:37 +0000
@@ -6017,4 +6017,49 @@ DROP TABLE t1, t2;
 
 --echo # End of test for bug#13974177.
 
+--echo #
+--echo # Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+--echo #
+
+CREATE TABLE t1 (
+  pk INT,
+  col_int_key INT,
+  col_varchar_key VARCHAR(1),
+  PRIMARY KEY (pk),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+
+CREATE TABLE t2 (
+  pk INT,
+  col_int_key INT,
+  col_varchar_key VARCHAR(1),
+  col_varchar_nokey VARCHAR(1),
+  PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+CREATE TABLE t3 (
+  i INT
+);
+
+let $query=
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+       FROM t2 AS subquery1_t1
+            JOIN t2 AS subquery1_t2
+            ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+     STRAIGHT_JOIN t2 AS table2
+     ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+   (SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+    FROM t1 AS subquery3_t1
+   )
+;
+
+eval explain $query;
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of test for bug#13971022.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-04-30 12:15:37 +0000
@@ -9559,5 +9559,59 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; LooseScan
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(<derived2>); Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-04-30 12:15:37 +0000
@@ -9564,6 +9564,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; LooseScan
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(<derived2>); Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-04-30 12:15:37 +0000
@@ -9565,6 +9565,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Materialize; Scan
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Materialize
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-04-30 12:15:37 +0000
@@ -9565,6 +9565,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; LooseScan
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(<derived2>); Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-04-30 12:15:37 +0000
@@ -9543,5 +9543,59 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-04-30 12:15:37 +0000
@@ -9544,6 +9544,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-04-30 12:15:37 +0000
@@ -9552,6 +9552,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	NULL
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-04-30 12:15:37 +0000
@@ -9545,6 +9545,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-04-30 12:15:37 +0000
@@ -9542,6 +9542,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	5	const	1	Using where
+1	PRIMARY	subquery3_t1	ref	col_varchar_key	col_varchar_key	4	table1.col_varchar_nokey	0	Using index; FirstMatch(<derived2>)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(subquery3_t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-04-30 12:15:37 +0000
@@ -9543,6 +9543,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	5	const	1	Using where
+1	PRIMARY	subquery3_t1	ref	col_varchar_key	col_varchar_key	4	table1.col_varchar_nokey	0	Using index; FirstMatch(<derived2>)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(subquery3_t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-04-30 12:15:37 +0000
@@ -9551,6 +9551,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	5	const	1	Using where
+1	PRIMARY	subquery3_t1	ref	col_varchar_key	col_varchar_key	4	table1.col_varchar_nokey	0	Using index; FirstMatch(<derived2>)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(subquery3_t1)
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-04-30 12:15:37 +0000
@@ -9544,6 +9544,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	5	const	1	Using where
+1	PRIMARY	subquery3_t1	ref	col_varchar_key	col_varchar_key	4	table1.col_varchar_nokey	0	Using index; FirstMatch(<derived2>)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(subquery3_t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-04-30 12:15:37 +0000
@@ -9544,5 +9544,59 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-04-30 12:15:37 +0000
@@ -9545,6 +9545,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-04-30 12:15:37 +0000
@@ -9553,6 +9553,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	NULL
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-04-30 12:15:37 +0000
@@ -9546,6 +9546,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-04-30 12:15:37 +0000
@@ -9557,5 +9557,59 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Materialize; Scan
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Materialize
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-04-30 12:15:37 +0000
@@ -9558,6 +9558,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Materialize; Scan
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Materialize
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-04-30 12:15:37 +0000
@@ -9559,6 +9559,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	NULL
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; End temporary
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-04-30 12:15:37 +0000
@@ -9559,6 +9559,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	subquery3_t1	index	col_varchar_key	col_varchar_key	9	NULL	0	Using where; Using index; Materialize; Scan
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	9	const,test.subquery3_t1.col_varchar_key	2	NULL
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Materialize
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-04-30 12:15:37 +0000
@@ -9629,5 +9629,59 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+4	SUBQUERY	subquery3_t1	system	NULL	NULL	NULL	NULL	0	const row not found
+3	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-04-30 12:15:37 +0000
@@ -9544,5 +9544,59 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-04-30 12:15:37 +0000
@@ -9545,6 +9545,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-04-30 12:15:37 +0000
@@ -9545,6 +9545,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index
+4	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # 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	2012-04-30 06:56:15 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-04-30 12:15:37 +0000
@@ -9546,6 +9546,60 @@ ON it2.col_time_nokey = it1.col_time_key
 x
 DROP TABLE t1, t2;
 # End of test for bug#13974177.
+#
+# Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
+#
+CREATE TABLE t1 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+pk INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+i INT
+);
+explain SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	table2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DERIVED	subquery1_t1	ALL	PRIMARY	NULL	NULL	NULL	1	NULL
+2	DERIVED	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t1.pk	1	Using index
+SELECT table1.pk AS field1
+FROM ( SELECT subquery1_t1. *
+FROM t2 AS subquery1_t1
+JOIN t2 AS subquery1_t2
+ON subquery1_t2.pk = subquery1_t1.pk) AS table1
+STRAIGHT_JOIN t2 AS table2
+ON table1.col_int_key IN (SELECT 7 FROM t3)
+WHERE table1.col_varchar_nokey IN
+(SELECT subquery3_t1.col_varchar_key AS subquery3_field1
+FROM t1 AS subquery3_t1
+)
+;
+field1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13971022.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc	2012-04-11 12:12:00 +0000
+++ b/sql/sql_planner.cc	2012-04-30 12:15:37 +0000
@@ -2408,7 +2408,7 @@ prev_record_reads(JOIN *join, uint idx, 
 bool Optimize_table_order::fix_semijoin_strategies()
 {
   table_map remaining_tables= 0;
-  table_map handled_tabs= 0;
+  table_map handled_tables= 0;
 
   DBUG_ENTER("Optimize_table_order::fix_semijoin_strategies");
 
@@ -2426,7 +2426,7 @@ bool Optimize_table_order::fix_semijoin_
     uint first;
     LINT_INIT(first); // Set by every branch except SJ_OPT_NONE which doesn't use it
 
-    if ((handled_tabs & pos->table->table->map) ||
+    if ((handled_tables & pos->table->table->map) ||
         pos->sj_strategy == SJ_OPT_NONE)
     {
       remaining_tables|= pos->table->table->map;
@@ -2545,8 +2545,7 @@ bool Optimize_table_order::fix_semijoin_
                                      "DuplicateWeedout");
     }
     
-    uint i_end= first + join->best_positions[first].n_sj_tables;
-    for (uint i= first; i < i_end; i++)
+    for (uint i= first; i <= tableno; i++)
     {
       /*
         Eliminate stale strategies. See comment in the
@@ -2554,7 +2553,7 @@ bool Optimize_table_order::fix_semijoin_
       */
       if (i != first)
         join->best_positions[i].sj_strategy= SJ_OPT_NONE;
-      handled_tabs |= join->best_positions[i].table->table->map;
+      handled_tables|= join->best_positions[i].table->table->map;
     }
 
     if (tableno != first)
@@ -3331,19 +3330,24 @@ void Optimize_table_order::advance_sj_st
     /* 
       LooseScan strategy can't handle interleaving between tables from the 
       semi-join that LooseScan is handling and any other tables.
-
-      If we were considering LooseScan for the join prefix (1)
-         and the table we're adding creates an interleaving (2)
-      then 
-         stop considering loose scan
     */
-    if ((pos->first_loosescan_table != MAX_TABLES) &&   // (1)
-        (first->table->emb_sj_nest->sj_inner_tables & remaining_tables) && //(2)
-        emb_sj_nest != first->table->emb_sj_nest) //(2)
+    if (pos->first_loosescan_table != MAX_TABLES)
     {
-      pos->first_loosescan_table= MAX_TABLES;
+      if (first->table->emb_sj_nest->sj_inner_tables &
+          (remaining_tables | new_join_tab->table->map))
+      {
+        // Handle all tables associated with the semi-join nest first:
+        if (emb_sj_nest != first->table->emb_sj_nest)
+          pos->first_loosescan_table= MAX_TABLES;
+      }
+      else
+      {
+        // Do not interleave any other semi-joined tables:
+        DBUG_ASSERT(emb_sj_nest != first->table->emb_sj_nest);
+        if (emb_sj_nest != NULL)
+          pos->first_loosescan_table= MAX_TABLES;
+      }
     }
-
     /*
       If we got an option to use LooseScan for the current table, start
       considering using LooseScan strategy

No bundle (reason: useless for push emails).