List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:March 27 2012 8:00am
Subject:bzr push into mysql-trunk branch (roy.lyseng:3892 to 3893) Bug#13855925
View as plain text  
 3893 Roy Lyseng	2012-03-27
      Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0' in ha_innobase::index_read
      
      This is a beauty: The crash occurs because create_ref_for_key() is
      unable to set up a ref key access, so Innodb is called with a key
      containing zero key parts. There is nothing wrong with
      create_ref_for_key(), the key selection made in best_access_path()
      was made based on an invalid "remaining_tables" argument, set up by
      Optimize_table_order::fix_semijoin_strategies().
      
      fix_semijoin_strategies() loops over all join_tabs in the fixed plan,
      building remaining_tables on the fly. This query contains two
      subqueries with three tables each, both converted into semi-joins.
      One of those semi-joins are processed as a materialized table and placed
      at the end of the plan (tables 6 to 8). When building remaining_tables,
      we add "s->table->map" for each table. But for a materialized
      semi-join, we replace join->best_positions contents with information
      from sjm_nest->nested_join->sjm.positions, and the table order in
      the copied plan may be different from the original order.
      However, we make a copy of the "s" pointer in the beginning,
      and after that copying, the "s" pointer is no longer pointing
      to the table representing the same location in the join_tab array.
      The effect is that remaining_tables is populated with one table bit from
      the original plan and two table bits from the final plan.
      
      After having processed all tables in the semi-join, one table is
      omitted from remaining_tables, and it happens that the omitted table
      has a possible key for "table2", which is placed at position 4 in the
      join_tab array. The omitted table is "subquery2_t1", at position 8.
      So when best_access_path() is called for "table2" later, it erroneously
      sets up a ref access based on values from "subquery2_t1".
      
      The solution is easy: Remove the "s" local variable and use
      "pos->table" when referring to the TABLE_LIST object.
      
      The query on the view is optimized differently from a query on the tables.
      This needs investigation and is filed as bug#13890970.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13855925.
      
      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#13855925.
      
      sql/sql_planner.cc
        In Optimize_table_order::fix_semijoin_strategies(), remove the
        local variable "s" and replace "s->" with "pos->table->" everywhere.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_planner.cc
      sql/sql_select.cc
 3892 Roy Lyseng	2012-03-26
      Bug#13845930: Segfault in st_join_table::and_with_condition
      
      This problem occurs during second execution of a query involving
      outer join and subquery to semi-join transformation. The outer join
      is correctly converted to an inner join, but in make_join_select(),
      the JOIN_TAB for the outer-joined table (alias3) has an on_expr_ref
      that is non-NULL, even though the first_inner pointer is NULL.
      
      The problem originates from convert_subquery_to_semijoin() where
      a "wrap nest" is created that will contain the table alias3 and
      the semi-joined tables sq1_alias1 and sq1_alias2. When creating
      the wrap nest, the join condition is transferred from the inner table
      (alias3) to the wrap nest. In this process, resetting the
      prep_join_cond field of the inner table was forgotten.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13845930.
      
      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#13845930.
      
      sql/sql_optimizer.cc
        In convert_subquery_to_semijoin(), when creating a wrap nest,
        reset the prep_join_cond field for the table whose join condition
        is copied to the wrap nest.

    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_optimizer.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-03-26 10:45:48 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-03-27 07:57:17 +0000
@@ -5474,4 +5474,87 @@ DROP TABLE t1, t2;
 
 --echo # End of test for bug#13845930.
 
