List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:May 3 2012 1:36pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3901 to 3902) WL#5561
View as plain text  
 3902 Roy Lyseng	2012-05-03
      WL#5561 - Enable semi-join transformation with outer join queries
      
      Fourth worklog commit, after Oystein's review.
      
      mysql-test/include/subquery_sj.inc
        Subquery-related test moved from type_time.test.
      
      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
        Updated test results.
      
      mysql-test/r/type_time.result
        Updated test results.
      
      mysql-test/t/type_time.test
        Subquery-related test moved to subquery_sj.inc.
       
      sql/sql_optimizer.cc
        Moved test for semi-join materialization applicability into
        semijoin_types_allow_materialization().

    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
      mysql-test/r/type_time.result
      mysql-test/t/type_time.test
      sql/sql_optimizer.cc
 3901 Roy Lyseng	2012-04-30
      Bug#13971022: Assert 'keyparts > 0' failed in create_ref_for_key...
      
      This bug has its cause in two problems in the code base.
      
      First, there is a bug in Optimize_table_order::fix_semijoin_strategies()
      that does not properly clear semi-join plan data that has been rejected.
      Second, there is a bug in Optimize_table_order::advance_sj_state() when
      keeping track of potential LooseScan table orders.
      
      For the first bug, when fix_semijoin_strategies() is invoked, there
      are two potential semi-join strategies in join->best_positions:
      
      Positions     0       1       2       3
                   <-- DupsWeedout -->
                   <-------- LooseScan ------>
      
      fix_semijoin_strategies() scans best_positions in reverse order,
      finding (correctly) the LooseScan strategy when looking at
      best_positions[3]. (After greedy_search() procedure, semi-join
      strategy choices are recorded in the last element.) The strategy
      information is then re-arranged so that semi-join strategy selection
      is recorded in the first element, and all other elements should be
      cleared. However, on doing this, only one element is actually cleared,
      because the number of elements to clear is taken from n_sj_tables.
      Thus, on the next round, the DupsWeedout strategy is found in
      best_positions[2], and then we have overlapping semi-join strategies,
      which is not supported by the optimizer.
      This problem is fixed by clearing semi-join information for all
      elements of best_positions that are covered by the semi-join strategy.
      
      For the second bug, look at the proposed table order:
      
        subquery3_t1 - t3 - table1 - table2
      
      subquery3_t1 is a semi-join inner table, for which a LooseScan strategy
      is possible. When looking at the next table (t3), which is another
      semi-join inner table from another semi-join nest, advance_sj_state()
      adds this table to the LooseScan prefix, even though this table has
      forward references (to table table1), and it belongs to another
      semi-join nest. Other semi-join inner tables must be semi-joined to
      the LooseScan driving table by application of FirstMatch strategy, and
      they must be in the same semi-join nest for this strategy to be possible.
      Thus, the criteria for setting up table order for LooseScan are wrong.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13971022.
      
      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#13971022.
      
      sql/sql_planner.cc
        In Optimize_table_order::fix_semijoin_strategies(), made sure that
        semi-join strategy information is properly cleared after fixing
        each strategy.
        In Optimize_table_order::advance_sj_state(), changed the LooseScan
        table order check to make sure that only tables from one semi-join
        nest take part in LooseScan operation.

    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
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-04-30 12:15:37 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-05-03 13:35:42 +0000
@@ -6062,4 +6062,69 @@ DROP TABLE t1, t2, t3;
 
 --echo # End of test for bug#13971022.
 
