diff options
Diffstat (limited to 'mysql-test/main/type_time.result')
| -rw-r--r-- | mysql-test/main/type_time.result | 2021 |
1 files changed, 2021 insertions, 0 deletions
diff --git a/mysql-test/main/type_time.result b/mysql-test/main/type_time.result new file mode 100644 index 00000000000..de0dd9a03a2 --- /dev/null +++ b/mysql-test/main/type_time.result @@ -0,0 +1,2021 @@ +drop table if exists t1; +create table t1 (t time); +insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10 1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34"); +Warnings: +Note 1265 Data truncated for column 't' at row 13 +insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32"); +select * from t1; +t +10:22:33 +12:34:56 +00:00:10 +00:12:34 +12:34:56 +123:45:59 +00:00:01 +01:23:00 +01:23:45 +00:00:10 +-241:22:33 +490:22:33 +20:33:34 +00:00:30 +00:12:30 +00:12:30 +12:30:00 +12:30:35 +36:30:31 +insert ignore into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a"); +Warnings: +Warning 1265 Data truncated for column 't' at row 1 +Warning 1265 Data truncated for column 't' at row 2 +Warning 1265 Data truncated for column 't' at row 3 +Warning 1265 Data truncated for column 't' at row 4 +Warning 1265 Data truncated for column 't' at row 6 +select * from t1; +t +10:22:33 +12:34:56 +00:00:10 +00:12:34 +12:34:56 +123:45:59 +00:00:01 +01:23:00 +01:23:45 +00:00:10 +-241:22:33 +490:22:33 +20:33:34 +00:00:30 +00:12:30 +00:12:30 +12:30:00 +12:30:35 +36:30:31 +00:00:10 +00:00:00 +00:00:00 +00:00:00 +262:22:00 +00:00:12 +drop table t1; +create table t1 (t time); +insert into t1 values ('09:00:00'),('13:00:00'),('19:38:34'), ('13:00:00'),('09:00:00'),('09:00:00'),('13:00:00'),('13:00:00'),('13:00:00'),('09:00:00'); +select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1; +t time_to_sec(t) sec_to_time(time_to_sec(t)) +09:00:00 32400 09:00:00 +13:00:00 46800 13:00:00 +19:38:34 70714 19:38:34 +13:00:00 46800 13:00:00 +09:00:00 32400 09:00:00 +09:00:00 32400 09:00:00 +13:00:00 46800 13:00:00 +13:00:00 46800 13:00:00 +13:00:00 46800 13:00:00 +09:00:00 32400 09:00:00 +select sec_to_time(time_to_sec(t)) from t1; +sec_to_time(time_to_sec(t)) +09:00:00 +13:00:00 +19:38:34 +13:00:00 +09:00:00 +09:00:00 +13:00:00 +13:00:00 +13:00:00 +09:00:00 +drop table t1; +End of 4.1 tests +select cast('100:55:50' as time) < cast('24:00:00' as time); +cast('100:55:50' as time) < cast('24:00:00' as time) +0 +select cast('100:55:50' as time) < cast('024:00:00' as time); +cast('100:55:50' as time) < cast('024:00:00' as time) +0 +select cast('300:55:50' as time) < cast('240:00:00' as time); +cast('300:55:50' as time) < cast('240:00:00' as time) +0 +select cast('100:55:50' as time) > cast('24:00:00' as time); +cast('100:55:50' as time) > cast('24:00:00' as time) +1 +select cast('100:55:50' as time) > cast('024:00:00' as time); +cast('100:55:50' as time) > cast('024:00:00' as time) +1 +select cast('300:55:50' as time) > cast('240:00:00' as time); +cast('300:55:50' as time) > cast('240:00:00' as time) +1 +create table t1 (f1 time); +insert into t1 values ('24:00:00'); +select cast('24:00:00' as time) = (select f1 from t1); +cast('24:00:00' as time) = (select f1 from t1) +1 +drop table t1; +create table t1(f1 time, f2 time); +insert into t1 values('20:00:00','150:00:00'); +select 1 from t1 where cast('100:00:00' as time) between f1 and f2; +1 +1 +drop table t1; +CREATE TABLE t1 ( +f2 date NOT NULL, +f3 int(11) unsigned NOT NULL default '0', +PRIMARY KEY (f3, f2) +); +insert into t1 values('2007-07-01', 1); +insert into t1 values('2007-07-01', 2); +insert into t1 values('2007-07-02', 1); +insert into t1 values('2007-07-02', 2); +SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2; +sum(f3) +3 +drop table t1; +# +# Bug #44792: valgrind warning when casting from time to time +# +CREATE TABLE t1 (c TIME); +INSERT INTO t1 VALUES ('0:00:00'); +SELECT CAST(c AS TIME) FROM t1; +CAST(c AS TIME) +00:00:00 +DROP TABLE t1; +End of 5.0 tests +# +# Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values +# +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +CREATE TABLE t1(f1 TIME); +INSERT INTO t1 VALUES ('23:38:57'); +SELECT TIMESTAMP(f1,'1') FROM t1; +TIMESTAMP(f1,'1') +2001-02-03 23:38:58 +DROP TABLE t1; +SET timestamp=DEFAULT; +End of 5.1 tests +create table t1 (a time); +insert t1 values (-131415); +select * from t1; +a +-13:14:15 +drop table t1; +create table t1 (f1 time , f2 varchar(5), key(f1)); +insert into t1 values ('00:20:01','a'),('00:20:03','b'); +select * from t1 force key (f1) where f1 < curdate(); +f1 f2 +select * from t1 ignore key (f1) where f1 < curdate(); +f1 f2 +select * from t1 force key (f1) where f1 > curdate(); +f1 f2 +00:20:01 a +00:20:03 b +select * from t1 ignore key (f1) where f1 > curdate(); +f1 f2 +00:20:01 a +00:20:03 b +delete from t1; +insert into t1 values ('-00:20:01','a'),('-00:20:03','b'); +select * from t1 force key (f1) where f1 < curdate(); +f1 f2 +-00:20:01 a +-00:20:03 b +select * from t1 ignore key (f1) where f1 < curdate(); +f1 f2 +-00:20:01 a +-00:20:03 b +drop table t1; +create table t1(f1 time); +insert into t1 values ('23:38:57'); +select f1, f1 = '2010-10-11 23:38:57' from t1; +f1 f1 = '2010-10-11 23:38:57' +23:38:57 0 +drop table t1; +# +# MDEV-4634 Crash in CONVERT_TZ +# +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +SELECT CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5'); +CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5') +2001-02-03 07:05:00 +SET timestamp=DEFAULT; +# +# MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))) +# +SELECT CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))); +CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))) +00:00:01 +SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00'))); +CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00'))) +768:00:01 +# +# MDEV-4859 Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1" +# +CREATE TABLE t1 (t0 TIME); +INSERT INTO t1 VALUES ('10:10:10'); +SELECT MAX(t0)+1 FROM t1; +MAX(t0)+1 +101011 +CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1; +SELECT * FROM t2; +MAX(t0)+1 +101011 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +MAX(t0)+1 int(9) YES NULL +DROP TABLE t2,t1; +CREATE TABLE t1 (t0 TIME); +INSERT INTO t1 VALUES ('10:10:10'); +SELECT MAX(t0)+1.1 FROM t1; +MAX(t0)+1.1 +101011.1 +CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1; +SELECT * FROM t2; +MAX(t0)+1.1 +101011.1 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +MAX(t0)+1.1 decimal(9,1) YES NULL +DROP TABLE t2,t1; +CREATE TABLE t1 (t0 TIME); +INSERT INTO t1 VALUES ('10:10:10'); +SELECT MAX(t0)+1e0 FROM t1; +MAX(t0)+1e0 +101011 +CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1; +SELECT * FROM t2; +MAX(t0)+1e0 +101011 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +MAX(t0)+1e0 double YES NULL +DROP TABLE t2,t1; +CREATE TABLE t1 (t1 TIME(1)); +INSERT INTO t1 VALUES ('10:10:10'); +SELECT MAX(t1)+1 FROM t1; +MAX(t1)+1 +101011.0 +CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1; +SELECT * FROM t2; +MAX(t1)+1 +101011.0 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +MAX(t1)+1 decimal(9,1) YES NULL +DROP TABLE t2,t1; +CREATE TABLE t1 (t0 DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 10:10:10'); +SELECT MAX(t0)+1 FROM t1; +MAX(t0)+1 +20010101101011 +CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1; +SELECT * FROM t2; +MAX(t0)+1 +20010101101011 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +MAX(t0)+1 bigint(16) YES NULL +DROP TABLE t2,t1; +CREATE TABLE t1 (t0 DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 10:10:10'); +SELECT MAX(t0)+1.1 FROM t1; +MAX(t0)+1.1 +20010101101011.1 +CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1; +SELECT * FROM t2; +MAX(t0)+1.1 +20010101101011.1 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +MAX(t0)+1.1 decimal(16,1) YES NULL +DROP TABLE t2,t1; +CREATE TABLE t1 (t0 DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 10:10:10'); +SELECT MAX(t0)+1e0 FROM t1; +MAX(t0)+1e0 +20010101101011 +CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1; +SELECT * FROM t2; +MAX(t0)+1e0 +20010101101011 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +MAX(t0)+1e0 double YES NULL +DROP TABLE t2,t1; +CREATE TABLE t1 (t1 DATETIME(1)); +INSERT INTO t1 VALUES ('2001-01-01 10:10:10'); +SELECT MAX(t1)+1 FROM t1; +MAX(t1)+1 +20010101101011.0 +CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1; +SELECT * FROM t2; +MAX(t1)+1 +20010101101011.0 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +MAX(t1)+1 decimal(16,1) YES NULL +DROP TABLE t2,t1; +# +# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column +# +SET sql_mode=traditional; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); +ERROR 22007: Incorrect time value: '18446744069414584320' for column `test`.`t1`.`a` at row 1 +SET sql_mode=DEFAULT; +INSERT IGNORE INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +SELECT * FROM t1; +a +838:59:59.999999 +DROP TABLE t1; +SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED); +TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED) +0 +Warnings: +Warning 1292 Truncated incorrect time value: '18446744069414584320' +# +# MDEV-6100 No warning on CAST(9000000 AS TIME) +# +SELECT CAST(9000000 AS TIME); +CAST(9000000 AS TIME) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '9000000' +# +# End of 5.3 tests +# +CREATE TABLE t1 (f1 TIME); +INSERT INTO t1 VALUES ('24:00:00'); +SELECT '24:00:00' = (SELECT f1 FROM t1); +'24:00:00' = (SELECT f1 FROM t1) +1 +SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1); +CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1) +1 +SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1); +CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1) +0 +TRUNCATE t1; +INSERT INTO t1 VALUES ('-24:00:00'); +SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1); +CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1) +0 +SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1); +CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1) +1 +SELECT '-24:00:00' = (SELECT f1 FROM t1); +'-24:00:00' = (SELECT f1 FROM t1) +1 +DROP TABLE t1; +# +# MDEV-6592 Assertion `ltime->day == 0' failed with TIMESTAMP, MAKETIME +# +CREATE TABLE t1 (d DATE, c VARCHAR(10), KEY(d)) engine=myisam; +INSERT INTO t1 VALUES ('2008-10-02','2008-10-02'), ('2008-10-02','2008-10-02'); +SELECT * FROM t1 WHERE TIMESTAMP(c,'02:04:42') AND d <=> MAKETIME(97,0,7); +d c +DROP TABLE t1; +# +# End of 5.5 tests +# +# +# Start of 10.0 tests +# +# +# MDEV-6102 Comparison between TIME and DATETIME does not use CURRENT_DATE +# +SET timestamp=UNIX_TIMESTAMP('2014-04-14 01:02:03'); +SELECT CAST(TIME'10:20:30' AS DATETIME), TIME'10:20:30'=TIMESTAMP'2014-04-14 10:20:30' AS cmp; +CAST(TIME'10:20:30' AS DATETIME) cmp +2014-04-14 10:20:30 1 +SET timestamp=DEFAULT; +# +# End of 10.0 tests +# +# +# Start of 10.1 tests +# +# +# MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x' +# +# Trailing garbage in string literals +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +00:00:00 +00:00:01 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00x'; +a +00:00:00 +Warnings: +Warning 1292 Truncated incorrect time value: '00:00:00x' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00x'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Warning 1292 Truncated incorrect time value: '00:00:00x' +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00x'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Warning 1292 Truncated incorrect time value: '00:00:00x' +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() +DROP TABLE t1; +# Trailing fractional digits in string literals +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +00:00:00 +00:00:01 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00.000000'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() +DROP TABLE t1; +# Trailing fractional digits in temporal literals +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +00:00:00 +00:00:01 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.000000' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.000000' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() +DROP TABLE t1; +# Trailing fractional digits in temporal literals, same precision +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000'; +a +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00.000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.000000' and <cache>(octet_length(TIME'00:00:00.000000')) = 30 + rand() +DROP TABLE t1; +# Leading spaces in string literals +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +00:00:00 +00:00:01 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=' 00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=' 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' 00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() +DROP TABLE t1; +# Numeric format in string literals +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +00:00:00 +00:00:01 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='000000'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='000000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() +DROP TABLE t1; +# +# MDEV-8766 Wrong result for SELECT..WHERE LENGTH(time_column)=8 AND time_column=TIMESTAMP'2001-01-01 10:20:31' +# +SET timestamp=UNIX_TIMESTAMP('2001-01-01 01:02:03'); +# TIMESTAMP literal with the same scale, ok to propagate +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31'; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31'; +a +10:20:31 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() +DROP TABLE t1; +# TIMESTAMP literal with a bigger scale and fractional second truncation +# Ok to propagate with precision truncation +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.123'; +a +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123'; +a +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() +DROP TABLE t1; +# TIMESTAMP literal with a bigger scale and no fractional second truncation +# Ok to propagate +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.000'; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.000'; +a +10:20:31 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() +DROP TABLE t1; +# TIMESTAMP literal with a smaller scale +# Ok to propagate +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.123'; +a +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123'; +a +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(octet_length(TIME'10:20:31.123000')) = 30 + rand() +DROP TABLE t1; +# TIME literal with a bigger scale and fractional second truncation +# Ok to propagate with precision truncation +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a=TIME'10:20:31.123'; +a +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123'; +a +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() +DROP TABLE t1; +# TIME literal with a bigger scale and no fractional second truncation +# Ok to propagate +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a=TIME'10:20:31.000'; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.000'; +a +10:20:31 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.000' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.000' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() +DROP TABLE t1; +# TIME literal with a smaller scale +# Ok to propagate +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a=TIME'10:20:31.123'; +a +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123'; +a +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123' and <cache>(octet_length(TIME'10:20:31.123000')) = 30 + rand() +DROP TABLE t1; +# TIME-alike string literal with a bigger scale and fractional second truncation +# Ok to propagate with precision truncation +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a='10:20:31.123'; +a +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123'; +a +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() +DROP TABLE t1; +# TIME-alike string literal with a bigger scale and no fractional second truncation +# Ok to propagate +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a='10:20:31.000'; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +10:20:31 +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.000'; +a +10:20:31 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.000'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() +DROP TABLE t1; +# TIME-alike string literal with a smaller scale +# Ok to propagate +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31'); +SELECT * FROM t1 WHERE a='10:20:31.123'; +a +SELECT * FROM t1 WHERE LENGTH(a)=8; +a +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123'; +a +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.123'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(octet_length(TIME'10:20:31.123000')) = 30 + rand() +DROP TABLE t1; +SET timestamp=DEFAULT; +SET @@old_mode=zero_date_time_cast; +# TIMESTAMP literal, old mode +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('0000-00-00 10:20:30'),('0000-00-00 10:20:31'); +INSERT INTO t1 VALUES ('0000-00-01 10:20:30'),('0000-00-01 10:20:31'); +INSERT INTO t1 VALUES ('31 10:20:30'),('32 10:20:30'),('33 10:20:30'),('34 10:20:30'); +SELECT * FROM t1; +a +10:20:30 +10:20:31 +34:20:30 +34:20:31 +754:20:30 +778:20:30 +802:20:30 +826:20:30 +# Old mode, TIMESTAMP literal, zero YYYYMMDD, Ok to propagate +SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30'; +a +10:20:30 +SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=8; +a +10:20:30 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=8; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=30+RAND(); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(octet_length(TIME'10:20:30')) = 30 + rand() +# Old mode, TIMESTAMP literal, non-zero YYYYMMDD, no propagation +SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30'; +a +34:20:30 +SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=8; +a +34:20:30 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=8; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 8 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=30+RAND(); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 30 + rand() +# Old mode, TIMESTAMP-alike string literal, zero YYYYMMDD, Ok to propagate +SELECT * FROM t1 WHERE a='0000-00-00 10:20:30'; +a +10:20:30 +SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=8; +a +10:20:30 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=8; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=30+RAND(); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(octet_length(TIME'10:20:30')) = 30 + rand() +# Old mode, TIMESTAMP-alike literal, non-zero YYYYMMDD, no propagation +SELECT * FROM t1 WHERE a='0000-00-01 10:20:30'; +a +34:20:30 +SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8; +a +34:20:30 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 8 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=30+RAND(); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 30 + rand() +DROP TABLE t1; +SET @@old_mode=DEFAULT; +# +# MDEV-8706 Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00' +# +SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30'); +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00'; +a +00:00:00 +SELECT * FROM t1 WHERE a='00:00:00'; +a +00:00:00 +SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00'; +a +00:00:00 +SELECT * FROM t1 WHERE a>=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a>=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' +DROP TABLE t1; +SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30'); +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE DATE(a)<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE DATE(a)<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and timestamp('2015-08-08',TIME'00:00:00' + rand()) <= TIMESTAMP'2015-08-30 00:00:00.1' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00.1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.100000' and timestamp('2015-08-08',TIME'00:00:00' + rand()) <= TIMESTAMP'2015-08-30 00:00:00.1' +DROP TABLE t1; +SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30'); +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); +SELECT * FROM t1 WHERE DATE(a)<=DATE'2015-08-30' AND a='00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE DATE(a)<=DATE'2015-08-30' AND a='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=DATE'2015-08-30' AND a='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and timestamp('2015-08-08',TIME'00:00:00' + rand()) <= DATE'2015-08-30' +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=DATE'2015-08-30' AND a='00:00:00.1'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.100000' and timestamp('2015-08-08',TIME'00:00:00' + rand()) <= DATE'2015-08-30' +DROP TABLE t1; +SET timestamp=DEFAULT; +# +# MDEV-8795 Equal expression propagation does not work for temporal literals +# +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:01'),('00:00:02'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:01' AND COALESCE(a)>=TIME'00:00:01'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = TIME'00:00:01' +DROP TABLE t1; +# +# MDEV-8793 Wrong result set for SELECT ... WHERE COALESCE(time_column)=TIME('00:00:00') AND COALESCE(time_column)=DATE('2015-09-11') +# +SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20'); +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES('10:20:30'),('00:00:00'); +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00'); +a +00:00:00 +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +# TIME cast + DATE cast +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:00' +# TIME cast + DATE literal +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:00' +# TIME literal + DATE cast +SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = TIME'00:00:00' +# TIME literal + DATE literal +SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = TIME'00:00:00' +# TIME-alike string literal + DATE cast +SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:00' and coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00' +# TIME-alike string literal + DATE literal +SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '00:00:00' and coalesce(`test`.`t1`.`a`) = DATE'2015-09-11' +# TIME-alike integer literal + DATE cast +SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 0 and coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00' +# TIME-alike integer literal + DATE literal +SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = 0 and coalesce(`test`.`t1`.`a`) = DATE'2015-09-11' +# DATE cast + TIME cast +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:00:00'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:00:00'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00' +# DATE cast + TIME literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00' +# DATE cast + TIME-alike string literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00' and coalesce(`test`.`t1`.`a`) = '00:00:00' +# DATE cast + TIME-alike integer literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = '2015-09-11 00:00:00' and coalesce(`test`.`t1`.`a`) = 0 +# DATE literal + TIME cast +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00'); +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = DATE'2015-09-11' +# DATE literal + TIME literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME'00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME'00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = DATE'2015-09-11' +# DATE literal + TIME-alike string literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)='00:00:00'; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)='00:00:00'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = DATE'2015-09-11' and coalesce(`test`.`t1`.`a`) = '00:00:00' +# DATE literal + TIME-alike integer literal +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=0; +a +00:00:00 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = DATE'2015-09-11' and coalesce(`test`.`t1`.`a`) = 0 +DROP TABLE t1; +SET timestamp=DEFAULT; +# +# MDEV-8814 Wrong result for WHERE datetime_column > TIME('00:00:00') +# +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49'); +SELECT * FROM t1 WHERE a>TIME'00:00:00'; +a +SELECT * FROM t1 WHERE a>TIME('00:00:00'); +a +DROP TABLE t1; +# +# MDEV-8660 TIME(int_zerofill_column) returns a wrong result +# +CREATE TABLE t1 (a BIGINT(15), b BIGINT(15) ZEROFILL); +INSERT INTO t1 VALUES (9,9); +SELECT TIME(a),TIME(b) FROM t1; +TIME(a) TIME(b) +00:00:09 00:00:09 +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (-9223372036854775808); +SELECT CAST(a AS TIME), CAST(-9223372036854775808 AS TIME) FROM t1; +CAST(a AS TIME) CAST(-9223372036854775808 AS TIME) +-838:59:59 -838:59:59 +Warnings: +Warning 1292 Incorrect time value: '-9223372036854775808' for column `test`.`t1`.`a` at row 1 +Warning 1292 Truncated incorrect time value: '-9223372036854775808' +DROP TABLE t1; +CREATE TABLE t1 (a INT, b DECIMAL, c DOUBLE); +INSERT INTO t1 VALUES (-9000000,-9000000,-9000000); +INSERT INTO t1 VALUES (-1,-1,-1); +INSERT INTO t1 VALUES (0,0,0),(1,1,1),(9,9,9); +INSERT INTO t1 VALUES (9000000,9000000,9000000); +SELECT a, TIME(a),TIME(b),TIME(c) FROM t1 ORDER BY a; +a TIME(a) TIME(b) TIME(c) +-9000000 -838:59:59 -838:59:59 -838:59:59.999999 +-1 -00:00:01 -00:00:01 -00:00:01.000000 +0 00:00:00 00:00:00 00:00:00.000000 +1 00:00:01 00:00:01 00:00:01.000000 +9 00:00:09 00:00:09 00:00:09.000000 +9000000 838:59:59 838:59:59 838:59:59.999999 +Warnings: +Warning 1292 Incorrect time value: '-9000000' for column `test`.`t1`.`a` at row 1 +Warning 1292 Incorrect time value: '-9000000' for column `test`.`t1`.`b` at row 1 +Warning 1292 Incorrect time value: '-9000000' for column `test`.`t1`.`c` at row 1 +Warning 1292 Incorrect time value: '9000000' for column `test`.`t1`.`a` at row 6 +Warning 1292 Incorrect time value: '9000000' for column `test`.`t1`.`b` at row 6 +Warning 1292 Incorrect time value: '9000000' for column `test`.`t1`.`c` at row 6 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b DECIMAL, c DOUBLE); +INSERT INTO t1 VALUES (0,0,0),(1,1,1),(9,9,9); +INSERT INTO t1 VALUES (9000000,9000000,9000000); +SELECT a, TIME(a),TIME(b),TIME(c) FROM t1 ORDER BY a; +a TIME(a) TIME(b) TIME(c) +0 00:00:00 00:00:00 00:00:00.000000 +1 00:00:01 00:00:01 00:00:01.000000 +9 00:00:09 00:00:09 00:00:09.000000 +9000000 838:59:59 838:59:59 838:59:59.999999 +Warnings: +Warning 1292 Incorrect time value: '9000000' for column `test`.`t1`.`a` at row 4 +Warning 1292 Incorrect time value: '9000000' for column `test`.`t1`.`b` at row 4 +Warning 1292 Incorrect time value: '9000000' for column `test`.`t1`.`c` at row 4 +DROP TABLE t1; +# +# MDEV-8862 Wrong field type for MAX(COALESCE(datetime_column)) +# +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:10:10'),('10:20:30'); +SELECT MAX(a), MAX(COALESCE(a)) FROM t1; +MAX(a) MAX(COALESCE(a)) +10:20:30 10:20:30 +DROP TABLE t1; +# +# MDEV-15321: different results when using value of optimizer_use_condition_selectivity=4 and =1 +# +SET @save_old_mode=@@old_mode; +SET @@old_mode=zero_date_time_cast; +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('0000-00-00 10:20:30'),('0000-00-00 10:20:31'); +INSERT INTO t1 VALUES ('0000-00-01 10:20:30'),('0000-00-01 10:20:31'); +INSERT INTO t1 VALUES ('31 10:20:30'),('32 10:20:30'),('33 10:20:30'),('34 10:20:30'); +SET @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +SET @@optimizer_use_condition_selectivity=1; +SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8; +a +34:20:30 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 8 +SET @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8; +a +34:20:30 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 8 +drop table t1; +SET @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set @@old_mode= @save_old_mode; +# +# MDEV-21319 COUNT(*) returns 1, actual SELECT returns no result in 10.3.21, but 1 result in 10.1.41 +# +CREATE OR REPLACE TABLE t1 +( +id INT NOT NULL PRIMARY KEY, +id2 INT, +k TINYINT, +j INT, +t TIME, +KEY k1 (id2,k,j,t) +); +INSERT INTO t1 VALUES +(53,54,1,0,'10:09:59'), +(54,54,1,0,'16:28:41'), +(55,54,1,0,'16:29:10'), +(56,54,1,0,'16:29:43'), +(57,54,1,0,'16:30:16'), +(58,54,1,0,'16:30:49'), +(59,54,1,0,'16:31:23'), +(60,54,1,0,'16:31:55'), +(61,54,1,0,'16:32:28'), +(62,54,1,0,'16:33:01'), +(63,54,1,0,'16:33:34'), +(64,54,1,0,'16:34:07'), +(65,54,1,0,'16:34:40'), +(66,54,1,0,'16:35:13'), +(67,54,1,0,'16:35:46'), +(68,54,1,0,'16:36:19'); +SELECT t FROM t1 GROUP BY t HAVING t=MAX(t); +t +10:09:59 +16:28:41 +16:29:10 +16:29:43 +16:30:16 +16:30:49 +16:31:23 +16:31:55 +16:32:28 +16:33:01 +16:33:34 +16:34:07 +16:34:40 +16:35:13 +16:35:46 +16:36:19 +SELECT t FROM t1 WHERE id2=54 AND j=0 AND k=1 GROUP BY t HAVING t=MAX(t); +t +10:09:59 +16:28:41 +16:29:10 +16:29:43 +16:30:16 +16:30:49 +16:31:23 +16:31:55 +16:32:28 +16:33:01 +16:33:34 +16:34:07 +16:34:40 +16:35:13 +16:35:46 +16:36:19 +DROP TABLE t1; +# +# End of 10.1 tests +# +# +# Start of 10.2 tests +# +# +# MDEV-9393 Split Copy_field::get_copy_func() into virtual methods in Field +# +CREATE TABLE t1 (a YEAR, b TIME, c YEAR); +CREATE TABLE t2 (a YEAR); +INSERT INTO t2 VALUES (0),(1999),(2000),(2030),(2050),(2070); +INSERT INTO t1 (a,b,c) SELECT a,a,a FROM t2; +Warnings: +Warning 1265 Data truncated for column 'b' at row 2 +Warning 1265 Data truncated for column 'b' at row 6 +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +ALTER TABLE t1 MODIFY c TIME; +Warnings: +Warning 1265 Data truncated for column 'c' at row 2 +Warning 1265 Data truncated for column 'c' at row 6 +SELECT * FROM t1; +a b c +0000 00:00:00 00:00:00 +1999 00:00:00 00:00:00 +2000 00:20:00 00:20:00 +2030 00:20:30 00:20:30 +2050 00:20:50 00:20:50 +2070 00:00:00 00:00:00 +DROP TABLE t1,t2; +# +# MDEV-10817 CAST(MAX(DATE'2001-01-01') AS TIME) returns a wrong result +# +SELECT CAST(DATE'2001-01-01' AS TIME); +CAST(DATE'2001-01-01' AS TIME) +00:00:00 +SELECT CAST(MAX(DATE'2001-01-01') AS TIME); +CAST(MAX(DATE'2001-01-01') AS TIME) +00:00:00 +CREATE FUNCTION f1() RETURNS DATE RETURN DATE'2001-01-01'; +SELECT CAST(f1() AS TIME); +CAST(f1() AS TIME) +00:00:00 +DROP FUNCTION f1; +# +# End of 10.2 tests +# +# +# Start of 10.3 tests +# +# +# MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result +# +SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00'); +SELECT TIME'10:20:30' IN (102030,TIME'10:20:31'); +TIME'10:20:30' IN (102030,TIME'10:20:31') +1 +SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32'); +TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32') +1 +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('10:20:30'),('10:20:31'),('10:20:32'); +SELECT a FROM t1 WHERE a IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32') ORDER BY a; +a +10:20:30 +10:20:31 +10:20:32 +DROP TABLE t1; +SET timestamp=DEFAULT; +# +# MDEV-15176 Storing DATETIME-alike VARCHAR data into TIME produces wrong results +# +SET sql_mode=''; +CREATE OR REPLACE TABLE t0 (d VARCHAR(64)); +INSERT INTO t0 VALUES ('0000-00-00 10:20:30'); +INSERT INTO t0 VALUES ('0000-00-01 10:20:30'); +INSERT INTO t0 VALUES ('0000-01-00 10:20:30'); +INSERT INTO t0 VALUES ('0000-01-01 10:20:30'); +INSERT INTO t0 VALUES ('0001-00-00 10:20:30'); +INSERT INTO t0 VALUES ('0001-00-01 10:20:30'); +INSERT INTO t0 VALUES ('0001-01-00 10:20:30'); +INSERT INTO t0 VALUES ('0001-01-01 10:20:30'); +SET @@global.mysql56_temporal_format=false; +CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1)); +INSERT INTO t1 SELECT d,d,d FROM t0; +Warnings: +Note 1265 Data truncated for column 't0' at row 3 +Note 1265 Data truncated for column 't1' at row 3 +Note 1265 Data truncated for column 't0' at row 4 +Note 1265 Data truncated for column 't1' at row 4 +Note 1265 Data truncated for column 't0' at row 5 +Note 1265 Data truncated for column 't1' at row 5 +Note 1265 Data truncated for column 't0' at row 6 +Note 1265 Data truncated for column 't1' at row 6 +Note 1265 Data truncated for column 't0' at row 7 +Note 1265 Data truncated for column 't1' at row 7 +Note 1265 Data truncated for column 't0' at row 8 +Note 1265 Data truncated for column 't1' at row 8 +SELECT * FROM t1 ORDER BY d; +d t0 t1 +0000-00-00 10:20:30 10:20:30 10:20:30.0 +0000-00-01 10:20:30 34:20:30 34:20:30.0 +0000-01-00 10:20:30 10:20:30 10:20:30.0 +0000-01-01 10:20:30 10:20:30 10:20:30.0 +0001-00-00 10:20:30 10:20:30 10:20:30.0 +0001-00-01 10:20:30 10:20:30 10:20:30.0 +0001-01-00 10:20:30 10:20:30 10:20:30.0 +0001-01-01 10:20:30 10:20:30 10:20:30.0 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1)); +INSERT INTO t1 SELECT CONCAT(d,'x'),CONCAT(d,'x'),CONCAT(d,'x') FROM t0; +Warnings: +Warning 1265 Data truncated for column 't0' at row 1 +Warning 1265 Data truncated for column 't1' at row 1 +Warning 1265 Data truncated for column 't0' at row 2 +Warning 1265 Data truncated for column 't1' at row 2 +Warning 1265 Data truncated for column 't0' at row 3 +Warning 1265 Data truncated for column 't1' at row 3 +Warning 1265 Data truncated for column 't0' at row 4 +Warning 1265 Data truncated for column 't1' at row 4 +Warning 1265 Data truncated for column 't0' at row 5 +Warning 1265 Data truncated for column 't1' at row 5 +Warning 1265 Data truncated for column 't0' at row 6 +Warning 1265 Data truncated for column 't1' at row 6 +Warning 1265 Data truncated for column 't0' at row 7 +Warning 1265 Data truncated for column 't1' at row 7 +Warning 1265 Data truncated for column 't0' at row 8 +Warning 1265 Data truncated for column 't1' at row 8 +SELECT * FROM t1; +d t0 t1 +0000-00-00 10:20:30x 10:20:30 10:20:30.0 +0000-00-01 10:20:30x 34:20:30 34:20:30.0 +0000-01-00 10:20:30x 10:20:30 10:20:30.0 +0000-01-01 10:20:30x 10:20:30 10:20:30.0 +0001-00-00 10:20:30x 10:20:30 10:20:30.0 +0001-00-01 10:20:30x 10:20:30 10:20:30.0 +0001-01-00 10:20:30x 10:20:30 10:20:30.0 +0001-01-01 10:20:30x 10:20:30 10:20:30.0 +DROP TABLE t1; +SET @@global.mysql56_temporal_format=true; +CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1)); +INSERT INTO t1 SELECT d,d,d FROM t0; +Warnings: +Note 1265 Data truncated for column 't0' at row 3 +Note 1265 Data truncated for column 't1' at row 3 +Note 1265 Data truncated for column 't0' at row 4 +Note 1265 Data truncated for column 't1' at row 4 +Note 1265 Data truncated for column 't0' at row 5 +Note 1265 Data truncated for column 't1' at row 5 +Note 1265 Data truncated for column 't0' at row 6 +Note 1265 Data truncated for column 't1' at row 6 +Note 1265 Data truncated for column 't0' at row 7 +Note 1265 Data truncated for column 't1' at row 7 +Note 1265 Data truncated for column 't0' at row 8 +Note 1265 Data truncated for column 't1' at row 8 +SELECT * FROM t1; +d t0 t1 +0000-00-00 10:20:30 10:20:30 10:20:30.0 +0000-00-01 10:20:30 34:20:30 34:20:30.0 +0000-01-00 10:20:30 10:20:30 10:20:30.0 +0000-01-01 10:20:30 10:20:30 10:20:30.0 +0001-00-00 10:20:30 10:20:30 10:20:30.0 +0001-00-01 10:20:30 10:20:30 10:20:30.0 +0001-01-00 10:20:30 10:20:30 10:20:30.0 +0001-01-01 10:20:30 10:20:30 10:20:30.0 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1)); +INSERT INTO t1 SELECT CONCAT(d,'x'),CONCAT(d,'x'),CONCAT(d,'x') FROM t0; +Warnings: +Warning 1265 Data truncated for column 't0' at row 1 +Warning 1265 Data truncated for column 't1' at row 1 +Warning 1265 Data truncated for column 't0' at row 2 +Warning 1265 Data truncated for column 't1' at row 2 +Warning 1265 Data truncated for column 't0' at row 3 +Warning 1265 Data truncated for column 't1' at row 3 +Warning 1265 Data truncated for column 't0' at row 4 +Warning 1265 Data truncated for column 't1' at row 4 +Warning 1265 Data truncated for column 't0' at row 5 +Warning 1265 Data truncated for column 't1' at row 5 +Warning 1265 Data truncated for column 't0' at row 6 +Warning 1265 Data truncated for column 't1' at row 6 +Warning 1265 Data truncated for column 't0' at row 7 +Warning 1265 Data truncated for column 't1' at row 7 +Warning 1265 Data truncated for column 't0' at row 8 +Warning 1265 Data truncated for column 't1' at row 8 +SELECT * FROM t1 ORDER BY d; +d t0 t1 +0000-00-00 10:20:30x 10:20:30 10:20:30.0 +0000-00-01 10:20:30x 34:20:30 34:20:30.0 +0000-01-00 10:20:30x 10:20:30 10:20:30.0 +0000-01-01 10:20:30x 10:20:30 10:20:30.0 +0001-00-00 10:20:30x 10:20:30 10:20:30.0 +0001-00-01 10:20:30x 10:20:30 10:20:30.0 +0001-01-00 10:20:30x 10:20:30 10:20:30.0 +0001-01-01 10:20:30x 10:20:30 10:20:30.0 +DROP TABLE t1; +DROP TABLE t0; +SET sql_mode=DEFAULT; +# +# MDEV-15287 Bad result for LEAST/GREATEST(datetime_alike_string, time) +# +SELECT +GREATEST('2010-01-01 10:10:10',TIME('-20:20:20')) AS gt_minus20_implicit, +GREATEST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('-20:20:20')) AS gt_minis20_explicit, +GREATEST('2010-01-01 10:10:10',TIME('20:20:20')) AS gt_plus20_implicit, +GREATEST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('20:20:20')) AS gt_plus20_explicit; +gt_minus20_implicit 10:10:10.000000 +gt_minis20_explicit 10:10:10.000000 +gt_plus20_implicit 20:20:20.000000 +gt_plus20_explicit 20:20:20.000000 +SELECT +HOUR(GREATEST('2010-01-01 10:10:10',TIME('-20:20:20'))) AS gt_minus20_implicit, +HOUR(GREATEST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('-20:20:20'))) AS gt_minis20_explicit, +HOUR(GREATEST('2010-01-01 10:10:10',TIME('20:20:20'))) AS gt_plus20_implicit, +HOUR(GREATEST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('20:20:20'))) AS gt_plus20_explicit; +gt_minus20_implicit 10 +gt_minis20_explicit 10 +gt_plus20_implicit 20 +gt_plus20_explicit 20 +SELECT +LEAST('2010-01-01 10:10:10',TIME('-20:20:20')) AS lt_minus20_implicit, +LEAST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('-20:20:20')) AS lt_minus20_explicit, +LEAST('2010-01-01 10:10:10',TIME('20:20:20')) AS lt_plus20_implicit, +LEAST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('20:20:20')) AS lt_plus20_explicit; +lt_minus20_implicit -20:20:20.000000 +lt_minus20_explicit -20:20:20.000000 +lt_plus20_implicit 10:10:10.000000 +lt_plus20_explicit 10:10:10.000000 +SELECT +HOUR(LEAST('2010-01-01 10:10:10',TIME('-20:20:20'))) AS lt_minus20_implicit, +HOUR(LEAST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('-20:20:20'))) AS lt_minus20_explicit, +HOUR(LEAST('2010-01-01 10:10:10',TIME('20:20:20'))) AS lt_plus20_implicit, +HOUR(LEAST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('20:20:20'))) AS lt_plus20_explicit; +lt_minus20_implicit 20 +lt_minus20_explicit 20 +lt_plus20_implicit 10 +lt_plus20_explicit 10 +SELECT +GREATEST('2010-01-01 10:10:10',TIME('-200:20:20')) AS gt_minus200_implicit, +GREATEST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('-200:20:20')) AS gt_minus200_explictit, +GREATEST('2010-01-01 10:10:10',TIME('200:20:20')) AS gt_plus200_implicit, +GREATEST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('200:20:20')) AS gt_plus200_explicit; +gt_minus200_implicit 10:10:10.000000 +gt_minus200_explictit 10:10:10.000000 +gt_plus200_implicit 200:20:20.000000 +gt_plus200_explicit 200:20:20.000000 +SELECT +HOUR(GREATEST('2010-01-01 10:10:10',TIME('-200:20:20'))) AS gt_minus200_implicit, +HOUR(GREATEST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('-200:20:20'))) AS gt_minus200_explictit, +HOUR(GREATEST('2010-01-01 10:10:10',TIME('200:20:20'))) AS gt_plus200_implicit, +HOUR(GREATEST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('200:20:20'))) AS gt_plus200_explicit; +gt_minus200_implicit 10 +gt_minus200_explictit 10 +gt_plus200_implicit 200 +gt_plus200_explicit 200 +SELECT +LEAST('2010-01-01 10:10:10',TIME('-200:20:20')) AS lt_minus200_implicit, +LEAST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('-200:20:20')) AS lt_minus200_explictit, +LEAST('2010-01-01 10:10:10',TIME('200:20:20')) AS lt_plus200_implicit, +LEAST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('200:20:20')) AS lt_plus200_explicit; +lt_minus200_implicit -200:20:20.000000 +lt_minus200_explictit -200:20:20.000000 +lt_plus200_implicit 10:10:10.000000 +lt_plus200_explicit 10:10:10.000000 +SELECT +HOUR(LEAST('2010-01-01 10:10:10',TIME('-200:20:20'))) AS lt_minus200_implicit, +HOUR(LEAST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('-200:20:20'))) AS lt_minus200_explictit, +HOUR(LEAST('2010-01-01 10:10:10',TIME('200:20:20'))) AS lt_plus200_implicit, +HOUR(LEAST(CAST('2010-01-01 10:10:10' AS TIME(6)),TIME('200:20:20'))) AS lt_plus200_explicit; +lt_minus200_implicit 200 +lt_minus200_explictit 200 +lt_plus200_implicit 10 +lt_plus200_explicit 10 +# +# MDEV-15293 CAST(AS TIME) returns bad results for LAST_VALUE(),NAME_CONST(),SP variable +# +SELECT CAST(DATE'2001-01-01' AS TIME); +CAST(DATE'2001-01-01' AS TIME) +00:00:00 +SELECT CAST(LAST_VALUE(DATE'2001-01-01') AS TIME); +CAST(LAST_VALUE(DATE'2001-01-01') AS TIME) +00:00:00 +SELECT CAST(NAME_CONST('name',DATE'2001-01-01') AS TIME); +CAST(NAME_CONST('name',DATE'2001-01-01') AS TIME) +00:00:00 +BEGIN NOT ATOMIC +DECLARE a DATE DEFAULT '2001-01-01'; +SELECT CAST(a AS TIME); +END; +$$ +CAST(a AS TIME) +00:00:00 +CREATE OR REPLACE TABLE t1 (dt DATE,country VARCHAR(10), amount INT); +INSERT INTO t1 VALUES ('2000-01-01','DE',102); +SELECT +dt, country, amount, +FIRST_VALUE(dt) OVER () AS first, +MINUTE(FIRST_VALUE(dt) OVER ()) AS m_first, +LAST_VALUE(dt) OVER () AS last, +MINUTE(LAST_VALUE(dt) OVER ()) AS m_last +FROM t1 +ORDER BY country, dt; +dt country amount first m_first last m_last +2000-01-01 DE 102 2000-01-01 0 2000-01-01 0 +SELECT +dt, country, amount, +FIRST_VALUE(dt) OVER () AS first, +CAST(FIRST_VALUE(dt) OVER () AS TIME) AS t_first, +LAST_VALUE(dt) OVER () AS last, +CAST(LAST_VALUE(dt) OVER () AS TIME) AS t_last +FROM t1 +ORDER BY country, dt; +dt country amount first t_first last t_last +2000-01-01 DE 102 2000-01-01 00:00:00 2000-01-01 00:00:00 +DROP TABLE t1; +# +# MDEV-15310 Range optimizer does not work well for "WHERE temporal_column NOT IN (const_list)" +# +# +# TIME(0), positive within 24 hour +# +CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:01', 'no'); +INSERT INTO t1 VALUES ('23:00:02', 'no'); +INSERT INTO t1 VALUES ('23:00:03', 'yes'); +INSERT INTO t1 VALUES ('23:00:04', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL 5 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02'); +a filler +23:00:03 yes +23:00:04 yes +DROP TABLE t1; +# +# TIME(0), negative +# +CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:01', 'no'); +INSERT INTO t1 VALUES ('-23:00:02', 'no'); +INSERT INTO t1 VALUES ('-23:00:03', 'yes'); +INSERT INTO t1 VALUES ('-23:00:04', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL 4 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02'); +a filler +-23:00:04 yes +-23:00:03 yes +DROP TABLE t1; +# +# TIME(0), positive ouside 24 hours +# +CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:01', 'no'); +INSERT INTO t1 VALUES ('24:00:02', 'no'); +INSERT INTO t1 VALUES ('24:00:03', 'yes'); +INSERT INTO t1 VALUES ('24:00:04', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL 5 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02'); +a filler +24:00:03 yes +24:00:04 yes +DROP TABLE t1; +# +# TIME(0), negative, ouside 24 hours +# +CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:01', 'no'); +INSERT INTO t1 VALUES ('-24:00:02', 'no'); +INSERT INTO t1 VALUES ('-24:00:03', 'yes'); +INSERT INTO t1 VALUES ('-24:00:04', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-24:00:01','-24:00:02'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL 4 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('-24:00:01','-24:00:02'); +a filler +-24:00:04 yes +-24:00:03 yes +DROP TABLE t1; +# +# TIME(0), positive, huge +# +CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:01', 'no'); +INSERT INTO t1 VALUES ('838:00:02', 'no'); +INSERT INTO t1 VALUES ('838:00:03', 'yes'); +INSERT INTO t1 VALUES ('838:00:04', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('838:00:01','838:00:02'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL 5 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('838:00:01','838:00:02'); +a filler +838:00:03 yes +838:00:04 yes +DROP TABLE t1; +# +# TIME(0), negative, huge +# +CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:01', 'no'); +INSERT INTO t1 VALUES ('-838:00:02', 'no'); +INSERT INTO t1 VALUES ('-838:00:03', 'yes'); +INSERT INTO t1 VALUES ('-838:00:04', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-838:00:01','-838:00:02'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL 4 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('-838:00:01','-838:00:02'); +a filler +-838:00:04 yes +-838:00:03 yes +DROP TABLE t1; +# +# TIME(1), positive within 24 hours +# +CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('23:00:02.1', 'no'); +INSERT INTO t1 VALUES ('23:00:03.1', 'yes'); +INSERT INTO t1 VALUES ('23:00:04.1', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 5 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1'); +a filler +23:00:03.1 yes +23:00:04.1 yes +DROP TABLE t1; +# +# TIME(1), negative within 24 hours +# +CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:02.1', 'no'); +INSERT INTO t1 VALUES ('-23:00:03.1', 'yes'); +INSERT INTO t1 VALUES ('-23:00:04.1', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01.1','-23:00:02.1'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 4 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('-23:00:01.1','-23:00:02.1'); +a filler +-23:00:04.1 yes +-23:00:03.1 yes +DROP TABLE t1; +# +# TIME(1), positive, huge +# +CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('838:00:02.1', 'no'); +INSERT INTO t1 VALUES ('838:00:03.1', 'yes'); +INSERT INTO t1 VALUES ('838:00:04.1', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('838:00:01.1','838:00:02.1'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 5 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('838:00:01.1','838:00:02.1'); +a filler +838:00:03.1 yes +838:00:04.1 yes +DROP TABLE t1; +# +# TIME(1), negative, huge +# +CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:01.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:02.1', 'no'); +INSERT INTO t1 VALUES ('-838:00:03.1', 'yes'); +INSERT INTO t1 VALUES ('-838:00:04.1', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-838:00:01.1','-838:00:02.1'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 4 Using index condition +SELECT * FROM t1 WHERE a NOT IN ('-838:00:01.1','-838:00:02.1'); +a filler +-838:00:04.1 yes +-838:00:03.1 yes +DROP TABLE t1; |