diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2014-06-11 10:08:08 +0200 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2014-06-11 10:08:08 +0200 |
| commit | 6e8d49b8f51a573fa13a40a9b0bffe5424830db6 (patch) | |
| tree | 5f78e028b36ce27bedde8be77bde5adea2ad67fc | |
| parent | 2510f9c6066f8702fad6865330bf99031f9b4b60 (diff) | |
| download | mariadb-git-6e8d49b8f51a573fa13a40a9b0bffe5424830db6.tar.gz | |
MDEV-6065 MySQL Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH TIME/DATETIME COMPARE"
fix for ref like "indexed_time = datetime"
| -rw-r--r-- | mysql-test/r/type_time_6065.result | 2310 | ||||
| -rw-r--r-- | mysql-test/t/type_time_6065.test | 177 | ||||
| -rw-r--r-- | sql/field.cc | 55 | ||||
| -rw-r--r-- | sql/field.h | 12 | ||||
| -rw-r--r-- | sql/sql_time.cc | 3 | ||||
| -rw-r--r-- | sql/sql_time.h | 1 |
6 files changed, 2553 insertions, 5 deletions
diff --git a/mysql-test/r/type_time_6065.result b/mysql-test/r/type_time_6065.result new file mode 100644 index 00000000000..db3efc3bcbb --- /dev/null +++ b/mysql-test/r/type_time_6065.result @@ -0,0 +1,2310 @@ +SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35'); +CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key)); +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)); +INSERT INTO t2 SELECT * FROM t1; +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key = col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 test.t1.col_time_key 1 100.00 Using where; Using index +Warnings: +Note 1003 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 9 test.t1.col_time_key 1 100.00 Using where; Using index +Warnings: +Note 1003 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 (flat, BNL join) +Warnings: +Note 1003 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 (flat, BNL join) +Warnings: +Note 1003 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 9 test.t1.col_time_key 1 100.00 Using where; Using index +Warnings: +Note 1003 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 9 test.t1.col_time_key 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key = col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key = col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 Using where +1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index +Warnings: +Note 1003 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 Using where +1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using where; Using index +1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index +Warnings: +Note 1003 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 9 NULL 5 100.00 Using where; Using index +1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key = col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key >= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `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 (flat, BNL join) +Warnings: +Note 1003 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 (flat, BNL join) +Warnings: +Note 1003 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 `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 `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key >= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key >= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 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 `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 9 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 `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key >= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key > col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `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 (flat, BNL join) +Warnings: +Note 1003 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 (flat, BNL join) +Warnings: +Note 1003 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 `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 `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key > col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key > col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 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 `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 9 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 `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key > col_time_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key <= col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-30 00:00:00 +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-29 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-01-31 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-01 00:00:00 +24:00:00 2012-02-02 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `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 (flat, BNL join) +Warnings: +Note 1003 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 (flat, BNL join) +Warnings: +Note 1003 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 `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 `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key <= col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +-24:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +00:00:00 2012-01-31 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +24:00:00 2012-02-01 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +48:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key <= col_datetime_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 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 `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 9 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 `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) +SELECT * FROM +t2 force INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key <= col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 -48:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 -24:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 00:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 24:00:00 +2012-02-01 00:00:00 48:00:00 +2012-02-02 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 ignore INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_time_key < col_datetime_key; +col_time_key col_datetime_key +-24:00:00 2012-01-31 00:00:00 +-24:00:00 2012-02-01 00:00:00 +-24:00:00 2012-02-02 00:00:00 +-48:00:00 2012-01-30 00:00:00 +-48:00:00 2012-01-31 00:00:00 +-48:00:00 2012-02-01 00:00:00 +-48:00:00 2012-02-02 00:00:00 +00:00:00 2012-02-01 00:00:00 +00:00:00 2012-02-02 00:00:00 +24:00:00 2012-02-02 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t1 ignore INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `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 (flat, BNL join) +Warnings: +Note 1003 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 (flat, BNL join) +Warnings: +Note 1003 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 `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 `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) +SELECT * FROM +t1 force INDEX (col_time_key) +STRAIGHT_JOIN +t2 force INDEX (col_datetime_key) +WHERE col_datetime_key < col_time_key; +col_time_key col_datetime_key +-24:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-29 00:00:00 +00:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-29 00:00:00 +24:00:00 2012-01-30 00:00:00 +24:00:00 2012-01-31 00:00:00 +48:00:00 2012-01-29 00:00:00 +48:00:00 2012-01-30 00:00:00 +48:00:00 2012-01-31 00:00:00 +48:00:00 2012-02-01 00:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 ignore INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +col_datetime_key col_time_key +2012-01-29 00:00:00 -24:00:00 +2012-01-29 00:00:00 00:00:00 +2012-01-29 00:00:00 24:00:00 +2012-01-29 00:00:00 48:00:00 +2012-01-30 00:00:00 00:00:00 +2012-01-30 00:00:00 24:00:00 +2012-01-30 00:00:00 48:00:00 +2012-01-31 00:00:00 24:00:00 +2012-01-31 00:00:00 48:00:00 +2012-02-01 00:00:00 48:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) +SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_time_key < col_datetime_key; +col_datetime_key col_time_key +2012-01-30 00:00:00 -48:00:00 +2012-01-31 00:00:00 -24:00:00 +2012-01-31 00:00:00 -48:00:00 +2012-02-01 00:00:00 -24:00:00 +2012-02-01 00:00:00 -48:00:00 +2012-02-01 00:00:00 00:00:00 +2012-02-02 00:00:00 -24:00:00 +2012-02-02 00:00:00 -48:00:00 +2012-02-02 00:00:00 00:00:00 +2012-02-02 00:00:00 24:00:00 +EXPLAIN EXTENDED SELECT * FROM +t2 ignore INDEX (col_datetime_key) +STRAIGHT_JOIN +t1 force INDEX (col_time_key) +WHERE col_datetime_key < col_time_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 NULL 5 100.00 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 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 9 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 `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 9 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 `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; +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) +); +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) +); +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) +); +INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'), +('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'), +('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'), +('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'), +('02:59:24'), ('00:01:58'); +EXPLAIN EXTENDED SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key) +ON t3.col_time_key > t2.col_datetime_key; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system col_datetime_key NULL NULL NULL 1 100.00 +1 SIMPLE t3 index col_time_key col_time_key 4 NULL 20 100.00 Using where; Using index +Warnings: +Note 1003 select 1 AS `col_int_nokey`,'2001-11-04 19:07:55' AS `col_datetime_key`,'k' AS `col_varchar_key`,`test`.`t3`.`col_time_key` AS `col_time_key` from `test`.`t3` FORCE INDEX (`col_time_key`) where (`test`.`t3`.`col_time_key` > '2001-11-04 19:07:55') +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 system col_datetime_key NULL NULL NULL 1 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 20 100.00 Using where +Warnings: +Note 1003 select 1 AS `col_int_nokey`,'2001-11-04 19:07:55' AS `col_datetime_key`,'k' AS `col_varchar_key`,`test`.`t3`.`col_time_key` AS `col_time_key` from `test`.`t3` IGNORE INDEX (`col_time_key`) where (`test`.`t3`.`col_time_key` > '2001-11-04 19:07:55') +SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key) +ON t3.col_time_key > t2.col_datetime_key; +col_int_nokey col_datetime_key col_varchar_key col_time_key +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:00:00 +1 2001-11-04 19:07:55 k 00:01:58 +1 2001-11-04 19:07:55 k 00:21:38 +1 2001-11-04 19:07:55 k 02:59:24 +1 2001-11-04 19:07:55 k 03:53:16 +1 2001-11-04 19:07:55 k 04:08:02 +1 2001-11-04 19:07:55 k 05:03:03 +1 2001-11-04 19:07:55 k 07:05:51 +1 2001-11-04 19:07:55 k 09:16:38 +1 2001-11-04 19:07:55 k 10:14:58 +1 2001-11-04 19:07:55 k 10:50:38 +1 2001-11-04 19:07:55 k 11:14:24 +1 2001-11-04 19:07:55 k 15:37:26 +1 2001-11-04 19:07:55 k 15:57:25 +1 2001-11-04 19:07:55 k 16:25:11 +1 2001-11-04 19:07:55 k 19:22:21 +1 2001-11-04 19:07:55 k 19:47:59 +1 2001-11-04 19:07:55 k 21:22:34 +EXPLAIN EXTENDED SELECT outr.col_int_nokey +FROM t2 as outr +STRAIGHT_JOIN t3 AS outr2 +ON outr2.col_time_key > outr.col_datetime_key +WHERE outr.col_int_nokey IN ( +SELECT col_int_key +FROM t1 AS innr +WHERE innr.pk >= innr.col_int_nokey +) AND ( +outr.col_int_nokey <= 6 +OR +outr.col_varchar_key IS NULL +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY outr system col_datetime_key NULL NULL NULL 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY outr2 index col_time_key col_time_key 4 NULL 20 100.00 Using where; Using index; Using join buffer (flat, BNL join) +2 MATERIALIZED innr ref col_int_key col_int_key 4 const 2 100.00 Using where +Warnings: +Note 1003 select 1 AS `col_int_nokey` from `test`.`t3` `outr2` semi join (`test`.`t1` `innr`) where ((`test`.`innr`.`col_int_key` = 1) and (`test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey`) and (`test`.`outr2`.`col_time_key` > '2001-11-04 19:07:55')) +SELECT outr.col_int_nokey +FROM t2 as outr +STRAIGHT_JOIN t3 AS outr2 +ON outr2.col_time_key > outr.col_datetime_key +WHERE outr.col_int_nokey IN ( +SELECT col_int_key +FROM t1 AS innr +WHERE innr.pk >= innr.col_int_nokey +) AND ( +outr.col_int_nokey <= 6 +OR +outr.col_varchar_key IS NULL +); +col_int_nokey +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +DROP TABLE t1,t2,t3; +SET TIMESTAMP=0; diff --git a/mysql-test/t/type_time_6065.test b/mysql-test/t/type_time_6065.test new file mode 100644 index 00000000000..6e29b849be5 --- /dev/null +++ b/mysql-test/t/type_time_6065.test @@ -0,0 +1,177 @@ +# +# MDEV-6065 MySQL 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)); +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)); +INSERT INTO t2 SELECT * FROM t1; + +let $cnt_0=5; +let $operator= =; +# For operator in =, >=, >, <=, < +while ($cnt_0) +{ + let $cnt_1=2; + let $first_table=t1; + # for table in t1,t2 + while ($cnt_1) + { + if ($first_table==t1) + { + let $first_index=col_time_key; + let $second_table=t2; + let $second_index=col_datetime_key; + } + if ($first_table==t2) + { + let $first_index=col_datetime_key; + let $second_table=t1; + let $second_index=col_time_key; + } + let $cnt_2=2; + let $first_index_hint=ignore; + # for first_index_hint in ignore,force + while ($cnt_2) + { + let $cnt_3=2; + let $second_index_hint=ignore; + # for second_index_hint in ignore, force + while ($cnt_3) + { + let $cnt_4=2; + let $first_operand=col_time_key; + # for first_operand in col_time_key, col_datetime_key + while ($cnt_4) + { + if ($first_operand==col_time_key) + { + let $second_operand=col_datetime_key; + } + if ($first_operand==col_datetime_key) + { + let $second_operand=col_time_key; + } + + eval EXPLAIN EXTENDED SELECT * FROM + $first_table $first_index_hint INDEX ($first_index) + STRAIGHT_JOIN + $second_table $second_index_hint INDEX ($second_index) + WHERE $first_operand $operator $second_operand; + --sorted_result + eval SELECT * FROM + $first_table $first_index_hint INDEX ($first_index) + STRAIGHT_JOIN + $second_table $second_index_hint INDEX ($second_index) + WHERE $first_operand $operator $second_operand; + + let $first_operand=col_datetime_key; + dec $cnt_4; + } + let $second_index_hint=force; + dec $cnt_3; + } + let $first_index_hint=force; + dec $cnt_2; + } + let $first_table=t2; + dec $cnt_1; + } + if ($cnt_0==5) + { + let $operator= >=; + } + if ($cnt_0==4) + { + let $operator= >; + } + if ($cnt_0==3) + { + let $operator= <=; + } + if ($cnt_0==2) + { + let $operator= <; + } + dec $cnt_0; +} + +DROP TABLE t1,t2; + +# +# 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) +); + +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) +); + +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) +); + +INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'), +('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'), +('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'), +('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'), +('02:59:24'), ('00:01:58'); + +let $query=SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key) + ON t3.col_time_key > t2.col_datetime_key; +eval EXPLAIN EXTENDED $query; +--sorted_result +eval $query; + +let $query=SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key) + ON t3.col_time_key > t2.col_datetime_key; +eval EXPLAIN EXTENDED $query; +--sorted_result +eval $query; + +let $query=SELECT outr.col_int_nokey +FROM t2 as outr + STRAIGHT_JOIN t3 AS outr2 + ON outr2.col_time_key > outr.col_datetime_key +WHERE outr.col_int_nokey IN ( + SELECT col_int_key + FROM t1 AS innr + WHERE innr.pk >= innr.col_int_nokey +) AND ( + outr.col_int_nokey <= 6 + OR + outr.col_varchar_key IS NULL +); +eval EXPLAIN EXTENDED $query; +--sorted_result +eval $query; + +DROP TABLE t1,t2,t3; +SET TIMESTAMP=0; # back to current time + +# +# End of 10.0 tests +# diff --git a/sql/field.cc b/sql/field.cc index 13cad813b82..103a8920d7e 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -5316,12 +5316,41 @@ int Field_time::store(const char *from,uint len,CHARSET_INFO *cs) } +/** + subtract a given number of days from DATETIME, return TIME + + optimized version of calc_time_diff() + + @note it might generate TIME values outside of the valid TIME range! +*/ +static void calc_datetime_days_diff(MYSQL_TIME *ltime, long days) +{ + long daydiff= calc_daynr(ltime->year, ltime->month, ltime->day) - days; + ltime->year= ltime->month= 0; + if (daydiff >=0 ) + ltime->day= daydiff; + else + { + longlong timediff= ((((daydiff * 24LL + + ltime->hour) * 60LL + + ltime->minute) * 60LL + + ltime->second) * 1000000LL + + ltime->second_part); + unpack_time(timediff, ltime); + } + ltime->time_type= MYSQL_TIMESTAMP_TIME; +} + + int Field_time::store_time_dec(MYSQL_TIME *ltime, uint dec) { MYSQL_TIME l_time= *ltime; ErrConvTime str(ltime); int was_cut= 0; + if (curdays && l_time.time_type != MYSQL_TIMESTAMP_TIME) + calc_datetime_days_diff(&l_time, curdays); + int have_smth_to_conv= !check_time_range(&l_time, decimals(), &was_cut); return store_TIME_with_warning(&l_time, &str, was_cut, have_smth_to_conv); } @@ -5356,8 +5385,30 @@ int Field_time::store(longlong nr, bool unsigned_val) return store_TIME_with_warning(<ime, &str, was_cut, have_smth_to_conv); } - - + + +void Field_time::set_curdays(THD *thd) +{ + MYSQL_TIME ltime; + set_current_date(thd, <ime); + curdays= calc_daynr(ltime.year, ltime.month, ltime.day); +} + + +Field *Field_time::new_key_field(MEM_ROOT *root, TABLE *new_table, + uchar *new_ptr, uint32 length, + uchar *new_null_ptr, uint new_null_bit) +{ + THD *thd= get_thd(); + Field_time *res= + (Field_time*) Field::new_key_field(root, new_table, new_ptr, length, + new_null_ptr, new_null_bit); + if (!(thd->variables.old_behavior & OLD_MODE_ZERO_DATE_TIME_CAST) && res) + res->set_curdays(thd); + return res; +} + + double Field_time::val_real(void) { ASSERT_COLUMN_MARKED_FOR_READ; diff --git a/sql/field.h b/sql/field.h index 2938209004d..b5f332f5edc 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1852,6 +1852,12 @@ public: class Field_time :public Field_temporal { + /* + when this Field_time instance is used for storing values for index lookups + (see class store_key, Field::new_key_field(), etc), the following + might be set to TO_DAYS(CURDATE()). See also Field_time::store_time_dec() + */ + long curdays; protected: virtual void store_TIME(MYSQL_TIME *ltime); int store_TIME_with_warning(MYSQL_TIME *ltime, const ErrConv *str, @@ -1861,7 +1867,7 @@ public: uchar null_bit_arg, enum utype unireg_check_arg, const char *field_name_arg) :Field_temporal(ptr_arg, length_arg, null_ptr_arg, null_bit_arg, - unireg_check_arg, field_name_arg) + unireg_check_arg, field_name_arg), curdays(0) {} enum_field_types type() const { return MYSQL_TYPE_TIME;} enum ha_base_keytype key_type() const { return HA_KEYTYPE_INT24; } @@ -1880,6 +1886,10 @@ public: uint32 pack_length() const { return 3; } void sql_type(String &str) const; uint size_of() const { return sizeof(*this); } + void set_curdays(THD *thd); + Field *new_key_field(MEM_ROOT *root, TABLE *new_table, + uchar *new_ptr, uint32 length, + uchar *new_null_ptr, uint new_null_bit); }; diff --git a/sql/sql_time.cc b/sql/sql_time.cc index e0b17a918ee..cc824298bc5 100644 --- a/sql/sql_time.cc +++ b/sql/sql_time.cc @@ -1259,8 +1259,7 @@ mix_date_and_time(MYSQL_TIME *to, const MYSQL_TIME *from) /** Get current date in DATE format */ -static void -set_current_date(THD *thd, MYSQL_TIME *to) +void set_current_date(THD *thd, MYSQL_TIME *to) { thd->variables.time_zone->gmt_sec_to_TIME(to, thd->query_start()); thd->time_zone_used= 1; diff --git a/sql/sql_time.h b/sql/sql_time.h index 24a87922fa9..dc8e4668e1e 100644 --- a/sql/sql_time.h +++ b/sql/sql_time.h @@ -33,6 +33,7 @@ typedef struct st_known_date_time_format KNOWN_DATE_TIME_FORMAT; ulong convert_period_to_month(ulong period); ulong convert_month_to_period(ulong month); +void set_current_date(THD *thd, MYSQL_TIME *to); bool time_to_datetime(MYSQL_TIME *ltime); void time_to_daytime_interval(MYSQL_TIME *l_time); bool get_date_from_daynr(long daynr,uint *year, uint *month, uint *day); |