List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:March 14 2012 12:08pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3887 to 3888) Bug#13838810
View as plain text  
 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#13838810Roy Lyseng14 Mar