From: Roy Lyseng
Date: March 29 2012 1:09pm
Subject: bzr push into mysql-trunk branch (roy.lyseng:3893 to 3894) Bug#13897959
List-Archive: http://lists.mysql.com/commits/143358
X-Bug: 13897959
Message-Id: <20120329130947.36D8B211@tyr67.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3894 Roy Lyseng	2012-03-29
      Bug#13897959: Segfault in setup_semijoin_dups_elimination()
      
      WL#5561 added a feature to extend a duplicate weedout range to
      include all inner tables of an outer join. The test for this failed
      to check for a NULL pointer in table patterns like this:
      
        (ot1 LEFT JOIN ot2) semi join it1
      
      When the last table of the semi-join range (it1) had to be checked
      for such extension, the first_inner pointer of its JOIN_TAB can be
      NULL. Following this pointer caused a segmentation fault.
      
      mysql-test/include/subquery_sj.inc
        Test case for bug#13897959
      
      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
        Results for test case for bug#13897959
      
      sql/sql_select.cc
        In setup_semijoin_dups_elimination() extend a test to handle a
        NULL pointer.

    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_select.cc
 3893 Roy Lyseng	2012-03-27
      Bug#13855925: Assert 'prebuilt->search_tuple->n_fields > 0' in ha_innobase::index_read
      
      This is a beauty: The crash occurs because create_ref_for_key() is
      unable to set up a ref key access, so Innodb is called with a key
      containing zero key parts. There is nothing wrong with
      create_ref_for_key(), the key selection made in best_access_path()
      was made based on an invalid "remaining_tables" argument, set up by
      Optimize_table_order::fix_semijoin_strategies().
      
      fix_semijoin_strategies() loops over all join_tabs in the fixed plan,
      building remaining_tables on the fly. This query contains two
      subqueries with three tables each, both converted into semi-joins.
      One of those semi-joins are processed as a materialized table and placed
      at the end of the plan (tables 6 to 8). When building remaining_tables,
      we add "s->table->map" for each table. But for a materialized
      semi-join, we replace join->best_positions contents with information
      from sjm_nest->nested_join->sjm.positions, and the table order in
      the copied plan may be different from the original order.
      However, we make a copy of the "s" pointer in the beginning,
      and after that copying, the "s" pointer is no longer pointing
      to the table representing the same location in the join_tab array.
      The effect is that remaining_tables is populated with one table bit from
      the original plan and two table bits from the final plan.
      
      After having processed all tables in the semi-join, one table is
      omitted from remaining_tables, and it happens that the omitted table
      has a possible key for "table2", which is placed at position 4 in the
      join_tab array. The omitted table is "subquery2_t1", at position 8.
      So when best_access_path() is called for "table2" later, it erroneously
      sets up a ref access based on values from "subquery2_t1".
      
      The solution is easy: Remove the "s" local variable and use
      "pos->table" when referring to the TABLE_LIST object.
      
      The query on the view is optimized differently from a query on the tables.
      This needs investigation and is filed as bug#13890970.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13855925.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
        Added test case results for bug#13855925.
      
      sql/sql_planner.cc
        In Optimize_table_order::fix_semijoin_strategies(), remove the
        local variable "s" and replace "s->" with "pos->table->" everywhere.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_planner.cc
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-03-27 07:57:17 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-03-29 06:47:42 +0000
@@ -5557,4 +5557,43 @@ DROP TABLE t1,t2,t3;
 
 --echo # End of test for bug#13855925.
 
+--echo #
+--echo # Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+--echo #
+
+CREATE TABLE t1 (
+  col_datetime_key DATETIME DEFAULT NULL,
+  KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES
+ ('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+ ('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+ ('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+
+CREATE TABLE t2 (
+  col_date_nokey date DEFAULT NULL,
+  col_time_key time DEFAULT NULL,
+  col_datetime_key datetime DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_varchar_nokey varchar(1) DEFAULT NULL,
+  KEY col_time_key (col_time_key),
+  KEY col_datetime_key (col_datetime_key),
+  KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+  SELECT col_varchar_nokey
+  FROM t2 AS parent1
+  WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13897959.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-03-29 06:47:42 +0000
@@ -8677,5 +8677,37 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-03-29 06:47:42 +0000
@@ -8679,6 +8679,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-03-29 06:47:42 +0000
@@ -8676,5 +8676,37 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-03-29 06:47:42 +0000
@@ -8677,6 +8677,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-03-29 06:47:42 +0000
@@ -8677,6 +8677,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-03-29 06:47:42 +0000
@@ -8677,6 +8677,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-03-29 06:47:42 +0000
@@ -8679,6 +8679,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-03-29 06:47:42 +0000
@@ -8677,5 +8677,37 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-03-29 06:47:42 +0000
@@ -8679,6 +8679,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-03-29 06:47:42 +0000
@@ -8677,5 +8677,37 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-03-29 06:47:42 +0000
@@ -8678,6 +8678,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-03-29 06:47:42 +0000
@@ -8679,6 +8679,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-03-29 06:47:42 +0000
@@ -8753,5 +8753,37 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-03-29 06:47:42 +0000
@@ -8688,5 +8688,37 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-03-29 06:47:42 +0000
@@ -8689,6 +8689,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-03-29 06:47:42 +0000
@@ -8689,6 +8689,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result	2012-03-27 07:57:17 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-03-29 06:47:42 +0000
@@ -8690,6 +8690,38 @@ v
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 # End of test for bug#13855925.
+#
+# Bug#13897959: Segfault in setup_semijoin_dups_elimination()
+#
+CREATE TABLE t1 (
+col_datetime_key DATETIME DEFAULT NULL,
+KEY col_datetime_key (col_datetime_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+('2001-04-18 00:00:00'), ('2008-12-18 19:39:55'),
+('2000-08-01 12:19:39'), ('2004-09-25 21:29:06'),
+('2009-09-20 09:11:48'), ('2004-03-27 09:32:04');
+CREATE TABLE t2 (
+col_date_nokey date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key(col_varchar_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('1900-01-01',NULL,'2001-11-04 19:07:55','x','x');
+SELECT grandparent1.col_varchar_nokey
+FROM t2 AS grandparent1 LEFT JOIN t1 USING (col_datetime_key)
+WHERE grandparent1.col_varchar_nokey IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE parent1.col_time_key > grandparent1.col_date_nokey
+);
+col_varchar_nokey
+DROP TABLE t1, t2;
+# End of test for bug#13897959.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-03-27 07:57:17 +0000
+++ b/sql/sql_select.cc	2012-03-29 06:47:42 +0000
@@ -535,7 +535,8 @@ static bool setup_semijoin_dups_eliminat
         }
 
         JOIN_TAB *const first_sj_tab= join->join_tab+first_table;
-        if (first_sj_tab->first_inner != last_sj_tab->first_inner)
+        if (last_sj_tab->first_inner != NULL &&
+            first_sj_tab->first_inner != last_sj_tab->first_inner)
         {
           /*
             The first duplicate weedout table is an outer table of an outer join

No bundle (reason: useless for push emails).