+--echo #
+--echo # Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+--echo # TIME/DATETIME COMPARE" - Subquery part of test.
+--echo #
+
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+
+CREATE TABLE t1 (
+  pk INT NOT NULL,
+  col_int_nokey INT,
+  col_int_key INT NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+
+CREATE TABLE t2 (
+  col_int_nokey INT NOT NULL,
+  col_datetime_key DATETIME NOT NULL,
+  col_varchar_key VARCHAR(1) NOT NULL,
+  KEY col_datetime_key (col_datetime_key),
+  KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+
+CREATE TABLE t3 (
+  col_time_key TIME,
+  KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+
+let $query=
+SELECT outr.col_int_nokey
+FROM t2 as outr
+  STRAIGHT_JOIN t3 AS outr2
+  ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+  SELECT col_int_key
+  FROM t1 AS innr
+  WHERE innr.pk >= innr.col_int_nokey
+) AND (
+  outr.col_int_nokey <= 6
+  OR
+  outr.col_varchar_key IS NULL
+);
+eval EXPLAIN EXTENDED $query;
+--sorted_result
+eval $query;
+
+DROP TABLE t1,t2,t3;
+
+SET TIMESTAMP = DEFAULT;
+
+--echo # End of test for bug#13623473.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2012-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-05-03 13:35:42 +0000
@@ -9613,5 +9613,94 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; FirstMatch(outr)
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-05-03 13:35:42 +0000
@@ -9618,6 +9618,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; FirstMatch(outr); Using join buffer (Batched Key Access)
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-05-03 13:35:42 +0000
@@ -9619,6 +9619,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; FirstMatch(outr); Using join buffer (Batched Key Access)
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-05-03 13:35:42 +0000
@@ -9619,6 +9619,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; FirstMatch(outr); Using join buffer (Batched Key Access (unique))
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-05-03 13:35:42 +0000
@@ -9597,5 +9597,94 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; Start temporary; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-05-03 13:35:42 +0000
@@ -9598,6 +9598,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-05-03 13:35:42 +0000
@@ -9606,6 +9606,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-05-03 13:35:42 +0000
@@ -9599,6 +9599,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-05-03 13:35:42 +0000
@@ -9596,6 +9596,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; FirstMatch(outr)
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-05-03 13:35:42 +0000
@@ -9597,6 +9597,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; FirstMatch(outr)
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-05-03 13:35:42 +0000
@@ -9605,6 +9605,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; FirstMatch(outr)
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-05-03 13:35:42 +0000
@@ -9598,6 +9598,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; FirstMatch(outr)
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-05-03 13:35:42 +0000
@@ -9598,5 +9598,94 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; Start temporary; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-05-03 13:35:42 +0000
@@ -9599,6 +9599,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-05-03 13:35:42 +0000
@@ -9607,6 +9607,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-05-03 13:35:42 +0000
@@ -9600,6 +9600,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-05-03 13:35:42 +0000
@@ -9611,5 +9611,94 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; Start temporary; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-05-03 13:35:42 +0000
@@ -9612,6 +9612,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-05-03 13:35:42 +0000
@@ -9613,6 +9613,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-05-03 13:35:42 +0000
@@ -9613,6 +9613,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	SIMPLE	innr	ref	col_int_key	col_int_key	4	test.outr.col_int_nokey	1	100.00	Using where; End temporary
+1	SIMPLE	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` semi join (`test`.`t1` `innr`) straight_join `test`.`t3` `outr2` where ((`test`.`innr`.`col_int_key` = `test`.`outr`.`col_int_nokey`) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-05-03 13:35:42 +0000
@@ -9683,5 +9683,94 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	PRIMARY	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+2	SUBQUERY	innr	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,`test`.`outr`.`col_int_nokey` in ( <materialize> (/* select#2 */ select `test`.`innr`.`col_int_key` from `test`.`t1` `innr` where (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) ), <primary_index_lookup>(`test`.`outr`.`col_int_nokey` in <temporary table> on distinct_key where ((`test`.`outr`.`col_int_nokey` = `materialized subselect`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-05-03 13:35:42 +0000
@@ -9598,5 +9598,94 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	PRIMARY	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+2	DEPENDENT SUBQUERY	innr	index_subquery	col_int_key	col_int_key	4	func	1	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-05-03 13:35:42 +0000
@@ -9599,6 +9599,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	PRIMARY	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+2	DEPENDENT SUBQUERY	innr	index_subquery	col_int_key	col_int_key	4	func	1	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-05-03 13:35:42 +0000
@@ -9599,6 +9599,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	PRIMARY	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+2	DEPENDENT SUBQUERY	innr	index_subquery	col_int_key	col_int_key	4	func	1	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # 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-04-30 12:15:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-05-03 13:35:42 +0000
@@ -9600,6 +9600,95 @@ FROM t1 AS subquery3_t1
 field1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13971022.
+#
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE" - Subquery part of test.
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
+(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
+(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
+(27,3,5), (28,6,0), (29,6,3);
+CREATE TABLE t2 (
+col_int_nokey INT NOT NULL,
+col_datetime_key DATETIME NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+KEY col_datetime_key (col_datetime_key),
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');
+CREATE TABLE t3 (
+col_time_key TIME,
+KEY col_time_key (col_time_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
+('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
+('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
+('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
+('02:59:24'), ('00:01:58');
+EXPLAIN EXTENDED SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
+1	PRIMARY	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
+2	DEPENDENT SUBQUERY	innr	index_subquery	col_int_key	col_int_key	4	func	1	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
+SELECT outr.col_int_nokey
+FROM t2 as outr
+STRAIGHT_JOIN t3 AS outr2
+ON outr2.col_time_key > outr.col_datetime_key
+WHERE outr.col_int_nokey IN (
+SELECT col_int_key
+FROM t1 AS innr
+WHERE innr.pk >= innr.col_int_nokey
+) AND (
+outr.col_int_nokey <= 6
+OR
+outr.col_varchar_key IS NULL
+);
+col_int_nokey
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+DROP TABLE t1,t2,t3;
+SET TIMESTAMP = DEFAULT;
+# End of test for bug#13623473.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/type_time.result'
--- a/mysql-test/r/type_time.result	2012-04-11 12:12:00 +0000
+++ b/mysql-test/r/type_time.result	2012-05-03 13:35:42 +0000
@@ -2701,64 +2701,8 @@ col_int_nokey	col_datetime_key	col_varch
 1	2001-11-04 19:07:55	k	19:22:21
 1	2001-11-04 19:07:55	k	19:47:59
 1	2001-11-04 19:07:55	k	21:22:34
-set @optimizer_switch_saved=@@session.optimizer_switch;
-set @@session.optimizer_switch='semijoin=off,materialization=off';
-EXPLAIN EXTENDED SELECT outr.col_int_nokey
-FROM t2 as outr
-STRAIGHT_JOIN t3 AS outr2
-ON outr2.col_time_key > outr.col_datetime_key
-WHERE outr.col_int_nokey IN (
-SELECT col_int_key
-FROM t1 AS innr
-WHERE innr.pk >= innr.col_int_nokey
-) AND (
-outr.col_int_nokey <= 6
-OR
-outr.col_varchar_key IS NULL
-);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	outr	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	Using where
-1	PRIMARY	outr2	ALL	col_time_key	NULL	NULL	NULL	20	100.00	Range checked for each record (index map: 0x1)
-2	DEPENDENT SUBQUERY	innr	index_subquery	col_int_key	col_int_key	4	func	1	100.00	Using where
-Warnings:
-Note	1003	/* select#1 */ select `test`.`outr`.`col_int_nokey` AS `col_int_nokey` from `test`.`t2` `outr` straight_join `test`.`t3` `outr2` where (<in_optimizer>(`test`.`outr`.`col_int_nokey`,<exists>(<index_lookup>(<cache>(`test`.`outr`.`col_int_nokey`) in t1 on col_int_key where ((`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (<cache>(`test`.`outr`.`col_int_nokey`) = `test`.`innr`.`col_int_key`))))) and (`test`.`outr`.`col_int_nokey` <= 6) and (`test`.`outr2`.`col_time_key` > `test`.`outr`.`col_datetime_key`))
-SELECT outr.col_int_nokey
-FROM t2 as outr
-STRAIGHT_JOIN t3 AS outr2
-ON outr2.col_time_key > outr.col_datetime_key
-WHERE outr.col_int_nokey IN (
-SELECT col_int_key
-FROM t1 AS innr
-WHERE innr.pk >= innr.col_int_nokey
-) AND (
-outr.col_int_nokey <= 6
-OR
-outr.col_varchar_key IS NULL
-);
-col_int_nokey
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-set @@session.optimizer_switch=@optimizer_switch_saved;
 DROP TABLE t1,t2,t3;
