1 files changed, 113 insertions, 0 deletions
diff --git a/mysql-test/main/type_date_round.test b/mysql-test/main/type_date_round.test
new file mode 100644
index 00000000000..61e1d0a401c
--- /dev/null
+++ b/mysql-test/main/type_date_round.test
@@ -0,0 +1,113 @@
+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;