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#13855925 | Roy Lyseng | 27 Mar |