-SET TIMESTAMP=0;
+SET TIMESTAMP = DEFAULT;
 #
 # End of 5.6 tests
 #

=== modified file 'mysql-test/t/type_time.test'
--- a/mysql-test/t/type_time.test	2012-02-13 11:57:09 +0000
+++ b/mysql-test/t/type_time.test	2012-05-03 13:35:42 +0000
@@ -420,30 +420,8 @@ eval EXPLAIN EXTENDED $query;
 --sorted_result
 eval $query;
 
-set @optimizer_switch_saved=@@session.optimizer_switch;
-set @@session.optimizer_switch='semijoin=off,materialization=off';
-
-let $query=SELECT outr.col_int_nokey
-FROM t2 as outr
-  STRAIGHT_JOIN t3 AS outr2
-  ON outr2.col_time_key > outr.col_datetime_key
-WHERE outr.col_int_nokey IN (
-  SELECT col_int_key
-  FROM t1 AS innr
-  WHERE innr.pk >= innr.col_int_nokey
-) AND (
-  outr.col_int_nokey <= 6
-  OR
-  outr.col_varchar_key IS NULL
-);
-eval EXPLAIN EXTENDED $query;
---sorted_result
-eval $query;
-
-set @@session.optimizer_switch=@optimizer_switch_saved;
-
 DROP TABLE t1,t2,t3;
