1SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', ''); 2SET @default_sql_mode=@@sql_mode; 3# 4# TIME: SET 5# 6CREATE TABLE t1 (a TIME(3), b TIME(4)); 7INSERT INTO t1 VALUES(NULL,'00:00:00.9999'); 8UPDATE t1 SET a=b; 9SELECT a FROM t1; 10a 1100:00:01.000 12DROP TABLE t1; 13CREATE TABLE t1 (a TIME(3), b VARCHAR(64)); 14INSERT INTO t1 VALUES(NULL,'00:00:00.9999'); 15INSERT INTO t1 VALUES(NULL,'00:00:00.9999999'); 16UPDATE t1 SET a=b; 17Warnings: 18Note 1265 Data truncated for column 'a' at row 2 19SELECT a FROM t1; 20a 2100:00:01.000 2200:00:01.000 23DROP TABLE t1; 24CREATE TABLE t1 (a TIME(3), b DECIMAL(38,10)); 25INSERT INTO t1 VALUES(NULL,0.9999); 26INSERT INTO t1 VALUES(NULL,0.9999999); 27UPDATE t1 SET a=b; 28SELECT a FROM t1; 29a 3000:00:01.000 3100:00:01.000 32DROP TABLE t1; 33CREATE TABLE t1 (a TIME(3), b DOUBLE); 34INSERT INTO t1 VALUES(NULL,0.9999); 35INSERT INTO t1 VALUES(NULL,0.9999999); 36UPDATE t1 SET a=b; 37SELECT a FROM t1; 38a 3900:00:01.000 4000:00:01.000 41DROP TABLE t1; 42CREATE TABLE t1 (a TIME(6), b VARCHAR(64)); 43INSERT INTO t1 VALUES(NULL,'00:00:00.9999999'); 44UPDATE t1 SET a=b; 45Warnings: 46Note 1265 Data truncated for column 'a' at row 1 47SELECT a FROM t1; 48a 4900:00:01.000000 50DROP TABLE t1; 51CREATE TABLE t1 (a TIME(6), b DECIMAL(38,10)); 52INSERT INTO t1 VALUES(NULL,0.9999999); 53UPDATE t1 SET a=b; 54SELECT a FROM t1; 55a 5600:00:01.000000 57DROP TABLE t1; 58CREATE TABLE t1 (a TIME(6), b DOUBLE); 59INSERT INTO t1 VALUES(NULL,0.9999999); 60UPDATE t1 SET a=b; 61SELECT a FROM t1; 62a 6300:00:01.000000 64DROP TABLE t1; 65# 66# TIME: ALTER 67# 68CREATE TABLE t1 (a TIME(4)); 69INSERT INTO t1 VALUES('00:00:00.9999'); 70ALTER TABLE t1 MODIFY a TIME(3); 71SELECT a FROM t1; 72a 7300:00:01.000 74DROP TABLE t1; 75CREATE TABLE t1 (a VARCHAR(64)); 76INSERT INTO t1 VALUES('00:00:00.9999'); 77INSERT INTO t1 VALUES('00:00:00.9999999'); 78ALTER TABLE t1 MODIFY a TIME(3); 79Warnings: 80Note 1292 Truncated incorrect time value: '00:00:00.9999999' 81SELECT a FROM t1; 82a 8300:00:01.000 8400:00:01.000 85DROP TABLE t1; 86CREATE TABLE t1 (a DECIMAL(38,10)); 87INSERT INTO t1 VALUES(0.9999); 88INSERT INTO t1 VALUES(0.9999999); 89ALTER TABLE t1 MODIFY a TIME(3); 90SELECT a FROM t1; 91a 9200:00:01.000 9300:00:01.000 94DROP TABLE t1; 95CREATE TABLE t1 (a DOUBLE); 96INSERT INTO t1 VALUES(0.9999); 97INSERT INTO t1 VALUES(0.9999999); 98ALTER TABLE t1 MODIFY a TIME(3); 99Warnings: 100Note 1265 Data truncated for column 'a' at row 2 101SELECT a FROM t1; 102a 10300:00:01.000 10400:00:01.000 105DROP TABLE t1; 106# 107# TIME: CAST 108# 109CREATE TABLE t1 (a TIME(4)); 110INSERT INTO t1 VALUES('00:00:00.9999'); 111SELECT a, CAST(a AS TIME(3)) FROM t1; 112a CAST(a AS TIME(3)) 11300:00:00.9999 00:00:01.000 114DROP TABLE t1; 115CREATE TABLE t1 (a VARCHAR(64)); 116INSERT INTO t1 VALUES('00:00:00.9999'); 117INSERT INTO t1 VALUES('00:00:00.9999999'); 118SELECT a, CAST(a AS TIME(3)) FROM t1; 119a CAST(a AS TIME(3)) 12000:00:00.9999 00:00:01.000 12100:00:00.9999999 00:00:01.000 122Warnings: 123Note 1292 Truncated incorrect time value: '00:00:00.9999999' 124DROP TABLE t1; 125CREATE TABLE t1 (a DECIMAL(38,10)); 126INSERT INTO t1 VALUES(0.9999); 127INSERT INTO t1 VALUES(0.9999999); 128SELECT a, CAST(a AS TIME(3)) FROM t1; 129a CAST(a AS TIME(3)) 1300.9999000000 00:00:01.000 1310.9999999000 00:00:01.000 132DROP TABLE t1; 133CREATE TABLE t1 (a DOUBLE); 134INSERT INTO t1 VALUES(0.9999); 135INSERT INTO t1 VALUES(0.9999999); 136SELECT a, CAST(a AS TIME(3)) FROM t1; 137a CAST(a AS TIME(3)) 1380.9999 00:00:01.000 1390.9999999 00:00:01.000 140DROP TABLE t1; 141CREATE TABLE t1 (a VARCHAR(64)); 142INSERT INTO t1 VALUES('00:00:00.9999999'); 143SELECT a, CAST(a AS TIME(6)) FROM t1; 144a CAST(a AS TIME(6)) 14500:00:00.9999999 00:00:01.000000 146Warnings: 147Note 1292 Truncated incorrect time value: '00:00:00.9999999' 148DROP TABLE t1; 149CREATE TABLE t1 (a DECIMAL(38,10)); 150INSERT INTO t1 VALUES(0.9999999); 151SELECT a, CAST(a AS TIME(6)) FROM t1; 152a CAST(a AS TIME(6)) 1530.9999999000 00:00:01.000000 154DROP TABLE t1; 155CREATE TABLE t1 (a DOUBLE); 156INSERT INTO t1 VALUES(0.9999999); 157SELECT a, CAST(a AS TIME(6)) FROM t1; 158a CAST(a AS TIME(6)) 1590.9999999 00:00:01.000000 160DROP TABLE t1; 161# 162# NOW 163# 164SET time_zone='+00:00'; 165SET timestamp=UNIX_TIMESTAMP('2010-12-31 23:59:59.999999'); 166CREATE OR REPLACE TABLE t1 (id SERIAL, a TIME(4)); 167INSERT INTO t1 (a) VALUES (now(6)); 168Warnings: 169Note 1265 Data truncated for column 'a' at row 1 170INSERT INTO t1 (a) VALUES (CURRENT_TIMESTAMP(6)); 171Warnings: 172Note 1265 Data truncated for column 'a' at row 1 173INSERT INTO t1 (a) VALUES (CURRENT_TIME(6)); 174SELECT * FROM t1; 175id a 1761 24:00:00.0000 1772 24:00:00.0000 1783 24:00:00.0000 179DROP TABLE t1; 180SET timestamp=DEFAULT; 181SET time_zone=DEFAULT; 182# 183# Equal field propagation 184# 185CREATE TABLE t1 (a TIME(6)); 186INSERT INTO t1 VALUES (0.999999); 187INSERT INTO t1 VALUES (0.9999999); 188SELECT * FROM t1 WHERE a=0.9999999; 189a 19000:00:01.000000 191SELECT * FROM t1 WHERE a='0.9999999'; 192a 19300:00:01.000000 194Warnings: 195Note 1292 Truncated incorrect time value: '0.9999999' 196SELECT * FROM t1 WHERE a='0.9999999' AND a>='0.9999999'; 197a 19800:00:01.000000 199Warnings: 200Note 1292 Truncated incorrect time value: '0.9999999' 201Note 1292 Truncated incorrect time value: '0.9999999' 202SELECT * FROM t1 WHERE a='0.9999999' AND CONCAT(a)='00:00:01.000000'; 203a 20400:00:01.000000 205Warnings: 206Note 1292 Truncated incorrect time value: '0.9999999' 207EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0.9999999' AND a>='0.9999999'; 208id select_type table type possible_keys key key_len ref rows filtered Extra 2091 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 210Warnings: 211Note 1292 Truncated incorrect time value: '0.9999999' 212Note 1292 Truncated incorrect time value: '0.9999999' 213Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:01' 214EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0.9999999' AND CONCAT(a)='00:00:01.000000'; 215id select_type table type possible_keys key key_len ref rows filtered Extra 2161 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 217Warnings: 218Note 1292 Truncated incorrect time value: '0.9999999' 219Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:01' 220DROP TABLE t1; 221# 222# Comparing non-temporal to TIME 223# 224CREATE TABLE t1 (a VARCHAR(64)); 225INSERT t1 VALUES ('22:59:59.9999999'); 226SELECT * FROM t1 WHERE a=TIME'23:00:00'; 227a 22822:59:59.9999999 229Warnings: 230Note 1292 Truncated incorrect time value: '22:59:59.9999999' 231SELECT * FROM t1 WHERE CONCAT(a)=TIME'23:00:00'; 232a 23322:59:59.9999999 234Warnings: 235Note 1292 Truncated incorrect time value: '22:59:59.9999999' 236SELECT * FROM t1 WHERE COALESCE(a)=TIME'23:00:00'; 237a 23822:59:59.9999999 239Warnings: 240Note 1292 Truncated incorrect time value: '22:59:59.9999999' 241DROP TABLE t1; 242CREATE TABLE t1 (a DECIMAL(32,7)); 243INSERT t1 VALUES (225959.9999999); 244SELECT * FROM t1 WHERE a=TIME'23:00:00'; 245a 246225959.9999999 247SELECT * FROM t1 WHERE COALESCE(a)=TIME'23:00:00'; 248a 249225959.9999999 250DROP TABLE t1; 251# 252# Literal corner case 253# 254SELECT TIME'838:59:59.999999'; 255TIME'838:59:59.999999' 256838:59:59.999999 257SELECT TIME'838:59:59.9999999'; 258ERROR HY000: Incorrect TIME value: '838:59:59.9999999' 259SELECT TIME'839:00:00'; 260ERROR HY000: Incorrect TIME value: '839:00:00' 261