1drop table if exists t1; 2SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 3Warnings: 4Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 5create table t1 (t time); 6insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10 1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34"); 7Warnings: 8Note 1265 Data truncated for column 't' at row 13 9insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32"); 10select * from t1; 11t 1210:22:33 1312:34:57 1400:00:10 1500:12:34 1612:34:57 17123:46:00 1800:00:01 1901:23:00 2001:23:45 2100:00:10 22-241:22:33 23490:22:33 2420:33:34 2500:00:30 2600:12:30 2700:12:30 2812:30:00 2912:30:35 3036:30:31 31insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a"); 32Warnings: 33Warning 1265 Data truncated for column 't' at row 1 34Warning 1264 Out of range value for column 't' at row 2 35Warning 1264 Out of range value for column 't' at row 3 36Warning 1264 Out of range value for column 't' at row 4 37Warning 1265 Data truncated for column 't' at row 6 38select * from t1; 39t 4010:22:33 4112:34:57 4200:00:10 4300:12:34 4412:34:57 45123:46:00 4600:00:01 4701:23:00 4801:23:45 4900:00:10 50-241:22:33 51490:22:33 5220:33:34 5300:00:30 5400:12:30 5500:12:30 5612:30:00 5712:30:35 5836:30:31 5900:00:10 6000:00:00 61838:59:59 62838:59:59 63262:22:00 6400:00:12 65drop table t1; 66create table t1 (t time); 67insert into t1 values ('09:00:00'),('13:00:00'),('19:38:34'), ('13:00:00'),('09:00:00'),('09:00:00'),('13:00:00'),('13:00:00'),('13:00:00'),('09:00:00'); 68select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1; 69t time_to_sec(t) sec_to_time(time_to_sec(t)) 7009:00:00 32400 09:00:00 7113:00:00 46800 13:00:00 7219:38:34 70714 19:38:34 7313:00:00 46800 13:00:00 7409:00:00 32400 09:00:00 7509:00:00 32400 09:00:00 7613:00:00 46800 13:00:00 7713:00:00 46800 13:00:00 7813:00:00 46800 13:00:00 7909:00:00 32400 09:00:00 80select sec_to_time(time_to_sec(t)) from t1; 81sec_to_time(time_to_sec(t)) 8209:00:00 8313:00:00 8419:38:34 8513:00:00 8609:00:00 8709:00:00 8813:00:00 8913:00:00 9013:00:00 9109:00:00 92drop table t1; 93CREATE TABLE t1 (t TIME); 94INSERT INTO t1 VALUES (+10), (+10.0), (+10e0); 95INSERT INTO t1 VALUES (-10), (-10.0), (-10e0); 96SELECT * FROM t1; 97t 9800:00:10 9900:00:10 10000:00:10 101-00:00:10 102-00:00:10 103-00:00:10 104DROP TABLE t1; 105SELECT CAST(235959.123456 AS TIME); 106CAST(235959.123456 AS TIME) 10723:59:59 108SELECT CAST(0.235959123456e+6 AS TIME); 109CAST(0.235959123456e+6 AS TIME) 11023:59:59 111SELECT CAST(235959123456e-6 AS TIME); 112CAST(235959123456e-6 AS TIME) 11323:59:59 114SELECT CAST(235959.1234567 AS TIME); 115CAST(235959.1234567 AS TIME) 11623:59:59 117SELECT CAST(0.2359591234567e6 AS TIME); 118CAST(0.2359591234567e6 AS TIME) 11923:59:59 120SELECT CAST(0.2359591234567e+30 AS TIME); 121CAST(0.2359591234567e+30 AS TIME) 122NULL 123Warnings: 124Warning 1292 Truncated incorrect time value: '2.359591234567e29' 125End of 4.1 tests 126select cast('100:55:50' as time) < cast('24:00:00' as time); 127cast('100:55:50' as time) < cast('24:00:00' as time) 1280 129select cast('100:55:50' as time) < cast('024:00:00' as time); 130cast('100:55:50' as time) < cast('024:00:00' as time) 1310 132select cast('300:55:50' as time) < cast('240:00:00' as time); 133cast('300:55:50' as time) < cast('240:00:00' as time) 1340 135select cast('100:55:50' as time) > cast('24:00:00' as time); 136cast('100:55:50' as time) > cast('24:00:00' as time) 1371 138select cast('100:55:50' as time) > cast('024:00:00' as time); 139cast('100:55:50' as time) > cast('024:00:00' as time) 1401 141select cast('300:55:50' as time) > cast('240:00:00' as time); 142cast('300:55:50' as time) > cast('240:00:00' as time) 1431 144create table t1 (f1 time); 145insert into t1 values ('24:00:00'); 146select cast('24:00:00' as time) = (select f1 from t1); 147cast('24:00:00' as time) = (select f1 from t1) 1481 149drop table t1; 150create table t1(f1 time, f2 time); 151insert into t1 values('20:00:00','150:00:00'); 152select 1 from t1 where cast('100:00:00' as time) between f1 and f2; 1531 1541 155drop table t1; 156CREATE TABLE t1 ( 157f2 date NOT NULL, 158f3 int(11) unsigned NOT NULL default '0', 159PRIMARY KEY (f3, f2) 160); 161insert into t1 values('2007-07-01', 1); 162insert into t1 values('2007-07-01', 2); 163insert into t1 values('2007-07-02', 1); 164insert into t1 values('2007-07-02', 2); 165SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2; 166sum(f3) 1673 168drop table t1; 169# 170# Bug #44792: valgrind warning when casting from time to time 171# 172CREATE TABLE t1 (c TIME); 173INSERT INTO t1 VALUES ('0:00:00'); 174SELECT CAST(c AS TIME) FROM t1; 175CAST(c AS TIME) 17600:00:00 177DROP TABLE t1; 178End of 5.0 tests 179# 180# Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values 181# 182SET @@timestamp=UNIX_TIMESTAMP('2001-01-01 01:00:00'); 183CREATE TABLE t1(f1 TIME); 184INSERT INTO t1 VALUES ('23:38:57'); 185SELECT TIMESTAMP(f1,'1') FROM t1; 186TIMESTAMP(f1,'1') 1872001-01-01 23:38:58 188DROP TABLE t1; 189SET @@timestamp=default; 190End of 5.1 tests 191CREATE TABLE t1 (f1 TIME); 192INSERT INTO t1 VALUES ('24:00:00'); 193SELECT '24:00:00' = (SELECT f1 FROM t1); 194'24:00:00' = (SELECT f1 FROM t1) 1951 196SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1); 197CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1) 1981 199SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1); 200CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1) 2010 202TRUNCATE t1; 203INSERT INTO t1 VALUES ('-24:00:00'); 204SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1); 205CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1) 2060 207SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1); 208CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1) 2091 210SELECT '-24:00:00' = (SELECT f1 FROM t1); 211'-24:00:00' = (SELECT f1 FROM t1) 2121 213DROP TABLE t1; 214# 215# Start of 5.6 tests 216# 217# 218# WL#946 Testing <=> operator with TIME 219# 220CREATE TABLE t1 (a TIME); 221INSERT INTO t1 VALUES ('20:00:00'),('19:20:30'); 222SELECT * FROM t1 WHERE a<=>'19:20:30'; 223a 22419:20:30 225SELECT * FROM t1 WHERE a<=>TIME'19:20:30'; 226a 22719:20:30 228SELECT * FROM t1 WHERE a<=>192030; 229a 23019:20:30 231DROP TABLE t1; 232# 233# WL#946: Testing rounding 234# 235CREATE TABLE t1 (a TIME); 236INSERT INTO t1 VALUES ('10:10:10.9999994'), ('10:10:10.9999995'); 237INSERT INTO t1 VALUES (101010.9999994), (101010.9999995); 238SELECT * FROM t1; 239a 24010:10:11 24110:10:11 24210:10:11 24310:10:11 244DROP TABLE t1; 245# 246# Before WL#946 TIME did not reject too big negative minutes/seconds 247# 248CREATE TABLE t1 (a TIME); 249INSERT INTO t1 VALUES ('-10:60:59'), ('-10:59:60'), (-106059), (-105960); 250Warnings: 251Warning 1264 Out of range value for column 'a' at row 1 252Warning 1264 Out of range value for column 'a' at row 2 253Warning 1264 Out of range value for column 'a' at row 3 254Warning 1264 Out of range value for column 'a' at row 4 255SELECT * FROM t1; 256a 25700:00:00 25800:00:00 25900:00:00 26000:00:00 261DROP TABLE t1; 262# 263# WL#946 Make sure case from number to TIME properly handles 264# too big negative minutes/secons 265# 266CREATE TABLE t1 (a INT); 267INSERT INTO t1 VALUES (-106059), (-105960); 268SELECT CAST(a AS TIME) FROM t1; 269CAST(a AS TIME) 270NULL 271NULL 272Warnings: 273Warning 1292 Truncated incorrect time value: '-106059' 274Warning 1292 Truncated incorrect time value: '-105960' 275DROP TABLE t1; 276# 277# WL#946 Checking function TIME() 278# 279SELECT TIME('1000009:10:10'); 280TIME('1000009:10:10') 281838:59:59 282Warnings: 283Warning 1292 Truncated incorrect time value: '1000009:10:10' 284SELECT TIME('1000009:10:10.1999999999999'); 285TIME('1000009:10:10.1999999999999') 286838:59:59.000000 287Warnings: 288Warning 1292 Truncated incorrect time value: '1000009:10:10.1999999999999' 289SELECT TIME('10000090:10:10'); 290TIME('10000090:10:10') 291838:59:59 292Warnings: 293Warning 1292 Truncated incorrect time value: '10000090:10:10' 294SELECT TIME('10000090:10:10.1999999999999'); 295TIME('10000090:10:10.1999999999999') 296838:59:59.000000 297Warnings: 298Warning 1292 Truncated incorrect time value: '10000090:10:10.1999999999999' 299SELECT TIME('100000900:10:10'); 300TIME('100000900:10:10') 301838:59:59 302Warnings: 303Warning 1292 Truncated incorrect time value: '100000900:10:10' 304SELECT TIME('100000900:10:10.1999999999999'); 305TIME('100000900:10:10.1999999999999') 306838:59:59.000000 307Warnings: 308Warning 1292 Truncated incorrect time value: '100000900:10:10.1999999999999' 309SELECT TIME('1000009000:10:10'); 310TIME('1000009000:10:10') 311838:59:59 312Warnings: 313Warning 1292 Truncated incorrect time value: '1000009000:10:10' 314SELECT TIME('1000009000:10:10.1999999999999'); 315TIME('1000009000:10:10.1999999999999') 316838:59:59.000000 317Warnings: 318Warning 1292 Truncated incorrect time value: '1000009000:10:10.1999999999999' 319SELECT TIME('10000090000:10:10'); 320TIME('10000090000:10:10') 321NULL 322Warnings: 323Warning 1292 Truncated incorrect time value: '10000090000:10:10' 324SELECT TIME('10000090000:10:10.1999999999999'); 325TIME('10000090000:10:10.1999999999999') 326NULL 327Warnings: 328Warning 1292 Truncated incorrect time value: '10000090000:10:10.1999999999999' 329# 330# Checking Item_func_if::val_str with TIME arguments 331# 332SELECT CAST(IF(1, TIME'00:00:00',TIME'00:00:00') AS CHAR); 333CAST(IF(1, TIME'00:00:00',TIME'00:00:00') AS CHAR) 33400:00:00 335# 336# Checking Item_func_case::val_str with TIME arguments 337# 338SELECT CAST(CASE WHEN 1 THEN TIME'00:00:00' ELSE TIME'00:00:00' END AS CHAR); 339CAST(CASE WHEN 1 THEN TIME'00:00:00' ELSE TIME'00:00:00' END AS CHAR) 34000:00:00 341# 342# Testing CASE with TIME type without a found item 343# 344SELECT CAST(CASE WHEN 0 THEN '01:01:01' END AS TIME); 345CAST(CASE WHEN 0 THEN '01:01:01' END AS TIME) 346NULL 347SELECT CAST(CASE WHEN 0 THEN TIME'01:01:01' END AS TIME); 348CAST(CASE WHEN 0 THEN TIME'01:01:01' END AS TIME) 349NULL 350# 351# Testing COALESCE with TIME type without a found item 352# 353SELECT COALESCE(TIME(NULL)); 354COALESCE(TIME(NULL)) 355NULL 356# 357# Testing TIME field with NULL value with NOT IN 358# 359CREATE TABLE t1 (a TIME); 360INSERT INTO t1 VALUES (NULL); 361SELECT * FROM t1 WHERE a NOT IN (TIME'20:20:20',TIME'10:10:10'); 362a 363DROP TABLE t1; 364# 365# Testing Item_func_numhybrid::val_int when TIME type 366# 367CREATE TABLE t1 (a TIME); 368INSERT INTO t1 VALUES ('10:10:10'); 369SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1; 370CAST(COALESCE(a,a) AS SIGNED) 371101010 372DROP TABLE t1; 373# 374# Testing Item_func_numhybrid::val_decimal when TIME type 375# 376CREATE TABLE t1 (a TIME); 377INSERT INTO t1 VALUES ('10:10:10'); 378SELECT CAST(COALESCE(a,a) AS DECIMAL(23,6)) FROM t1; 379CAST(COALESCE(a,a) AS DECIMAL(23,6)) 380101010.000000 381DROP TABLE t1; 382# 383# Testing Item_func_numhybrid::get_time when non-temporal type 384# 385SELECT CAST(COALESCE(10,20) AS TIME); 386CAST(COALESCE(10,20) AS TIME) 38700:00:10 388# 389# Testing Item_func_min_max::get_time when DATE type and NULL 390# 391SELECT CAST(LEAST(DATE(NULL), DATE(NULL)) AS TIME); 392CAST(LEAST(DATE(NULL), DATE(NULL)) AS TIME) 393NULL 394# 395# Testing Item_func_min_max::get_time with non-temporal arguments 396# 397SELECT CAST(LEAST(111111,222222) AS TIME); 398CAST(LEAST(111111,222222) AS TIME) 39911:11:11 400# 401# Item::get_time_from_numeric 402# 403SELECT CAST(SUM(0) AS TIME); 404CAST(SUM(0) AS TIME) 40500:00:00 406SELECT CAST(SUM(0 + 0e0) AS TIME); 407CAST(SUM(0 + 0e0) AS TIME) 40800:00:00 409SET timestamp=1322115328; 410SELECT CAST(UNIX_TIMESTAMP() AS TIME); 411CAST(UNIX_TIMESTAMP() AS TIME) 412NULL 413Warnings: 414Warning 1292 Truncated incorrect time value: '1322115328' 415SET timestamp=default; 416# 417# Item::get_time_from_non_temporal 418# 419SELECT TIME(154559.616 + 0e0); 420TIME(154559.616 + 0e0) 42115:45:59.616000 422# 423# Item_name_const::get_time 424# 425SELECT TIME(NAME_CONST('a', 0)); 426TIME(NAME_CONST('a', 0)) 42700:00:00 428# 429# Item_cache_datetime::get_time 430# 431CREATE TABLE t1 (a DATE); 432INSERT INTO t1 VALUES (0); 433SELECT TIME(MIN(a)) FROM t1; 434TIME(MIN(a)) 43500:00:00 436DROP TABLE t1; 437# 438# Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH 439# TIME/DATETIME COMPARE" 440# 441# Systematic testing of ref access and range scan 442SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35'); 443CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key)) ENGINE=InnoDB; 444INSERT INTO t1 VALUES ('00:00:00'),('-24:00:00'),('-48:00:00'),('24:00:00'),('48:00:00'); 445CREATE TABLE t2 (col_datetime_key DATETIME, KEY(col_datetime_key)) ENGINE=InnoDB; 446INSERT INTO t2 SELECT * FROM t1; 447ANALYZE TABLE t1; 448ANALYZE TABLE t2; 449EXPLAIN EXTENDED SELECT * FROM 450t1 ignore INDEX (col_time_key) 451STRAIGHT_JOIN 452t2 ignore INDEX (col_datetime_key) 453WHERE col_time_key = col_datetime_key; 454id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4551 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 4561 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 457Warnings: 458Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 459Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) 460SELECT * FROM 461t1 ignore INDEX (col_time_key) 462STRAIGHT_JOIN 463t2 ignore INDEX (col_datetime_key) 464WHERE col_time_key = col_datetime_key; 465col_time_key col_datetime_key 466-24:00:00 2012-01-30 00:00:00 467-48:00:00 2012-01-29 00:00:00 46800:00:00 2012-01-31 00:00:00 46924:00:00 2012-02-01 00:00:00 47048:00:00 2012-02-02 00:00:00 471EXPLAIN EXTENDED SELECT * FROM 472t1 ignore INDEX (col_time_key) 473STRAIGHT_JOIN 474t2 ignore INDEX (col_datetime_key) 475WHERE col_datetime_key = col_time_key; 476id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4771 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 4781 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 479Warnings: 480Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 481Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) 482SELECT * FROM 483t1 ignore INDEX (col_time_key) 484STRAIGHT_JOIN 485t2 ignore INDEX (col_datetime_key) 486WHERE col_datetime_key = col_time_key; 487col_time_key col_datetime_key 488-24:00:00 2012-01-30 00:00:00 489-48:00:00 2012-01-29 00:00:00 49000:00:00 2012-01-31 00:00:00 49124:00:00 2012-02-01 00:00:00 49248:00:00 2012-02-02 00:00:00 493EXPLAIN EXTENDED SELECT * FROM 494t1 ignore INDEX (col_time_key) 495STRAIGHT_JOIN 496t2 force INDEX (col_datetime_key) 497WHERE col_time_key = col_datetime_key; 498id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4991 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where 5001 SIMPLE t2 NULL ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index 501Warnings: 502Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 503Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) 504SELECT * FROM 505t1 ignore INDEX (col_time_key) 506STRAIGHT_JOIN 507t2 force INDEX (col_datetime_key) 508WHERE col_time_key = col_datetime_key; 509col_time_key col_datetime_key 510-24:00:00 2012-01-30 00:00:00 511-48:00:00 2012-01-29 00:00:00 51200:00:00 2012-01-31 00:00:00 51324:00:00 2012-02-01 00:00:00 51448:00:00 2012-02-02 00:00:00 515EXPLAIN EXTENDED SELECT * FROM 516t1 ignore INDEX (col_time_key) 517STRAIGHT_JOIN 518t2 force INDEX (col_datetime_key) 519WHERE col_datetime_key = col_time_key; 520id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5211 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 Using where 5221 SIMPLE t2 NULL ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index 523Warnings: 524Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 525Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) 526SELECT * FROM 527t1 ignore INDEX (col_time_key) 528STRAIGHT_JOIN 529t2 force INDEX (col_datetime_key) 530WHERE col_datetime_key = col_time_key; 531col_time_key col_datetime_key 532-24:00:00 2012-01-30 00:00:00 533-48:00:00 2012-01-29 00:00:00 53400:00:00 2012-01-31 00:00:00 53524:00:00 2012-02-01 00:00:00 53648:00:00 2012-02-02 00:00:00 537EXPLAIN EXTENDED SELECT * FROM 538t1 force INDEX (col_time_key) 539STRAIGHT_JOIN 540t2 ignore INDEX (col_datetime_key) 541WHERE col_time_key = col_datetime_key; 542id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5431 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 5441 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 545Warnings: 546Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 547Warning 1739 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 548Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) 549SELECT * FROM 550t1 force INDEX (col_time_key) 551STRAIGHT_JOIN 552t2 ignore INDEX (col_datetime_key) 553WHERE col_time_key = col_datetime_key; 554col_time_key col_datetime_key 555-24:00:00 2012-01-30 00:00:00 556-48:00:00 2012-01-29 00:00:00 55700:00:00 2012-01-31 00:00:00 55824:00:00 2012-02-01 00:00:00 55948:00:00 2012-02-02 00:00:00 560EXPLAIN EXTENDED SELECT * FROM 561t1 force INDEX (col_time_key) 562STRAIGHT_JOIN 563t2 ignore INDEX (col_datetime_key) 564WHERE col_datetime_key = col_time_key; 565id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5661 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 5671 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 568Warnings: 569Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 570Warning 1739 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 571Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) 572SELECT * FROM 573t1 force INDEX (col_time_key) 574STRAIGHT_JOIN 575t2 ignore INDEX (col_datetime_key) 576WHERE col_datetime_key = col_time_key; 577col_time_key col_datetime_key 578-24:00:00 2012-01-30 00:00:00 579-48:00:00 2012-01-29 00:00:00 58000:00:00 2012-01-31 00:00:00 58124:00:00 2012-02-01 00:00:00 58248:00:00 2012-02-02 00:00:00 583EXPLAIN EXTENDED SELECT * FROM 584t1 force INDEX (col_time_key) 585STRAIGHT_JOIN 586t2 force INDEX (col_datetime_key) 587WHERE col_time_key = col_datetime_key; 588id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5891 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index 5901 SIMPLE t2 NULL ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index 591Warnings: 592Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 593Warning 1739 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 594Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) 595SELECT * FROM 596t1 force INDEX (col_time_key) 597STRAIGHT_JOIN 598t2 force INDEX (col_datetime_key) 599WHERE col_time_key = col_datetime_key; 600col_time_key col_datetime_key 601-24:00:00 2012-01-30 00:00:00 602-48:00:00 2012-01-29 00:00:00 60300:00:00 2012-01-31 00:00:00 60424:00:00 2012-02-01 00:00:00 60548:00:00 2012-02-02 00:00:00 606EXPLAIN EXTENDED SELECT * FROM 607t1 force INDEX (col_time_key) 608STRAIGHT_JOIN 609t2 force INDEX (col_datetime_key) 610WHERE col_datetime_key = col_time_key; 611id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6121 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index 6131 SIMPLE t2 NULL ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index 614Warnings: 615Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 616Warning 1739 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 617Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) 618SELECT * FROM 619t1 force INDEX (col_time_key) 620STRAIGHT_JOIN 621t2 force INDEX (col_datetime_key) 622WHERE col_datetime_key = col_time_key; 623col_time_key col_datetime_key 624-24:00:00 2012-01-30 00:00:00 625-48:00:00 2012-01-29 00:00:00 62600:00:00 2012-01-31 00:00:00 62724:00:00 2012-02-01 00:00:00 62848:00:00 2012-02-02 00:00:00 629EXPLAIN EXTENDED SELECT * FROM 630t2 ignore INDEX (col_datetime_key) 631STRAIGHT_JOIN 632t1 ignore INDEX (col_time_key) 633WHERE col_time_key = col_datetime_key; 634id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6351 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 6361 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 637Warnings: 638Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 639Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) 640SELECT * FROM 641t2 ignore INDEX (col_datetime_key) 642STRAIGHT_JOIN 643t1 ignore INDEX (col_time_key) 644WHERE col_time_key = col_datetime_key; 645col_datetime_key col_time_key 6462012-01-29 00:00:00 -48:00:00 6472012-01-30 00:00:00 -24:00:00 6482012-01-31 00:00:00 00:00:00 6492012-02-01 00:00:00 24:00:00 6502012-02-02 00:00:00 48:00:00 651EXPLAIN EXTENDED SELECT * FROM 652t2 ignore INDEX (col_datetime_key) 653STRAIGHT_JOIN 654t1 ignore INDEX (col_time_key) 655WHERE col_datetime_key = col_time_key; 656id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6571 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 6581 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 659Warnings: 660Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 661Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) 662SELECT * FROM 663t2 ignore INDEX (col_datetime_key) 664STRAIGHT_JOIN 665t1 ignore INDEX (col_time_key) 666WHERE col_datetime_key = col_time_key; 667col_datetime_key col_time_key 6682012-01-29 00:00:00 -48:00:00 6692012-01-30 00:00:00 -24:00:00 6702012-01-31 00:00:00 00:00:00 6712012-02-01 00:00:00 24:00:00 6722012-02-02 00:00:00 48:00:00 673EXPLAIN EXTENDED SELECT * FROM 674t2 ignore INDEX (col_datetime_key) 675STRAIGHT_JOIN 676t1 force INDEX (col_time_key) 677WHERE col_time_key = col_datetime_key; 678id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6791 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 6801 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 20.00 Using where; Using index; Using join buffer (Block Nested Loop) 681Warnings: 682Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 683Warning 1739 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 684Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 685Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) 686SELECT * FROM 687t2 ignore INDEX (col_datetime_key) 688STRAIGHT_JOIN 689t1 force INDEX (col_time_key) 690WHERE col_time_key = col_datetime_key; 691col_datetime_key col_time_key 6922012-01-29 00:00:00 -48:00:00 6932012-01-30 00:00:00 -24:00:00 6942012-01-31 00:00:00 00:00:00 6952012-02-01 00:00:00 24:00:00 6962012-02-02 00:00:00 48:00:00 697EXPLAIN EXTENDED SELECT * FROM 698t2 ignore INDEX (col_datetime_key) 699STRAIGHT_JOIN 700t1 force INDEX (col_time_key) 701WHERE col_datetime_key = col_time_key; 702id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7031 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 7041 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 20.00 Using where; Using index; Using join buffer (Block Nested Loop) 705Warnings: 706Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 707Warning 1739 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 708Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 709Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) 710SELECT * FROM 711t2 ignore INDEX (col_datetime_key) 712STRAIGHT_JOIN 713t1 force INDEX (col_time_key) 714WHERE col_datetime_key = col_time_key; 715col_datetime_key col_time_key 7162012-01-29 00:00:00 -48:00:00 7172012-01-30 00:00:00 -24:00:00 7182012-01-31 00:00:00 00:00:00 7192012-02-01 00:00:00 24:00:00 7202012-02-02 00:00:00 48:00:00 721EXPLAIN EXTENDED SELECT * FROM 722t2 force INDEX (col_datetime_key) 723STRAIGHT_JOIN 724t1 ignore INDEX (col_time_key) 725WHERE col_time_key = col_datetime_key; 726id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7271 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 7281 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 729Warnings: 730Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 731Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) 732SELECT * FROM 733t2 force INDEX (col_datetime_key) 734STRAIGHT_JOIN 735t1 ignore INDEX (col_time_key) 736WHERE col_time_key = col_datetime_key; 737col_datetime_key col_time_key 7382012-01-29 00:00:00 -48:00:00 7392012-01-30 00:00:00 -24:00:00 7402012-01-31 00:00:00 00:00:00 7412012-02-01 00:00:00 24:00:00 7422012-02-02 00:00:00 48:00:00 743EXPLAIN EXTENDED SELECT * FROM 744t2 force INDEX (col_datetime_key) 745STRAIGHT_JOIN 746t1 ignore INDEX (col_time_key) 747WHERE col_datetime_key = col_time_key; 748id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7491 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 7501 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop) 751Warnings: 752Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 753Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) 754SELECT * FROM 755t2 force INDEX (col_datetime_key) 756STRAIGHT_JOIN 757t1 ignore INDEX (col_time_key) 758WHERE col_datetime_key = col_time_key; 759col_datetime_key col_time_key 7602012-01-29 00:00:00 -48:00:00 7612012-01-30 00:00:00 -24:00:00 7622012-01-31 00:00:00 00:00:00 7632012-02-01 00:00:00 24:00:00 7642012-02-02 00:00:00 48:00:00 765EXPLAIN EXTENDED SELECT * FROM 766t2 force INDEX (col_datetime_key) 767STRAIGHT_JOIN 768t1 force INDEX (col_time_key) 769WHERE col_time_key = col_datetime_key; 770id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7711 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 7721 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 20.00 Using where; Using index; Using join buffer (Block Nested Loop) 773Warnings: 774Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 775Warning 1739 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 776Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 777Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`) 778SELECT * FROM 779t2 force INDEX (col_datetime_key) 780STRAIGHT_JOIN 781t1 force INDEX (col_time_key) 782WHERE col_time_key = col_datetime_key; 783col_datetime_key col_time_key 7842012-01-29 00:00:00 -48:00:00 7852012-01-30 00:00:00 -24:00:00 7862012-01-31 00:00:00 00:00:00 7872012-02-01 00:00:00 24:00:00 7882012-02-02 00:00:00 48:00:00 789EXPLAIN EXTENDED SELECT * FROM 790t2 force INDEX (col_datetime_key) 791STRAIGHT_JOIN 792t1 force INDEX (col_time_key) 793WHERE col_datetime_key = col_time_key; 794id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7951 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 7961 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 20.00 Using where; Using index; Using join buffer (Block Nested Loop) 797Warnings: 798Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 799Warning 1739 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 800Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 801Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`) 802SELECT * FROM 803t2 force INDEX (col_datetime_key) 804STRAIGHT_JOIN 805t1 force INDEX (col_time_key) 806WHERE col_datetime_key = col_time_key; 807col_datetime_key col_time_key 8082012-01-29 00:00:00 -48:00:00 8092012-01-30 00:00:00 -24:00:00 8102012-01-31 00:00:00 00:00:00 8112012-02-01 00:00:00 24:00:00 8122012-02-02 00:00:00 48:00:00 813EXPLAIN EXTENDED SELECT * FROM 814t1 ignore INDEX (col_time_key) 815STRAIGHT_JOIN 816t2 ignore INDEX (col_datetime_key) 817WHERE col_time_key >= col_datetime_key; 818id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8191 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 8201 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 821Warnings: 822Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 823Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) 824SELECT * FROM 825t1 ignore INDEX (col_time_key) 826STRAIGHT_JOIN 827t2 ignore INDEX (col_datetime_key) 828WHERE col_time_key >= col_datetime_key; 829col_time_key col_datetime_key 830-24:00:00 2012-01-29 00:00:00 831-24:00:00 2012-01-30 00:00:00 832-48:00:00 2012-01-29 00:00:00 83300:00:00 2012-01-29 00:00:00 83400:00:00 2012-01-30 00:00:00 83500:00:00 2012-01-31 00:00:00 83624:00:00 2012-01-29 00:00:00 83724:00:00 2012-01-30 00:00:00 83824:00:00 2012-01-31 00:00:00 83924:00:00 2012-02-01 00:00:00 84048:00:00 2012-01-29 00:00:00 84148:00:00 2012-01-30 00:00:00 84248:00:00 2012-01-31 00:00:00 84348:00:00 2012-02-01 00:00:00 84448:00:00 2012-02-02 00:00:00 845EXPLAIN EXTENDED SELECT * FROM 846t1 ignore INDEX (col_time_key) 847STRAIGHT_JOIN 848t2 ignore INDEX (col_datetime_key) 849WHERE col_datetime_key >= col_time_key; 850id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8511 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 8521 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 853Warnings: 854Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 855Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) 856SELECT * FROM 857t1 ignore INDEX (col_time_key) 858STRAIGHT_JOIN 859t2 ignore INDEX (col_datetime_key) 860WHERE col_datetime_key >= col_time_key; 861col_time_key col_datetime_key 862-24:00:00 2012-01-30 00:00:00 863-24:00:00 2012-01-31 00:00:00 864-24:00:00 2012-02-01 00:00:00 865-24:00:00 2012-02-02 00:00:00 866-48:00:00 2012-01-29 00:00:00 867-48:00:00 2012-01-30 00:00:00 868-48:00:00 2012-01-31 00:00:00 869-48:00:00 2012-02-01 00:00:00 870-48:00:00 2012-02-02 00:00:00 87100:00:00 2012-01-31 00:00:00 87200:00:00 2012-02-01 00:00:00 87300:00:00 2012-02-02 00:00:00 87424:00:00 2012-02-01 00:00:00 87524:00:00 2012-02-02 00:00:00 87648:00:00 2012-02-02 00:00:00 877EXPLAIN EXTENDED SELECT * FROM 878t1 ignore INDEX (col_time_key) 879STRAIGHT_JOIN 880t2 force INDEX (col_datetime_key) 881WHERE col_time_key >= col_datetime_key; 882id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8831 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 8841 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 885Warnings: 886Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 887Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) 888SELECT * FROM 889t1 ignore INDEX (col_time_key) 890STRAIGHT_JOIN 891t2 force INDEX (col_datetime_key) 892WHERE col_time_key >= col_datetime_key; 893col_time_key col_datetime_key 894-24:00:00 2012-01-29 00:00:00 895-24:00:00 2012-01-30 00:00:00 896-48:00:00 2012-01-29 00:00:00 89700:00:00 2012-01-29 00:00:00 89800:00:00 2012-01-30 00:00:00 89900:00:00 2012-01-31 00:00:00 90024:00:00 2012-01-29 00:00:00 90124:00:00 2012-01-30 00:00:00 90224:00:00 2012-01-31 00:00:00 90324:00:00 2012-02-01 00:00:00 90448:00:00 2012-01-29 00:00:00 90548:00:00 2012-01-30 00:00:00 90648:00:00 2012-01-31 00:00:00 90748:00:00 2012-02-01 00:00:00 90848:00:00 2012-02-02 00:00:00 909EXPLAIN EXTENDED SELECT * FROM 910t1 ignore INDEX (col_time_key) 911STRAIGHT_JOIN 912t2 force INDEX (col_datetime_key) 913WHERE col_datetime_key >= col_time_key; 914id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9151 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 9161 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 917Warnings: 918Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 919Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) 920SELECT * FROM 921t1 ignore INDEX (col_time_key) 922STRAIGHT_JOIN 923t2 force INDEX (col_datetime_key) 924WHERE col_datetime_key >= col_time_key; 925col_time_key col_datetime_key 926-24:00:00 2012-01-30 00:00:00 927-24:00:00 2012-01-31 00:00:00 928-24:00:00 2012-02-01 00:00:00 929-24:00:00 2012-02-02 00:00:00 930-48:00:00 2012-01-29 00:00:00 931-48:00:00 2012-01-30 00:00:00 932-48:00:00 2012-01-31 00:00:00 933-48:00:00 2012-02-01 00:00:00 934-48:00:00 2012-02-02 00:00:00 93500:00:00 2012-01-31 00:00:00 93600:00:00 2012-02-01 00:00:00 93700:00:00 2012-02-02 00:00:00 93824:00:00 2012-02-01 00:00:00 93924:00:00 2012-02-02 00:00:00 94048:00:00 2012-02-02 00:00:00 941EXPLAIN EXTENDED SELECT * FROM 942t1 force INDEX (col_time_key) 943STRAIGHT_JOIN 944t2 ignore INDEX (col_datetime_key) 945WHERE col_time_key >= col_datetime_key; 946id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9471 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 9481 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 949Warnings: 950Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 951Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) 952SELECT * FROM 953t1 force INDEX (col_time_key) 954STRAIGHT_JOIN 955t2 ignore INDEX (col_datetime_key) 956WHERE col_time_key >= col_datetime_key; 957col_time_key col_datetime_key 958-24:00:00 2012-01-29 00:00:00 959-24:00:00 2012-01-30 00:00:00 960-48:00:00 2012-01-29 00:00:00 96100:00:00 2012-01-29 00:00:00 96200:00:00 2012-01-30 00:00:00 96300:00:00 2012-01-31 00:00:00 96424:00:00 2012-01-29 00:00:00 96524:00:00 2012-01-30 00:00:00 96624:00:00 2012-01-31 00:00:00 96724:00:00 2012-02-01 00:00:00 96848:00:00 2012-01-29 00:00:00 96948:00:00 2012-01-30 00:00:00 97048:00:00 2012-01-31 00:00:00 97148:00:00 2012-02-01 00:00:00 97248:00:00 2012-02-02 00:00:00 973EXPLAIN EXTENDED SELECT * FROM 974t1 force INDEX (col_time_key) 975STRAIGHT_JOIN 976t2 ignore INDEX (col_datetime_key) 977WHERE col_datetime_key >= col_time_key; 978id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9791 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 9801 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 981Warnings: 982Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 983Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) 984SELECT * FROM 985t1 force INDEX (col_time_key) 986STRAIGHT_JOIN 987t2 ignore INDEX (col_datetime_key) 988WHERE col_datetime_key >= col_time_key; 989col_time_key col_datetime_key 990-24:00:00 2012-01-30 00:00:00 991-24:00:00 2012-01-31 00:00:00 992-24:00:00 2012-02-01 00:00:00 993-24:00:00 2012-02-02 00:00:00 994-48:00:00 2012-01-29 00:00:00 995-48:00:00 2012-01-30 00:00:00 996-48:00:00 2012-01-31 00:00:00 997-48:00:00 2012-02-01 00:00:00 998-48:00:00 2012-02-02 00:00:00 99900:00:00 2012-01-31 00:00:00 100000:00:00 2012-02-01 00:00:00 100100:00:00 2012-02-02 00:00:00 100224:00:00 2012-02-01 00:00:00 100324:00:00 2012-02-02 00:00:00 100448:00:00 2012-02-02 00:00:00 1005EXPLAIN EXTENDED SELECT * FROM 1006t1 force INDEX (col_time_key) 1007STRAIGHT_JOIN 1008t2 force INDEX (col_datetime_key) 1009WHERE col_time_key >= col_datetime_key; 1010id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10111 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 10121 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1013Warnings: 1014Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1015Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) 1016SELECT * FROM 1017t1 force INDEX (col_time_key) 1018STRAIGHT_JOIN 1019t2 force INDEX (col_datetime_key) 1020WHERE col_time_key >= col_datetime_key; 1021col_time_key col_datetime_key 1022-24:00:00 2012-01-29 00:00:00 1023-24:00:00 2012-01-30 00:00:00 1024-48:00:00 2012-01-29 00:00:00 102500:00:00 2012-01-29 00:00:00 102600:00:00 2012-01-30 00:00:00 102700:00:00 2012-01-31 00:00:00 102824:00:00 2012-01-29 00:00:00 102924:00:00 2012-01-30 00:00:00 103024:00:00 2012-01-31 00:00:00 103124:00:00 2012-02-01 00:00:00 103248:00:00 2012-01-29 00:00:00 103348:00:00 2012-01-30 00:00:00 103448:00:00 2012-01-31 00:00:00 103548:00:00 2012-02-01 00:00:00 103648:00:00 2012-02-02 00:00:00 1037EXPLAIN EXTENDED SELECT * FROM 1038t1 force INDEX (col_time_key) 1039STRAIGHT_JOIN 1040t2 force INDEX (col_datetime_key) 1041WHERE col_datetime_key >= col_time_key; 1042id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10431 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 10441 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1045Warnings: 1046Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1047Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) 1048SELECT * FROM 1049t1 force INDEX (col_time_key) 1050STRAIGHT_JOIN 1051t2 force INDEX (col_datetime_key) 1052WHERE col_datetime_key >= col_time_key; 1053col_time_key col_datetime_key 1054-24:00:00 2012-01-30 00:00:00 1055-24:00:00 2012-01-31 00:00:00 1056-24:00:00 2012-02-01 00:00:00 1057-24:00:00 2012-02-02 00:00:00 1058-48:00:00 2012-01-29 00:00:00 1059-48:00:00 2012-01-30 00:00:00 1060-48:00:00 2012-01-31 00:00:00 1061-48:00:00 2012-02-01 00:00:00 1062-48:00:00 2012-02-02 00:00:00 106300:00:00 2012-01-31 00:00:00 106400:00:00 2012-02-01 00:00:00 106500:00:00 2012-02-02 00:00:00 106624:00:00 2012-02-01 00:00:00 106724:00:00 2012-02-02 00:00:00 106848:00:00 2012-02-02 00:00:00 1069EXPLAIN EXTENDED SELECT * FROM 1070t2 ignore INDEX (col_datetime_key) 1071STRAIGHT_JOIN 1072t1 ignore INDEX (col_time_key) 1073WHERE col_time_key >= col_datetime_key; 1074id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10751 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 10761 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1077Warnings: 1078Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1079Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) 1080SELECT * FROM 1081t2 ignore INDEX (col_datetime_key) 1082STRAIGHT_JOIN 1083t1 ignore INDEX (col_time_key) 1084WHERE col_time_key >= col_datetime_key; 1085col_datetime_key col_time_key 10862012-01-29 00:00:00 -24:00:00 10872012-01-29 00:00:00 -48:00:00 10882012-01-29 00:00:00 00:00:00 10892012-01-29 00:00:00 24:00:00 10902012-01-29 00:00:00 48:00:00 10912012-01-30 00:00:00 -24:00:00 10922012-01-30 00:00:00 00:00:00 10932012-01-30 00:00:00 24:00:00 10942012-01-30 00:00:00 48:00:00 10952012-01-31 00:00:00 00:00:00 10962012-01-31 00:00:00 24:00:00 10972012-01-31 00:00:00 48:00:00 10982012-02-01 00:00:00 24:00:00 10992012-02-01 00:00:00 48:00:00 11002012-02-02 00:00:00 48:00:00 1101EXPLAIN EXTENDED SELECT * FROM 1102t2 ignore INDEX (col_datetime_key) 1103STRAIGHT_JOIN 1104t1 ignore INDEX (col_time_key) 1105WHERE col_datetime_key >= col_time_key; 1106id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11071 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 11081 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1109Warnings: 1110Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1111Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) 1112SELECT * FROM 1113t2 ignore INDEX (col_datetime_key) 1114STRAIGHT_JOIN 1115t1 ignore INDEX (col_time_key) 1116WHERE col_datetime_key >= col_time_key; 1117col_datetime_key col_time_key 11182012-01-29 00:00:00 -48:00:00 11192012-01-30 00:00:00 -24:00:00 11202012-01-30 00:00:00 -48:00:00 11212012-01-31 00:00:00 -24:00:00 11222012-01-31 00:00:00 -48:00:00 11232012-01-31 00:00:00 00:00:00 11242012-02-01 00:00:00 -24:00:00 11252012-02-01 00:00:00 -48:00:00 11262012-02-01 00:00:00 00:00:00 11272012-02-01 00:00:00 24:00:00 11282012-02-02 00:00:00 -24:00:00 11292012-02-02 00:00:00 -48:00:00 11302012-02-02 00:00:00 00:00:00 11312012-02-02 00:00:00 24:00:00 11322012-02-02 00:00:00 48:00:00 1133EXPLAIN EXTENDED SELECT * FROM 1134t2 ignore INDEX (col_datetime_key) 1135STRAIGHT_JOIN 1136t1 force INDEX (col_time_key) 1137WHERE col_time_key >= col_datetime_key; 1138id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11391 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 11401 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 1141Warnings: 1142Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1143Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 1144Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) 1145SELECT * FROM 1146t2 ignore INDEX (col_datetime_key) 1147STRAIGHT_JOIN 1148t1 force INDEX (col_time_key) 1149WHERE col_time_key >= col_datetime_key; 1150col_datetime_key col_time_key 11512012-01-29 00:00:00 -24:00:00 11522012-01-29 00:00:00 -48:00:00 11532012-01-29 00:00:00 00:00:00 11542012-01-29 00:00:00 24:00:00 11552012-01-29 00:00:00 48:00:00 11562012-01-30 00:00:00 -24:00:00 11572012-01-30 00:00:00 00:00:00 11582012-01-30 00:00:00 24:00:00 11592012-01-30 00:00:00 48:00:00 11602012-01-31 00:00:00 00:00:00 11612012-01-31 00:00:00 24:00:00 11622012-01-31 00:00:00 48:00:00 11632012-02-01 00:00:00 24:00:00 11642012-02-01 00:00:00 48:00:00 11652012-02-02 00:00:00 48:00:00 1166EXPLAIN EXTENDED SELECT * FROM 1167t2 ignore INDEX (col_datetime_key) 1168STRAIGHT_JOIN 1169t1 force INDEX (col_time_key) 1170WHERE col_datetime_key >= col_time_key; 1171id select_type table partitions type possible_keys key key_len ref rows filtered Extra 11721 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 11731 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 1174Warnings: 1175Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1176Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 1177Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) 1178SELECT * FROM 1179t2 ignore INDEX (col_datetime_key) 1180STRAIGHT_JOIN 1181t1 force INDEX (col_time_key) 1182WHERE col_datetime_key >= col_time_key; 1183col_datetime_key col_time_key 11842012-01-29 00:00:00 -48:00:00 11852012-01-30 00:00:00 -24:00:00 11862012-01-30 00:00:00 -48:00:00 11872012-01-31 00:00:00 -24:00:00 11882012-01-31 00:00:00 -48:00:00 11892012-01-31 00:00:00 00:00:00 11902012-02-01 00:00:00 -24:00:00 11912012-02-01 00:00:00 -48:00:00 11922012-02-01 00:00:00 00:00:00 11932012-02-01 00:00:00 24:00:00 11942012-02-02 00:00:00 -24:00:00 11952012-02-02 00:00:00 -48:00:00 11962012-02-02 00:00:00 00:00:00 11972012-02-02 00:00:00 24:00:00 11982012-02-02 00:00:00 48:00:00 1199EXPLAIN EXTENDED SELECT * FROM 1200t2 force INDEX (col_datetime_key) 1201STRAIGHT_JOIN 1202t1 ignore INDEX (col_time_key) 1203WHERE col_time_key >= col_datetime_key; 1204id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12051 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 12061 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1207Warnings: 1208Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1209Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) 1210SELECT * FROM 1211t2 force INDEX (col_datetime_key) 1212STRAIGHT_JOIN 1213t1 ignore INDEX (col_time_key) 1214WHERE col_time_key >= col_datetime_key; 1215col_datetime_key col_time_key 12162012-01-29 00:00:00 -24:00:00 12172012-01-29 00:00:00 -48:00:00 12182012-01-29 00:00:00 00:00:00 12192012-01-29 00:00:00 24:00:00 12202012-01-29 00:00:00 48:00:00 12212012-01-30 00:00:00 -24:00:00 12222012-01-30 00:00:00 00:00:00 12232012-01-30 00:00:00 24:00:00 12242012-01-30 00:00:00 48:00:00 12252012-01-31 00:00:00 00:00:00 12262012-01-31 00:00:00 24:00:00 12272012-01-31 00:00:00 48:00:00 12282012-02-01 00:00:00 24:00:00 12292012-02-01 00:00:00 48:00:00 12302012-02-02 00:00:00 48:00:00 1231EXPLAIN EXTENDED SELECT * FROM 1232t2 force INDEX (col_datetime_key) 1233STRAIGHT_JOIN 1234t1 ignore INDEX (col_time_key) 1235WHERE col_datetime_key >= col_time_key; 1236id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12371 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 12381 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1239Warnings: 1240Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1241Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) 1242SELECT * FROM 1243t2 force INDEX (col_datetime_key) 1244STRAIGHT_JOIN 1245t1 ignore INDEX (col_time_key) 1246WHERE col_datetime_key >= col_time_key; 1247col_datetime_key col_time_key 12482012-01-29 00:00:00 -48:00:00 12492012-01-30 00:00:00 -24:00:00 12502012-01-30 00:00:00 -48:00:00 12512012-01-31 00:00:00 -24:00:00 12522012-01-31 00:00:00 -48:00:00 12532012-01-31 00:00:00 00:00:00 12542012-02-01 00:00:00 -24:00:00 12552012-02-01 00:00:00 -48:00:00 12562012-02-01 00:00:00 00:00:00 12572012-02-01 00:00:00 24:00:00 12582012-02-02 00:00:00 -24:00:00 12592012-02-02 00:00:00 -48:00:00 12602012-02-02 00:00:00 00:00:00 12612012-02-02 00:00:00 24:00:00 12622012-02-02 00:00:00 48:00:00 1263EXPLAIN EXTENDED SELECT * FROM 1264t2 force INDEX (col_datetime_key) 1265STRAIGHT_JOIN 1266t1 force INDEX (col_time_key) 1267WHERE col_time_key >= col_datetime_key; 1268id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12691 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 12701 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 1271Warnings: 1272Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1273Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 1274Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`) 1275SELECT * FROM 1276t2 force INDEX (col_datetime_key) 1277STRAIGHT_JOIN 1278t1 force INDEX (col_time_key) 1279WHERE col_time_key >= col_datetime_key; 1280col_datetime_key col_time_key 12812012-01-29 00:00:00 -24:00:00 12822012-01-29 00:00:00 -48:00:00 12832012-01-29 00:00:00 00:00:00 12842012-01-29 00:00:00 24:00:00 12852012-01-29 00:00:00 48:00:00 12862012-01-30 00:00:00 -24:00:00 12872012-01-30 00:00:00 00:00:00 12882012-01-30 00:00:00 24:00:00 12892012-01-30 00:00:00 48:00:00 12902012-01-31 00:00:00 00:00:00 12912012-01-31 00:00:00 24:00:00 12922012-01-31 00:00:00 48:00:00 12932012-02-01 00:00:00 24:00:00 12942012-02-01 00:00:00 48:00:00 12952012-02-02 00:00:00 48:00:00 1296EXPLAIN EXTENDED SELECT * FROM 1297t2 force INDEX (col_datetime_key) 1298STRAIGHT_JOIN 1299t1 force INDEX (col_time_key) 1300WHERE col_datetime_key >= col_time_key; 1301id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13021 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 13031 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 1304Warnings: 1305Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1306Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 1307Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`) 1308SELECT * FROM 1309t2 force INDEX (col_datetime_key) 1310STRAIGHT_JOIN 1311t1 force INDEX (col_time_key) 1312WHERE col_datetime_key >= col_time_key; 1313col_datetime_key col_time_key 13142012-01-29 00:00:00 -48:00:00 13152012-01-30 00:00:00 -24:00:00 13162012-01-30 00:00:00 -48:00:00 13172012-01-31 00:00:00 -24:00:00 13182012-01-31 00:00:00 -48:00:00 13192012-01-31 00:00:00 00:00:00 13202012-02-01 00:00:00 -24:00:00 13212012-02-01 00:00:00 -48:00:00 13222012-02-01 00:00:00 00:00:00 13232012-02-01 00:00:00 24:00:00 13242012-02-02 00:00:00 -24:00:00 13252012-02-02 00:00:00 -48:00:00 13262012-02-02 00:00:00 00:00:00 13272012-02-02 00:00:00 24:00:00 13282012-02-02 00:00:00 48:00:00 1329EXPLAIN EXTENDED SELECT * FROM 1330t1 ignore INDEX (col_time_key) 1331STRAIGHT_JOIN 1332t2 ignore INDEX (col_datetime_key) 1333WHERE col_time_key > col_datetime_key; 1334id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13351 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 13361 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1337Warnings: 1338Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1339Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 1340SELECT * FROM 1341t1 ignore INDEX (col_time_key) 1342STRAIGHT_JOIN 1343t2 ignore INDEX (col_datetime_key) 1344WHERE col_time_key > col_datetime_key; 1345col_time_key col_datetime_key 1346-24:00:00 2012-01-29 00:00:00 134700:00:00 2012-01-29 00:00:00 134800:00:00 2012-01-30 00:00:00 134924:00:00 2012-01-29 00:00:00 135024:00:00 2012-01-30 00:00:00 135124:00:00 2012-01-31 00:00:00 135248:00:00 2012-01-29 00:00:00 135348:00:00 2012-01-30 00:00:00 135448:00:00 2012-01-31 00:00:00 135548:00:00 2012-02-01 00:00:00 1356EXPLAIN EXTENDED SELECT * FROM 1357t1 ignore INDEX (col_time_key) 1358STRAIGHT_JOIN 1359t2 ignore INDEX (col_datetime_key) 1360WHERE col_datetime_key > col_time_key; 1361id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13621 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 13631 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1364Warnings: 1365Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1366Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) 1367SELECT * FROM 1368t1 ignore INDEX (col_time_key) 1369STRAIGHT_JOIN 1370t2 ignore INDEX (col_datetime_key) 1371WHERE col_datetime_key > col_time_key; 1372col_time_key col_datetime_key 1373-24:00:00 2012-01-31 00:00:00 1374-24:00:00 2012-02-01 00:00:00 1375-24:00:00 2012-02-02 00:00:00 1376-48:00:00 2012-01-30 00:00:00 1377-48:00:00 2012-01-31 00:00:00 1378-48:00:00 2012-02-01 00:00:00 1379-48:00:00 2012-02-02 00:00:00 138000:00:00 2012-02-01 00:00:00 138100:00:00 2012-02-02 00:00:00 138224:00:00 2012-02-02 00:00:00 1383EXPLAIN EXTENDED SELECT * FROM 1384t1 ignore INDEX (col_time_key) 1385STRAIGHT_JOIN 1386t2 force INDEX (col_datetime_key) 1387WHERE col_time_key > col_datetime_key; 1388id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13891 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 13901 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1391Warnings: 1392Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1393Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 1394SELECT * FROM 1395t1 ignore INDEX (col_time_key) 1396STRAIGHT_JOIN 1397t2 force INDEX (col_datetime_key) 1398WHERE col_time_key > col_datetime_key; 1399col_time_key col_datetime_key 1400-24:00:00 2012-01-29 00:00:00 140100:00:00 2012-01-29 00:00:00 140200:00:00 2012-01-30 00:00:00 140324:00:00 2012-01-29 00:00:00 140424:00:00 2012-01-30 00:00:00 140524:00:00 2012-01-31 00:00:00 140648:00:00 2012-01-29 00:00:00 140748:00:00 2012-01-30 00:00:00 140848:00:00 2012-01-31 00:00:00 140948:00:00 2012-02-01 00:00:00 1410EXPLAIN EXTENDED SELECT * FROM 1411t1 ignore INDEX (col_time_key) 1412STRAIGHT_JOIN 1413t2 force INDEX (col_datetime_key) 1414WHERE col_datetime_key > col_time_key; 1415id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14161 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 14171 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1418Warnings: 1419Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1420Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) 1421SELECT * FROM 1422t1 ignore INDEX (col_time_key) 1423STRAIGHT_JOIN 1424t2 force INDEX (col_datetime_key) 1425WHERE col_datetime_key > col_time_key; 1426col_time_key col_datetime_key 1427-24:00:00 2012-01-31 00:00:00 1428-24:00:00 2012-02-01 00:00:00 1429-24:00:00 2012-02-02 00:00:00 1430-48:00:00 2012-01-30 00:00:00 1431-48:00:00 2012-01-31 00:00:00 1432-48:00:00 2012-02-01 00:00:00 1433-48:00:00 2012-02-02 00:00:00 143400:00:00 2012-02-01 00:00:00 143500:00:00 2012-02-02 00:00:00 143624:00:00 2012-02-02 00:00:00 1437EXPLAIN EXTENDED SELECT * FROM 1438t1 force INDEX (col_time_key) 1439STRAIGHT_JOIN 1440t2 ignore INDEX (col_datetime_key) 1441WHERE col_time_key > col_datetime_key; 1442id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14431 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 14441 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1445Warnings: 1446Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1447Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 1448SELECT * FROM 1449t1 force INDEX (col_time_key) 1450STRAIGHT_JOIN 1451t2 ignore INDEX (col_datetime_key) 1452WHERE col_time_key > col_datetime_key; 1453col_time_key col_datetime_key 1454-24:00:00 2012-01-29 00:00:00 145500:00:00 2012-01-29 00:00:00 145600:00:00 2012-01-30 00:00:00 145724:00:00 2012-01-29 00:00:00 145824:00:00 2012-01-30 00:00:00 145924:00:00 2012-01-31 00:00:00 146048:00:00 2012-01-29 00:00:00 146148:00:00 2012-01-30 00:00:00 146248:00:00 2012-01-31 00:00:00 146348:00:00 2012-02-01 00:00:00 1464EXPLAIN EXTENDED SELECT * FROM 1465t1 force INDEX (col_time_key) 1466STRAIGHT_JOIN 1467t2 ignore INDEX (col_datetime_key) 1468WHERE col_datetime_key > col_time_key; 1469id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14701 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 14711 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1472Warnings: 1473Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1474Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) 1475SELECT * FROM 1476t1 force INDEX (col_time_key) 1477STRAIGHT_JOIN 1478t2 ignore INDEX (col_datetime_key) 1479WHERE col_datetime_key > col_time_key; 1480col_time_key col_datetime_key 1481-24:00:00 2012-01-31 00:00:00 1482-24:00:00 2012-02-01 00:00:00 1483-24:00:00 2012-02-02 00:00:00 1484-48:00:00 2012-01-30 00:00:00 1485-48:00:00 2012-01-31 00:00:00 1486-48:00:00 2012-02-01 00:00:00 1487-48:00:00 2012-02-02 00:00:00 148800:00:00 2012-02-01 00:00:00 148900:00:00 2012-02-02 00:00:00 149024:00:00 2012-02-02 00:00:00 1491EXPLAIN EXTENDED SELECT * FROM 1492t1 force INDEX (col_time_key) 1493STRAIGHT_JOIN 1494t2 force INDEX (col_datetime_key) 1495WHERE col_time_key > col_datetime_key; 1496id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14971 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 14981 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1499Warnings: 1500Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1501Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 1502SELECT * FROM 1503t1 force INDEX (col_time_key) 1504STRAIGHT_JOIN 1505t2 force INDEX (col_datetime_key) 1506WHERE col_time_key > col_datetime_key; 1507col_time_key col_datetime_key 1508-24:00:00 2012-01-29 00:00:00 150900:00:00 2012-01-29 00:00:00 151000:00:00 2012-01-30 00:00:00 151124:00:00 2012-01-29 00:00:00 151224:00:00 2012-01-30 00:00:00 151324:00:00 2012-01-31 00:00:00 151448:00:00 2012-01-29 00:00:00 151548:00:00 2012-01-30 00:00:00 151648:00:00 2012-01-31 00:00:00 151748:00:00 2012-02-01 00:00:00 1518EXPLAIN EXTENDED SELECT * FROM 1519t1 force INDEX (col_time_key) 1520STRAIGHT_JOIN 1521t2 force INDEX (col_datetime_key) 1522WHERE col_datetime_key > col_time_key; 1523id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15241 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 15251 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1526Warnings: 1527Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1528Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) 1529SELECT * FROM 1530t1 force INDEX (col_time_key) 1531STRAIGHT_JOIN 1532t2 force INDEX (col_datetime_key) 1533WHERE col_datetime_key > col_time_key; 1534col_time_key col_datetime_key 1535-24:00:00 2012-01-31 00:00:00 1536-24:00:00 2012-02-01 00:00:00 1537-24:00:00 2012-02-02 00:00:00 1538-48:00:00 2012-01-30 00:00:00 1539-48:00:00 2012-01-31 00:00:00 1540-48:00:00 2012-02-01 00:00:00 1541-48:00:00 2012-02-02 00:00:00 154200:00:00 2012-02-01 00:00:00 154300:00:00 2012-02-02 00:00:00 154424:00:00 2012-02-02 00:00:00 1545EXPLAIN EXTENDED SELECT * FROM 1546t2 ignore INDEX (col_datetime_key) 1547STRAIGHT_JOIN 1548t1 ignore INDEX (col_time_key) 1549WHERE col_time_key > col_datetime_key; 1550id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15511 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 15521 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1553Warnings: 1554Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1555Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 1556SELECT * FROM 1557t2 ignore INDEX (col_datetime_key) 1558STRAIGHT_JOIN 1559t1 ignore INDEX (col_time_key) 1560WHERE col_time_key > col_datetime_key; 1561col_datetime_key col_time_key 15622012-01-29 00:00:00 -24:00:00 15632012-01-29 00:00:00 00:00:00 15642012-01-29 00:00:00 24:00:00 15652012-01-29 00:00:00 48:00:00 15662012-01-30 00:00:00 00:00:00 15672012-01-30 00:00:00 24:00:00 15682012-01-30 00:00:00 48:00:00 15692012-01-31 00:00:00 24:00:00 15702012-01-31 00:00:00 48:00:00 15712012-02-01 00:00:00 48:00:00 1572EXPLAIN EXTENDED SELECT * FROM 1573t2 ignore INDEX (col_datetime_key) 1574STRAIGHT_JOIN 1575t1 ignore INDEX (col_time_key) 1576WHERE col_datetime_key > col_time_key; 1577id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15781 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 15791 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1580Warnings: 1581Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1582Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) 1583SELECT * FROM 1584t2 ignore INDEX (col_datetime_key) 1585STRAIGHT_JOIN 1586t1 ignore INDEX (col_time_key) 1587WHERE col_datetime_key > col_time_key; 1588col_datetime_key col_time_key 15892012-01-30 00:00:00 -48:00:00 15902012-01-31 00:00:00 -24:00:00 15912012-01-31 00:00:00 -48:00:00 15922012-02-01 00:00:00 -24:00:00 15932012-02-01 00:00:00 -48:00:00 15942012-02-01 00:00:00 00:00:00 15952012-02-02 00:00:00 -24:00:00 15962012-02-02 00:00:00 -48:00:00 15972012-02-02 00:00:00 00:00:00 15982012-02-02 00:00:00 24:00:00 1599EXPLAIN EXTENDED SELECT * FROM 1600t2 ignore INDEX (col_datetime_key) 1601STRAIGHT_JOIN 1602t1 force INDEX (col_time_key) 1603WHERE col_time_key > col_datetime_key; 1604id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16051 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 16061 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 1607Warnings: 1608Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1609Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 1610Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 1611SELECT * FROM 1612t2 ignore INDEX (col_datetime_key) 1613STRAIGHT_JOIN 1614t1 force INDEX (col_time_key) 1615WHERE col_time_key > col_datetime_key; 1616col_datetime_key col_time_key 16172012-01-29 00:00:00 -24:00:00 16182012-01-29 00:00:00 00:00:00 16192012-01-29 00:00:00 24:00:00 16202012-01-29 00:00:00 48:00:00 16212012-01-30 00:00:00 00:00:00 16222012-01-30 00:00:00 24:00:00 16232012-01-30 00:00:00 48:00:00 16242012-01-31 00:00:00 24:00:00 16252012-01-31 00:00:00 48:00:00 16262012-02-01 00:00:00 48:00:00 1627EXPLAIN EXTENDED SELECT * FROM 1628t2 ignore INDEX (col_datetime_key) 1629STRAIGHT_JOIN 1630t1 force INDEX (col_time_key) 1631WHERE col_datetime_key > col_time_key; 1632id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16331 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 16341 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 1635Warnings: 1636Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1637Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 1638Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) 1639SELECT * FROM 1640t2 ignore INDEX (col_datetime_key) 1641STRAIGHT_JOIN 1642t1 force INDEX (col_time_key) 1643WHERE col_datetime_key > col_time_key; 1644col_datetime_key col_time_key 16452012-01-30 00:00:00 -48:00:00 16462012-01-31 00:00:00 -24:00:00 16472012-01-31 00:00:00 -48:00:00 16482012-02-01 00:00:00 -24:00:00 16492012-02-01 00:00:00 -48:00:00 16502012-02-01 00:00:00 00:00:00 16512012-02-02 00:00:00 -24:00:00 16522012-02-02 00:00:00 -48:00:00 16532012-02-02 00:00:00 00:00:00 16542012-02-02 00:00:00 24:00:00 1655EXPLAIN EXTENDED SELECT * FROM 1656t2 force INDEX (col_datetime_key) 1657STRAIGHT_JOIN 1658t1 ignore INDEX (col_time_key) 1659WHERE col_time_key > col_datetime_key; 1660id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16611 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 16621 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1663Warnings: 1664Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1665Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 1666SELECT * FROM 1667t2 force INDEX (col_datetime_key) 1668STRAIGHT_JOIN 1669t1 ignore INDEX (col_time_key) 1670WHERE col_time_key > col_datetime_key; 1671col_datetime_key col_time_key 16722012-01-29 00:00:00 -24:00:00 16732012-01-29 00:00:00 00:00:00 16742012-01-29 00:00:00 24:00:00 16752012-01-29 00:00:00 48:00:00 16762012-01-30 00:00:00 00:00:00 16772012-01-30 00:00:00 24:00:00 16782012-01-30 00:00:00 48:00:00 16792012-01-31 00:00:00 24:00:00 16802012-01-31 00:00:00 48:00:00 16812012-02-01 00:00:00 48:00:00 1682EXPLAIN EXTENDED SELECT * FROM 1683t2 force INDEX (col_datetime_key) 1684STRAIGHT_JOIN 1685t1 ignore INDEX (col_time_key) 1686WHERE col_datetime_key > col_time_key; 1687id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16881 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 16891 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1690Warnings: 1691Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1692Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) 1693SELECT * FROM 1694t2 force INDEX (col_datetime_key) 1695STRAIGHT_JOIN 1696t1 ignore INDEX (col_time_key) 1697WHERE col_datetime_key > col_time_key; 1698col_datetime_key col_time_key 16992012-01-30 00:00:00 -48:00:00 17002012-01-31 00:00:00 -24:00:00 17012012-01-31 00:00:00 -48:00:00 17022012-02-01 00:00:00 -24:00:00 17032012-02-01 00:00:00 -48:00:00 17042012-02-01 00:00:00 00:00:00 17052012-02-02 00:00:00 -24:00:00 17062012-02-02 00:00:00 -48:00:00 17072012-02-02 00:00:00 00:00:00 17082012-02-02 00:00:00 24:00:00 1709EXPLAIN EXTENDED SELECT * FROM 1710t2 force INDEX (col_datetime_key) 1711STRAIGHT_JOIN 1712t1 force INDEX (col_time_key) 1713WHERE col_time_key > col_datetime_key; 1714id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17151 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 17161 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 1717Warnings: 1718Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1719Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 1720Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 1721SELECT * FROM 1722t2 force INDEX (col_datetime_key) 1723STRAIGHT_JOIN 1724t1 force INDEX (col_time_key) 1725WHERE col_time_key > col_datetime_key; 1726col_datetime_key col_time_key 17272012-01-29 00:00:00 -24:00:00 17282012-01-29 00:00:00 00:00:00 17292012-01-29 00:00:00 24:00:00 17302012-01-29 00:00:00 48:00:00 17312012-01-30 00:00:00 00:00:00 17322012-01-30 00:00:00 24:00:00 17332012-01-30 00:00:00 48:00:00 17342012-01-31 00:00:00 24:00:00 17352012-01-31 00:00:00 48:00:00 17362012-02-01 00:00:00 48:00:00 1737EXPLAIN EXTENDED SELECT * FROM 1738t2 force INDEX (col_datetime_key) 1739STRAIGHT_JOIN 1740t1 force INDEX (col_time_key) 1741WHERE col_datetime_key > col_time_key; 1742id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17431 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 17441 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 1745Warnings: 1746Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1747Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 1748Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`) 1749SELECT * FROM 1750t2 force INDEX (col_datetime_key) 1751STRAIGHT_JOIN 1752t1 force INDEX (col_time_key) 1753WHERE col_datetime_key > col_time_key; 1754col_datetime_key col_time_key 17552012-01-30 00:00:00 -48:00:00 17562012-01-31 00:00:00 -24:00:00 17572012-01-31 00:00:00 -48:00:00 17582012-02-01 00:00:00 -24:00:00 17592012-02-01 00:00:00 -48:00:00 17602012-02-01 00:00:00 00:00:00 17612012-02-02 00:00:00 -24:00:00 17622012-02-02 00:00:00 -48:00:00 17632012-02-02 00:00:00 00:00:00 17642012-02-02 00:00:00 24:00:00 1765EXPLAIN EXTENDED SELECT * FROM 1766t1 ignore INDEX (col_time_key) 1767STRAIGHT_JOIN 1768t2 ignore INDEX (col_datetime_key) 1769WHERE col_time_key <= col_datetime_key; 1770id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17711 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 17721 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1773Warnings: 1774Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1775Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) 1776SELECT * FROM 1777t1 ignore INDEX (col_time_key) 1778STRAIGHT_JOIN 1779t2 ignore INDEX (col_datetime_key) 1780WHERE col_time_key <= col_datetime_key; 1781col_time_key col_datetime_key 1782-24:00:00 2012-01-30 00:00:00 1783-24:00:00 2012-01-31 00:00:00 1784-24:00:00 2012-02-01 00:00:00 1785-24:00:00 2012-02-02 00:00:00 1786-48:00:00 2012-01-29 00:00:00 1787-48:00:00 2012-01-30 00:00:00 1788-48:00:00 2012-01-31 00:00:00 1789-48:00:00 2012-02-01 00:00:00 1790-48:00:00 2012-02-02 00:00:00 179100:00:00 2012-01-31 00:00:00 179200:00:00 2012-02-01 00:00:00 179300:00:00 2012-02-02 00:00:00 179424:00:00 2012-02-01 00:00:00 179524:00:00 2012-02-02 00:00:00 179648:00:00 2012-02-02 00:00:00 1797EXPLAIN EXTENDED SELECT * FROM 1798t1 ignore INDEX (col_time_key) 1799STRAIGHT_JOIN 1800t2 ignore INDEX (col_datetime_key) 1801WHERE col_datetime_key <= col_time_key; 1802id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18031 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 18041 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1805Warnings: 1806Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1807Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) 1808SELECT * FROM 1809t1 ignore INDEX (col_time_key) 1810STRAIGHT_JOIN 1811t2 ignore INDEX (col_datetime_key) 1812WHERE col_datetime_key <= col_time_key; 1813col_time_key col_datetime_key 1814-24:00:00 2012-01-29 00:00:00 1815-24:00:00 2012-01-30 00:00:00 1816-48:00:00 2012-01-29 00:00:00 181700:00:00 2012-01-29 00:00:00 181800:00:00 2012-01-30 00:00:00 181900:00:00 2012-01-31 00:00:00 182024:00:00 2012-01-29 00:00:00 182124:00:00 2012-01-30 00:00:00 182224:00:00 2012-01-31 00:00:00 182324:00:00 2012-02-01 00:00:00 182448:00:00 2012-01-29 00:00:00 182548:00:00 2012-01-30 00:00:00 182648:00:00 2012-01-31 00:00:00 182748:00:00 2012-02-01 00:00:00 182848:00:00 2012-02-02 00:00:00 1829EXPLAIN EXTENDED SELECT * FROM 1830t1 ignore INDEX (col_time_key) 1831STRAIGHT_JOIN 1832t2 force INDEX (col_datetime_key) 1833WHERE col_time_key <= col_datetime_key; 1834id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18351 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 18361 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1837Warnings: 1838Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1839Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) 1840SELECT * FROM 1841t1 ignore INDEX (col_time_key) 1842STRAIGHT_JOIN 1843t2 force INDEX (col_datetime_key) 1844WHERE col_time_key <= col_datetime_key; 1845col_time_key col_datetime_key 1846-24:00:00 2012-01-30 00:00:00 1847-24:00:00 2012-01-31 00:00:00 1848-24:00:00 2012-02-01 00:00:00 1849-24:00:00 2012-02-02 00:00:00 1850-48:00:00 2012-01-29 00:00:00 1851-48:00:00 2012-01-30 00:00:00 1852-48:00:00 2012-01-31 00:00:00 1853-48:00:00 2012-02-01 00:00:00 1854-48:00:00 2012-02-02 00:00:00 185500:00:00 2012-01-31 00:00:00 185600:00:00 2012-02-01 00:00:00 185700:00:00 2012-02-02 00:00:00 185824:00:00 2012-02-01 00:00:00 185924:00:00 2012-02-02 00:00:00 186048:00:00 2012-02-02 00:00:00 1861EXPLAIN EXTENDED SELECT * FROM 1862t1 ignore INDEX (col_time_key) 1863STRAIGHT_JOIN 1864t2 force INDEX (col_datetime_key) 1865WHERE col_datetime_key <= col_time_key; 1866id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18671 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 18681 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1869Warnings: 1870Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1871Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) 1872SELECT * FROM 1873t1 ignore INDEX (col_time_key) 1874STRAIGHT_JOIN 1875t2 force INDEX (col_datetime_key) 1876WHERE col_datetime_key <= col_time_key; 1877col_time_key col_datetime_key 1878-24:00:00 2012-01-29 00:00:00 1879-24:00:00 2012-01-30 00:00:00 1880-48:00:00 2012-01-29 00:00:00 188100:00:00 2012-01-29 00:00:00 188200:00:00 2012-01-30 00:00:00 188300:00:00 2012-01-31 00:00:00 188424:00:00 2012-01-29 00:00:00 188524:00:00 2012-01-30 00:00:00 188624:00:00 2012-01-31 00:00:00 188724:00:00 2012-02-01 00:00:00 188848:00:00 2012-01-29 00:00:00 188948:00:00 2012-01-30 00:00:00 189048:00:00 2012-01-31 00:00:00 189148:00:00 2012-02-01 00:00:00 189248:00:00 2012-02-02 00:00:00 1893EXPLAIN EXTENDED SELECT * FROM 1894t1 force INDEX (col_time_key) 1895STRAIGHT_JOIN 1896t2 ignore INDEX (col_datetime_key) 1897WHERE col_time_key <= col_datetime_key; 1898id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18991 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 19001 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1901Warnings: 1902Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1903Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) 1904SELECT * FROM 1905t1 force INDEX (col_time_key) 1906STRAIGHT_JOIN 1907t2 ignore INDEX (col_datetime_key) 1908WHERE col_time_key <= col_datetime_key; 1909col_time_key col_datetime_key 1910-24:00:00 2012-01-30 00:00:00 1911-24:00:00 2012-01-31 00:00:00 1912-24:00:00 2012-02-01 00:00:00 1913-24:00:00 2012-02-02 00:00:00 1914-48:00:00 2012-01-29 00:00:00 1915-48:00:00 2012-01-30 00:00:00 1916-48:00:00 2012-01-31 00:00:00 1917-48:00:00 2012-02-01 00:00:00 1918-48:00:00 2012-02-02 00:00:00 191900:00:00 2012-01-31 00:00:00 192000:00:00 2012-02-01 00:00:00 192100:00:00 2012-02-02 00:00:00 192224:00:00 2012-02-01 00:00:00 192324:00:00 2012-02-02 00:00:00 192448:00:00 2012-02-02 00:00:00 1925EXPLAIN EXTENDED SELECT * FROM 1926t1 force INDEX (col_time_key) 1927STRAIGHT_JOIN 1928t2 ignore INDEX (col_datetime_key) 1929WHERE col_datetime_key <= col_time_key; 1930id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19311 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 19321 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 1933Warnings: 1934Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1935Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) 1936SELECT * FROM 1937t1 force INDEX (col_time_key) 1938STRAIGHT_JOIN 1939t2 ignore INDEX (col_datetime_key) 1940WHERE col_datetime_key <= col_time_key; 1941col_time_key col_datetime_key 1942-24:00:00 2012-01-29 00:00:00 1943-24:00:00 2012-01-30 00:00:00 1944-48:00:00 2012-01-29 00:00:00 194500:00:00 2012-01-29 00:00:00 194600:00:00 2012-01-30 00:00:00 194700:00:00 2012-01-31 00:00:00 194824:00:00 2012-01-29 00:00:00 194924:00:00 2012-01-30 00:00:00 195024:00:00 2012-01-31 00:00:00 195124:00:00 2012-02-01 00:00:00 195248:00:00 2012-01-29 00:00:00 195348:00:00 2012-01-30 00:00:00 195448:00:00 2012-01-31 00:00:00 195548:00:00 2012-02-01 00:00:00 195648:00:00 2012-02-02 00:00:00 1957EXPLAIN EXTENDED SELECT * FROM 1958t1 force INDEX (col_time_key) 1959STRAIGHT_JOIN 1960t2 force INDEX (col_datetime_key) 1961WHERE col_time_key <= col_datetime_key; 1962id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19631 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 19641 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1965Warnings: 1966Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1967Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) 1968SELECT * FROM 1969t1 force INDEX (col_time_key) 1970STRAIGHT_JOIN 1971t2 force INDEX (col_datetime_key) 1972WHERE col_time_key <= col_datetime_key; 1973col_time_key col_datetime_key 1974-24:00:00 2012-01-30 00:00:00 1975-24:00:00 2012-01-31 00:00:00 1976-24:00:00 2012-02-01 00:00:00 1977-24:00:00 2012-02-02 00:00:00 1978-48:00:00 2012-01-29 00:00:00 1979-48:00:00 2012-01-30 00:00:00 1980-48:00:00 2012-01-31 00:00:00 1981-48:00:00 2012-02-01 00:00:00 1982-48:00:00 2012-02-02 00:00:00 198300:00:00 2012-01-31 00:00:00 198400:00:00 2012-02-01 00:00:00 198500:00:00 2012-02-02 00:00:00 198624:00:00 2012-02-01 00:00:00 198724:00:00 2012-02-02 00:00:00 198848:00:00 2012-02-02 00:00:00 1989EXPLAIN EXTENDED SELECT * FROM 1990t1 force INDEX (col_time_key) 1991STRAIGHT_JOIN 1992t2 force INDEX (col_datetime_key) 1993WHERE col_datetime_key <= col_time_key; 1994id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19951 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 19961 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 1997Warnings: 1998Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 1999Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) 2000SELECT * FROM 2001t1 force INDEX (col_time_key) 2002STRAIGHT_JOIN 2003t2 force INDEX (col_datetime_key) 2004WHERE col_datetime_key <= col_time_key; 2005col_time_key col_datetime_key 2006-24:00:00 2012-01-29 00:00:00 2007-24:00:00 2012-01-30 00:00:00 2008-48:00:00 2012-01-29 00:00:00 200900:00:00 2012-01-29 00:00:00 201000:00:00 2012-01-30 00:00:00 201100:00:00 2012-01-31 00:00:00 201224:00:00 2012-01-29 00:00:00 201324:00:00 2012-01-30 00:00:00 201424:00:00 2012-01-31 00:00:00 201524:00:00 2012-02-01 00:00:00 201648:00:00 2012-01-29 00:00:00 201748:00:00 2012-01-30 00:00:00 201848:00:00 2012-01-31 00:00:00 201948:00:00 2012-02-01 00:00:00 202048:00:00 2012-02-02 00:00:00 2021EXPLAIN EXTENDED SELECT * FROM 2022t2 ignore INDEX (col_datetime_key) 2023STRAIGHT_JOIN 2024t1 ignore INDEX (col_time_key) 2025WHERE col_time_key <= col_datetime_key; 2026id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20271 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 20281 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2029Warnings: 2030Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2031Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) 2032SELECT * FROM 2033t2 ignore INDEX (col_datetime_key) 2034STRAIGHT_JOIN 2035t1 ignore INDEX (col_time_key) 2036WHERE col_time_key <= col_datetime_key; 2037col_datetime_key col_time_key 20382012-01-29 00:00:00 -48:00:00 20392012-01-30 00:00:00 -24:00:00 20402012-01-30 00:00:00 -48:00:00 20412012-01-31 00:00:00 -24:00:00 20422012-01-31 00:00:00 -48:00:00 20432012-01-31 00:00:00 00:00:00 20442012-02-01 00:00:00 -24:00:00 20452012-02-01 00:00:00 -48:00:00 20462012-02-01 00:00:00 00:00:00 20472012-02-01 00:00:00 24:00:00 20482012-02-02 00:00:00 -24:00:00 20492012-02-02 00:00:00 -48:00:00 20502012-02-02 00:00:00 00:00:00 20512012-02-02 00:00:00 24:00:00 20522012-02-02 00:00:00 48:00:00 2053EXPLAIN EXTENDED SELECT * FROM 2054t2 ignore INDEX (col_datetime_key) 2055STRAIGHT_JOIN 2056t1 ignore INDEX (col_time_key) 2057WHERE col_datetime_key <= col_time_key; 2058id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20591 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 20601 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2061Warnings: 2062Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2063Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) 2064SELECT * FROM 2065t2 ignore INDEX (col_datetime_key) 2066STRAIGHT_JOIN 2067t1 ignore INDEX (col_time_key) 2068WHERE col_datetime_key <= col_time_key; 2069col_datetime_key col_time_key 20702012-01-29 00:00:00 -24:00:00 20712012-01-29 00:00:00 -48:00:00 20722012-01-29 00:00:00 00:00:00 20732012-01-29 00:00:00 24:00:00 20742012-01-29 00:00:00 48:00:00 20752012-01-30 00:00:00 -24:00:00 20762012-01-30 00:00:00 00:00:00 20772012-01-30 00:00:00 24:00:00 20782012-01-30 00:00:00 48:00:00 20792012-01-31 00:00:00 00:00:00 20802012-01-31 00:00:00 24:00:00 20812012-01-31 00:00:00 48:00:00 20822012-02-01 00:00:00 24:00:00 20832012-02-01 00:00:00 48:00:00 20842012-02-02 00:00:00 48:00:00 2085EXPLAIN EXTENDED SELECT * FROM 2086t2 ignore INDEX (col_datetime_key) 2087STRAIGHT_JOIN 2088t1 force INDEX (col_time_key) 2089WHERE col_time_key <= col_datetime_key; 2090id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20911 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 20921 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2093Warnings: 2094Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2095Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2096Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) 2097SELECT * FROM 2098t2 ignore INDEX (col_datetime_key) 2099STRAIGHT_JOIN 2100t1 force INDEX (col_time_key) 2101WHERE col_time_key <= col_datetime_key; 2102col_datetime_key col_time_key 21032012-01-29 00:00:00 -48:00:00 21042012-01-30 00:00:00 -24:00:00 21052012-01-30 00:00:00 -48:00:00 21062012-01-31 00:00:00 -24:00:00 21072012-01-31 00:00:00 -48:00:00 21082012-01-31 00:00:00 00:00:00 21092012-02-01 00:00:00 -24:00:00 21102012-02-01 00:00:00 -48:00:00 21112012-02-01 00:00:00 00:00:00 21122012-02-01 00:00:00 24:00:00 21132012-02-02 00:00:00 -24:00:00 21142012-02-02 00:00:00 -48:00:00 21152012-02-02 00:00:00 00:00:00 21162012-02-02 00:00:00 24:00:00 21172012-02-02 00:00:00 48:00:00 2118EXPLAIN EXTENDED SELECT * FROM 2119t2 ignore INDEX (col_datetime_key) 2120STRAIGHT_JOIN 2121t1 force INDEX (col_time_key) 2122WHERE col_datetime_key <= col_time_key; 2123id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21241 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 21251 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2126Warnings: 2127Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2128Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2129Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) 2130SELECT * FROM 2131t2 ignore INDEX (col_datetime_key) 2132STRAIGHT_JOIN 2133t1 force INDEX (col_time_key) 2134WHERE col_datetime_key <= col_time_key; 2135col_datetime_key col_time_key 21362012-01-29 00:00:00 -24:00:00 21372012-01-29 00:00:00 -48:00:00 21382012-01-29 00:00:00 00:00:00 21392012-01-29 00:00:00 24:00:00 21402012-01-29 00:00:00 48:00:00 21412012-01-30 00:00:00 -24:00:00 21422012-01-30 00:00:00 00:00:00 21432012-01-30 00:00:00 24:00:00 21442012-01-30 00:00:00 48:00:00 21452012-01-31 00:00:00 00:00:00 21462012-01-31 00:00:00 24:00:00 21472012-01-31 00:00:00 48:00:00 21482012-02-01 00:00:00 24:00:00 21492012-02-01 00:00:00 48:00:00 21502012-02-02 00:00:00 48:00:00 2151EXPLAIN EXTENDED SELECT * FROM 2152t2 force INDEX (col_datetime_key) 2153STRAIGHT_JOIN 2154t1 ignore INDEX (col_time_key) 2155WHERE col_time_key <= col_datetime_key; 2156id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21571 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 21581 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2159Warnings: 2160Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2161Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) 2162SELECT * FROM 2163t2 force INDEX (col_datetime_key) 2164STRAIGHT_JOIN 2165t1 ignore INDEX (col_time_key) 2166WHERE col_time_key <= col_datetime_key; 2167col_datetime_key col_time_key 21682012-01-29 00:00:00 -48:00:00 21692012-01-30 00:00:00 -24:00:00 21702012-01-30 00:00:00 -48:00:00 21712012-01-31 00:00:00 -24:00:00 21722012-01-31 00:00:00 -48:00:00 21732012-01-31 00:00:00 00:00:00 21742012-02-01 00:00:00 -24:00:00 21752012-02-01 00:00:00 -48:00:00 21762012-02-01 00:00:00 00:00:00 21772012-02-01 00:00:00 24:00:00 21782012-02-02 00:00:00 -24:00:00 21792012-02-02 00:00:00 -48:00:00 21802012-02-02 00:00:00 00:00:00 21812012-02-02 00:00:00 24:00:00 21822012-02-02 00:00:00 48:00:00 2183EXPLAIN EXTENDED SELECT * FROM 2184t2 force INDEX (col_datetime_key) 2185STRAIGHT_JOIN 2186t1 ignore INDEX (col_time_key) 2187WHERE col_datetime_key <= col_time_key; 2188id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21891 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 21901 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2191Warnings: 2192Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2193Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) 2194SELECT * FROM 2195t2 force INDEX (col_datetime_key) 2196STRAIGHT_JOIN 2197t1 ignore INDEX (col_time_key) 2198WHERE col_datetime_key <= col_time_key; 2199col_datetime_key col_time_key 22002012-01-29 00:00:00 -24:00:00 22012012-01-29 00:00:00 -48:00:00 22022012-01-29 00:00:00 00:00:00 22032012-01-29 00:00:00 24:00:00 22042012-01-29 00:00:00 48:00:00 22052012-01-30 00:00:00 -24:00:00 22062012-01-30 00:00:00 00:00:00 22072012-01-30 00:00:00 24:00:00 22082012-01-30 00:00:00 48:00:00 22092012-01-31 00:00:00 00:00:00 22102012-01-31 00:00:00 24:00:00 22112012-01-31 00:00:00 48:00:00 22122012-02-01 00:00:00 24:00:00 22132012-02-01 00:00:00 48:00:00 22142012-02-02 00:00:00 48:00:00 2215EXPLAIN EXTENDED SELECT * FROM 2216t2 force INDEX (col_datetime_key) 2217STRAIGHT_JOIN 2218t1 force INDEX (col_time_key) 2219WHERE col_time_key <= col_datetime_key; 2220id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22211 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 22221 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2223Warnings: 2224Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2225Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2226Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`) 2227SELECT * FROM 2228t2 force INDEX (col_datetime_key) 2229STRAIGHT_JOIN 2230t1 force INDEX (col_time_key) 2231WHERE col_time_key <= col_datetime_key; 2232col_datetime_key col_time_key 22332012-01-29 00:00:00 -48:00:00 22342012-01-30 00:00:00 -24:00:00 22352012-01-30 00:00:00 -48:00:00 22362012-01-31 00:00:00 -24:00:00 22372012-01-31 00:00:00 -48:00:00 22382012-01-31 00:00:00 00:00:00 22392012-02-01 00:00:00 -24:00:00 22402012-02-01 00:00:00 -48:00:00 22412012-02-01 00:00:00 00:00:00 22422012-02-01 00:00:00 24:00:00 22432012-02-02 00:00:00 -24:00:00 22442012-02-02 00:00:00 -48:00:00 22452012-02-02 00:00:00 00:00:00 22462012-02-02 00:00:00 24:00:00 22472012-02-02 00:00:00 48:00:00 2248EXPLAIN EXTENDED SELECT * FROM 2249t2 force INDEX (col_datetime_key) 2250STRAIGHT_JOIN 2251t1 force INDEX (col_time_key) 2252WHERE col_datetime_key <= col_time_key; 2253id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22541 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 22551 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2256Warnings: 2257Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2258Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2259Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`) 2260SELECT * FROM 2261t2 force INDEX (col_datetime_key) 2262STRAIGHT_JOIN 2263t1 force INDEX (col_time_key) 2264WHERE col_datetime_key <= col_time_key; 2265col_datetime_key col_time_key 22662012-01-29 00:00:00 -24:00:00 22672012-01-29 00:00:00 -48:00:00 22682012-01-29 00:00:00 00:00:00 22692012-01-29 00:00:00 24:00:00 22702012-01-29 00:00:00 48:00:00 22712012-01-30 00:00:00 -24:00:00 22722012-01-30 00:00:00 00:00:00 22732012-01-30 00:00:00 24:00:00 22742012-01-30 00:00:00 48:00:00 22752012-01-31 00:00:00 00:00:00 22762012-01-31 00:00:00 24:00:00 22772012-01-31 00:00:00 48:00:00 22782012-02-01 00:00:00 24:00:00 22792012-02-01 00:00:00 48:00:00 22802012-02-02 00:00:00 48:00:00 2281EXPLAIN EXTENDED SELECT * FROM 2282t1 ignore INDEX (col_time_key) 2283STRAIGHT_JOIN 2284t2 ignore INDEX (col_datetime_key) 2285WHERE col_time_key < col_datetime_key; 2286id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22871 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 22881 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2289Warnings: 2290Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2291Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) 2292SELECT * FROM 2293t1 ignore INDEX (col_time_key) 2294STRAIGHT_JOIN 2295t2 ignore INDEX (col_datetime_key) 2296WHERE col_time_key < col_datetime_key; 2297col_time_key col_datetime_key 2298-24:00:00 2012-01-31 00:00:00 2299-24:00:00 2012-02-01 00:00:00 2300-24:00:00 2012-02-02 00:00:00 2301-48:00:00 2012-01-30 00:00:00 2302-48:00:00 2012-01-31 00:00:00 2303-48:00:00 2012-02-01 00:00:00 2304-48:00:00 2012-02-02 00:00:00 230500:00:00 2012-02-01 00:00:00 230600:00:00 2012-02-02 00:00:00 230724:00:00 2012-02-02 00:00:00 2308EXPLAIN EXTENDED SELECT * FROM 2309t1 ignore INDEX (col_time_key) 2310STRAIGHT_JOIN 2311t2 ignore INDEX (col_datetime_key) 2312WHERE col_datetime_key < col_time_key; 2313id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23141 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 23151 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2316Warnings: 2317Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2318Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) 2319SELECT * FROM 2320t1 ignore INDEX (col_time_key) 2321STRAIGHT_JOIN 2322t2 ignore INDEX (col_datetime_key) 2323WHERE col_datetime_key < col_time_key; 2324col_time_key col_datetime_key 2325-24:00:00 2012-01-29 00:00:00 232600:00:00 2012-01-29 00:00:00 232700:00:00 2012-01-30 00:00:00 232824:00:00 2012-01-29 00:00:00 232924:00:00 2012-01-30 00:00:00 233024:00:00 2012-01-31 00:00:00 233148:00:00 2012-01-29 00:00:00 233248:00:00 2012-01-30 00:00:00 233348:00:00 2012-01-31 00:00:00 233448:00:00 2012-02-01 00:00:00 2335EXPLAIN EXTENDED SELECT * FROM 2336t1 ignore INDEX (col_time_key) 2337STRAIGHT_JOIN 2338t2 force INDEX (col_datetime_key) 2339WHERE col_time_key < col_datetime_key; 2340id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23411 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 23421 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 2343Warnings: 2344Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2345Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) 2346SELECT * FROM 2347t1 ignore INDEX (col_time_key) 2348STRAIGHT_JOIN 2349t2 force INDEX (col_datetime_key) 2350WHERE col_time_key < col_datetime_key; 2351col_time_key col_datetime_key 2352-24:00:00 2012-01-31 00:00:00 2353-24:00:00 2012-02-01 00:00:00 2354-24:00:00 2012-02-02 00:00:00 2355-48:00:00 2012-01-30 00:00:00 2356-48:00:00 2012-01-31 00:00:00 2357-48:00:00 2012-02-01 00:00:00 2358-48:00:00 2012-02-02 00:00:00 235900:00:00 2012-02-01 00:00:00 236000:00:00 2012-02-02 00:00:00 236124:00:00 2012-02-02 00:00:00 2362EXPLAIN EXTENDED SELECT * FROM 2363t1 ignore INDEX (col_time_key) 2364STRAIGHT_JOIN 2365t2 force INDEX (col_datetime_key) 2366WHERE col_datetime_key < col_time_key; 2367id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23681 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 23691 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 2370Warnings: 2371Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2372Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) 2373SELECT * FROM 2374t1 ignore INDEX (col_time_key) 2375STRAIGHT_JOIN 2376t2 force INDEX (col_datetime_key) 2377WHERE col_datetime_key < col_time_key; 2378col_time_key col_datetime_key 2379-24:00:00 2012-01-29 00:00:00 238000:00:00 2012-01-29 00:00:00 238100:00:00 2012-01-30 00:00:00 238224:00:00 2012-01-29 00:00:00 238324:00:00 2012-01-30 00:00:00 238424:00:00 2012-01-31 00:00:00 238548:00:00 2012-01-29 00:00:00 238648:00:00 2012-01-30 00:00:00 238748:00:00 2012-01-31 00:00:00 238848:00:00 2012-02-01 00:00:00 2389EXPLAIN EXTENDED SELECT * FROM 2390t1 force INDEX (col_time_key) 2391STRAIGHT_JOIN 2392t2 ignore INDEX (col_datetime_key) 2393WHERE col_time_key < col_datetime_key; 2394id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23951 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 23961 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2397Warnings: 2398Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2399Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) 2400SELECT * FROM 2401t1 force INDEX (col_time_key) 2402STRAIGHT_JOIN 2403t2 ignore INDEX (col_datetime_key) 2404WHERE col_time_key < col_datetime_key; 2405col_time_key col_datetime_key 2406-24:00:00 2012-01-31 00:00:00 2407-24:00:00 2012-02-01 00:00:00 2408-24:00:00 2012-02-02 00:00:00 2409-48:00:00 2012-01-30 00:00:00 2410-48:00:00 2012-01-31 00:00:00 2411-48:00:00 2012-02-01 00:00:00 2412-48:00:00 2012-02-02 00:00:00 241300:00:00 2012-02-01 00:00:00 241400:00:00 2012-02-02 00:00:00 241524:00:00 2012-02-02 00:00:00 2416EXPLAIN EXTENDED SELECT * FROM 2417t1 force INDEX (col_time_key) 2418STRAIGHT_JOIN 2419t2 ignore INDEX (col_datetime_key) 2420WHERE col_datetime_key < col_time_key; 2421id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24221 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 24231 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2424Warnings: 2425Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2426Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` IGNORE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) 2427SELECT * FROM 2428t1 force INDEX (col_time_key) 2429STRAIGHT_JOIN 2430t2 ignore INDEX (col_datetime_key) 2431WHERE col_datetime_key < col_time_key; 2432col_time_key col_datetime_key 2433-24:00:00 2012-01-29 00:00:00 243400:00:00 2012-01-29 00:00:00 243500:00:00 2012-01-30 00:00:00 243624:00:00 2012-01-29 00:00:00 243724:00:00 2012-01-30 00:00:00 243824:00:00 2012-01-31 00:00:00 243948:00:00 2012-01-29 00:00:00 244048:00:00 2012-01-30 00:00:00 244148:00:00 2012-01-31 00:00:00 244248:00:00 2012-02-01 00:00:00 2443EXPLAIN EXTENDED SELECT * FROM 2444t1 force INDEX (col_time_key) 2445STRAIGHT_JOIN 2446t2 force INDEX (col_datetime_key) 2447WHERE col_time_key < col_datetime_key; 2448id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24491 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 24501 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 2451Warnings: 2452Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2453Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) 2454SELECT * FROM 2455t1 force INDEX (col_time_key) 2456STRAIGHT_JOIN 2457t2 force INDEX (col_datetime_key) 2458WHERE col_time_key < col_datetime_key; 2459col_time_key col_datetime_key 2460-24:00:00 2012-01-31 00:00:00 2461-24:00:00 2012-02-01 00:00:00 2462-24:00:00 2012-02-02 00:00:00 2463-48:00:00 2012-01-30 00:00:00 2464-48:00:00 2012-01-31 00:00:00 2465-48:00:00 2012-02-01 00:00:00 2466-48:00:00 2012-02-02 00:00:00 246700:00:00 2012-02-01 00:00:00 246800:00:00 2012-02-02 00:00:00 246924:00:00 2012-02-02 00:00:00 2470EXPLAIN EXTENDED SELECT * FROM 2471t1 force INDEX (col_time_key) 2472STRAIGHT_JOIN 2473t2 force INDEX (col_datetime_key) 2474WHERE col_datetime_key < col_time_key; 2475id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24761 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 100.00 Using index 24771 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 5 33.33 Range checked for each record (index map: 0x1) 2478Warnings: 2479Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2480Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) 2481SELECT * FROM 2482t1 force INDEX (col_time_key) 2483STRAIGHT_JOIN 2484t2 force INDEX (col_datetime_key) 2485WHERE col_datetime_key < col_time_key; 2486col_time_key col_datetime_key 2487-24:00:00 2012-01-29 00:00:00 248800:00:00 2012-01-29 00:00:00 248900:00:00 2012-01-30 00:00:00 249024:00:00 2012-01-29 00:00:00 249124:00:00 2012-01-30 00:00:00 249224:00:00 2012-01-31 00:00:00 249348:00:00 2012-01-29 00:00:00 249448:00:00 2012-01-30 00:00:00 249548:00:00 2012-01-31 00:00:00 249648:00:00 2012-02-01 00:00:00 2497EXPLAIN EXTENDED SELECT * FROM 2498t2 ignore INDEX (col_datetime_key) 2499STRAIGHT_JOIN 2500t1 ignore INDEX (col_time_key) 2501WHERE col_time_key < col_datetime_key; 2502id select_type table partitions type possible_keys key key_len ref rows filtered Extra 25031 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 25041 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2505Warnings: 2506Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2507Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) 2508SELECT * FROM 2509t2 ignore INDEX (col_datetime_key) 2510STRAIGHT_JOIN 2511t1 ignore INDEX (col_time_key) 2512WHERE col_time_key < col_datetime_key; 2513col_datetime_key col_time_key 25142012-01-30 00:00:00 -48:00:00 25152012-01-31 00:00:00 -24:00:00 25162012-01-31 00:00:00 -48:00:00 25172012-02-01 00:00:00 -24:00:00 25182012-02-01 00:00:00 -48:00:00 25192012-02-01 00:00:00 00:00:00 25202012-02-02 00:00:00 -24:00:00 25212012-02-02 00:00:00 -48:00:00 25222012-02-02 00:00:00 00:00:00 25232012-02-02 00:00:00 24:00:00 2524EXPLAIN EXTENDED SELECT * FROM 2525t2 ignore INDEX (col_datetime_key) 2526STRAIGHT_JOIN 2527t1 ignore INDEX (col_time_key) 2528WHERE col_datetime_key < col_time_key; 2529id select_type table partitions type possible_keys key key_len ref rows filtered Extra 25301 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 25311 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2532Warnings: 2533Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2534Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) 2535SELECT * FROM 2536t2 ignore INDEX (col_datetime_key) 2537STRAIGHT_JOIN 2538t1 ignore INDEX (col_time_key) 2539WHERE col_datetime_key < col_time_key; 2540col_datetime_key col_time_key 25412012-01-29 00:00:00 -24:00:00 25422012-01-29 00:00:00 00:00:00 25432012-01-29 00:00:00 24:00:00 25442012-01-29 00:00:00 48:00:00 25452012-01-30 00:00:00 00:00:00 25462012-01-30 00:00:00 24:00:00 25472012-01-30 00:00:00 48:00:00 25482012-01-31 00:00:00 24:00:00 25492012-01-31 00:00:00 48:00:00 25502012-02-01 00:00:00 48:00:00 2551EXPLAIN EXTENDED SELECT * FROM 2552t2 ignore INDEX (col_datetime_key) 2553STRAIGHT_JOIN 2554t1 force INDEX (col_time_key) 2555WHERE col_time_key < col_datetime_key; 2556id select_type table partitions type possible_keys key key_len ref rows filtered Extra 25571 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 25581 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2559Warnings: 2560Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2561Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2562Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) 2563SELECT * FROM 2564t2 ignore INDEX (col_datetime_key) 2565STRAIGHT_JOIN 2566t1 force INDEX (col_time_key) 2567WHERE col_time_key < col_datetime_key; 2568col_datetime_key col_time_key 25692012-01-30 00:00:00 -48:00:00 25702012-01-31 00:00:00 -24:00:00 25712012-01-31 00:00:00 -48:00:00 25722012-02-01 00:00:00 -24:00:00 25732012-02-01 00:00:00 -48:00:00 25742012-02-01 00:00:00 00:00:00 25752012-02-02 00:00:00 -24:00:00 25762012-02-02 00:00:00 -48:00:00 25772012-02-02 00:00:00 00:00:00 25782012-02-02 00:00:00 24:00:00 2579EXPLAIN EXTENDED SELECT * FROM 2580t2 ignore INDEX (col_datetime_key) 2581STRAIGHT_JOIN 2582t1 force INDEX (col_time_key) 2583WHERE col_datetime_key < col_time_key; 2584id select_type table partitions type possible_keys key key_len ref rows filtered Extra 25851 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 25861 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2587Warnings: 2588Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2589Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2590Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) 2591SELECT * FROM 2592t2 ignore INDEX (col_datetime_key) 2593STRAIGHT_JOIN 2594t1 force INDEX (col_time_key) 2595WHERE col_datetime_key < col_time_key; 2596col_datetime_key col_time_key 25972012-01-29 00:00:00 -24:00:00 25982012-01-29 00:00:00 00:00:00 25992012-01-29 00:00:00 24:00:00 26002012-01-29 00:00:00 48:00:00 26012012-01-30 00:00:00 00:00:00 26022012-01-30 00:00:00 24:00:00 26032012-01-30 00:00:00 48:00:00 26042012-01-31 00:00:00 24:00:00 26052012-01-31 00:00:00 48:00:00 26062012-02-01 00:00:00 48:00:00 2607EXPLAIN EXTENDED SELECT * FROM 2608t2 force INDEX (col_datetime_key) 2609STRAIGHT_JOIN 2610t1 ignore INDEX (col_time_key) 2611WHERE col_time_key < col_datetime_key; 2612id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26131 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 26141 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2615Warnings: 2616Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2617Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) 2618SELECT * FROM 2619t2 force INDEX (col_datetime_key) 2620STRAIGHT_JOIN 2621t1 ignore INDEX (col_time_key) 2622WHERE col_time_key < col_datetime_key; 2623col_datetime_key col_time_key 26242012-01-30 00:00:00 -48:00:00 26252012-01-31 00:00:00 -24:00:00 26262012-01-31 00:00:00 -48:00:00 26272012-02-01 00:00:00 -24:00:00 26282012-02-01 00:00:00 -48:00:00 26292012-02-01 00:00:00 00:00:00 26302012-02-02 00:00:00 -24:00:00 26312012-02-02 00:00:00 -48:00:00 26322012-02-02 00:00:00 00:00:00 26332012-02-02 00:00:00 24:00:00 2634EXPLAIN EXTENDED SELECT * FROM 2635t2 force INDEX (col_datetime_key) 2636STRAIGHT_JOIN 2637t1 ignore INDEX (col_time_key) 2638WHERE col_datetime_key < col_time_key; 2639id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26401 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 26411 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop) 2642Warnings: 2643Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2644Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) 2645SELECT * FROM 2646t2 force INDEX (col_datetime_key) 2647STRAIGHT_JOIN 2648t1 ignore INDEX (col_time_key) 2649WHERE col_datetime_key < col_time_key; 2650col_datetime_key col_time_key 26512012-01-29 00:00:00 -24:00:00 26522012-01-29 00:00:00 00:00:00 26532012-01-29 00:00:00 24:00:00 26542012-01-29 00:00:00 48:00:00 26552012-01-30 00:00:00 00:00:00 26562012-01-30 00:00:00 24:00:00 26572012-01-30 00:00:00 48:00:00 26582012-01-31 00:00:00 24:00:00 26592012-01-31 00:00:00 48:00:00 26602012-02-01 00:00:00 48:00:00 2661EXPLAIN EXTENDED SELECT * FROM 2662t2 force INDEX (col_datetime_key) 2663STRAIGHT_JOIN 2664t1 force INDEX (col_time_key) 2665WHERE col_time_key < col_datetime_key; 2666id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26671 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 26681 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2669Warnings: 2670Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2671Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2672Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`) 2673SELECT * FROM 2674t2 force INDEX (col_datetime_key) 2675STRAIGHT_JOIN 2676t1 force INDEX (col_time_key) 2677WHERE col_time_key < col_datetime_key; 2678col_datetime_key col_time_key 26792012-01-30 00:00:00 -48:00:00 26802012-01-31 00:00:00 -24:00:00 26812012-01-31 00:00:00 -48:00:00 26822012-02-01 00:00:00 -24:00:00 26832012-02-01 00:00:00 -48:00:00 26842012-02-01 00:00:00 00:00:00 26852012-02-02 00:00:00 -24:00:00 26862012-02-02 00:00:00 -48:00:00 26872012-02-02 00:00:00 00:00:00 26882012-02-02 00:00:00 24:00:00 2689EXPLAIN EXTENDED SELECT * FROM 2690t2 force INDEX (col_datetime_key) 2691STRAIGHT_JOIN 2692t1 force INDEX (col_time_key) 2693WHERE col_datetime_key < col_time_key; 2694id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26951 SIMPLE t2 NULL index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index 26961 SIMPLE t1 NULL index col_time_key col_time_key 4 NULL 5 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2697Warnings: 2698Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2699Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2700Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`) 2701SELECT * FROM 2702t2 force INDEX (col_datetime_key) 2703STRAIGHT_JOIN 2704t1 force INDEX (col_time_key) 2705WHERE col_datetime_key < col_time_key; 2706col_datetime_key col_time_key 27072012-01-29 00:00:00 -24:00:00 27082012-01-29 00:00:00 00:00:00 27092012-01-29 00:00:00 24:00:00 27102012-01-29 00:00:00 48:00:00 27112012-01-30 00:00:00 00:00:00 27122012-01-30 00:00:00 24:00:00 27132012-01-30 00:00:00 48:00:00 27142012-01-31 00:00:00 24:00:00 27152012-01-31 00:00:00 48:00:00 27162012-02-01 00:00:00 48:00:00 2717DROP TABLE t1,t2; 2718 2719# Original test of the bug report 2720 2721CREATE TABLE t1 ( 2722pk INT NOT NULL AUTO_INCREMENT, 2723col_int_nokey INT, 2724col_int_key INT NOT NULL, 2725PRIMARY KEY (pk), 2726KEY col_int_key (col_int_key) 2727) ENGINE=InnoDB; 2728INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3), 2729(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6), 2730(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9), 2731(27,3,5), (28,6,0), (29,6,3); 2732CREATE TABLE t2 ( 2733col_int_nokey INT NOT NULL, 2734col_datetime_key DATETIME NOT NULL, 2735col_varchar_key VARCHAR(1) NOT NULL, 2736KEY col_datetime_key (col_datetime_key), 2737KEY col_varchar_key (col_varchar_key) 2738) ENGINE=InnoDB; 2739INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k'); 2740CREATE TABLE t3 ( 2741col_time_key TIME, 2742KEY col_time_key (col_time_key) 2743) ENGINE=InnoDB; 2744INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'), 2745('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'), 2746('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'), 2747('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'), 2748('02:59:24'), ('00:01:58'); 2749ANALYZE TABLE t1; 2750ANALYZE TABLE t2; 2751ANALYZE TABLE t3; 2752EXPLAIN EXTENDED SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key) 2753ON t3.col_time_key > t2.col_datetime_key; 2754id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27551 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 1 100.00 NULL 27561 SIMPLE t3 NULL index col_time_key col_time_key 4 NULL 20 33.33 Using where; Using index; Using join buffer (Block Nested Loop) 2757Warnings: 2758Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2759Warning 1739 Cannot use range access on index 'col_time_key' due to type or collation conversion on field 'col_time_key' 2760Note 1003 /* select#1 */ select `test`.`t2`.`col_int_nokey` AS `col_int_nokey`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t3`.`col_time_key` AS `col_time_key` from `test`.`t2` straight_join `test`.`t3` FORCE INDEX (`col_time_key`) where (`test`.`t3`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 2761SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key) 2762ON t3.col_time_key > t2.col_datetime_key; 2763col_int_nokey col_datetime_key col_varchar_key col_time_key 27641 2001-11-04 19:07:55 k 00:00:00 27651 2001-11-04 19:07:55 k 00:00:00 27661 2001-11-04 19:07:55 k 00:00:00 27671 2001-11-04 19:07:55 k 00:01:58 27681 2001-11-04 19:07:55 k 00:21:38 27691 2001-11-04 19:07:55 k 02:59:24 27701 2001-11-04 19:07:55 k 03:53:16 27711 2001-11-04 19:07:55 k 04:08:02 27721 2001-11-04 19:07:55 k 05:03:03 27731 2001-11-04 19:07:55 k 07:05:51 27741 2001-11-04 19:07:55 k 09:16:38 27751 2001-11-04 19:07:55 k 10:14:58 27761 2001-11-04 19:07:55 k 10:50:38 27771 2001-11-04 19:07:55 k 11:14:24 27781 2001-11-04 19:07:55 k 15:37:26 27791 2001-11-04 19:07:55 k 15:57:25 27801 2001-11-04 19:07:55 k 16:25:11 27811 2001-11-04 19:07:55 k 19:22:21 27821 2001-11-04 19:07:55 k 19:47:59 27831 2001-11-04 19:07:55 k 21:22:34 2784EXPLAIN EXTENDED SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key) 2785ON t3.col_time_key > t2.col_datetime_key; 2786id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27871 SIMPLE t2 NULL ALL col_datetime_key NULL NULL NULL 1 100.00 NULL 27881 SIMPLE t3 NULL ALL NULL NULL NULL NULL 20 33.33 Using where; Using join buffer (Block Nested Loop) 2789Warnings: 2790Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 2791Note 1003 /* select#1 */ select `test`.`t2`.`col_int_nokey` AS `col_int_nokey`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t3`.`col_time_key` AS `col_time_key` from `test`.`t2` straight_join `test`.`t3` IGNORE INDEX (`col_time_key`) where (`test`.`t3`.`col_time_key` > `test`.`t2`.`col_datetime_key`) 2792SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key) 2793ON t3.col_time_key > t2.col_datetime_key; 2794col_int_nokey col_datetime_key col_varchar_key col_time_key 27951 2001-11-04 19:07:55 k 00:00:00 27961 2001-11-04 19:07:55 k 00:00:00 27971 2001-11-04 19:07:55 k 00:00:00 27981 2001-11-04 19:07:55 k 00:01:58 27991 2001-11-04 19:07:55 k 00:21:38 28001 2001-11-04 19:07:55 k 02:59:24 28011 2001-11-04 19:07:55 k 03:53:16 28021 2001-11-04 19:07:55 k 04:08:02 28031 2001-11-04 19:07:55 k 05:03:03 28041 2001-11-04 19:07:55 k 07:05:51 28051 2001-11-04 19:07:55 k 09:16:38 28061 2001-11-04 19:07:55 k 10:14:58 28071 2001-11-04 19:07:55 k 10:50:38 28081 2001-11-04 19:07:55 k 11:14:24 28091 2001-11-04 19:07:55 k 15:37:26 28101 2001-11-04 19:07:55 k 15:57:25 28111 2001-11-04 19:07:55 k 16:25:11 28121 2001-11-04 19:07:55 k 19:22:21 28131 2001-11-04 19:07:55 k 19:47:59 28141 2001-11-04 19:07:55 k 21:22:34 2815DROP TABLE t1,t2,t3; 2816SET TIMESTAMP = DEFAULT; 2817SET sql_mode = default; 2818# 2819# End of 5.6 tests 2820# 2821