1 files changed, 147 insertions, 0 deletions
diff --git a/mysql-test/main/type_datetime_round.test b/mysql-test/main/type_datetime_round.test
new file mode 100644
index 00000000000..15aec2cf4e0
--- /dev/null
+++ b/mysql-test/main/type_datetime_round.test
@@ -0,0 +1,147 @@
+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';