3896 Roy Lyseng 2012-03-30
Bug#13907277: Segfault in evaluate_null_complemented_join_record
Similar problem as reported in bug#13838810, but in another code branch.
The problem may occur for an outer join operation embedded inside
a semi-join operation that is embedded inside another outer join
operation. For a nested outer join operation, the first inner table
of an embedded outer join must have the JOIN_TAB::first_upper field
set to point to the first inner table of the embedding outer join.
make_outerjoin_info() set this field properly for all outer joins
inside nested join objects, but not for a table with only
"outer_join" set. If there was a semi-join nest between the two
outer join nests, the outer join was not properly identified and
first_upper was not set.
mysql-test/include/subquery_sj.inc
Added test case for bug#13907277.
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#13907277.
sql/sql_optimizer.cc
In make_outerjoin_info(), for multiple inner tables of an outer
join, check whether the embedding join nest is not an outer join
nest (thus, it has to be a semi-join nest). In that case, set
JOIN_TAB::first_upper to the first table in the outer join nest
embedding the semi-join 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
3895 Guilhem Bichot 2012-03-29
Fix for Bug#13898625 ASSERT `(REMAINING_TABLES_AFTER != 0) ...' IN
BEST_EXTENSION_BY_LIMITED_SEARCH
@ mysql-test/r/subquery_sj_all.result
All second EXECUTE used to crash. I checked the new results.
@ mysql-test/r/subquery_sj_mat.result
each second EXECUTE used to crash.
Results are correct (first query: "WHERE alias3.pk IN "
rejects all rows; second query: same; third query:
t1 is empty).
@ sql/sql_optimizer.cc
Scenario of the bug follows, using the first query added to
subquery_sj.inc as example.
* Cause of crash:
select_lex->leaf_tables correctly has 6 tables
(alias2,alias3,alias1,t2,sq2_alias2,sq1_alias1) at 1st execution and
only 5 (alias2,alias3,alias1,t2,sq1_alias1) at 2nd execution (misses
sq2_alias2). Note that t2 means "t2 merged from view_c".
* Table maps are set up in setup_table_map() (called from
JOIN::prepare()) at each execution. The 5th table gets always bit
(1<<4). So at 1st execution sq2_alias2 gets (1<<4) whereas at second
execution sq1_alias1 gets (1<<4)
* sj_inner_tables of semijoin nest (t2,sq2_alias2) is set up in
pull_out_semijoin_tables() which is called only at first execution: it
is thus set to a value containing (1<<4) (sq2_alias2).
* However, at second execution, sj_inner_tables is reused (in
advance_sj_state(), optimize_semijoin_nests_for_materialization(),
...), but this time (1<<4) in it is interpreted as sq1_alias1!
Like if sq1_alias1 was in the nest of view_c!! This leads to various
crashes.
* Now the root cause: why is sq2_alias2 missing from
select_lex->leaf_tables at 2nd execution? because leaf_tables is built
from JOIN::tables_list, which misses sq2_alias2.
* Why is sq2_alias2 missing from JOIN::tables_list?
Because at first execution, convert_subquery_to_semijoin() does:
/*
Reconnect the next_leaf chain.
*/
for (tl= parent_lex->leaf_tables; tl->next_leaf; tl= tl->next_leaf)
{}
tl->next_leaf= subq_lex->leaf_tables;
/*
Same as above for next_local chain. This needed only for re-execution.
(The next_local chain always starts with SELECT_LEX::table_list)
*/
for (tl= parent_lex->get_table_list(); tl->next_local; tl= tl->next_local)
{}
tl->next_local= subq_lex->leaf_tables; <<<<<<<<<<<<<<<<<BUG
subq_lex->leaf_tables is (t2,sq2_alias2) if we follow the next_leaf pointer, but
is only (t2) if we follow next_local. So we wrongly add only t2 to
JOIN::tables_list.
The fix: append subq_lex->get_table_list(), it is more logical and
symmetric, and it has (t2,sq2_alias2) if we follow next_local.
@ sql/sql_planner.cc
Assert, which would have fired for some of the added queries,
without the bugfix.
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
sql/sql_planner.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-03-29 13:14:51 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-03-30 12:36:24 +0000
@@ -5734,4 +5734,51 @@ eval EXPLAIN $query;
DROP TABLE t1;
DROP VIEW view_b;
+--echo #
+--echo # Bug#13907277: Segfault in evaluate_null_complemented_join_record
+--echo #
+
+CREATE TABLE t1 (
+ pk INTEGER,
+ col_varchar_nokey VARCHAR(1),
+ col_varchar_key VARCHAR(1),
+ PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+
+CREATE TABLE t2 (
+ pk INTEGER,
+ PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+
+CREATE TABLE t3 (
+ pk INTEGER,
+ col_int_nokey INTEGER,
+ col_int_key INTEGER,
+ col_varchar_nokey VARCHAR(1),
+ PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+
+let $query=
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+ RIGHT JOIN t2 AS outer_t2
+ ON outer_t1.col_int_nokey IN
+ (SELECT inner_t1.col_int_nokey
+ FROM t3 AS inner_t1
+ LEFT JOIN t1 AS inner_t2
+ INNER JOIN t1 AS inner_t3
+ ON inner_t3.pk = inner_t2.pk
+ ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+ );
+
+eval explain $query;
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of test for bug#13907277.
+
--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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-03-30 12:36:24 +0000
@@ -8925,5 +8925,60 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; FirstMatch(outer_t1)
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; FirstMatch(outer_t1)
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; FirstMatch(outer_t1)
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-03-30 12:36:24 +0000
@@ -8927,6 +8927,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; FirstMatch(outer_t1)
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-03-30 12:36:24 +0000
@@ -8924,5 +8924,60 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-03-30 12:36:24 +0000
@@ -8925,6 +8925,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-03-30 12:36:24 +0000
@@ -8925,6 +8925,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-03-30 12:36:24 +0000
@@ -8925,6 +8925,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; FirstMatch(outer_t1)
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; FirstMatch(outer_t1)
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; FirstMatch(outer_t1)
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-03-30 12:36:24 +0000
@@ -8927,6 +8927,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; FirstMatch(outer_t1)
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-03-30 12:36:24 +0000
@@ -8925,5 +8925,60 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-03-30 12:36:24 +0000
@@ -8927,6 +8927,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-03-30 12:36:24 +0000
@@ -8925,5 +8925,60 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-03-30 12:36:24 +0000
@@ -8926,6 +8926,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-03-30 12:36:24 +0000
@@ -8927,6 +8927,61 @@ id select_type table type possible_keys
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY inner_t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY inner_t2 ALL PRIMARY NULL NULL NULL 1
+1 PRIMARY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where; End temporary
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-03-30 12:36:24 +0000
@@ -9005,5 +9005,60 @@ id select_type table type possible_keys
2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 1 Using where
+2 SUBQUERY inner_t1 system NULL NULL NULL NULL 1
+2 SUBQUERY inner_t2 ALL PRIMARY NULL NULL NULL 1
+2 SUBQUERY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-03-30 12:36:24 +0000
@@ -8940,5 +8940,60 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY inner_t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY inner_t2 ALL PRIMARY NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-03-30 12:36:24 +0000
@@ -8941,6 +8941,61 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY inner_t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY inner_t2 ALL PRIMARY NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-03-30 12:36:24 +0000
@@ -8941,6 +8941,61 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY inner_t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY inner_t2 ALL PRIMARY NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# 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-29 13:14:51 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-03-30 12:36:24 +0000
@@ -8942,6 +8942,61 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
DROP TABLE t1;
DROP VIEW view_b;
+#
+# Bug#13907277: Segfault in evaluate_null_complemented_join_record
+#
+CREATE TABLE t1 (
+pk INTEGER,
+col_varchar_nokey VARCHAR(1),
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1, 'x', 'x');
+CREATE TABLE t2 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk INTEGER,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+);
+INSERT INTO t3 VALUES (1, 6, 5, 'r');
+explain SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY outer_t2 system NULL NULL NULL NULL 1
+1 PRIMARY outer_t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY inner_t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY inner_t2 ALL PRIMARY NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY inner_t3 eq_ref PRIMARY PRIMARY 4 test.inner_t2.pk 1 Using where
+SELECT outer_t1.pk, outer_t2.pk
+FROM t3 AS outer_t1
+RIGHT JOIN t2 AS outer_t2
+ON outer_t1.col_int_nokey IN
+(SELECT inner_t1.col_int_nokey
+FROM t3 AS inner_t1
+LEFT JOIN t1 AS inner_t2
+INNER JOIN t1 AS inner_t3
+ON inner_t3.pk = inner_t2.pk
+ON inner_t3.col_varchar_nokey = inner_t2.col_varchar_key
+);
+pk pk
+1 1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13907277.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-03-29 13:14:51 +0000
+++ b/sql/sql_optimizer.cc 2012-03-30 12:36:24 +0000
@@ -5719,8 +5719,15 @@ make_outerjoin_info(JOIN *join)
nested_join->first_nested= tab;
tab->on_expr_ref= embedding->join_cond_ref();
tab->cond_equal= tbl->cond_equal;
- if (embedding->embedding)
- tab->first_upper= embedding->embedding->nested_join->first_nested;
+ TABLE_LIST *const outer_nest= embedding->embedding;
+ if (outer_nest)
+ {
+ // This outer join nest is embedded in another join nest
+ if (outer_nest->nested_join->nj_map)
+ tab->first_upper= outer_nest->nested_join->first_nested;
+ else if (outer_nest->embedding)
+ tab->first_upper= outer_nest->embedding->nested_join->first_nested;
+ }
}
if (!tab->first_inner)
tab->first_inner= nested_join->first_nested;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3895 to 3896) Bug#13907277 | Roy Lyseng | 31 Mar |