-SET TIMESTAMP=0; # back to current time
+SET TIMESTAMP = DEFAULT;
 
 --echo #
 --echo # End of 5.6 tests

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-04-30 06:56:15 +0000
+++ b/sql/sql_optimizer.cc	2012-05-03 13:35:42 +0000
@@ -3558,11 +3558,11 @@ void semijoin_types_allow_materializatio
   List_iterator<Item> it1(sj_nest->nested_join->sj_outer_exprs);
   List_iterator<Item> it2(sj_nest->nested_join->sj_inner_exprs);
 
-  sj_nest->nested_join->sjm.scan_allowed= FALSE;
-  sj_nest->nested_join->sjm.lookup_allowed= FALSE;
+  sj_nest->nested_join->sjm.scan_allowed= false;
+  sj_nest->nested_join->sjm.lookup_allowed= false;
 
-  bool all_are_fields= TRUE;
-  bool blobs_involved= FALSE;
+  bool all_are_fields= true;
+  bool blobs_involved= false;
   Item *outer, *inner;
   while (outer= it1++, inner= it2++)
   {
@@ -3574,6 +3574,25 @@ void semijoin_types_allow_materializatio
   }
   sj_nest->nested_join->sjm.scan_allowed= all_are_fields;
   sj_nest->nested_join->sjm.lookup_allowed= !blobs_involved;
+
+  if (sj_nest->embedding)
+  {
+    DBUG_ASSERT(sj_nest->embedding->join_cond());
+    /*
+      There are two issues that prevent materialization strategy from being
+      used when a semi-join nest is on the inner side of an outer join:
+      1. If the semi-join contains dependencies to outer tables,
+         materialize-scan strategy cannot be used.
+      2. sub_select_sjm() lacks support for setup of first_unmatched
+         and evaluation of triggered conditions, as in
+         evaluate_null_complemented_join_record().
+      Handle this by disabling materialization strategies:
+    */
+    sj_nest->nested_join->sjm.scan_allowed= false;
+    sj_nest->nested_join->sjm.lookup_allowed= false;
+    DBUG_VOID_RETURN;
+  }
+
   DBUG_PRINT("info",("semijoin_types_allow_materialization: ok, allowed"));
 
   DBUG_VOID_RETURN;
@@ -3996,24 +4015,6 @@ static bool optimize_semijoin_nests_for_
         Check whether data types allow execution with materialization.
       */
       semijoin_types_allow_materialization(sj_nest);
-
-      if (sj_nest->embedding)
-      {
-        DBUG_ASSERT(sj_nest->embedding->join_cond());
-        /*
-          There are two issues that prevent materialization strategy
-          from being used when a semi-join nest is on the inner side of
-          an outer join:
-          1. If the semi-join contains dependencies to outer tables,
-             materialize-scan strategy cannot be used.
-          2. sub_select_sjm() lacks support for setup of first_unmatched
-             and evaluation of triggered conditions, as in
-             evaluate_null_complemented_join_record().
-          Handle this by disabling materialization strategies:
-        */
-        sj_nest->nested_join->sjm.scan_allowed= false;
-        sj_nest->nested_join->sjm.lookup_allowed= false;
-      }
 
       if (sj_nest->nested_join->sjm.scan_allowed || 
           sj_nest->nested_join->sjm.lookup_allowed)

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3901 to 3902) WL#5561Roy Lyseng8 May