SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
SET @default_sql_mode=@@sql_mode;

--echo #
--echo # TIME: SET
--echo #

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;
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;
SELECT a FROM t1;
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;
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;
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;
SELECT a FROM t1;
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;
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;
DROP TABLE t1;

--echo #
--echo # TIME: ALTER
--echo #

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;
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);
SELECT a FROM t1;
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;
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);
SELECT a FROM t1;
DROP TABLE t1;

--echo #
--echo # TIME: CAST
--echo #

CREATE TABLE t1 (a TIME(4));
INSERT INTO t1 VALUES('00:00:00.9999');
SELECT a, CAST(a AS TIME(3)) FROM t1;
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;
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;
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;
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;
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;
DROP TABLE t1;

CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES(0.9999999);
SELECT a, CAST(a AS TIME(6)) FROM t1;
DROP TABLE t1;


--echo #
--echo # NOW
--echo #

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));
INSERT INTO t1 (a) VALUES (CURRENT_TIMESTAMP(6));
INSERT INTO t1 (a) VALUES (CURRENT_TIME(6));
SELECT * FROM t1;
DROP TABLE t1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;


--echo #
--echo # Equal field propagation
--echo #

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;
SELECT * FROM t1 WHERE a='0.9999999';
SELECT * FROM t1 WHERE a='0.9999999' AND a>='0.9999999';
SELECT * FROM t1 WHERE a='0.9999999' AND CONCAT(a)='00:00:01.000000';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0.9999999' AND a>='0.9999999';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0.9999999' AND CONCAT(a)='00:00:01.000000';
DROP TABLE t1;


--echo #
--echo # Comparing non-temporal to TIME
--echo #

CREATE TABLE t1 (a VARCHAR(64));
INSERT t1 VALUES ('22:59:59.9999999');
SELECT * FROM t1 WHERE a=TIME'23:00:00';
SELECT * FROM t1 WHERE CONCAT(a)=TIME'23:00:00';
SELECT * FROM t1 WHERE COALESCE(a)=TIME'23:00:00';
DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(32,7));
INSERT t1 VALUES (225959.9999999);
SELECT * FROM t1 WHERE a=TIME'23:00:00';
SELECT * FROM t1 WHERE COALESCE(a)=TIME'23:00:00';
DROP TABLE t1;

--echo #
--echo # Literal corner case
--echo #

SELECT TIME'838:59:59.999999';
--error ER_WRONG_VALUE
SELECT TIME'838:59:59.9999999';
--error ER_WRONG_VALUE
SELECT TIME'839:00:00';