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

--echo #
--echo # DATETIME: SET
--echo #

CREATE TABLE t1 (a DATETIME(3), b DATETIME(4));
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999');
UPDATE t1 SET a=b;
SELECT a FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DATETIME(3), b VARCHAR(64));
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999999');
UPDATE t1 SET a=b;
SELECT a FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DATETIME(3), b DECIMAL(38,10));
INSERT INTO t1 VALUES(NULL,20001231235959.9999);
INSERT INTO t1 VALUES(NULL,20001231235959.9999999);
UPDATE t1 SET a=b;
SELECT a FROM t1;
DROP TABLE t1;

--echo #
--echo # DATETIME: ALTER
--echo #

CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
ALTER TABLE t1 MODIFY a DATETIME(3);
SELECT a FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999');
ALTER TABLE t1 MODIFY a DATETIME(3);
SELECT a FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(20001231235959.9999);
INSERT INTO t1 VALUES(20001231235959.9999999);
ALTER TABLE t1 MODIFY a DATETIME(3);
SELECT a FROM t1;
DROP TABLE t1;


--echo #
--echo # Corner case:
--echo # ALTER DATETIME to a shorter DATETIME
--echo # All values round, maximum possible value truncates.
--echo #

SET time_zone='+00:00';
CREATE TABLE t1 (ID INT, a DATETIME(6), comment VARCHAR(64));
INSERT INTO t1 VALUES (0, '9999-12-30 23:59:58.999999', 'Should round');
INSERT INTO t1 VALUES (1, '9999-12-31 22:59:59.999999', 'Should round');
INSERT INTO t1 VALUES (2, '9999-12-31 23:59:58.999999', 'Should round');
INSERT INTO t1 VALUES (3, '9999-12-31 23:59:59.999999', 'Should truncate');
ALTER TABLE t1 MODIFY a DATETIME(5);
SELECT * FROM t1;
DROP TABLE t1;
SET time_zone=DEFAULT;


--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 DATETIME(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 # DATETIME: CAST
--echo #

CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
SELECT a, CAST(a AS DATETIME(3)) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999');
SELECT a, CAST(a AS DATETIME(3)) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(20001231235959.9999);
INSERT INTO t1 VALUES(20001231235959.9999999);
SELECT a, CAST(a AS DATETIME(3)) FROM t1;
DROP TABLE t1;


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

CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES (20010101235959.999999);
INSERT INTO t1 VALUES (20010101235959.9999999);
SELECT * FROM t1 WHERE a=20010101235959.9999999;
SELECT * FROM t1 WHERE a='20010101235959.9999999';
SELECT * FROM t1 WHERE a='20010101235959.9999999' AND a>='20010101235959.9999999';
SELECT * FROM t1 WHERE a='20010101235959.9999999' AND CONCAT(a)='2001-01-02 00:00:00.000000';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='20010101235959.9999999' AND a>='20010101235959.9999999';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='20010101235959.9999999' AND CONCAT(a)='2001-01-02 00:00:00.000000';
DROP TABLE t1;

--echo #
--echo # Comparing non-temporal to DATETIME
--echo #

CREATE TABLE t1 (a VARCHAR(64));
INSERT t1 VALUES ('2001-01-01 23:59:59.9999999');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-02 00:00:00';
SELECT * FROM t1 WHERE CONCAT(a)=TIMESTAMP'2001-01-02 00:00:00';
SELECT * FROM t1 WHERE COALESCE(a)=TIMESTAMP'2001-01-02 00:00:00';
DROP TABLE t1;

CREATE TABLE t1 (a DECIMAL(32,7));
INSERT t1 VALUES (20010101235959.9999999);
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-02 00:00:00';
SELECT * FROM t1 WHERE COALESCE(a)=TIMESTAMP'2001-01-02 00:00:00';
DROP TABLE t1;

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

SELECT TIMESTAMP'9999-12-31 23:59:59.999999';
--error ER_WRONG_VALUE
SELECT TIME'9999-12-31 23:59:59.9999999';