3888 Roy Lyseng 2012-03-14
Bug#13838810: Segfault in evaluate_null_complemented_join_record
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#13838810.
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#13838810.
sql/sql_optimizer.cc
In make_outerjoin_info(), for a single inner table 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
3887 Roy Lyseng 2012-02-13
WL#5561 - Enable semi-join transformation with outer join queries
mysql-test/include/join_cache.inc
Turned off semi-join to preserve plan for an existing test.
mysql-test/include/subquery_sj.inc
Added new tests for queries that combine outer join with semi-join.
See HLS of WL#5561 for a basic description of the new tests.
The tests are grouped according to the "subquery patterns" defined
in the WL.
mysql-test/r/join_cache_bka.result
mysql-test/r/join_cache_bka_nixbnl.result
mysql-test/r/join_cache_bkaunique.result
mysql-test/r/join_cache_bnl.result
mysql-test/r/join_cache_nojb.result
Turned off semi-join to preserve plan for an existing test.
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_mat_all.result
Some queries containing inner joins and outer joins that previously
where executed as "DEPENDENT SUBQUERY" are now transformed
to semi-joins.
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
Contains new results for tests added in WL#5561.
Some queries containing inner joins and outer joins that previously
where executed as "DEPENDENT SUBQUERY" are now transformed
to semi-joins.
Some results are reordered because of the new plans.
mysql-test/r/subselect_innodb.result
Some plans changed because of semi-join transformation.
mysql-test/r/type_time.result
Turned off semi-join to preserve plan for an existing test.
mysql-test/r/view.result
One result reordering because plan was changed.
mysql-test/suite/opt_trace/r/general_no_prot_all.result
mysql-test/suite/opt_trace/r/general_ps_prot_all.result
Changed optimizer trace because plans were changed.
mysql-test/t/type_time.test
Turned off semi-join to preserve plan for an existing test.
sql/item_func.cc
Item_func::fix_fields() was called for a previously fixed item.
In order to avoid having yet another test in the caller, the
DBUG_ASSERT(fixed == 0) was removed and fix_fields() will instead
exit early when the item is already fixed.
See LLD section 2.1.
sql/sql_executor.cc
do_sj_dups_weedout(): Corrected bug in NULL handling when writing
duplicate weedout row.
See LLD section 2.7.
evaluate_null_complemented_join_record(): Made sure that
duplicate weedout table is properly reset also when we add a
null complemented row.
See LLD section 2.6.
sql/sql_optimizer.cc
record_semijoin_nests() was split out from simplify_joins(), because
sometimes a semi-join nest was recorded twice.
Simplifying the complex simplify_joins() (sic) was also reasonable.
See LLD section 2.9.
semijoin_types_allow_materialization(): Small interface change.
optimize_semijoin_nests(): as a short-term measure, disallow
materialization when there are dependencies to the outer tables.
See LLD section 2.2.
build_bitmap_for_nested_join(): Updated to use the new nj_xxx fields.
Record tables belonging in semi-join nests in the embedding outer join
nest (if any).
See LLD section 2.5.
convert_subquery_to_semijoin(): Removed disabling of semi-join
transformation when there are outer joins in the outer query.
See LLD chapter 1.
convert_subquery_to_semijoin(): Made changes to on_expr to be
reflected in the permanent data as well (prep_on_expr).
See LLD section 2.4.
make_outerjoin_info(): Updated to use the new nj_xxx fields.
See LLD section 2.5.
sql/sql_planner.cc
check_interleaving_with_nj(): Updated to ignore join nests without
nj_map information, and use new nj_xxx fields.
backout_nj_sj_state(): Updated to use new nj_xxx fields.
sql/sql_select.cc
setup_semijoin_dups_elimination(): Code that assures that duplicate
weedout extends across all tables of an outer join operation.
See LLD section 2.8.
setup_join_buffering(): Eliminated use of join cache when there
is an outer join operation and first match strategy is used.
sql/table.cc
When resolving a view, call fix_fields() on the view's WHERE
condition only once. This fix_fields() call enables some needed
initial transformations to the condition, and is prerequisite to
separate out the view check option. However for subsequent
executions, the view condition is part of complete query condition,
and will be resolved as part of this. There was a crash because of
this, because fix_fields() did non-permanent transformations on
subquery transformed to semi-join on second execution.
sql/table.h
Replaced fields used to analyze nested joins with new ones:
nj_total, nj_counter, nj_map.
See LLD section 2.5.
removed:
mysql-test/suite/rpl/t/rpl_mixed_crash_safe-slave.opt.THIS
modified:
mysql-test/include/join_cache.inc
mysql-test/include/subquery_sj.inc
mysql-test/r/join_cache_bka.result
mysql-test/r/join_cache_bka_nixbnl.result
mysql-test/r/join_cache_bkaunique.result
mysql-test/r/join_cache_bnl.result
mysql-test/r/join_cache_nojb.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
mysql-test/r/subselect_innodb.result
mysql-test/r/type_time.result
mysql-test/r/view.result
mysql-test/suite/opt_trace/r/general_no_prot_all.result
mysql-test/suite/opt_trace/r/general_ps_prot_all.result
mysql-test/t/type_time.test
sql/item_func.cc
sql/sql_executor.cc
sql/sql_optimizer.cc
sql/sql_planner.cc
sql/sql_select.cc
sql/table.cc
sql/table.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-02-13 11:57:09 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-03-14 12:03:54 +0000
@@ -5290,4 +5290,60 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
DROP TABLE t1;
+--echo # Bug#13838810: Segfault in evaluate_null_complemented_join_record
+
+CREATE TABLE t1 (
+ pk int NOT NULL,
+ col_int_nokey int DEFAULT NULL,
+ col_int_key int DEFAULT NULL,
+ col_varchar_key varchar(1) DEFAULT NULL,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key),
+ KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+
+CREATE TABLE t2 (
+ pk int NOT NULL,
+ col_varchar_nokey varchar(1) DEFAULT NULL,
+ PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (1,'x');
+
+CREATE TABLE t3 (
+ pk int NOT NULL,
+ col_varchar_key varchar(1) DEFAULT NULL,
+ col_varchar_nokey varchar(1) DEFAULT NULL,
+ PRIMARY KEY (pk),
+ KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t3 VALUES
+ (1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+
+let $query=
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+ LEFT JOIN t1 AS table2
+ LEFT JOIN t1 AS table3
+ ON table3.col_int_key = table2.col_int_key
+ ON table3.pk = table2.col_int_nokey AND
+ table1.col_varchar_nokey IN (
+ SELECT subquery3_t1.col_varchar_nokey
+ FROM t3 AS subquery3_t1
+ LEFT JOIN t1 AS subquery3_t2
+ ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+ WHERE subquery3_t2.col_int_nokey <> 9
+ )
+;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of test for bug#13838810.
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-03-14 12:03:54 +0000
@@ -8368,5 +8368,69 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; FirstMatch(table3)
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; FirstMatch(table3)
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; FirstMatch(table3)
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-03-14 12:03:54 +0000
@@ -8370,6 +8370,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; FirstMatch(table3)
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-03-14 12:03:54 +0000
@@ -8367,5 +8367,69 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-03-14 12:03:54 +0000
@@ -8368,6 +8368,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-03-14 12:03:54 +0000
@@ -8368,6 +8368,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-03-14 12:03:54 +0000
@@ -8368,6 +8368,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; FirstMatch(table3)
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; FirstMatch(table3)
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; FirstMatch(table3)
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-03-14 12:03:54 +0000
@@ -8370,6 +8370,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; FirstMatch(table3)
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-03-14 12:03:54 +0000
@@ -8368,5 +8368,69 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-03-14 12:03:54 +0000
@@ -8370,6 +8370,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-03-14 12:03:54 +0000
@@ -8368,5 +8368,69 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-03-14 12:03:54 +0000
@@ -8369,6 +8369,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ref col_varchar_key col_varchar_key 4 test.subquery3_t1.col_varchar_key 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-03-14 12:03:54 +0000
@@ -8370,6 +8370,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY table2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY table3 ref col_int_key col_int_key 5 test.table2.col_int_key 1 Using where; Using index
+1 PRIMARY subquery3_t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where; End temporary
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-03-14 12:03:54 +0000
@@ -8444,5 +8444,69 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL col_int_key NULL NULL NULL 1 Using where
+1 PRIMARY table3 eq_ref PRIMARY,col_int_key PRIMARY 4 test.table2.col_int_nokey 1 Using where
+2 SUBQUERY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where
+2 SUBQUERY subquery3_t1 ref col_varchar_key col_varchar_key 4 test.subquery3_t2.col_varchar_key 1
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-03-14 12:03:54 +0000
@@ -8379,5 +8379,69 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL col_int_key NULL NULL NULL 1 Using where
+1 PRIMARY table3 eq_ref PRIMARY,col_int_key PRIMARY 4 test.table2.col_int_nokey 1 Using where
+2 DEPENDENT SUBQUERY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY subquery3_t1 ref col_varchar_key col_varchar_key 4 test.subquery3_t2.col_varchar_key 1 Using where
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-03-14 12:03:54 +0000
@@ -8380,6 +8380,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL col_int_key NULL NULL NULL 1 Using where
+1 PRIMARY table3 eq_ref PRIMARY,col_int_key PRIMARY 4 test.table2.col_int_nokey 1 Using where
+2 DEPENDENT SUBQUERY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY subquery3_t1 ref col_varchar_key col_varchar_key 4 test.subquery3_t2.col_varchar_key 1 Using where
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-03-14 12:03:54 +0000
@@ -8380,6 +8380,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL col_int_key NULL NULL NULL 1 Using where
+1 PRIMARY table3 eq_ref PRIMARY,col_int_key PRIMARY 4 test.table2.col_int_nokey 1 Using where
+2 DEPENDENT SUBQUERY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY subquery3_t1 ref col_varchar_key col_varchar_key 4 test.subquery3_t2.col_varchar_key 1 Using where
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-03-14 12:03:54 +0000
@@ -8381,6 +8381,70 @@ SELECT MIN(pk) FROM t1 WHERE pk IN (SELE
MIN(pk)
NULL
DROP TABLE t1;
+# Bug#13838810: Segfault in evaluate_null_complemented_join_record
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int DEFAULT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,NULL,8,'x');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'x');
+CREATE TABLE t3 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL);
+EXPLAIN SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY table1 ALL NULL NULL NULL NULL 1
+1 PRIMARY table2 ALL col_int_key NULL NULL NULL 1 Using where
+1 PRIMARY table3 eq_ref PRIMARY,col_int_key PRIMARY 4 test.table2.col_int_nokey 1 Using where
+2 DEPENDENT SUBQUERY subquery3_t2 ALL col_varchar_key NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY subquery3_t1 ref col_varchar_key col_varchar_key 4 test.subquery3_t2.col_varchar_key 1 Using where
+SELECT table1.pk,table2.pk, table3.pk
+FROM t2 AS table1
+LEFT JOIN t1 AS table2
+LEFT JOIN t1 AS table3
+ON table3.col_int_key = table2.col_int_key
+ON table3.pk = table2.col_int_nokey AND
+table1.col_varchar_nokey IN (
+SELECT subquery3_t1.col_varchar_nokey
+FROM t3 AS subquery3_t1
+LEFT JOIN t1 AS subquery3_t2
+ON subquery3_t2.col_varchar_key = subquery3_t1.col_varchar_key
+WHERE subquery3_t2.col_int_nokey <> 9
+)
+;
+pk pk pk
+1 NULL NULL
+DROP TABLE t1, t2, t3;
+# End of test for bug#13838810.
# 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-02-13 11:57:09 +0000
+++ b/sql/sql_optimizer.cc 2012-03-14 12:03:54 +0000
@@ -5690,12 +5690,27 @@ make_outerjoin_info(JOIN *join)
tab->on_expr_ref= tbl->join_cond_ref();
tab->cond_equal= tbl->cond_equal;
if (embedding)
- tab->first_upper= embedding->nested_join->first_nested;
+ {
+ // This outer join nest is embedded in another join nest
+ if (embedding->nested_join->nj_map)
+ {
+ // The embedding nest is an outer join nest, link the join-tabs:
+ tab->first_upper= embedding->nested_join->first_nested;
+ }
+ else if (embedding->embedding)
+ {
+ /*
+ The embedding nest is not an outer join nest, but there is a
+ nest that embeds this nest, which must be an outer join nest.
+ */
+ tab->first_upper= embedding->embedding->nested_join->first_nested;
+ }
+ }
}
for ( ; embedding ; embedding= embedding->embedding)
{
- /* Ignore sj-nests: */
- if (!embedding->join_cond())
+ // Ignore all join nests that are not outer join nests (ie semi-joins):
+ if (!embedding->nested_join->nj_map)
continue;
NESTED_JOIN *nested_join= embedding->nested_join;
if (!nested_join->nj_counter)
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3887 to 3888) Bug#13838810 | Roy Lyseng | 14 Mar |
