List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:February 7 2012 2:51pm
Subject:bzr push into mysql-trunk branch (guilhem.bichot:3851 to 3852) Bug#13651009
View as plain text  
 3852 Guilhem Bichot	2012-02-07
      Fix for Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY HAS AN EMPTY RESULT
      Empty const table didn't cause the query to have an empty result set.
     @ mysql-test/include/subquery.inc
        Though the bug isn't exactly about subqueries (does not require
        a subquery, only a derived table), I put the testcase here
        because I want it tested with subquery materialization when
        merged in the wl6095 tree - it used to cause an assert in
        the wl6095 code).
        Without the code fix, both SELECTs using derived tables
        would return a row full of zeroes ("0" for int,
        "00:00:00" for time...), and "SELECT @var3" would
        return 12.
     @ sql/sql_executor.cc
        comments
     @ sql/sql_optimizer.cc
        Scenario of the bug follows (for the queries in subquery.inc).
        All tables are one-or-zero-row MyISAM tables.
        $subq is of the form:
          SELECT ... FROM t1 WHERE ... ,
        When run as a standalone query, it returns zero rows (because t1 is
        empty).
        But when $subq is put inside the definition of a derived table:
          SELECT ... FROM ($subq) AS alias3;
        then one row is returned, wrongly. Here is how it happens.
        * When we are materializing alias3, we start evaluating $subq; this
        finds that t1 is "an empty const table".
        * We thus come to this place in JOIN::optimize():
          if (const_table_map != found_const_table_map &&
              !(select_options & SELECT_DESCRIBE) &&
              (!conds ||
               !(conds->used_tables() & RAND_TABLE_BIT) ||
               select_lex->master_unit() == &thd->lex->unit))
          {
            zero_result_cause= "no matching row in const table";
            DBUG_PRINT("error",("Error: %s", zero_result_cause));
            goto setup_subq_exit;
          }
        * const_table_map != found_const_table_map (because there is an empty
        const table, which is in const_table_map and not in
        found_const_table_map)
        * this isn't EXPLAIN
        * this isn't the top query (we are inside a derived table)
        * the WHERE clause has RAND_TABLE_BIT because it uses a "random"
        construct, indeed:
        ** in the first testcase of subquery.inc, the trick of writing and
        reading @var3 in the same statement makes the optimizer correctly
        believe that @var3 is a changing value (thus, "random" - unpredictable
        for the optimizer)
        ** in the second testcase, both user variables and "NOT IN
        (inner subquery)" do the same trick.
        So, we don't enter the if() block. No short-circuit. Query is not
        marked as "empty result" in the optimization phase. t1's record buffer
        has been filled with NULLs (see mark_as_null_row() in
        join_read_system()).
        We then reach this place in do_select():
          if (join->tables == join->const_tables)
          {
            if (!join->conds || join->conds->val_int())
        We evaluate the WHERE (remember: on a pseudo-row of t1 filled with
        NULLs). In the first testcase, the WHERE is satisfied by the NULL
        row, so this row is written into the materialized derived temporary
        table. In the second testcase, in all logic the WHERE should not be
        satisfied (NULLs NOT IN (non-empty-inner-subq) should be UNKNOWN), but
        because columns of t1 are not NULLable, triggered conditions are not
        created in the inner subquery (see
        Item_in_subselect::row_value_transformer()), so things go further
        wrong.
        
        Afterwards, this temporary one-NULL-row table is read, and bad data is
        output (zeroes: there is conversion from NULL to zeroes at some stage,
        because columns of t1 are not NULLable; if I declare columns as
        NULLable I get NULLs in the output).
        
        The bugfix: in this code
          if (const_table_map != found_const_table_map &&
              !(select_options & SELECT_DESCRIBE) &&
              (!conds ||
               !(conds->used_tables() & RAND_TABLE_BIT) ||
               select_lex->master_unit() == &thd->lex->unit))
          {
            zero_result_cause= "no matching row in const table";
            DBUG_PRINT("error",("Error: %s", zero_result_cause));
            goto setup_subq_exit;
          }
        the two last conditions are wrong.
        If "const_table_map != found_const_table_map" it means that one empty
        const table has been found. It may have been found in two ways:
        * join_read_system() i.e. it's a table which has really zero rows
        (case of t1, in our scenario). In that case, the result of this query
        specification is guaranteed empty, no matter the WHERE condition, no
        matter if this query specification is a subquery or a top query.
        * join_read_const() i.e. it's a table which has a unique index, and we
        have in the WHERE something like unique_index=constant, and there is
        no match for "constant" in the index. Again, there is no need to check
        any other condition in the if(). make_join_statistics(), which
        extracts const tables, takes proper precautions to detect that "we
        have all information" to mark a table const. For example, if we have
        "unique_index=outer_table.column", where outer_table is outer to this
        query specification, then we won't mark as constant because we know
        that this equality depends on OUTER_REF_TABLE_BIT, which is "not an
        available table" at this stage (search for "// Found everything for
         ref"). Same for "unique_index=RAND()": RAND_TABLE_BIT is not
        available. So there is no need for the test on RAND_TABLE_BIT and
        on the placement of the query specification. By the way, those two
        conditions were added in 2004 with commit comment "af"...
        
        By deleting those wrong extra conditions, we don't let the NULL row go
        out.
        It would also be possible to delete '!(select_options &
        SELECT_DESCRIBE)' but it has been found that this condition
        - causes no wrong query result (it's only in EXPLAIN)
        - leads to a clearer EXPLAIN output: indeed, if we shortcut at this
        stage, EXPLAIN does not contain information about what table was found
        constant.
        - so it was decided to not touch this condition.

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_bka.result
      mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_bka.result
      mysql-test/r/subquery_none_bka_nixbnl.result
      sql/sql_executor.cc
      sql/sql_optimizer.cc
 3851 Guilhem Bichot	2012-02-07 [merge]
      merge from trunk

    modified:
      storage/myisam/mi_dbug.c
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2012-02-07 12:02:00 +0000
+++ b/mysql-test/include/subquery.inc	2012-02-07 14:50:31 +0000
@@ -5730,3 +5730,77 @@ eval $query;
 DROP TABLE t1;
 DROP VIEW v1,v2;
 
+--echo #
+--echo # Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+--echo # HAS AN EMPTY RESULT
+--echo #
+
+CREATE TABLE t1 (
+  pk int NOT NULL,
+  col_int_nokey int NOT NULL,
+  col_int_key int NOT NULL,
+  col_time_key time NOT NULL,
+  col_varchar_key varchar(1) NOT NULL,
+  col_varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key),
+  KEY col_time_key (col_time_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2 (
+  pk int NOT NULL AUTO_INCREMENT,
+  col_int_nokey int NOT NULL,
+  col_int_key int NOT NULL,
+  col_time_key time NOT NULL,
+  col_varchar_key varchar(1) NOT NULL,
+  col_varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key),
+  KEY col_time_key (col_time_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+
+SET @var2:=4, @var3:=8;
+
+--echo
+--echo Testcase without inner subquery
+
+let $subq=
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+      sq4_alias1.col_varchar_key = @var3;
+
+eval EXPLAIN $subq;
+eval $subq;
+SELECT @var3;
+
+# Now as derived table:
+eval EXPLAIN SELECT * FROM ( $subq ) AS alias3;
+eval SELECT * FROM ( $subq ) AS alias3;
+SELECT @var3;
+
+--echo
+--echo Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+let $subq=
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+      NOT IN
+      (SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+              c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+       FROM t2 AS c_sq1_alias1
+       WHERE (c_sq1_alias1.col_int_nokey != @var2
+              OR c_sq1_alias1.pk != @var3));
+
+eval EXPLAIN $subq;
+eval $subq;
+# Now as derived table:
+eval EXPLAIN SELECT * FROM ( $subq ) AS alias3;
+eval SELECT * FROM ( $subq ) AS alias3;
+
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/ps_2myisam.result'
--- a/mysql-test/r/ps_2myisam.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ps_2myisam.result	2012-02-07 13:09:03 +0000
@@ -2962,25 +2962,31 @@ test_sequence
 -- insert into date/time columns --
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c13' at row 1

=== modified file 'mysql-test/r/ps_3innodb.result'
--- a/mysql-test/r/ps_3innodb.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ps_3innodb.result	2012-02-07 13:09:03 +0000
@@ -2945,25 +2945,31 @@ test_sequence
 -- insert into date/time columns --
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c13' at row 1

=== modified file 'mysql-test/r/ps_4heap.result'
--- a/mysql-test/r/ps_4heap.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ps_4heap.result	2012-02-07 13:09:03 +0000
@@ -2946,25 +2946,31 @@ test_sequence
 -- insert into date/time columns --
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c13' at row 1

=== modified file 'mysql-test/r/ps_5merge.result'
--- a/mysql-test/r/ps_5merge.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ps_5merge.result	2012-02-07 13:09:03 +0000
@@ -2882,25 +2882,31 @@ test_sequence
 -- insert into date/time columns --
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c13' at row 1
@@ -5902,25 +5908,31 @@ test_sequence
 -- insert into date/time columns --
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
 Warnings:
 Note	1265	Data truncated for column 'c13' at row 1
+Note	1265	Data truncated for column 'c16' at row 1
 Warning	1265	Data truncated for column 'c17' at row 1
 Warnings:
 Warning	1265	Data truncated for column 'c13' at row 1

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_all.result	2012-02-07 14:50:31 +0000
@@ -6970,4 +6970,114 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_all_bka.result	2012-02-07 14:50:31 +0000
@@ -6971,5 +6971,115 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result	2012-02-07 14:50:31 +0000
@@ -6971,5 +6971,115 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2012-02-07 14:50:31 +0000
@@ -6970,4 +6970,114 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result	2012-02-07 14:50:31 +0000
@@ -6971,5 +6971,115 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result	2012-02-07 14:50:31 +0000
@@ -6971,5 +6971,115 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_none.result	2012-02-07 14:50:31 +0000
@@ -6969,4 +6969,114 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_none_bka.result	2012-02-07 14:50:31 +0000
@@ -6970,5 +6970,115 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_none_bka_nixbnl.result	2012-02-07 12:02:00 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result	2012-02-07 14:50:31 +0000
@@ -6970,5 +6970,115 @@ v
 v
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+# HAS AN EMPTY RESULT
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+col_time_key time NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+SET @var2:=4, @var3:=8;
+
+Testcase without inner subquery
+EXPLAIN SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+@var3:=12	pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+SELECT @var3;
+@var3
+8
+
+Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+EXPLAIN SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3));
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+EXPLAIN SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	c_sq1_alias1	system	PRIMARY,col_varchar_key	NULL	NULL	NULL	1	
+SELECT * FROM ( SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+NOT IN
+(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+FROM t2 AS c_sq1_alias1
+WHERE (c_sq1_alias1.col_int_nokey != @var2
+OR c_sq1_alias1.pk != @var3)) ) AS alias3;
+pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/type_temporal_fractional.result'
--- a/mysql-test/r/type_temporal_fractional.result	2012-02-07 11:56:30 +0000
+++ b/mysql-test/r/type_temporal_fractional.result	2012-02-07 13:09:03 +0000
@@ -17027,6 +17027,9 @@ INSERT INTO t1 VALUES
 (SEC_TO_TIME(46),'20030628121312.000376','20020323065409.039577'),            
 (SEC_TO_TIME(48),'20071219173306.048009',NULL),            
 (SEC_TO_TIME(50),'0000-00-00',NULL);
+Warnings:
+Note	1265	Data truncated for column 'c_time' at row 2
+Note	1265	Data truncated for column 'c_time' at row 3
 SELECT c_time FROM t1;
 c_time
 00:00:00

=== modified file 'mysql-test/r/type_time.result'
--- a/mysql-test/r/type_time.result	2012-01-24 13:17:11 +0000
+++ b/mysql-test/r/type_time.result	2012-02-07 13:09:03 +0000
@@ -1,6 +1,8 @@
 drop table if exists t1;
 create table t1 (t time);
 insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10  1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34");
+Warnings:
+Note	1265	Data truncated for column 't' at row 13
 insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32");
 select * from t1;
 t
@@ -430,5 +432,2330 @@ TIME(MIN(a))
 00:00:00
 DROP TABLE t1;
 #
+# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+# TIME/DATETIME COMPARE"
+#
+# Systematic testing of ref access and range scan
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('00:00:00'),('-24:00:00'),('-48:00:00'),('24:00:00'),('48:00:00');
+CREATE TABLE t2 (col_datetime_key DATETIME, KEY(col_datetime_key)) ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key = col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key = col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key = col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key = col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key = col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+1	SIMPLE	t2	ref	col_datetime_key	col_datetime_key	6	test.t1.col_time_key	1	100.00	Using where; Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key = col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key = col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+1	SIMPLE	t2	ref	col_datetime_key	col_datetime_key	6	test.t1.col_time_key	1	100.00	Using where; Using index
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key = col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key = col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Warning	1713	Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key = col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key = col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Warning	1713	Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key = col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key = col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index
+1	SIMPLE	t2	ref	col_datetime_key	col_datetime_key	6	test.t1.col_time_key	1	100.00	Using where; Using index
+Warnings:
+Warning	1713	Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key = col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key = col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index
+1	SIMPLE	t2	ref	col_datetime_key	col_datetime_key	6	test.t1.col_time_key	1	100.00	Using where; Using index
+Warnings:
+Warning	1713	Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key = col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key = col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key = col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key = col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key = col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key = col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Warning	1713	Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key = col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key = col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Warning	1713	Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key = col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key = col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key = col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key = col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key = col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key = col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Warning	1713	Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key = col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key = col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Warning	1713	Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key = col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key >= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key >= col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key >= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key >= col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-29 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-01-31 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-01 00:00:00
+24:00:00	2012-02-02 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key >= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key >= col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key >= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key >= col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-29 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-01-31 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-01 00:00:00
+24:00:00	2012-02-02 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key >= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key >= col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key >= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key >= col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-29 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-01-31 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-01 00:00:00
+24:00:00	2012-02-02 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key >= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key >= col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key >= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key >= col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-29 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-01-31 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-01 00:00:00
+24:00:00	2012-02-02 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key >= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key >= col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	-48:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-01 00:00:00	48:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key >= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key >= col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key >= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key >= col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	-48:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-01 00:00:00	48:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key >= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key >= col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key >= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key >= col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	-48:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-01 00:00:00	48:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key >= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key >= col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key >= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key >= col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	-48:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-01 00:00:00	48:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key >= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key >= col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key > col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key > col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key > col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key > col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key > col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key > col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key > col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key > col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key > col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key > col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key > col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key > col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key > col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key > col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key > col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key > col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key > col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key > col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key > col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key > col_time_key;
+col_datetime_key	col_time_key
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key > col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key > col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key > col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key > col_time_key;
+col_datetime_key	col_time_key
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key > col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key > col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key > col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key > col_time_key;
+col_datetime_key	col_time_key
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key > col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key > col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key > col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key > col_time_key;
+col_datetime_key	col_time_key
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key <= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key <= col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-29 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-01-31 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-01 00:00:00
+24:00:00	2012-02-02 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key <= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key <= col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key <= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key <= col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-29 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-01-31 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-01 00:00:00
+24:00:00	2012-02-02 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key <= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key <= col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key <= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key <= col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-29 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-01-31 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-01 00:00:00
+24:00:00	2012-02-02 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key <= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key <= col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key <= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key <= col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-30 00:00:00
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-29 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-01-31 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-01 00:00:00
+24:00:00	2012-02-02 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key <= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key <= col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+-24:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+00:00:00	2012-01-31 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+24:00:00	2012-02-01 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+48:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key <= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key <= col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key <= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key <= col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	-48:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-01 00:00:00	48:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key <= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key <= col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key <= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key <= col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	-48:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-01 00:00:00	48:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key <= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key <= col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key <= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key <= col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	-48:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-01 00:00:00	48:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key <= col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key <= col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key <= col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key <= col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	-48:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	-24:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	00:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	24:00:00
+2012-02-01 00:00:00	48:00:00
+2012-02-02 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key < col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key < col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key < col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key < col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key < col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key < col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key < col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 ignore INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key < col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key < col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_time_key < col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key < col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 ignore INDEX (col_datetime_key)
+WHERE col_datetime_key < col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key < col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_time_key < col_datetime_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-31 00:00:00
+-24:00:00	2012-02-01 00:00:00
+-24:00:00	2012-02-02 00:00:00
+-48:00:00	2012-01-30 00:00:00
+-48:00:00	2012-01-31 00:00:00
+-48:00:00	2012-02-01 00:00:00
+-48:00:00	2012-02-02 00:00:00
+00:00:00	2012-02-01 00:00:00
+00:00:00	2012-02-02 00:00:00
+24:00:00	2012-02-02 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key < col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using index
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t1 force INDEX (col_time_key)
+STRAIGHT_JOIN
+t2 force INDEX (col_datetime_key)
+WHERE col_datetime_key < col_time_key;
+col_time_key	col_datetime_key
+-24:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-29 00:00:00
+00:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-29 00:00:00
+24:00:00	2012-01-30 00:00:00
+24:00:00	2012-01-31 00:00:00
+48:00:00	2012-01-29 00:00:00
+48:00:00	2012-01-30 00:00:00
+48:00:00	2012-01-31 00:00:00
+48:00:00	2012-02-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key < col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key < col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key < col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key < col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key < col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key < col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key < col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 ignore INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key < col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key < col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_time_key < col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key < col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 ignore INDEX (col_time_key)
+WHERE col_datetime_key < col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	48:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key < col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_time_key < col_datetime_key;
+col_datetime_key	col_time_key
+2012-01-30 00:00:00	-48:00:00
+2012-01-31 00:00:00	-24:00:00
+2012-01-31 00:00:00	-48:00:00
+2012-02-01 00:00:00	-24:00:00
+2012-02-01 00:00:00	-48:00:00
+2012-02-01 00:00:00	00:00:00
+2012-02-02 00:00:00	-24:00:00
+2012-02-02 00:00:00	-48:00:00
+2012-02-02 00:00:00	00:00:00
+2012-02-02 00:00:00	24:00:00
+EXPLAIN EXTENDED SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key < col_time_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	index	col_datetime_key	col_datetime_key	6	NULL	5	100.00	Using index
+1	SIMPLE	t1	index	col_time_key	col_time_key	4	NULL	5	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
+SELECT * FROM
+t2 force INDEX (col_datetime_key)
+STRAIGHT_JOIN
+t1 force INDEX (col_time_key)
+WHERE col_datetime_key < col_time_key;
+col_datetime_key	col_time_key
+2012-01-29 00:00:00	-24:00:00
+2012-01-29 00:00:00	00:00:00
+2012-01-29 00:00:00	24:00:00
+2012-01-29 00:00:00	48:00:00
+2012-01-30 00:00:00	00:00:00
+2012-01-30 00:00:00	24:00:00
+2012-01-30 00:00:00	48:00:00
+2012-01-31 00:00:00	24:00:00
+2012-01-31 00:00:00	48:00:00
+2012-02-01 00:00:00	48:00:00
+DROP TABLE t1,t2;
+
+# Original test of the bug report
+
+CREATE TABLE t1 (
+pk INT NOT NULL AUTO_INCREMENT,
+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 * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key)
+ON t3.col_time_key > t2.col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t3	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`.`t2`.`col_int_nokey` AS `col_int_nokey`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t3`.`col_time_key` AS `col_time_key` from `test`.`t2` straight_join `test`.`t3` FORCE INDEX (`col_time_key`) where (`test`.`t3`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key)
+ON t3.col_time_key > t2.col_datetime_key;
+col_int_nokey	col_datetime_key	col_varchar_key	col_time_key
+1	2001-11-04 19:07:55	k	00:00:00
+1	2001-11-04 19:07:55	k	00:00:00
+1	2001-11-04 19:07:55	k	00:00:00
+1	2001-11-04 19:07:55	k	00:01:58
+1	2001-11-04 19:07:55	k	00:21:38
+1	2001-11-04 19:07:55	k	02:59:24
+1	2001-11-04 19:07:55	k	03:53:16
+1	2001-11-04 19:07:55	k	04:08:02
+1	2001-11-04 19:07:55	k	05:03:03
+1	2001-11-04 19:07:55	k	07:05:51
+1	2001-11-04 19:07:55	k	09:16:38
+1	2001-11-04 19:07:55	k	10:14:58
+1	2001-11-04 19:07:55	k	10:50:38
+1	2001-11-04 19:07:55	k	11:14:24
+1	2001-11-04 19:07:55	k	15:37:26
+1	2001-11-04 19:07:55	k	15:57:25
+1	2001-11-04 19:07:55	k	16:25:11
+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
+EXPLAIN EXTENDED SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key)
+ON t3.col_time_key > t2.col_datetime_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	col_datetime_key	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where; Using join buffer (Block Nested Loop)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`col_int_nokey` AS `col_int_nokey`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t3`.`col_time_key` AS `col_time_key` from `test`.`t2` straight_join `test`.`t3` IGNORE INDEX (`col_time_key`) where (`test`.`t3`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
+SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key)
+ON t3.col_time_key > t2.col_datetime_key;
+col_int_nokey	col_datetime_key	col_varchar_key	col_time_key
+1	2001-11-04 19:07:55	k	00:00:00
+1	2001-11-04 19:07:55	k	00:00:00
+1	2001-11-04 19:07:55	k	00:00:00
+1	2001-11-04 19:07:55	k	00:01:58
+1	2001-11-04 19:07:55	k	00:21:38
+1	2001-11-04 19:07:55	k	02:59:24
+1	2001-11-04 19:07:55	k	03:53:16
+1	2001-11-04 19:07:55	k	04:08:02
+1	2001-11-04 19:07:55	k	05:03:03
+1	2001-11-04 19:07:55	k	07:05:51
+1	2001-11-04 19:07:55	k	09:16:38
+1	2001-11-04 19:07:55	k	10:14:58
+1	2001-11-04 19:07:55	k	10:50:38
+1	2001-11-04 19:07:55	k	11:14:24
+1	2001-11-04 19:07:55	k	15:37:26
+1	2001-11-04 19:07:55	k	15:57:25
+1	2001-11-04 19:07:55	k	16:25:11
+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
+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=0;
+#
 # End of 5.6 tests
 #

=== modified file 'mysql-test/t/type_time.test'
--- a/mysql-test/t/type_time.test	2012-01-24 13:17:11 +0000
+++ b/mysql-test/t/type_time.test	2012-02-07 13:09:03 +0000
@@ -266,5 +266,180 @@ SELECT TIME(MIN(a)) FROM t1;
 DROP TABLE t1;
 
 --echo #
+--echo # Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH
+--echo # TIME/DATETIME COMPARE"
+--echo #
+
+--echo # Systematic testing of ref access and range scan
+
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
+
+CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('00:00:00'),('-24:00:00'),('-48:00:00'),('24:00:00'),('48:00:00');
+CREATE TABLE t2 (col_datetime_key DATETIME, KEY(col_datetime_key)) ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+
+let $cnt_0=5;
+let $operator= =;
+# For operator in =, >=, >, <=, <
+while ($cnt_0)
+{  
+  let $cnt_1=2;
+  let $first_table=t1;
+  # for table in t1,t2
+  while ($cnt_1)
+  {
+    if ($first_table==t1)
+    {
+      let $first_index=col_time_key;
+      let $second_table=t2;
+      let $second_index=col_datetime_key;
+    }
+    if ($first_table==t2)
+    {
+      let $first_index=col_datetime_key;
+      let $second_table=t1;
+      let $second_index=col_time_key;
+    }
+    let $cnt_2=2;
+    let $first_index_hint=ignore;
+    # for first_index_hint in ignore,force
+    while ($cnt_2)
+    {
+      let $cnt_3=2;
+      let $second_index_hint=ignore;
+      # for second_index_hint in ignore, force
+      while ($cnt_3)
+      {
+        let $cnt_4=2;
+        let $first_operand=col_time_key;
+        # for first_operand in col_time_key, col_datetime_key
+        while ($cnt_4)
+        {
+          if ($first_operand==col_time_key)
+          {
+            let $second_operand=col_datetime_key;
+          }
+          if ($first_operand==col_datetime_key)
+          {
+            let $second_operand=col_time_key;
+          }
+  
+          eval EXPLAIN EXTENDED SELECT * FROM
+               $first_table $first_index_hint INDEX ($first_index)
+               STRAIGHT_JOIN
+               $second_table $second_index_hint INDEX ($second_index)
+               WHERE $first_operand $operator $second_operand;
+          --sorted_result
+          eval SELECT * FROM
+               $first_table $first_index_hint INDEX ($first_index)
+               STRAIGHT_JOIN
+               $second_table $second_index_hint INDEX ($second_index)
+               WHERE $first_operand $operator $second_operand;
+  
+          let $first_operand=col_datetime_key;
+          dec $cnt_4;
+        }
+        let $second_index_hint=force;
+        dec $cnt_3;
+      }
+      let $first_index_hint=force;
+      dec $cnt_2;
+    }
+    let $first_table=t2;
+    dec $cnt_1;
+  }
+  if ($cnt_0==5)
+  {
+    let $operator= >=;
+  }
+  if ($cnt_0==4)
+  {
+    let $operator= >;
+  }
+  if ($cnt_0==3)
+  {
+    let $operator= <=;
+  }
+  if ($cnt_0==2)
+  {
+    let $operator= <;
+  }
+  dec $cnt_0;
+}
+
+DROP TABLE t1,t2;
+
+--echo
+--echo # Original test of the bug report
+--echo
+
+CREATE TABLE t1 (
+  pk INT NOT NULL AUTO_INCREMENT,
+  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 * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key)
+           ON t3.col_time_key > t2.col_datetime_key;
+eval EXPLAIN EXTENDED $query;
+--sorted_result
+eval $query;
+
+let $query=SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key)
+           ON t3.col_time_key > t2.col_datetime_key;
+eval EXPLAIN EXTENDED $query;
+--sorted_result
+eval $query;
+
+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=0; # back to current time
+
+--echo #
 --echo # End of 5.6 tests
 --echo #

=== modified file 'sql/field.cc'
--- a/sql/field.cc	2012-01-31 15:16:16 +0000
+++ b/sql/field.cc	2012-02-07 13:09:03 +0000
@@ -5808,7 +5808,21 @@ longlong Field_timef::val_time_temporal(
 
 int Field_timef::store_internal(const MYSQL_TIME *ltime, int *warnings)
 {
-  return store_packed(TIME_to_longlong_time_packed(ltime));
+  const int res= store_packed(TIME_to_longlong_time_packed(ltime));
+  if (res == 0 && non_zero_date(ltime))
+  {
+    /*
+      The DATE part got lost; we warn, like in
+      Field_newdate::store_internal.
+    */
+    *warnings|= MYSQL_TIME_NOTE_TRUNCATED;
+    /*
+      Field_newdate returns 3 in this case (which value "3" will trigger some
+      code in get_mm_leaf(), see the test for err==3 there).
+      But, to introduce no behaviour change for now, we return 0.
+    */
+  }
+  return res;
 }
 
 

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2012-02-03 07:45:19 +0000
+++ b/sql/opt_range.cc	2012-02-07 13:09:03 +0000
@@ -115,7 +115,7 @@
 #include "sql_select.h"
 #include "opt_trace.h"
 #include "filesort.h"         // filesort_free_buffers
-#include "sql_optimizer.h"    // is_indexed_agg_distinct
+#include "sql_optimizer.h"    // is_indexed_agg_distinct,field_time_cmp_date
 
 using std::min;
 using std::max;
@@ -6354,14 +6354,18 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
 
        WHERE latin1_swedish_ci_colimn = BINARY 'a '
 
-  */
-  if (field->result_type() == STRING_RESULT &&
-      field->match_collation_to_optimize_range() &&
-      value->result_type() == STRING_RESULT &&
-      key_part->image_type == Field::itRAW &&
-      field->charset() != conf_func->compare_collation() &&
-      !(conf_func->compare_collation()->state & MY_CS_BINSORT &&
-        (type == Item_func::EQUAL_FUNC || type == Item_func::EQ_FUNC)))
+    3. Grep for IndexedTimeComparedToDate. If 'value' is a DATETIME part,
+       using the index on the TIME column would retain only the TIME part of
+       'value', giving false comparison results.
+  */
+  if ((field->result_type() == STRING_RESULT &&
+       field->match_collation_to_optimize_range() &&
+       value->result_type() == STRING_RESULT &&
+       key_part->image_type == Field::itRAW &&
+       field->charset() != conf_func->compare_collation() &&
+       !(conf_func->compare_collation()->state & MY_CS_BINSORT &&
+         (type == Item_func::EQUAL_FUNC || type == Item_func::EQ_FUNC))) ||
+      field_time_cmp_date(field, value))
   {
     if (param->using_real_indexes &&
         param->thd->lex->describe & DESCRIBE_EXTENDED)

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2012-01-31 11:19:25 +0000
+++ b/sql/sql_executor.cc	2012-02-07 14:50:31 +0000
@@ -2564,9 +2564,9 @@ test_if_quick_select(JOIN_TAB *tab)
    Reads content of constant table
    @param tab  table
    @param pos  position of table in query plan
-   @retval 0   ok
-   @retval >0  error
-   @retval <0  ??
+   @retval 0   ok, one row was found or one NULL-complemented row was created
+   @retval -1  ok, no row was found and no NULL-complemented row was created
+   @retval 1   error
 */
 
 int
@@ -2687,6 +2687,16 @@ join_read_const_table(JOIN_TAB *tab, POS
 }
 
 
+/**
+  Read a constant table when there is at most one matching row, using a table
+  scan.
+
+  @param tab			Table to read
+
+  @retval  0  Row was found
+  @retval  -1 Row was not found
+  @retval  1  Got an error (other than row not found) during read
+*/
 static int
 join_read_system(JOIN_TAB *tab)
 {
@@ -2713,16 +2723,14 @@ join_read_system(JOIN_TAB *tab)
 
 
 /**
-  Read a [constant] table when there is at most one matching row.
+  Read a constant table when there is at most one matching row, using an
+  index lookup.
 
   @param tab			Table to read
 
-  @retval
-    0	Row was found
-  @retval
-    -1   Row was not found
-  @retval
-    1   Got an error (other than row not found) during read
+  @retval 0  Row was found
+  @retval -1 Row was not found
+  @retval 1  Got an error (other than row not found) during read
 */
 
 static int

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-02-06 12:47:30 +0000
+++ b/sql/sql_optimizer.cc	2012-02-07 14:50:31 +0000
@@ -377,11 +377,9 @@ JOIN::optimize()
   }
 
   if (const_table_map != found_const_table_map &&
-      !(select_options & SELECT_DESCRIBE) &&
-      (!conds ||
-       !(conds->used_tables() & RAND_TABLE_BIT) ||
-       select_lex->master_unit() == &thd->lex->unit)) // upper level SELECT
+      !(select_options & SELECT_DESCRIBE))
   {
+    // There is at least one empty const table
     zero_result_cause= "no matching row in const table";
     DBUG_PRINT("error",("Error: %s", zero_result_cause));
     goto setup_subq_exit;
@@ -4561,10 +4559,23 @@ add_key_field(KEY_FIELD **key_fields,uin
             Can't optimize datetime_column=indexed_varchar_column,
             also can't use indexes if the effective collation
             of the operation differ from the field collation.
+            IndexedTimeComparedToDate: can't optimize
+            'indexed_time = temporal_expr_with_date_part' because:
+            - without index, a TIME column with value '48:00:00' is equal to a
+            DATETIME column with value 'CURDATE() + 2 days'
+            - with ref access into the TIME column, CURDATE() + 2 days becomes
+            "00:00:00" (Field_timef::store_internal() simply extracts the time
+            part from the datetime) which is a lookup key which does not match
+            "48:00:00"; so ref access is not be able to give the same result
+            as without index, so is disabled.
+            On the other hand, we can optimize indexed_datetime = time
+            because Field_temporal_with_date::store_time() will convert
+            48:00:00 to CURDATE() + 2 days which is the correct lookup key.
           */
           if ((!field->is_temporal() && value[0]->is_temporal()) ||
               (field->cmp_type() == STRING_RESULT &&
-               field->charset() != cond->compare_collation()))
+               field->charset() != cond->compare_collation()) ||
+              field_time_cmp_date(field, value[0]))
           {
             warn_index_not_applicable(stat->join->thd, field, possible_keys);
             return;

=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h	2012-01-20 15:30:14 +0000
+++ b/sql/sql_optimizer.h	2012-02-07 13:09:03 +0000
@@ -514,5 +514,16 @@ Item *
 make_cond_for_table(Item *cond, table_map tables, table_map used_table,
                     bool exclude_expensive_cond);
 
+/**
+   Returns true if arguments are a temporal Field having no date,
+   part and a temporal expression having a date part.
+   @param  f  Field
+   @param  v  Expression
+ */
+inline bool field_time_cmp_date(const Field *f, const Item *v)
+{
+  return f->is_temporal() && !f->is_temporal_with_date() &&
+    v->is_temporal_with_date();
+}
 
 #endif /* SQL_OPTIMIZER_INCLUDED */

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (guilhem.bichot:3851 to 3852) Bug#13651009Guilhem Bichot8 Feb