+--echo #
+--echo # Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+--echo #               in ha_innobase::index_read
+--echo #
+
+CREATE TABLE t1 (
+  pk INTEGER AUTO_INCREMENT,
+  col_int_nokey INT,
+  col_int_key INT,
+  col_varchar_key VARCHAR(1),
+  col_varchar_nokey VARCHAR(1),
+  PRIMARY KEY (pk),
+  KEY (col_varchar_key)
+) ENGINE=INNODB;
+
+INSERT INTO t1 (
+  col_int_key, col_int_nokey,
+  col_varchar_key, col_varchar_nokey
+) VALUES
+ (4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+ (0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+ (7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+ (0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+ (1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+
+CREATE TABLE t2 (
+  pk INT AUTO_INCREMENT,
+  col_int_nokey INT,
+  col_int_key INT,
+  PRIMARY KEY (pk),
+  KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+ (8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+ (2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+ (NULL, 6), (0, 2), (NULL, 9), (8, 6);
+
+CREATE TABLE t3 (
+  pk INT AUTO_INCREMENT,
+  col_varchar_key VARCHAR(1),
+  PRIMARY KEY (pk),
+  KEY (col_varchar_key)
+) ENGINE=INNODB;
+
+INSERT INTO t3 (col_varchar_key) VALUES
+ ('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+ ('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+     INNER JOIN (t1 AS table2
+       STRAIGHT_JOIN t2 AS table3
+       ON table3.col_int_key = table2.pk AND
+          table3.col_int_nokey = ANY
+           (SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+            FROM t2 AS subquery1_t1
+                 RIGHT OUTER JOIN t1 AS subquery1_t2
+                   INNER JOIN t1 AS subquery1_t3
+                   ON subquery1_t3.col_int_key = subquery1_t2.pk
+                 ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+            WHERE subquery1_t1.pk > 1
+           )
+      )
+     ON table3.col_int_key IN
+       (SELECT subquery2_t1.col_int_key AS subquery2_field1
+        FROM t2 AS subquery2_t1
+             RIGHT OUTER JOIN t3 AS subquery2_t2
+               LEFT OUTER JOIN t1 AS subquery2_t3
+               ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+             ON subquery2_t3.pk = subquery2_t2.pk
+       )
+;
+
+explain SELECT * FROM v1;
+SELECT * FROM v1;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo # End of test for bug#13855925.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2012-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-03-27 07:57:17 +0000
@@ -8574,5 +8574,108 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Start materialize; Scan
+1	PRIMARY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Start materialize
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+1	PRIMARY	subquery2_t1	index	col_int_key	col_int_key	5	NULL	20	Using index; End materialize; Using join buffer (Block Nested Loop)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Start materialize; Scan
+1	PRIMARY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where; Using join buffer (Batched Key Access)
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where; Using join buffer (Batched Key Access)
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Start materialize
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+1	PRIMARY	subquery2_t1	index	col_int_key	col_int_key	5	NULL	20	Using index; End materialize; Using join buffer (Block Nested Loop)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Start materialize; Scan
+1	PRIMARY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where; Using join buffer (Batched Key Access)
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; End materialize
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where; Using join buffer (Batched Key Access)
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Start materialize
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+1	PRIMARY	subquery2_t1	index	col_int_key	col_int_key	5	NULL	20	Using index; End materialize
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-03-27 07:57:17 +0000
@@ -8576,6 +8576,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Start materialize; Scan
+1	PRIMARY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where; Using join buffer (Batched Key Access (unique))
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where; Using join buffer (Batched Key Access (unique))
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Start materialize
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+1	PRIMARY	subquery2_t1	index	col_int_key	col_int_key	5	NULL	20	Using index; End materialize; Using join buffer (Block Nested Loop)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-03-27 07:57:17 +0000
@@ -8573,5 +8573,108 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; End temporary
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-03-27 07:57:17 +0000
@@ -8574,6 +8574,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; End temporary
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-03-27 07:57:17 +0000
@@ -8574,6 +8574,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; End temporary
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; End temporary
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-03-27 07:57:17 +0000
@@ -8574,6 +8574,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where; End temporary
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; FirstMatch(table3)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where; End temporary
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; FirstMatch(table3)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where; End temporary
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; FirstMatch(table3)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-03-27 07:57:17 +0000
@@ -8576,6 +8576,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where; End temporary
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; FirstMatch(table3)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-03-27 07:57:17 +0000
@@ -8574,5 +8574,108 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; End temporary
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; End temporary
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; End temporary
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-03-27 07:57:17 +0000
@@ -8576,6 +8576,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index; Start temporary
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	
+1	PRIMARY	subquery2_t1	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using index
+1	PRIMARY	subquery1_t3	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery1_t2	eq_ref	PRIMARY	PRIMARY	4	test.subquery1_t3.col_int_key	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where; End temporary
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-03-27 07:57:17 +0000
@@ -8574,5 +8574,108 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Start materialize; Scan
+1	PRIMARY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Start materialize
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+1	PRIMARY	subquery2_t1	index	col_int_key	col_int_key	5	NULL	20	Using index; End materialize; Using join buffer (Block Nested Loop)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Start materialize; Scan
+1	PRIMARY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Start materialize
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+1	PRIMARY	subquery2_t1	index	col_int_key	col_int_key	5	NULL	20	Using index; End materialize; Using join buffer (Block Nested Loop)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-03-27 07:57:17 +0000
@@ -8575,6 +8575,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Start materialize; Scan
+1	PRIMARY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; End materialize
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Start materialize
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+1	PRIMARY	subquery2_t1	index	col_int_key	col_int_key	5	NULL	20	Using index; End materialize
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-03-27 07:57:17 +0000
@@ -8576,6 +8576,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Start materialize; Scan
+1	PRIMARY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+1	PRIMARY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+1	PRIMARY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Start materialize
+1	PRIMARY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+1	PRIMARY	subquery2_t1	index	col_int_key	col_int_key	5	NULL	20	Using index; End materialize; Using join buffer (Block Nested Loop)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-03-27 07:57:17 +0000
@@ -8650,5 +8650,108 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+3	SUBQUERY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where
+3	SUBQUERY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+3	SUBQUERY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+4	SUBQUERY	subquery2_t2	index	NULL	col_varchar_key	4	NULL	21	Using index
+4	SUBQUERY	subquery2_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery2_t2.col_varchar_key	1	Using index
+4	SUBQUERY	subquery2_t1	index	NULL	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-03-27 07:57:17 +0000
@@ -8585,5 +8585,108 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+3	DEPENDENT SUBQUERY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+3	DEPENDENT SUBQUERY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	subquery2_t1	ref	col_int_key	col_int_key	5	func	1	Using index
+4	DEPENDENT SUBQUERY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-03-27 07:57:17 +0000
@@ -8586,6 +8586,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+3	DEPENDENT SUBQUERY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+3	DEPENDENT SUBQUERY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	subquery2_t1	ref	col_int_key	col_int_key	5	func	1	Using index
+4	DEPENDENT SUBQUERY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-03-27 07:57:17 +0000
@@ -8586,6 +8586,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+3	DEPENDENT SUBQUERY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+3	DEPENDENT SUBQUERY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index
+4	DEPENDENT SUBQUERY	subquery2_t1	ref	col_int_key	col_int_key	5	func	1	Using index
+4	DEPENDENT SUBQUERY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index
+4	DEPENDENT SUBQUERY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-26 10:45:48 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-03-27 07:57:17 +0000
@@ -8587,6 +8587,109 @@ field1
 DEALLOCATE prepare stmt;
 DROP TABLE t1, t2;
 # End of test for bug#13845930.
+#
+# Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0'
+#               in ha_innobase::index_read
+#
+CREATE TABLE t1 (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t1 (
+col_int_key, col_int_nokey,
+col_varchar_key, col_varchar_nokey
+) VALUES
+(4, 2, 'v','v'), (62, 150, 'v','v'), (7, NULL, 'c','c'), (1, 2, NULL, NULL),
+(0, 5, 'x','x'), (7, 3, 'i','i'), (7, 1, 'e','e'), (1, 4, 'p','p'),
+(7, NULL, 's','s'), (1, 2, 'j','j'), (5, 6, 'z','z'), (2, 6, 'c','c'),
+(0, 8, 'a','a'), (1, 2, 'q','q'), (8, 6, 'y','y'), (1, 8, NULL, NULL),
+(1, 3, 'r','r'), (9, 3, 'v','v'), (1, 9, NULL, NULL), (5, 6, 'r','r');
+CREATE TABLE t2 (
+pk INT AUTO_INCREMENT,
+col_int_nokey INT,
+col_int_key INT,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) AUTO_INCREMENT=10 ENGINE=INNODB;
+INSERT INTO t2 (col_int_key, col_int_nokey) VALUES
+(8, NULL), (7, 8), (1, 1), (7, 9), (9, 4), (NULL, 3), (1, 2), (9, NULL),
+(2, 2), (9, NULL), (2, 6), (4, 7), (0, 2), (4, 5), (8, 7), (NULL, 6),
+(NULL, 6), (0, 2), (NULL, 9), (8, 6);
+CREATE TABLE t3 (
+pk INT AUTO_INCREMENT,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key)
+) ENGINE=INNODB;
+INSERT INTO t3 (col_varchar_key) VALUES
+('c'), ('c'), ('q'), ('g'), ('e'), ('l'), (NULL), ('c'), ('h'), ('d'),
+('c'), ('i'), ('t'), ('g'), ('q'), ('l'), ('n'), ('z'), ('n'), ('r'), ('p');
+CREATE VIEW v1 AS
+SELECT table2.col_varchar_nokey AS field1
+FROM t2 AS table1
+INNER JOIN (t1 AS table2
+STRAIGHT_JOIN t2 AS table3
+ON table3.col_int_key = table2.pk AND
+table3.col_int_nokey = ANY
+(SELECT subquery1_t2.col_int_nokey AS subquery1_field1
+FROM t2 AS subquery1_t1
+RIGHT OUTER JOIN t1 AS subquery1_t2
+INNER JOIN t1 AS subquery1_t3
+ON subquery1_t3.col_int_key = subquery1_t2.pk
+ON subquery1_t3.col_varchar_key=subquery1_t2.col_varchar_nokey
+WHERE subquery1_t1.pk > 1
+)
+)
+ON table3.col_int_key IN
+(SELECT subquery2_t1.col_int_key AS subquery2_field1
+FROM t2 AS subquery2_t1
+RIGHT OUTER JOIN t3 AS subquery2_t2
+LEFT OUTER JOIN t1 AS subquery2_t3
+ON subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key
+ON subquery2_t3.pk = subquery2_t2.pk
+)
+;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	index	NULL	col_int_key	5	NULL	20	Using index
+1	PRIMARY	table2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	table3	ref	col_int_key	col_int_key	5	test.table2.pk	1	Using where
+3	DEPENDENT SUBQUERY	subquery1_t2	ALL	PRIMARY	NULL	NULL	NULL	20	Using where
+3	DEPENDENT SUBQUERY	subquery1_t3	ref	col_varchar_key	col_varchar_key	4	test.subquery1_t2.col_varchar_nokey	1	Using where
+3	DEPENDENT SUBQUERY	subquery1_t1	index	PRIMARY	col_int_key	5	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	subquery2_t1	ref	col_int_key	col_int_key	5	func	1	Using index
+4	DEPENDENT SUBQUERY	subquery2_t3	index	PRIMARY,col_varchar_key	col_varchar_key	4	NULL	20	Using where; Using index; Using join buffer (Block Nested Loop)
+4	DEPENDENT SUBQUERY	subquery2_t2	eq_ref	PRIMARY,col_varchar_key	PRIMARY	4	test.subquery2_t3.pk	1	Using where
+SELECT * FROM v1;
+field1
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+v
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+# End of test for bug#13855925.
 # 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-03-22 11:53:41 +0000
+++ b/sql/sql_planner.cc	2012-03-27 07:57:17 +0000
@@ -2495,14 +2495,14 @@ bool Optimize_table_order::fix_semijoin_
        tableno--)
   {
     POSITION *const pos= join->best_positions + tableno;
-    JOIN_TAB *const s= pos->table;
-    TABLE_LIST *const emb_sj_nest= s->emb_sj_nest;
+    TABLE_LIST *const emb_sj_nest= pos->table->emb_sj_nest;
     uint first;
     LINT_INIT(first); // Set by every branch except SJ_OPT_NONE which doesn't use it
 
-    if ((handled_tabs & s->table->map) || pos->sj_strategy == SJ_OPT_NONE)
+    if ((handled_tabs & pos->table->table->map) ||
+        pos->sj_strategy == SJ_OPT_NONE)
     {
-      remaining_tables |= s->table->map;
+      remaining_tables|= pos->table->table->map;
       continue;
     }
 
@@ -2632,9 +2632,11 @@ bool Optimize_table_order::fix_semijoin_
 
     if (tableno != first)
       pos->sj_strategy= SJ_OPT_NONE;
-    remaining_tables |= s->table->map;
+    remaining_tables |= pos->table->table->map;
   }
 
+  DBUG_ASSERT(remaining_tables == (join->all_table_map&~join->const_table_map));
+
   /* sjm.positions is no longer needed, reset the reference to it */
   List_iterator<TABLE_LIST> sj_list_it(join->select_lex->sj_nests);
   TABLE_LIST *sj_nest;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-03-22 11:53:41 +0000
+++ b/sql/sql_select.cc	2012-03-27 07:57:17 +0000
@@ -1442,6 +1442,8 @@ bool create_ref_for_key(JOIN *join, JOIN
     } while (keyuse->table == table && keyuse->key == key);
   } /* not ftkey */
 
+  DBUG_ASSERT(keyparts > 0);
+
   /* set up fieldref */
   j->ref.key_parts=keyparts;
   j->ref.key_length=length;

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3892 to 3893) Bug#13855925Roy Lyseng27 Mar