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
10:22:33
12:34:57
00:00:10
00:12:34
12:34:57
123:46:00
00:00:01
01:23:00
01:23:45
00:00:10
-241:22:33
490:22:33
20:33:34
00:00:30
00:12:30
00:12:30
12:30:00
12:30:35
36:30:31
insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
Warnings:
Warning	1265	Data truncated for column 't' at row 1
Warning	1264	Out of range value for column 't' at row 2
Warning	1264	Out of range value for column 't' at row 3
Warning	1264	Out of range value for column 't' at row 4
Warning	1265	Data truncated for column 't' at row 6
select * from t1;
t
10:22:33
12:34:57
00:00:10
00:12:34
12:34:57
123:46:00
00:00:01
01:23:00
01:23:45
00:00:10
-241:22:33
490:22:33
20:33:34
00:00:30
00:12:30
00:12:30
12:30:00
12:30:35
36:30:31
00:00:10
00:00:00
838:59:59
838:59:59
262:22:00
00:00:12
drop table t1;
create table t1 (t time);
insert into t1 values ('09:00:00'),('13:00:00'),('19:38:34'), ('13:00:00'),('09:00:00'),('09:00:00'),('13:00:00'),('13:00:00'),('13:00:00'),('09:00:00');
select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1;
t	time_to_sec(t)	sec_to_time(time_to_sec(t))
09:00:00	32400	09:00:00
13:00:00	46800	13:00:00
19:38:34	70714	19:38:34
13:00:00	46800	13:00:00
09:00:00	32400	09:00:00
09:00:00	32400	09:00:00
13:00:00	46800	13:00:00
13:00:00	46800	13:00:00
13:00:00	46800	13:00:00
09:00:00	32400	09:00:00
select sec_to_time(time_to_sec(t)) from t1;
sec_to_time(time_to_sec(t))
09:00:00
13:00:00
19:38:34
13:00:00
09:00:00
09:00:00
13:00:00
13:00:00
13:00:00
09:00:00
drop table t1;
CREATE TABLE t1 (t TIME);
INSERT INTO t1 VALUES (+10), (+10.0), (+10e0);
INSERT INTO t1 VALUES (-10), (-10.0), (-10e0);
SELECT * FROM t1;
t
00:00:10
00:00:10
00:00:10
-00:00:10
-00:00:10
-00:00:10
DROP TABLE t1;
SELECT CAST(235959.123456 AS TIME);
CAST(235959.123456 AS TIME)
23:59:59
SELECT CAST(0.235959123456e+6 AS TIME);
CAST(0.235959123456e+6 AS TIME)
23:59:59
SELECT CAST(235959123456e-6 AS TIME);
CAST(235959123456e-6 AS TIME)
23:59:59
SELECT CAST(235959.1234567 AS TIME);
CAST(235959.1234567 AS TIME)
23:59:59
SELECT CAST(0.2359591234567e6 AS TIME);
CAST(0.2359591234567e6 AS TIME)
23:59:59
SELECT CAST(0.2359591234567e+30 AS TIME);
CAST(0.2359591234567e+30 AS TIME)
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '2.359591234567e29'
End of 4.1 tests
select cast('100:55:50' as time) < cast('24:00:00' as time);
cast('100:55:50' as time) < cast('24:00:00' as time)
0
select cast('100:55:50' as time) < cast('024:00:00' as time);
cast('100:55:50' as time) < cast('024:00:00' as time)
0
select cast('300:55:50' as time) < cast('240:00:00' as time);
cast('300:55:50' as time) < cast('240:00:00' as time)
0
select cast('100:55:50' as time) > cast('24:00:00' as time);
cast('100:55:50' as time) > cast('24:00:00' as time)
1
select cast('100:55:50' as time) > cast('024:00:00' as time);
cast('100:55:50' as time) > cast('024:00:00' as time)
1
select cast('300:55:50' as time) > cast('240:00:00' as time);
cast('300:55:50' as time) > cast('240:00:00' as time)
1
create table t1 (f1 time);
insert into t1 values ('24:00:00');
select cast('24:00:00' as time) = (select f1 from t1);
cast('24:00:00' as time) = (select f1 from t1)
1
drop table t1;
create table t1(f1 time, f2 time);
insert into t1 values('20:00:00','150:00:00');
select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
1
1
drop table t1;
CREATE TABLE  t1 (
f2 date NOT NULL,
f3 int(11) unsigned NOT NULL default '0',
PRIMARY KEY  (f3, f2)
);
insert into t1 values('2007-07-01', 1);
insert into t1 values('2007-07-01', 2);
insert into t1 values('2007-07-02', 1);
insert into t1 values('2007-07-02', 2);
SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2;
sum(f3)
3
drop table t1;
#
# Bug #44792: valgrind warning when casting from time to time
#
CREATE TABLE t1 (c TIME);
INSERT INTO t1 VALUES ('0:00:00');
SELECT CAST(c AS TIME) FROM t1;
CAST(c AS TIME)
00:00:00
DROP TABLE t1;
End of 5.0 tests
#
# Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values
#
SET @@timestamp=UNIX_TIMESTAMP('2001-01-01 01:00:00');
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES ('23:38:57');
SELECT TIMESTAMP(f1,'1') FROM t1;
TIMESTAMP(f1,'1')
2001-01-01 23:38:58
DROP TABLE t1;
SET @@timestamp=default;
End of 5.1 tests
CREATE TABLE t1 (f1 TIME);
INSERT INTO t1 VALUES ('24:00:00');
SELECT      '24:00:00' = (SELECT f1 FROM t1);
'24:00:00' = (SELECT f1 FROM t1)
1
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1)
1
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1)
0
TRUNCATE t1;
INSERT INTO t1 VALUES ('-24:00:00');
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1)
0
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1)
1
SELECT '-24:00:00' = (SELECT f1 FROM t1);
'-24:00:00' = (SELECT f1 FROM t1)
1
DROP TABLE t1;
#
# Start of 5.6 tests
#
#
# WL#946 Testing <=> operator with TIME
#
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('20:00:00'),('19:20:30');
SELECT * FROM t1 WHERE a<=>'19:20:30';
a
19:20:30
SELECT * FROM t1 WHERE a<=>TIME'19:20:30';
a
19:20:30
SELECT * FROM t1 WHERE a<=>192030;
a
19:20:30
DROP TABLE t1;
#
# WL#946: Testing rounding
#
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:10:10.9999994'), ('10:10:10.9999995');
INSERT INTO t1 VALUES (101010.9999994), (101010.9999995);
SELECT * FROM t1;
a
10:10:11
10:10:11
10:10:11
10:10:11
DROP TABLE t1;
#
# Before WL#946 TIME did not reject too big negative minutes/seconds
#
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('-10:60:59'), ('-10:59:60'), (-106059), (-105960);
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
Warning	1264	Out of range value for column 'a' at row 2
Warning	1264	Out of range value for column 'a' at row 3
Warning	1264	Out of range value for column 'a' at row 4
SELECT * FROM t1;
a
00:00:00
00:00:00
00:00:00
00:00:00
DROP TABLE t1;
#
# WL#946 Make sure case from number to TIME properly handles
# too big negative minutes/secons
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (-106059), (-105960);
SELECT CAST(a AS TIME) FROM t1;
CAST(a AS TIME)
NULL
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '-106059'
Warning	1292	Truncated incorrect time value: '-105960'
DROP TABLE t1;
#
# WL#946 Checking function TIME()
#
SELECT TIME('1000009:10:10');
TIME('1000009:10:10')
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '1000009:10:10'
SELECT TIME('1000009:10:10.1999999999999');
TIME('1000009:10:10.1999999999999')
838:59:59.000000
Warnings:
Warning	1292	Truncated incorrect time value: '1000009:10:10.1999999999999'
SELECT TIME('10000090:10:10');
TIME('10000090:10:10')
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '10000090:10:10'
SELECT TIME('10000090:10:10.1999999999999');
TIME('10000090:10:10.1999999999999')
838:59:59.000000
Warnings:
Warning	1292	Truncated incorrect time value: '10000090:10:10.1999999999999'
SELECT TIME('100000900:10:10');
TIME('100000900:10:10')
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '100000900:10:10'
SELECT TIME('100000900:10:10.1999999999999');
TIME('100000900:10:10.1999999999999')
838:59:59.000000
Warnings:
Warning	1292	Truncated incorrect time value: '100000900:10:10.1999999999999'
SELECT TIME('1000009000:10:10');
TIME('1000009000:10:10')
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '1000009000:10:10'
SELECT TIME('1000009000:10:10.1999999999999');
TIME('1000009000:10:10.1999999999999')
838:59:59.000000
Warnings:
Warning	1292	Truncated incorrect time value: '1000009000:10:10.1999999999999'
SELECT TIME('10000090000:10:10');
TIME('10000090000:10:10')
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '10000090000:10:10'
SELECT TIME('10000090000:10:10.1999999999999');
TIME('10000090000:10:10.1999999999999')
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '10000090000:10:10.1999999999999'
#
# Checking Item_func_if::val_str with TIME arguments
#
SELECT CAST(IF(1, TIME'00:00:00',TIME'00:00:00') AS CHAR);
CAST(IF(1, TIME'00:00:00',TIME'00:00:00') AS CHAR)
00:00:00
#
# Checking Item_func_case::val_str with TIME arguments
#
SELECT CAST(CASE WHEN 1 THEN TIME'00:00:00' ELSE TIME'00:00:00' END AS CHAR);
CAST(CASE WHEN 1 THEN TIME'00:00:00' ELSE TIME'00:00:00' END AS CHAR)
00:00:00
#
# Testing CASE with TIME type without a found item
#
SELECT CAST(CASE WHEN 0 THEN '01:01:01' END AS TIME);
CAST(CASE WHEN 0 THEN '01:01:01' END AS TIME)
NULL
SELECT CAST(CASE WHEN 0 THEN TIME'01:01:01' END AS TIME);
CAST(CASE WHEN 0 THEN TIME'01:01:01' END AS TIME)
NULL
#
# Testing COALESCE with TIME type without a found item
#
SELECT COALESCE(TIME(NULL));
COALESCE(TIME(NULL))
NULL
#
# Testing TIME field with NULL value with NOT IN
#
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 WHERE a NOT IN (TIME'20:20:20',TIME'10:10:10');
a
DROP TABLE t1;
#
# Testing Item_func_numhybrid::val_int when TIME type
#
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:10:10');
SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1;
CAST(COALESCE(a,a) AS SIGNED)
101010
DROP TABLE t1;
#
# Testing Item_func_numhybrid::val_decimal when TIME type
#
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:10:10');
SELECT CAST(COALESCE(a,a) AS DECIMAL(23,6)) FROM t1;
CAST(COALESCE(a,a) AS DECIMAL(23,6))
101010.000000
DROP TABLE t1;
#
# Testing Item_func_numhybrid::get_time  when non-temporal type
#
SELECT CAST(COALESCE(10,20) AS TIME);
CAST(COALESCE(10,20) AS TIME)
00:00:10
#
# Testing Item_func_min_max::get_time when DATE type and NULL
#
SELECT CAST(LEAST(DATE(NULL), DATE(NULL)) AS TIME);
CAST(LEAST(DATE(NULL), DATE(NULL)) AS TIME)
NULL
#
# Testing Item_func_min_max::get_time with non-temporal arguments
#
SELECT CAST(LEAST(111111,222222) AS TIME);
CAST(LEAST(111111,222222) AS TIME)
11:11:11
#
# Item::get_time_from_numeric
#
SELECT CAST(SUM(0) AS TIME);
CAST(SUM(0) AS TIME)
00:00:00
SELECT CAST(SUM(0 + 0e0) AS TIME);
CAST(SUM(0 + 0e0) AS TIME)
00:00:00
SET timestamp=1322115328;
SELECT CAST(UNIX_TIMESTAMP() AS TIME);
CAST(UNIX_TIMESTAMP() AS TIME)
NULL
Warnings:
Warning	1292	Truncated incorrect time value: '1322115328'
SET timestamp=default;
#
# Item::get_time_from_non_temporal
#
SELECT TIME(154559.616 + 0e0);
TIME(154559.616 + 0e0)
15:45:59.616000
#
# Item_name_const::get_time
#
SELECT TIME(NAME_CONST('a', 0));
TIME(NAME_CONST('a', 0))
00:00:00
#
# Item_cache_datetime::get_time
#
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES (0);
SELECT TIME(MIN(a)) FROM t1;
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;
ANALYZE TABLE t1;
ANALYZE TABLE t2;
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	NULL
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	NULL
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	1739	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	1739	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	1739	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	1739	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	NULL
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	NULL
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	NULL
1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
Warnings:
Warning	1739	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	NULL
1	SIMPLE	t1	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
Warnings:
Warning	1739	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	1739	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	ALL	col_time_key	NULL	NULL	NULL	5	100.00	Range checked for each record (index map: 0x1)
Warnings:
Warning	1739	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	NULL
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	NULL
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	NULL
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`.`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	NULL
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	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`.`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	NULL
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	NULL
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	NULL
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	NULL
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`.`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	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`.`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	NULL
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	NULL
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	NULL
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`.`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	NULL
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	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`.`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	NULL
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	NULL
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	NULL
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	NULL
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`.`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	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`.`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	NULL
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	NULL
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	NULL
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`.`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	NULL
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	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`.`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	NULL
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	NULL
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	NULL
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	NULL
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`.`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	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`.`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	NULL
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	NULL
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	NULL
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`.`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	NULL
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	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`.`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	NULL
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	NULL
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	NULL
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	NULL
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`.`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	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`.`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');
ANALYZE TABLE t1;
ANALYZE TABLE t2;
ANALYZE TABLE t3;
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	NULL
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	NULL
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
DROP TABLE t1,t2,t3;
SET TIMESTAMP = DEFAULT;
#
# End of 5.6 tests
#
