SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
SET @default_sql_mode=@@sql_mode;
#
# TIME: SET
#
CREATE TABLE t1 (a TIME(3), b TIME(4));
INSERT INTO t1 VALUES(NULL,'00:00:00.9999');
UPDATE t1 SET a=b;
SELECT a FROM t1;
a
00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a TIME(3), b VARCHAR(64));
INSERT INTO t1 VALUES(NULL,'00:00:00.9999');
INSERT INTO t1 VALUES(NULL,'00:00:00.9999999');
UPDATE t1 SET a=b;
Warnings:
Note	1265	Data truncated for column 'a' at row 2
SELECT a FROM t1;
a
00:00:01.000
00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a TIME(3), b DECIMAL(38,10));
INSERT INTO t1 VALUES(NULL,0.9999);
INSERT INTO t1 VALUES(NULL,0.9999999);
UPDATE t1 SET a=b;
SELECT a FROM t1;
a
00:00:01.000
00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a TIME(3), b DOUBLE);
INSERT INTO t1 VALUES(NULL,0.9999);
INSERT INTO t1 VALUES(NULL,0.9999999);
UPDATE t1 SET a=b;
SELECT a FROM t1;
a
00:00:01.000
00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a TIME(6), b VARCHAR(64));
INSERT INTO t1 VALUES(NULL,'00:00:00.9999999');
UPDATE t1 SET a=b;
Warnings:
Note	1265	Data truncated for column 'a' at row 1
SELECT a FROM t1;
a
00:00:01.000000
DROP TABLE t1;
CREATE TABLE t1 (a TIME(6), b DECIMAL(38,10));
INSERT INTO t1 VALUES(NULL,0.9999999);
UPDATE t1 SET a=b;
SELECT a FROM t1;
a
00:00:01.000000
DROP TABLE t1;
CREATE TABLE t1 (a TIME(6), b DOUBLE);
INSERT INTO t1 VALUES(NULL,0.9999999);
UPDATE t1 SET a=b;
SELECT a FROM t1;
a
00:00:01.000000
DROP TABLE t1;
#
# TIME: ALTER
#
CREATE TABLE t1 (a TIME(4));
INSERT INTO t1 VALUES('00:00:00.9999');
ALTER TABLE t1 MODIFY a TIME(3);
SELECT a FROM t1;
a
00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('00:00:00.9999');
INSERT INTO t1 VALUES('00:00:00.9999999');
ALTER TABLE t1 MODIFY a TIME(3);
Warnings:
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
SELECT a FROM t1;
a
00:00:01.000
00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(0.9999);
INSERT INTO t1 VALUES(0.9999999);
ALTER TABLE t1 MODIFY a TIME(3);
SELECT a FROM t1;
a
00:00:01.000
00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES(0.9999);
INSERT INTO t1 VALUES(0.9999999);
ALTER TABLE t1 MODIFY a TIME(3);
Warnings:
Note	1265	Data truncated for column 'a' at row 2
SELECT a FROM t1;
a
00:00:01.000
00:00:01.000
DROP TABLE t1;
#
# TIME: CAST
#
CREATE TABLE t1 (a TIME(4));
INSERT INTO t1 VALUES('00:00:00.9999');
SELECT a, CAST(a AS TIME(3)) FROM t1;
a	CAST(a AS TIME(3))
00:00:00.9999	00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('00:00:00.9999');
INSERT INTO t1 VALUES('00:00:00.9999999');
SELECT a, CAST(a AS TIME(3)) FROM t1;
a	CAST(a AS TIME(3))
00:00:00.9999	00:00:01.000
00:00:00.9999999	00:00:01.000
Warnings:
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(0.9999);
INSERT INTO t1 VALUES(0.9999999);
SELECT a, CAST(a AS TIME(3)) FROM t1;
a	CAST(a AS TIME(3))
0.9999000000	00:00:01.000
0.9999999000	00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES(0.9999);
INSERT INTO t1 VALUES(0.9999999);
SELECT a, CAST(a AS TIME(3)) FROM t1;
a	CAST(a AS TIME(3))
0.9999	00:00:01.000
0.9999999	00:00:01.000
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('00:00:00.9999999');
SELECT a, CAST(a AS TIME(6)) FROM t1;
a	CAST(a AS TIME(6))
00:00:00.9999999	00:00:01.000000
Warnings:
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(0.9999999);
SELECT a, CAST(a AS TIME(6)) FROM t1;
a	CAST(a AS TIME(6))
0.9999999000	00:00:01.000000
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES(0.9999999);
SELECT a, CAST(a AS TIME(6)) FROM t1;
a	CAST(a AS TIME(6))
0.9999999	00:00:01.000000
DROP TABLE t1;
#
# NOW
#
SET time_zone='+00:00';
SET timestamp=UNIX_TIMESTAMP('2010-12-31 23:59:59.999999');
CREATE OR REPLACE TABLE t1 (id SERIAL, a TIME(4));
INSERT INTO t1 (a) VALUES (now(6));
Warnings:
Note	1265	Data truncated for column 'a' at row 1
INSERT INTO t1 (a) VALUES (CURRENT_TIMESTAMP(6));
Warnings:
Note	1265	Data truncated for column 'a' at row 1
INSERT INTO t1 (a) VALUES (CURRENT_TIME(6));
SELECT * FROM t1;
id	a
1	24:00:00.0000
2	24:00:00.0000
3	24:00:00.0000
DROP TABLE t1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;
#
# Equal field propagation
#
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES (0.999999);
INSERT INTO t1 VALUES (0.9999999);
SELECT * FROM t1 WHERE a=0.9999999;
a
00:00:01.000000
SELECT * FROM t1 WHERE a='0.9999999';
a
00:00:01.000000
Warnings:
Note	1292	Truncated incorrect time value: '0.9999999'
SELECT * FROM t1 WHERE a='0.9999999' AND a>='0.9999999';
a
00:00:01.000000
Warnings:
Note	1292	Truncated incorrect time value: '0.9999999'
Note	1292	Truncated incorrect time value: '0.9999999'
SELECT * FROM t1 WHERE a='0.9999999' AND CONCAT(a)='00:00:01.000000';
a
00:00:01.000000
Warnings:
Note	1292	Truncated incorrect time value: '0.9999999'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0.9999999' AND a>='0.9999999';
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	1292	Truncated incorrect time value: '0.9999999'
Note	1292	Truncated incorrect time value: '0.9999999'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:01'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0.9999999' AND CONCAT(a)='00:00:01.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	1292	Truncated incorrect time value: '0.9999999'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:01'
DROP TABLE t1;
#
# Comparing non-temporal to TIME
#
CREATE TABLE t1 (a VARCHAR(64));
INSERT t1 VALUES ('22:59:59.9999999');
SELECT * FROM t1 WHERE a=TIME'23:00:00';
a
22:59:59.9999999
Warnings:
Note	1292	Truncated incorrect time value: '22:59:59.9999999'
SELECT * FROM t1 WHERE CONCAT(a)=TIME'23:00:00';
a
22:59:59.9999999
Warnings:
Note	1292	Truncated incorrect time value: '22:59:59.9999999'
SELECT * FROM t1 WHERE COALESCE(a)=TIME'23:00:00';
a
22:59:59.9999999
Warnings:
Note	1292	Truncated incorrect time value: '22:59:59.9999999'
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(32,7));
INSERT t1 VALUES (225959.9999999);
SELECT * FROM t1 WHERE a=TIME'23:00:00';
a
225959.9999999
SELECT * FROM t1 WHERE COALESCE(a)=TIME'23:00:00';
a
225959.9999999
DROP TABLE t1;
#
# Literal corner case
#
SELECT TIME'838:59:59.999999';
TIME'838:59:59.999999'
838:59:59.999999
SELECT TIME'838:59:59.9999999';
ERROR HY000: Incorrect TIME value: '838:59:59.9999999'
SELECT TIME'839:00:00';
ERROR HY000: Incorrect TIME value: '839:00:00'