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

--echo #
--echo # DATE: SET
--echo #

CREATE TABLE t1 (a DATE, 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 DATE, 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 DATE, 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 # DATE: ALTER
--echo #

CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
ALTER TABLE t1 MODIFY a DATE;
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 DATE;
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 DATE;
SELECT a FROM t1;
DROP TABLE t1;

--echo #
--echo # DATE: CAST
--echo #

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

# This truncates microseconds but rounds nanoseconds (MySQL Bug #92475)
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 DATE) FROM t1;
DROP TABLE t1;

# This truncates microseconds but rounds nanoseconds (MySQL Bug #92475)
CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(20001231235959.9999);
INSERT INTO t1 VALUES(20001231235959.9999999);
SELECT a, CAST(a AS DATE) FROM t1;
DROP TABLE t1;


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

CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES (20010101);
INSERT INTO t1 VALUES (20010102);
# DATE is compared to non-temporal as DATETIME
# In the below queries nanoseconds should round to microseconds
SELECT * FROM t1 WHERE a= 20010101235959.9999999;
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999';
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.9999999';
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.9999999';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02';
DROP TABLE t1;


--echo #
--echo # Comparing non-temporal to DATE
--echo #

--echo # Although conversion from non-temporal to DATE (e.g. on SET) does not round,
--echo # comparison between non-temporal to DATE is performed as DATETIME.
--echo # So rounding does happen here.

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

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