MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMPbb-10.4-mdev16991

26 files changed, 3367 insertions, 20 deletions

diff --git a/mysql-test/main/func_time_round.result b/mysql-test/main/func_time_round.result
new file mode 100644
index 00000000000..208baae8cf9
--- /dev/null
+++ b/mysql-test/main/func_time_round.result
@@ -0,0 +1,1374 @@
+SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
+SET @default_sql_mode=@@sql_mode;
+CREATE TABLE t1_datetime_in_varchar (id SERIAL, a VARCHAR(64));
+INSERT INTO t1_datetime_in_varchar (a) VALUES
+('2000-12-31 23:59:59'),
+('2000-12-31 23:59:59.9'),
+('2000-12-31 23:59:59.99'),
+('2000-12-31 23:59:59.999'),
+('2000-12-31 23:59:59.9999'),
+('2000-12-31 23:59:59.99999'),
+('2000-12-31 23:59:59.999999'),
+('2000-12-31 23:59:59.9999999');
+CREATE TABLE t1_datetime_in_decimal (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_datetime_in_decimal (a) VALUES
+(20001231235959),
+(20001231235959.9),
+(20001231235959.99),
+(20001231235959.999),
+(20001231235959.9999),
+(20001231235959.99999),
+(20001231235959.999999),
+(20001231235959.9999999);
+CREATE TABLE t1_time_in_varchar (id SERIAL, a VARCHAR(64));
+INSERT INTO t1_time_in_varchar (a) VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.99'),
+('00:00:00.999'),
+('00:00:00.9999'),
+('00:00:00.99999'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+INSERT INTO t1_time_in_varchar (a) VALUES
+('837:59:59.9999999'),
+('838:59:59'),
+('838:59:59.9'),
+('838:59:59.99'),
+('838:59:59.999'),
+('838:59:59.9999'),
+('838:59:59.99999'),
+('838:59:59.999999'),
+('838:59:59.9999999'),
+('839:59:59.9999999'),
+('87649414:59:59.999999'),
+('87649414:59:59.9999999'),
+('87649415:59:59.999999'),
+('87649415:59:59.9999999');
+CREATE TABLE t1_time_in_decimal (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_time_in_decimal (a) VALUES
+(0),
+(0.9),
+(0.99),
+(0.999),
+(0.9999),
+(0.99999),
+(0.999999),
+(0.9999999);
+INSERT INTO t1_time_in_decimal (a) VALUES
+(8375959.9999999),
+(8385959),
+(8385959.9),
+(8385959.99),
+(8385959.999),
+(8385959.9999),
+(8385959.99999),
+(8385959.999999),
+(8385959.9999999),
+(8395959.9999999),
+(876494145959.999999),
+(876494145959.9999999),
+(876494155959.999999),
+(876494155959.9999999);
+#
+# TIME: LEAST/GREATEST
+#
+SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_varchar;
+GREATEST(TIME'00:00:00', a)
+00:00:00.000000
+00:00:00.900000
+00:00:00.990000
+00:00:00.999000
+00:00:00.999900
+00:00:00.999990
+00:00:00.999999
+00:00:01.000000
+838:00:00.000000
+838:59:59.000000
+838:59:59.900000
+838:59:59.990000
+838:59:59.999000
+838:59:59.999900
+838:59:59.999990
+838:59:59.999999
+838:59:59.999999
+838:59:59.999999
+838:59:59.999999
+838:59:59.999999
+838:59:59.999999
+838:59:59.999999
+Warnings:
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '837:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '838:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '839:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649414:59:59.999999'
+Warning	1292	Truncated incorrect time value: '87649414:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
+SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_decimal;
+GREATEST(TIME'00:00:00', a)
+00:00:00.000000
+00:00:00.900000
+00:00:00.990000
+00:00:00.999000
+00:00:00.999900
+00:00:00.999990
+00:00:00.999999
+00:00:01.000000
+838:00:00.000000
+838:59:59.000000
+838:59:59.900000
+838:59:59.990000
+838:59:59.999000
+838:59:59.999900
+838:59:59.999990
+838:59:59.999999
+838:59:59.999999
+838:59:59.999999
+NULL
+NULL
+NULL
+NULL
+Warnings:
+Warning	1292	Incorrect time value: '8385959.9999999000' for column 'a' at row 17
+Warning	1292	Incorrect time value: '8395959.9999999000' for column 'a' at row 18
+Warning	1292	Incorrect time value: '876494145959.9999990000' for column 'a' at row 19
+Warning	1292	Incorrect time value: '876494145959.9999999000' for column 'a' at row 20
+Warning	1292	Incorrect time value: '876494155959.9999990000' for column 'a' at row 21
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column 'a' at row 22
+SELECT GREATEST(TIME'00:00:00', '00:00:00.0000004');
+GREATEST(TIME'00:00:00', '00:00:00.0000004')
+00:00:00.000000
+Warnings:
+Note	1292	Truncated incorrect time value: '00:00:00.0000004'
+SELECT GREATEST(TIME'00:00:00', 0.0000004);
+GREATEST(TIME'00:00:00', 0.0000004)
+00:00:00.000000
+SELECT GREATEST(TIME'00:00:00', '00:00:00.0000005');
+GREATEST(TIME'00:00:00', '00:00:00.0000005')
+00:00:00.000001
+Warnings:
+Note	1292	Truncated incorrect time value: '00:00:00.0000005'
+SELECT GREATEST(TIME'00:00:00', 0.0000005);
+GREATEST(TIME'00:00:00', 0.0000005)
+00:00:00.000001
+#
+# Functions with a single TIME input, conversion from DATETIME-in-VARCHAR
+#
+SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SECOND(a)	CAST(a AS TIME(6))	a
+59	23:59:59.000000	2000-12-31 23:59:59
+59	23:59:59.900000	2000-12-31 23:59:59.9
+59	23:59:59.990000	2000-12-31 23:59:59.99
+59	23:59:59.999000	2000-12-31 23:59:59.999
+59	23:59:59.999900	2000-12-31 23:59:59.9999
+59	23:59:59.999990	2000-12-31 23:59:59.99999
+59	23:59:59.999999	2000-12-31 23:59:59.999999
+0	00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+MINUTE(a)	CAST(a AS TIME(6))	a
+59	23:59:59.000000	2000-12-31 23:59:59
+59	23:59:59.900000	2000-12-31 23:59:59.9
+59	23:59:59.990000	2000-12-31 23:59:59.99
+59	23:59:59.999000	2000-12-31 23:59:59.999
+59	23:59:59.999900	2000-12-31 23:59:59.9999
+59	23:59:59.999990	2000-12-31 23:59:59.99999
+59	23:59:59.999999	2000-12-31 23:59:59.999999
+0	00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+HOUR(a)	CAST(a AS TIME(6))	a
+23	23:59:59.000000	2000-12-31 23:59:59
+23	23:59:59.900000	2000-12-31 23:59:59.9
+23	23:59:59.990000	2000-12-31 23:59:59.99
+23	23:59:59.999000	2000-12-31 23:59:59.999
+23	23:59:59.999900	2000-12-31 23:59:59.9999
+23	23:59:59.999990	2000-12-31 23:59:59.99999
+23	23:59:59.999999	2000-12-31 23:59:59.999999
+0	00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+EXTRACT(SECOND FROM a)	CAST(a AS TIME(6))	a
+59	23:59:59.000000	2000-12-31 23:59:59
+59	23:59:59.900000	2000-12-31 23:59:59.9
+59	23:59:59.990000	2000-12-31 23:59:59.99
+59	23:59:59.999000	2000-12-31 23:59:59.999
+59	23:59:59.999900	2000-12-31 23:59:59.9999
+59	23:59:59.999990	2000-12-31 23:59:59.99999
+59	23:59:59.999999	2000-12-31 23:59:59.999999
+0	00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+EXTRACT(MINUTE FROM a)	CAST(a AS TIME(6))	a
+59	23:59:59.000000	2000-12-31 23:59:59
+59	23:59:59.900000	2000-12-31 23:59:59.9
+59	23:59:59.990000	2000-12-31 23:59:59.99
+59	23:59:59.999000	2000-12-31 23:59:59.999
+59	23:59:59.999900	2000-12-31 23:59:59.9999
+59	23:59:59.999990	2000-12-31 23:59:59.99999
+59	23:59:59.999999	2000-12-31 23:59:59.999999
+0	00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+EXTRACT(HOUR FROM a)	CAST(a AS TIME(6))	a
+23	23:59:59.000000	2000-12-31 23:59:59
+23	23:59:59.900000	2000-12-31 23:59:59.9
+23	23:59:59.990000	2000-12-31 23:59:59.99
+23	23:59:59.999000	2000-12-31 23:59:59.999
+23	23:59:59.999900	2000-12-31 23:59:59.9999
+23	23:59:59.999990	2000-12-31 23:59:59.99999
+23	23:59:59.999999	2000-12-31 23:59:59.999999
+0	00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+TIME_TO_SEC(a)	CAST(a AS TIME(6))	a
+86399.000000	23:59:59.000000	2000-12-31 23:59:59
+86399.900000	23:59:59.900000	2000-12-31 23:59:59.9
+86399.990000	23:59:59.990000	2000-12-31 23:59:59.99
+86399.999000	23:59:59.999000	2000-12-31 23:59:59.999
+86399.999900	23:59:59.999900	2000-12-31 23:59:59.9999
+86399.999990	23:59:59.999990	2000-12-31 23:59:59.99999
+86399.999999	23:59:59.999999	2000-12-31 23:59:59.999999
+0.000000	00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+#
+# Functions with a single TIME input, conversion from DATETIME-in-DECIMAL
+#
+SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SECOND(a)	CAST(a AS TIME(6))	a
+59	23:59:59.000000	20001231235959.0000000000
+59	23:59:59.900000	20001231235959.9000000000
+59	23:59:59.990000	20001231235959.9900000000
+59	23:59:59.999000	20001231235959.9990000000
+59	23:59:59.999900	20001231235959.9999000000
+59	23:59:59.999990	20001231235959.9999900000
+59	23:59:59.999999	20001231235959.9999990000
+0	00:00:00.000000	20001231235959.9999999000
+SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+MINUTE(a)	CAST(a AS TIME(6))	a
+59	23:59:59.000000	20001231235959.0000000000
+59	23:59:59.900000	20001231235959.9000000000
+59	23:59:59.990000	20001231235959.9900000000
+59	23:59:59.999000	20001231235959.9990000000
+59	23:59:59.999900	20001231235959.9999000000
+59	23:59:59.999990	20001231235959.9999900000
+59	23:59:59.999999	20001231235959.9999990000
+0	00:00:00.000000	20001231235959.9999999000
+SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+HOUR(a)	CAST(a AS TIME(6))	a
+23	23:59:59.000000	20001231235959.0000000000
+23	23:59:59.900000	20001231235959.9000000000
+23	23:59:59.990000	20001231235959.9900000000
+23	23:59:59.999000	20001231235959.9990000000
+23	23:59:59.999900	20001231235959.9999000000
+23	23:59:59.999990	20001231235959.9999900000
+23	23:59:59.999999	20001231235959.9999990000
+0	00:00:00.000000	20001231235959.9999999000
+SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+EXTRACT(SECOND FROM a)	CAST(a AS TIME(6))	a
+59	23:59:59.000000	20001231235959.0000000000
+59	23:59:59.900000	20001231235959.9000000000
+59	23:59:59.990000	20001231235959.9900000000
+59	23:59:59.999000	20001231235959.9990000000
+59	23:59:59.999900	20001231235959.9999000000
+59	23:59:59.999990	20001231235959.9999900000
+59	23:59:59.999999	20001231235959.9999990000
+0	00:00:00.000000	20001231235959.9999999000
+SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+EXTRACT(MINUTE FROM a)	CAST(a AS TIME(6))	a
+59	23:59:59.000000	20001231235959.0000000000
+59	23:59:59.900000	20001231235959.9000000000
+59	23:59:59.990000	20001231235959.9900000000
+59	23:59:59.999000	20001231235959.9990000000
+59	23:59:59.999900	20001231235959.9999000000
+59	23:59:59.999990	20001231235959.9999900000
+59	23:59:59.999999	20001231235959.9999990000
+0	00:00:00.000000	20001231235959.9999999000
+SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+EXTRACT(HOUR FROM a)	CAST(a AS TIME(6))	a
+23	23:59:59.000000	20001231235959.0000000000
+23	23:59:59.900000	20001231235959.9000000000
+23	23:59:59.990000	20001231235959.9900000000
+23	23:59:59.999000	20001231235959.9990000000
+23	23:59:59.999900	20001231235959.9999000000
+23	23:59:59.999990	20001231235959.9999900000
+23	23:59:59.999999	20001231235959.9999990000
+0	00:00:00.000000	20001231235959.9999999000
+SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+TIME_TO_SEC(a)	CAST(a AS TIME(6))	a
+86399.000000	23:59:59.000000	20001231235959.0000000000
+86399.900000	23:59:59.900000	20001231235959.9000000000
+86399.990000	23:59:59.990000	20001231235959.9900000000
+86399.999000	23:59:59.999000	20001231235959.9990000000
+86399.999900	23:59:59.999900	20001231235959.9999000000
+86399.999990	23:59:59.999990	20001231235959.9999900000
+86399.999999	23:59:59.999999	20001231235959.9999990000
+0.000000	00:00:00.000000	20001231235959.9999999000
+#
+# Functions with a single TIME interval input, conversion from TIME-interval-in-VARCHAR
+#
+SELECT
+EXTRACT(DAY FROM a),
+EXTRACT(HOUR FROM a),
+EXTRACT(MINUTE FROM a),
+EXTRACT(SECOND FROM a),
+EXTRACT(MICROSECOND FROM a),
+CAST(a AS INTERVAL DAY_SECOND(6)),
+a
+FROM t1_time_in_varchar ORDER BY id;
+EXTRACT(DAY FROM a)	EXTRACT(HOUR FROM a)	EXTRACT(MINUTE FROM a)	EXTRACT(SECOND FROM a)	EXTRACT(MICROSECOND FROM a)	CAST(a AS INTERVAL DAY_SECOND(6))	a
+0	0	0	0	0	00:00:00.000000	00:00:00
+0	0	0	0	900000	00:00:00.900000	00:00:00.9
+0	0	0	0	990000	00:00:00.990000	00:00:00.99
+0	0	0	0	999000	00:00:00.999000	00:00:00.999
+0	0	0	0	999900	00:00:00.999900	00:00:00.9999
+0	0	0	0	999990	00:00:00.999990	00:00:00.99999
+0	0	0	0	999999	00:00:00.999999	00:00:00.999999
+0	0	0	1	0	00:00:01.000000	00:00:00.9999999
+34	22	0	0	0	34 22:00:00.000000	837:59:59.9999999
+34	22	59	59	0	34 22:59:59.000000	838:59:59
+34	22	59	59	900000	34 22:59:59.900000	838:59:59.9
+34	22	59	59	990000	34 22:59:59.990000	838:59:59.99
+34	22	59	59	999000	34 22:59:59.999000	838:59:59.999
+34	22	59	59	999900	34 22:59:59.999900	838:59:59.9999
+34	22	59	59	999990	34 22:59:59.999990	838:59:59.99999
+34	22	59	59	999999	34 22:59:59.999999	838:59:59.999999
+34	23	0	0	0	34 23:00:00.000000	838:59:59.9999999
+35	0	0	0	0	35 00:00:00.000000	839:59:59.9999999
+3652058	22	59	59	999999	3652058 22:59:59.999999	87649414:59:59.999999
+3652058	23	0	0	0	3652058 23:00:00.000000	87649414:59:59.9999999
+3652058	23	59	59	999999	3652058 23:59:59.999999	87649415:59:59.999999
+3652058	23	59	59	999999	3652058 23:59:59.999999	87649415:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '837:59:59.9999999'
+Note	1292	Truncated incorrect time value: '837:59:59.9999999'
+Note	1292	Truncated incorrect time value: '837:59:59.9999999'
+Note	1292	Truncated incorrect time value: '837:59:59.9999999'
+Note	1292	Truncated incorrect time value: '837:59:59.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '837:59:59.9999999'
+Note	1292	Truncated incorrect time value: '838:59:59.9999999'
+Note	1292	Truncated incorrect time value: '838:59:59.9999999'
+Note	1292	Truncated incorrect time value: '838:59:59.9999999'
+Note	1292	Truncated incorrect time value: '838:59:59.9999999'
+Note	1292	Truncated incorrect time value: '838:59:59.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '838:59:59.9999999'
+Note	1292	Truncated incorrect time value: '839:59:59.9999999'
+Note	1292	Truncated incorrect time value: '839:59:59.9999999'
+Note	1292	Truncated incorrect time value: '839:59:59.9999999'
+Note	1292	Truncated incorrect time value: '839:59:59.9999999'
+Note	1292	Truncated incorrect time value: '839:59:59.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.9999999'
+Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
+Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
+Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
+Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
+Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '87649414:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '87649415:59:59.9999999'
+SELECT
+TIME_TO_SEC(a),
+CAST(a AS TIME(6)),
+a
+FROM t1_time_in_varchar ORDER BY id;
+TIME_TO_SEC(a)	CAST(a AS TIME(6))	a
+0.000000	00:00:00.000000	00:00:00
+0.900000	00:00:00.900000	00:00:00.9
+0.990000	00:00:00.990000	00:00:00.99
+0.999000	00:00:00.999000	00:00:00.999
+0.999900	00:00:00.999900	00:00:00.9999
+0.999990	00:00:00.999990	00:00:00.99999
+0.999999	00:00:00.999999	00:00:00.999999
+1.000000	00:00:01.000000	00:00:00.9999999
+3016800.000000	838:00:00.000000	837:59:59.9999999
+3020399.000000	838:59:59.000000	838:59:59
+3020399.900000	838:59:59.900000	838:59:59.9
+3020399.990000	838:59:59.990000	838:59:59.99
+3020399.999000	838:59:59.999000	838:59:59.999
+3020399.999900	838:59:59.999900	838:59:59.9999
+3020399.999990	838:59:59.999990	838:59:59.99999
+3020399.999999	838:59:59.999999	838:59:59.999999
+3020399.999999	838:59:59.999999	838:59:59.9999999
+3020399.999999	838:59:59.999999	839:59:59.9999999
+3020399.999999	838:59:59.999999	87649414:59:59.999999
+3020399.999999	838:59:59.999999	87649414:59:59.9999999
+3020399.999999	838:59:59.999999	87649415:59:59.999999
+3020399.999999	838:59:59.999999	87649415:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '837:59:59.9999999'
+Note	1292	Truncated incorrect time value: '837:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '838:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '838:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '839:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '839:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649414:59:59.999999'
+Warning	1292	Truncated incorrect time value: '87649414:59:59.999999'
+Warning	1292	Truncated incorrect time value: '87649414:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649414:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
+Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
+#
+# Functions with a single TIME interval input, conversion from TIME-interval-in-DECIMAL
+#
+SELECT
+EXTRACT(DAY FROM a),
+EXTRACT(HOUR FROM a),
+EXTRACT(MINUTE FROM a),
+EXTRACT(SECOND FROM a),
+EXTRACT(MICROSECOND FROM a),
+CAST(a AS INTERVAL DAY_SECOND(6)),
+a
+FROM t1_time_in_decimal ORDER BY id;
+EXTRACT(DAY FROM a)	EXTRACT(HOUR FROM a)	EXTRACT(MINUTE FROM a)	EXTRACT(SECOND FROM a)	EXTRACT(MICROSECOND FROM a)	CAST(a AS INTERVAL DAY_SECOND(6))	a
+0	0	0	0	0	00:00:00.000000	0.0000000000
+0	0	0	0	900000	00:00:00.900000	0.9000000000
+0	0	0	0	990000	00:00:00.990000	0.9900000000
+0	0	0	0	999000	00:00:00.999000	0.9990000000
+0	0	0	0	999900	00:00:00.999900	0.9999000000
+0	0	0	0	999990	00:00:00.999990	0.9999900000
+0	0	0	0	999999	00:00:00.999999	0.9999990000
+0	0	0	1	0	00:00:01.000000	0.9999999000
+34	22	0	0	0	34 22:00:00.000000	8375959.9999999000
+34	22	59	59	0	34 22:59:59.000000	8385959.0000000000
+34	22	59	59	900000	34 22:59:59.900000	8385959.9000000000
+34	22	59	59	990000	34 22:59:59.990000	8385959.9900000000
+34	22	59	59	999000	34 22:59:59.999000	8385959.9990000000
+34	22	59	59	999900	34 22:59:59.999900	8385959.9999000000
+34	22	59	59	999990	34 22:59:59.999990	8385959.9999900000
+34	22	59	59	999999	34 22:59:59.999999	8385959.9999990000
+34	23	0	0	0	34 23:00:00.000000	8385959.9999999000
+35	0	0	0	0	35 00:00:00.000000	8395959.9999999000
+3652058	22	59	59	999999	3652058 22:59:59.999999	876494145959.9999990000
+3652058	23	0	0	0	3652058 23:00:00.000000	876494145959.9999999000
+3652058	23	59	59	999999	3652058 23:59:59.999999	876494155959.9999990000
+3652058	23	59	59	999999	3652058 23:59:59.999999	876494155959.9999999000
+Warnings:
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.0000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9900000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9990000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999900000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999990000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999999000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8375959.9999999000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.0000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9000000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9900000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9990000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999000000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999900000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999990000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999999000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.9999999000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.9999990000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.9999999000'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.9999990000'
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column 'a' at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column 'a' at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column 'a' at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column 'a' at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column 'a' at row 22
+Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '876494155959.9999999000'
+SELECT
+TIME_TO_SEC(a),
+CAST(a AS TIME(6)),
+a
+FROM t1_time_in_decimal ORDER BY id;
+TIME_TO_SEC(a)	CAST(a AS TIME(6))	a
+0.000000	00:00:00.000000	0.0000000000
+0.900000	00:00:00.900000	0.9000000000
+0.990000	00:00:00.990000	0.9900000000
+0.999000	00:00:00.999000	0.9990000000
+0.999900	00:00:00.999900	0.9999000000
+0.999990	00:00:00.999990	0.9999900000
+0.999999	00:00:00.999999	0.9999990000
+1.000000	00:00:01.000000	0.9999999000
+3016800.000000	838:00:00.000000	8375959.9999999000
+3020399.000000	838:59:59.000000	8385959.0000000000
+3020399.900000	838:59:59.900000	8385959.9000000000
+3020399.990000	838:59:59.990000	8385959.9900000000
+3020399.999000	838:59:59.999000	8385959.9990000000
+3020399.999900	838:59:59.999900	8385959.9999000000
+3020399.999990	838:59:59.999990	8385959.9999900000
+3020399.999999	838:59:59.999999	8385959.9999990000
+3020399.999999	838:59:59.999999	8385959.9999999000
+3020399.999999	838:59:59.999999	8395959.9999999000
+NULL	NULL	876494145959.9999990000
+NULL	NULL	876494145959.9999999000
+NULL	NULL	876494155959.9999990000
+NULL	NULL	876494155959.9999999000
+Warnings:
+Warning	1292	Incorrect time value: '8385959.9999999000' for column 'a' at row 17
+Warning	1292	Incorrect time value: '8385959.9999999000' for column 'a' at row 17
+Warning	1292	Incorrect time value: '8395959.9999999000' for column 'a' at row 18
+Warning	1292	Incorrect time value: '8395959.9999999000' for column 'a' at row 18
+Warning	1292	Incorrect time value: '876494145959.9999990000' for column 'a' at row 19
+Warning	1292	Incorrect time value: '876494145959.9999990000' for column 'a' at row 19
+Warning	1292	Incorrect time value: '876494145959.9999999000' for column 'a' at row 20
+Warning	1292	Incorrect time value: '876494145959.9999999000' for column 'a' at row 20
+Warning	1292	Incorrect time value: '876494155959.9999990000' for column 'a' at row 21
+Warning	1292	Incorrect time value: '876494155959.9999990000' for column 'a' at row 21
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column 'a' at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column 'a' at row 22
+#
+# Functions with a single DATE input, conversion from DATETIME-in-VARCHAR
+#
+SELECT QUARTER(a), CAST(a AS DATE),  a FROM t1_datetime_in_varchar ORDER BY id;
+QUARTER(a)	CAST(a AS DATE)	a
+4	2000-12-31	2000-12-31 23:59:59
+4	2000-12-31	2000-12-31 23:59:59.9
+4	2000-12-31	2000-12-31 23:59:59.99
+4	2000-12-31	2000-12-31 23:59:59.999
+4	2000-12-31	2000-12-31 23:59:59.9999
+4	2000-12-31	2000-12-31 23:59:59.99999
+4	2000-12-31	2000-12-31 23:59:59.999999
+1	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+DAY(a)	CAST(a AS DATE)	a
+31	2000-12-31	2000-12-31 23:59:59
+31	2000-12-31	2000-12-31 23:59:59.9
+31	2000-12-31	2000-12-31 23:59:59.99
+31	2000-12-31	2000-12-31 23:59:59.999
+31	2000-12-31	2000-12-31 23:59:59.9999
+31	2000-12-31	2000-12-31 23:59:59.99999
+31	2000-12-31	2000-12-31 23:59:59.999999
+1	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+MONTH(a)	CAST(a AS DATE)	a
+12	2000-12-31	2000-12-31 23:59:59
+12	2000-12-31	2000-12-31 23:59:59.9
+12	2000-12-31	2000-12-31 23:59:59.99
+12	2000-12-31	2000-12-31 23:59:59.999
+12	2000-12-31	2000-12-31 23:59:59.9999
+12	2000-12-31	2000-12-31 23:59:59.99999
+12	2000-12-31	2000-12-31 23:59:59.999999
+1	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+YEAR(a)	CAST(a AS DATE)	a
+2000	2000-12-31	2000-12-31 23:59:59
+2000	2000-12-31	2000-12-31 23:59:59.9
+2000	2000-12-31	2000-12-31 23:59:59.99
+2000	2000-12-31	2000-12-31 23:59:59.999
+2000	2000-12-31	2000-12-31 23:59:59.9999
+2000	2000-12-31	2000-12-31 23:59:59.99999
+2000	2000-12-31	2000-12-31 23:59:59.999999
+2001	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+DAYNAME(a)	CAST(a AS DATE)	a
+Sunday	2000-12-31	2000-12-31 23:59:59
+Sunday	2000-12-31	2000-12-31 23:59:59.9
+Sunday	2000-12-31	2000-12-31 23:59:59.99
+Sunday	2000-12-31	2000-12-31 23:59:59.999
+Sunday	2000-12-31	2000-12-31 23:59:59.9999
+Sunday	2000-12-31	2000-12-31 23:59:59.99999
+Sunday	2000-12-31	2000-12-31 23:59:59.999999
+Monday	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+MONTHNAME(a)	CAST(a AS DATE)	a
+December	2000-12-31	2000-12-31 23:59:59
+December	2000-12-31	2000-12-31 23:59:59.9
+December	2000-12-31	2000-12-31 23:59:59.99
+December	2000-12-31	2000-12-31 23:59:59.999
+December	2000-12-31	2000-12-31 23:59:59.9999
+December	2000-12-31	2000-12-31 23:59:59.99999
+December	2000-12-31	2000-12-31 23:59:59.999999
+January	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+LAST_DAY(a)	CAST(a AS DATE)	a
+2000-12-31	2000-12-31	2000-12-31 23:59:59
+2000-12-31	2000-12-31	2000-12-31 23:59:59.9
+2000-12-31	2000-12-31	2000-12-31 23:59:59.99
+2000-12-31	2000-12-31	2000-12-31 23:59:59.999
+2000-12-31	2000-12-31	2000-12-31 23:59:59.9999
+2000-12-31	2000-12-31	2000-12-31 23:59:59.99999
+2000-12-31	2000-12-31	2000-12-31 23:59:59.999999
+2000-12-31	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+TO_DAYS(a)	CAST(a AS DATE)	a
+730850	2000-12-31	2000-12-31 23:59:59
+730850	2000-12-31	2000-12-31 23:59:59.9
+730850	2000-12-31	2000-12-31 23:59:59.99
+730850	2000-12-31	2000-12-31 23:59:59.999
+730850	2000-12-31	2000-12-31 23:59:59.9999
+730850	2000-12-31	2000-12-31 23:59:59.99999
+730850	2000-12-31	2000-12-31 23:59:59.999999
+730851	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+DAYOFYEAR(a)	CAST(a AS DATE)	a
+366	2000-12-31	2000-12-31 23:59:59
+366	2000-12-31	2000-12-31 23:59:59.9
+366	2000-12-31	2000-12-31 23:59:59.99
+366	2000-12-31	2000-12-31 23:59:59.999
+366	2000-12-31	2000-12-31 23:59:59.9999
+366	2000-12-31	2000-12-31 23:59:59.99999
+366	2000-12-31	2000-12-31 23:59:59.999999
+1	2000-12-31	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+CREATE TABLE t1 (a VARCHAR(32));
+INSERT INTO t1 VALUES
+('2002-01-05 23:59:59'),
+('2002-01-05 23:59:59.999999'),
+('2002-01-05 23:59:59.9999999');
+SELECT YEARWEEK(a), a FROM t1;
+YEARWEEK(a)	a
+200152	2002-01-05 23:59:59
+200152	2002-01-05 23:59:59.999999
+200201	2002-01-05 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999'
+SELECT WEEK(a), a FROM t1;
+WEEK(a)	a
+0	2002-01-05 23:59:59
+0	2002-01-05 23:59:59.999999
+1	2002-01-05 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999'
+SELECT WEEKDAY(a), a FROM t1;
+WEEKDAY(a)	a
+5	2002-01-05 23:59:59
+5	2002-01-05 23:59:59.999999
+6	2002-01-05 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999'
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(32,9));
+INSERT INTO t1 VALUES
+(20020105235959),
+(20020105235959.999999),
+(20020105235959.9999999);
+SELECT YEARWEEK(a), a FROM t1;
+YEARWEEK(a)	a
+200152	20020105235959.000000000
+200152	20020105235959.999999000
+200201	20020105235959.999999900
+SELECT WEEK(a), a FROM t1;
+WEEK(a)	a
+0	20020105235959.000000000
+0	20020105235959.999999000
+1	20020105235959.999999900
+SELECT WEEKDAY(a), a FROM t1;
+WEEKDAY(a)	a
+5	20020105235959.000000000
+5	20020105235959.999999000
+6	20020105235959.999999900
+DROP TABLE t1;
+#
+# Functions with a single DATE input, conversion from DATETIME-in-DECIMAL
+#
+SELECT QUARTER(a), CAST(a AS DATE),  a FROM t1_datetime_in_decimal ORDER BY id;
+QUARTER(a)	CAST(a AS DATE)	a
+4	2000-12-31	20001231235959.0000000000
+4	2000-12-31	20001231235959.9000000000
+4	2000-12-31	20001231235959.9900000000
+4	2000-12-31	20001231235959.9990000000
+4	2000-12-31	20001231235959.9999000000
+4	2000-12-31	20001231235959.9999900000
+4	2000-12-31	20001231235959.9999990000
+1	2000-12-31	20001231235959.9999999000
+SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+DAY(a)	CAST(a AS DATE)	a
+31	2000-12-31	20001231235959.0000000000
+31	2000-12-31	20001231235959.9000000000
+31	2000-12-31	20001231235959.9900000000
+31	2000-12-31	20001231235959.9990000000
+31	2000-12-31	20001231235959.9999000000
+31	2000-12-31	20001231235959.9999900000
+31	2000-12-31	20001231235959.9999990000
+1	2000-12-31	20001231235959.9999999000
+SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+MONTH(a)	CAST(a AS DATE)	a
+12	2000-12-31	20001231235959.0000000000
+12	2000-12-31	20001231235959.9000000000
+12	2000-12-31	20001231235959.9900000000
+12	2000-12-31	20001231235959.9990000000
+12	2000-12-31	20001231235959.9999000000
+12	2000-12-31	20001231235959.9999900000
+12	2000-12-31	20001231235959.9999990000
+1	2000-12-31	20001231235959.9999999000
+SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+YEAR(a)	CAST(a AS DATE)	a
+2000	2000-12-31	20001231235959.0000000000
+2000	2000-12-31	20001231235959.9000000000
+2000	2000-12-31	20001231235959.9900000000
+2000	2000-12-31	20001231235959.9990000000
+2000	2000-12-31	20001231235959.9999000000
+2000	2000-12-31	20001231235959.9999900000
+2000	2000-12-31	20001231235959.9999990000
+2001	2000-12-31	20001231235959.9999999000
+SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+DAYNAME(a)	CAST(a AS DATE)	a
+Sunday	2000-12-31	20001231235959.0000000000
+Sunday	2000-12-31	20001231235959.9000000000
+Sunday	2000-12-31	20001231235959.9900000000
+Sunday	2000-12-31	20001231235959.9990000000
+Sunday	2000-12-31	20001231235959.9999000000
+Sunday	2000-12-31	20001231235959.9999900000
+Sunday	2000-12-31	20001231235959.9999990000
+Monday	2000-12-31	20001231235959.9999999000
+SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+MONTHNAME(a)	CAST(a AS DATE)	a
+December	2000-12-31	20001231235959.0000000000
+December	2000-12-31	20001231235959.9000000000
+December	2000-12-31	20001231235959.9900000000
+December	2000-12-31	20001231235959.9990000000
+December	2000-12-31	20001231235959.9999000000
+December	2000-12-31	20001231235959.9999900000
+December	2000-12-31	20001231235959.9999990000
+January	2000-12-31	20001231235959.9999999000
+SELECT YEARWEEK(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+YEARWEEK(a)	CAST(a AS DATE)	a
+200053	2000-12-31	20001231235959.0000000000
+200053	2000-12-31	20001231235959.9000000000
+200053	2000-12-31	20001231235959.9900000000
+200053	2000-12-31	20001231235959.9990000000
+200053	2000-12-31	20001231235959.9999000000
+200053	2000-12-31	20001231235959.9999900000
+200053	2000-12-31	20001231235959.9999990000
+200053	2000-12-31	20001231235959.9999999000
+SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+LAST_DAY(a)	CAST(a AS DATE)	a
+2000-12-31	2000-12-31	20001231235959.0000000000
+2000-12-31	2000-12-31	20001231235959.9000000000
+2000-12-31	2000-12-31	20001231235959.9900000000
+2000-12-31	2000-12-31	20001231235959.9990000000
+2000-12-31	2000-12-31	20001231235959.9999000000
+2000-12-31	2000-12-31	20001231235959.9999900000
+2000-12-31	2000-12-31	20001231235959.9999990000
+2000-12-31	2000-12-31	20001231235959.9999999000
+SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+TO_DAYS(a)	CAST(a AS DATE)	a
+730850	2000-12-31	20001231235959.0000000000
+730850	2000-12-31	20001231235959.9000000000
+730850	2000-12-31	20001231235959.9900000000
+730850	2000-12-31	20001231235959.9990000000
+730850	2000-12-31	20001231235959.9999000000
+730850	2000-12-31	20001231235959.9999900000
+730850	2000-12-31	20001231235959.9999990000
+730851	2000-12-31	20001231235959.9999999000
+SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+DAYOFYEAR(a)	CAST(a AS DATE)	a
+366	2000-12-31	20001231235959.0000000000
+366	2000-12-31	20001231235959.9000000000
+366	2000-12-31	20001231235959.9900000000
+366	2000-12-31	20001231235959.9990000000
+366	2000-12-31	20001231235959.9999000000
+366	2000-12-31	20001231235959.9999900000
+366	2000-12-31	20001231235959.9999990000
+1	2000-12-31	20001231235959.9999999000
+SELECT DAYOFMONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+DAYOFMONTH(a)	CAST(a AS DATE)	a
+31	2000-12-31	20001231235959.0000000000
+31	2000-12-31	20001231235959.9000000000
+31	2000-12-31	20001231235959.9900000000
+31	2000-12-31	20001231235959.9990000000
+31	2000-12-31	20001231235959.9999000000
+31	2000-12-31	20001231235959.9999900000
+31	2000-12-31	20001231235959.9999990000
+1	2000-12-31	20001231235959.9999999000
+#
+# Functions with a single DATETIME input, conversion from DATETIME-in-VARCHAR
+#
+SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+TO_SECONDS(a)	CAST(a AS DATETIME(6))	a
+63145526399	2000-12-31 23:59:59.000000	2000-12-31 23:59:59
+63145526399	2000-12-31 23:59:59.900000	2000-12-31 23:59:59.9
+63145526399	2000-12-31 23:59:59.990000	2000-12-31 23:59:59.99
+63145526399	2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999
+63145526399	2000-12-31 23:59:59.999900	2000-12-31 23:59:59.9999
+63145526399	2000-12-31 23:59:59.999990	2000-12-31 23:59:59.99999
+63145526399	2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
+63145526400	2001-01-01 00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SET time_zone='+00:00';
+SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+UNIX_TIMESTAMP(a)	CAST(a AS DATETIME(6))	a
+978307199.000000	2000-12-31 23:59:59.000000	2000-12-31 23:59:59
+978307199.900000	2000-12-31 23:59:59.900000	2000-12-31 23:59:59.9
+978307199.990000	2000-12-31 23:59:59.990000	2000-12-31 23:59:59.99
+978307199.999000	2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999
+978307199.999900	2000-12-31 23:59:59.999900	2000-12-31 23:59:59.9999
+978307199.999990	2000-12-31 23:59:59.999990	2000-12-31 23:59:59.99999
+978307199.999999	2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
+978307200.000000	2001-01-01 00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SET time_zone=DEFAULT;
+SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+CONVERT_TZ(a, '+00:00','+00:00')	CAST(a AS DATETIME(6))	a
+2000-12-31 23:59:59.000000	2000-12-31 23:59:59.000000	2000-12-31 23:59:59
+2000-12-31 23:59:59.900000	2000-12-31 23:59:59.900000	2000-12-31 23:59:59.9
+2000-12-31 23:59:59.990000	2000-12-31 23:59:59.990000	2000-12-31 23:59:59.99
+2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999
+2000-12-31 23:59:59.999900	2000-12-31 23:59:59.999900	2000-12-31 23:59:59.9999
+2000-12-31 23:59:59.999990	2000-12-31 23:59:59.999990	2000-12-31 23:59:59.99999
+2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
+2001-01-01 00:00:00.000000	2001-01-01 00:00:00.000000	2000-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+#
+# Functions with a single DATETIME input, conversion from DATETIME-in-DECIMAL
+#
+SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+TO_SECONDS(a)	CAST(a AS DATETIME(6))	a
+63145526399	2000-12-31 23:59:59.000000	20001231235959.0000000000
+63145526399	2000-12-31 23:59:59.900000	20001231235959.9000000000
+63145526399	2000-12-31 23:59:59.990000	20001231235959.9900000000
+63145526399	2000-12-31 23:59:59.999000	20001231235959.9990000000
+63145526399	2000-12-31 23:59:59.999900	20001231235959.9999000000
+63145526399	2000-12-31 23:59:59.999990	20001231235959.9999900000
+63145526399	2000-12-31 23:59:59.999999	20001231235959.9999990000
+63145526400	2001-01-01 00:00:00.000000	20001231235959.9999999000
+SET time_zone='+00:00';
+SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+UNIX_TIMESTAMP(a)	CAST(a AS DATETIME(6))	a
+978307199.000000	2000-12-31 23:59:59.000000	20001231235959.0000000000
+978307199.900000	2000-12-31 23:59:59.900000	20001231235959.9000000000
+978307199.990000	2000-12-31 23:59:59.990000	20001231235959.9900000000
+978307199.999000	2000-12-31 23:59:59.999000	20001231235959.9990000000
+978307199.999900	2000-12-31 23:59:59.999900	20001231235959.9999000000
+978307199.999990	2000-12-31 23:59:59.999990	20001231235959.9999900000
+978307199.999999	2000-12-31 23:59:59.999999	20001231235959.9999990000
+978307200.000000	2001-01-01 00:00:00.000000	20001231235959.9999999000
+SET time_zone=DEFAULT;
+SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+CONVERT_TZ(a, '+00:00','+00:00')	CAST(a AS DATETIME(6))	a
+2000-12-31 23:59:59.000000	2000-12-31 23:59:59.000000	20001231235959.0000000000
+2000-12-31 23:59:59.900000	2000-12-31 23:59:59.900000	20001231235959.9000000000
+2000-12-31 23:59:59.990000	2000-12-31 23:59:59.990000	20001231235959.9900000000
+2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999000	20001231235959.9990000000
+2000-12-31 23:59:59.999900	2000-12-31 23:59:59.999900	20001231235959.9999000000
+2000-12-31 23:59:59.999990	2000-12-31 23:59:59.999990	20001231235959.9999900000
+2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999	20001231235959.9999990000
+2001-01-01 00:00:00.000000	2001-01-01 00:00:00.000000	20001231235959.9999999000
+DROP TABLE t1_datetime_in_varchar;
+DROP TABLE t1_datetime_in_decimal;
+DROP TABLE t1_time_in_varchar;
+DROP TABLE t1_time_in_decimal;
+#
+# Functions that construct DATETIME
+#
+SET time_zone='+00:00';
+CREATE TABLE t1_unix_timestamp (id SERIAL, a DECIMAL(30,10));
+INSERT INTO t1_unix_timestamp (a) VALUES
+(980639999),
+(980639999.9),
+(980639999.999999),
+(980639999.9999999),
+(2147483647),
+(2147483647.9),
+(2147483647.999999),
+(2147483647.9999999);
+SELECT a, FROM_UNIXTIME(a) FROM t1_unix_timestamp ORDER BY id;
+a	FROM_UNIXTIME(a)
+980639999.0000000000	2001-01-27 23:59:59.000000
+980639999.9000000000	2001-01-27 23:59:59.900000
+980639999.9999990000	2001-01-27 23:59:59.999999
+980639999.9999999000	2001-01-28 00:00:00.000000
+2147483647.0000000000	2038-01-19 03:14:07.000000
+2147483647.9000000000	2038-01-19 03:14:07.900000
+2147483647.9999990000	2038-01-19 03:14:07.999999
+2147483647.9999999000	NULL
+DROP TABLE t1_unix_timestamp;
+SET time_zone=DEFAULT;
+#
+# Functions that construct TIME
+#
+CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_sec (a) VALUES
+(59),
+(59.9),
+(59.999999),
+(59.9999999),
+(3020398),
+(3020398.999999),
+(3020398.9999999),
+(3020399),
+(3020399.999999),
+(3020399.9999999),
+(9223372036854775807),
+(9223372036854775807.9),
+(9223372036854775807.999999),
+(9223372036854775807.9999999),
+(18446744073709551615),
+(18446744073709551615.9),
+(18446744073709551615.999999),
+(18446744073709551615.9999999);
+SELECT a, SEC_TO_TIME(a) FROM t1_sec ORDER BY id;
+a	SEC_TO_TIME(a)
+59.0000000000	00:00:59.000000
+59.9000000000	00:00:59.900000
+59.9999990000	00:00:59.999999
+59.9999999000	00:01:00.000000
+3020398.0000000000	838:59:58.000000
+3020398.9999990000	838:59:58.999999
+3020398.9999999000	838:59:59.000000
+3020399.0000000000	838:59:59.000000
+3020399.9999990000	838:59:59.999999
+3020399.9999999000	838:59:59.999999
+9223372036854775807.0000000000	838:59:59.999999
+9223372036854775807.9000000000	838:59:59.999999
+9223372036854775807.9999990000	838:59:59.999999
+9223372036854775807.9999999000	838:59:59.999999
+18446744073709551615.0000000000	838:59:59.999999
+18446744073709551615.9000000000	838:59:59.999999
+18446744073709551615.9999990000	838:59:59.999999
+18446744073709551615.9999999000	838:59:59.999999
+Warnings:
+Warning	1292	Truncated incorrect seconds value: '3020400'
+Warning	1292	Truncated incorrect seconds value: '9223372036854775807.0000000000'
+Warning	1292	Truncated incorrect seconds value: '9223372036854775807.9000000000'
+Warning	1292	Truncated incorrect seconds value: '9223372036854775807.9999990000'
+Warning	1292	Truncated incorrect seconds value: '9223372036854775807.9999999000'
+Warning	1292	Truncated incorrect seconds value: '18446744073709551615.0000000000'
+Warning	1292	Truncated incorrect seconds value: '18446744073709551615.9000000000'
+Warning	1292	Truncated incorrect seconds value: '18446744073709551615.9999990000'
+Warning	1292	Truncated incorrect seconds value: '18446744073709551615.9999999000'
+DROP TABLE t1_sec;
+CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_sec (a) VALUES
+(0),
+(0.9),
+(0.999999),
+(0.9999999);
+SELECT a, MAKETIME(0, 0, a) FROM t1_sec ORDER BY id;
+a	MAKETIME(0, 0, a)
+0.0000000000	00:00:00.000000
+0.9000000000	00:00:00.900000
+0.9999990000	00:00:00.999999
+0.9999999000	00:00:01.000000
+DROP TABLE t1_sec;
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+SELECT
+'----',
+a,
+DATE_FORMAT(a, '%Y') AS yyyy,
+DATE_FORMAT(a, '%Y-%m-%d') AS d,
+DATE_FORMAT(a, '%H:%i:%s') AS t0,
+DATE_FORMAT(a, '%H:%i:%s.%f') AS t6,
+DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0,
+DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6
+FROM t1;
+----	----
+a	2017-12-31 23:59:59
+yyyy	2017
+d	2017-12-31
+t0	23:59:59
+t6	23:59:59.000000
+dt0	2017-12-31 23:59:59
+dt6	2017-12-31 23:59:59.000000
+----	----
+a	2017-12-31 23:59:59.9
+yyyy	2017
+d	2017-12-31
+t0	23:59:59
+t6	23:59:59.900000
+dt0	2017-12-31 23:59:59
+dt6	2017-12-31 23:59:59.900000
+----	----
+a	2017-12-31 23:59:59.999999
+yyyy	2017
+d	2017-12-31
+t0	23:59:59
+t6	23:59:59.999999
+dt0	2017-12-31 23:59:59
+dt6	2017-12-31 23:59:59.999999
+----	----
+a	2017-12-31 23:59:59.9999999
+yyyy	2018
+d	2018-01-01
+t0	00:00:00
+t6	00:00:00.000000
+dt0	2018-01-01 00:00:00
+dt6	2018-01-01 00:00:00.000000
+Warnings:
+Level	Note
+Code	1292
+Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Level	Note
+Code	1292
+Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Level	Note
+Code	1292
+Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Level	Note
+Code	1292
+Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Level	Note
+Code	1292
+Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Level	Note
+Code	1292
+Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(32,9));
+INSERT INTO t1 VALUES
+(20171231235959),
+(20171231235959.9),
+(20171231235959.999999),
+(20171231235959.9999999);
+SELECT
+'----',
+a,
+DATE_FORMAT(a, '%Y') AS yyyy,
+DATE_FORMAT(a, '%Y-%m-%d') AS d,
+DATE_FORMAT(a, '%H:%i:%s') AS t0,
+DATE_FORMAT(a, '%H:%i:%s.%f') AS t6,
+DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0,
+DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6
+FROM t1;
+----	----
+a	20171231235959.000000000
+yyyy	2017
+d	2017-12-31
+t0	23:59:59
+t6	23:59:59.000000
+dt0	2017-12-31 23:59:59
+dt6	2017-12-31 23:59:59.000000
+----	----
+a	20171231235959.900000000
+yyyy	2017
+d	2017-12-31
+t0	23:59:59
+t6	23:59:59.900000
+dt0	2017-12-31 23:59:59
+dt6	2017-12-31 23:59:59.900000
+----	----
+a	20171231235959.999999000
+yyyy	2017
+d	2017-12-31
+t0	23:59:59
+t6	23:59:59.999999
+dt0	2017-12-31 23:59:59
+dt6	2017-12-31 23:59:59.999999
+----	----
+a	20171231235959.999999900
+yyyy	2018
+d	2018-01-01
+t0	00:00:00
+t6	00:00:00.000000
+dt0	2018-01-01 00:00:00
+dt6	2018-01-01 00:00:00.000000
+DROP TABLE t1;
+#
+# Functions with two temporal parameters that round nanoseconds in both parameters in MySQL
+#
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+SELECT TIMESTAMP(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+TIMESTAMP(t1.a, t2.a)	a	a
+2017-12-31 23:59:59.000000	2017-12-31 23:59:59	00:00:00
+2017-12-31 23:59:59.900000	2017-12-31 23:59:59	00:00:00.9
+2017-12-31 23:59:59.999999	2017-12-31 23:59:59	00:00:00.999999
+2018-01-01 00:00:00.000000	2017-12-31 23:59:59	00:00:00.9999999
+2017-12-31 23:59:59.900000	2017-12-31 23:59:59.9	00:00:00
+2018-01-01 00:00:00.800000	2017-12-31 23:59:59.9	00:00:00.9
+2018-01-01 00:00:00.899999	2017-12-31 23:59:59.9	00:00:00.999999
+2018-01-01 00:00:00.900000	2017-12-31 23:59:59.9	00:00:00.9999999
+2017-12-31 23:59:59.999999	2017-12-31 23:59:59.999999	00:00:00
+2018-01-01 00:00:00.899999	2017-12-31 23:59:59.999999	00:00:00.9
+2018-01-01 00:00:00.999998	2017-12-31 23:59:59.999999	00:00:00.999999
+2018-01-01 00:00:00.999999	2017-12-31 23:59:59.999999	00:00:00.9999999
+2018-01-01 00:00:00.000000	2017-12-31 23:59:59.9999999	00:00:00
+2018-01-01 00:00:00.900000	2017-12-31 23:59:59.9999999	00:00:00.9
+2018-01-01 00:00:00.999999	2017-12-31 23:59:59.9999999	00:00:00.999999
+2018-01-01 00:00:01.000000	2017-12-31 23:59:59.9999999	00:00:00.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+SELECT ADDTIME(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+ADDTIME(t1.a, t2.a)	a	a
+2017-12-31 23:59:59	2017-12-31 23:59:59	00:00:00
+2017-12-31 23:59:59.900000	2017-12-31 23:59:59	00:00:00.9
+2017-12-31 23:59:59.999999	2017-12-31 23:59:59	00:00:00.999999
+2018-01-01 00:00:00	2017-12-31 23:59:59	00:00:00.9999999
+2017-12-31 23:59:59.900000	2017-12-31 23:59:59.9	00:00:00
+2018-01-01 00:00:00.800000	2017-12-31 23:59:59.9	00:00:00.9
+2018-01-01 00:00:00.899999	2017-12-31 23:59:59.9	00:00:00.999999
+2018-01-01 00:00:00.900000	2017-12-31 23:59:59.9	00:00:00.9999999
+2017-12-31 23:59:59.999999	2017-12-31 23:59:59.999999	00:00:00
+2018-01-01 00:00:00.899999	2017-12-31 23:59:59.999999	00:00:00.9
+2018-01-01 00:00:00.999998	2017-12-31 23:59:59.999999	00:00:00.999999
+2018-01-01 00:00:00.999999	2017-12-31 23:59:59.999999	00:00:00.9999999
+2018-01-01 00:00:00	2017-12-31 23:59:59.9999999	00:00:00
+2018-01-01 00:00:00.900000	2017-12-31 23:59:59.9999999	00:00:00.9
+2018-01-01 00:00:00.999999	2017-12-31 23:59:59.9999999	00:00:00.999999
+2018-01-01 00:00:01	2017-12-31 23:59:59.9999999	00:00:00.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('23:59:59'),
+('23:59:59.9'),
+('23:59:59.999999'),
+('23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+TIMEDIFF(t1.a, t2.a)	a	a
+23:59:59.000000	23:59:59	00:00:00
+23:59:58.100000	23:59:59	00:00:00.9
+23:59:58.000001	23:59:59	00:00:00.999999
+23:59:58.000000	23:59:59	00:00:00.9999999
+23:59:59.900000	23:59:59.9	00:00:00
+23:59:59.000000	23:59:59.9	00:00:00.9
+23:59:58.900001	23:59:59.9	00:00:00.999999
+23:59:58.900000	23:59:59.9	00:00:00.9999999
+23:59:59.999999	23:59:59.999999	00:00:00
+23:59:59.099999	23:59:59.999999	00:00:00.9
+23:59:59.000000	23:59:59.999999	00:00:00.999999
+23:59:58.999999	23:59:59.999999	00:00:00.9999999
+24:00:00.000000	23:59:59.9999999	00:00:00
+23:59:59.100000	23:59:59.9999999	00:00:00.9
+23:59:59.000001	23:59:59.9999999	00:00:00.999999
+23:59:59.000000	23:59:59.9999999	00:00:00.9999999
+Warnings:
+Note	1292	Truncated incorrect time value: '23:59:59.9999999'
+Note	1292	Truncated incorrect time value: '23:59:59.9999999'
+Note	1292	Truncated incorrect time value: '23:59:59.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '23:59:59.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2001-12-31 23:59:59'),
+('2001-12-31 23:59:59.9'),
+('2001-12-31 23:59:59.999999'),
+('2001-12-31 23:59:59.9999999');
+INSERT INTO t2 VALUES
+('2001-12-31 23:59:59'),
+('2001-12-31 23:59:59.9'),
+('2001-12-31 23:59:59.999999'),
+('2001-12-31 23:59:59.9999999');
+SELECT TIMESTAMPDIFF(MICROSECOND,t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+TIMESTAMPDIFF(MICROSECOND,t1.a, t2.a)	a	a
+0	2001-12-31 23:59:59	2001-12-31 23:59:59
+900000	2001-12-31 23:59:59	2001-12-31 23:59:59.9
+999999	2001-12-31 23:59:59	2001-12-31 23:59:59.999999
+1000000	2001-12-31 23:59:59	2001-12-31 23:59:59.9999999
+-900000	2001-12-31 23:59:59.9	2001-12-31 23:59:59
+0	2001-12-31 23:59:59.9	2001-12-31 23:59:59.9
+99999	2001-12-31 23:59:59.9	2001-12-31 23:59:59.999999
+100000	2001-12-31 23:59:59.9	2001-12-31 23:59:59.9999999
+-999999	2001-12-31 23:59:59.999999	2001-12-31 23:59:59
+-99999	2001-12-31 23:59:59.999999	2001-12-31 23:59:59.9
+0	2001-12-31 23:59:59.999999	2001-12-31 23:59:59.999999
+1	2001-12-31 23:59:59.999999	2001-12-31 23:59:59.9999999
+-1000000	2001-12-31 23:59:59.9999999	2001-12-31 23:59:59
+-100000	2001-12-31 23:59:59.9999999	2001-12-31 23:59:59.9
+-1	2001-12-31 23:59:59.9999999	2001-12-31 23:59:59.999999
+0	2001-12-31 23:59:59.9999999	2001-12-31 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('23:59:59'),
+('23:59:59.9'),
+('23:59:59.999999'),
+('23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+TIMEDIFF(t1.a, t2.a)	a	a
+23:59:59.000000	23:59:59	00:00:00
+23:59:58.100000	23:59:59	00:00:00.9
+23:59:58.000001	23:59:59	00:00:00.999999
+23:59:58.000000	23:59:59	00:00:00.9999999
+23:59:59.900000	23:59:59.9	00:00:00
+23:59:59.000000	23:59:59.9	00:00:00.9
+23:59:58.900001	23:59:59.9	00:00:00.999999
+23:59:58.900000	23:59:59.9	00:00:00.9999999
+23:59:59.999999	23:59:59.999999	00:00:00
+23:59:59.099999	23:59:59.999999	00:00:00.9
+23:59:59.000000	23:59:59.999999	00:00:00.999999
+23:59:58.999999	23:59:59.999999	00:00:00.9999999
+24:00:00.000000	23:59:59.9999999	00:00:00
+23:59:59.100000	23:59:59.9999999	00:00:00.9
+23:59:59.000001	23:59:59.9999999	00:00:00.999999
+23:59:59.000000	23:59:59.9999999	00:00:00.9999999
+Warnings:
+Note	1292	Truncated incorrect time value: '23:59:59.9999999'
+Note	1292	Truncated incorrect time value: '23:59:59.9999999'
+Note	1292	Truncated incorrect time value: '23:59:59.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+Note	1292	Truncated incorrect time value: '23:59:59.9999999'
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+DROP TABLE t1, t2;
+#
+# STR_TO_DATE behaviour is questionable in MySQL 5.6 (MySQL Bug #92474)
+#
+# It truncates nanoseconds, but this may change in the future.
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+SELECT
+a,
+STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s') AS c0,
+STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s.%f') AS c6
+FROM t1;
+a	c0	c6
+2017-12-31 23:59:59	2017-12-31 23:59:59	2017-12-31 23:59:59.000000
+2017-12-31 23:59:59.9	2017-12-31 23:59:59	2017-12-31 23:59:59.900000
+2017-12-31 23:59:59.999999	2017-12-31 23:59:59	2017-12-31 23:59:59.999999
+2017-12-31 23:59:59.9999999	2017-12-31 23:59:59	2017-12-31 23:59:59.999999
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9'
+Warning	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.999999'
+Warning	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Warning	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+DROP TABLE t1;
+#
+# DATE_ADD behaviour is questionable in MySQL 5.6 (MySQL Bug#92473)
+# It rounds nanoseconds in the first argument, but truncates nanoseconds in the second argument.
+# This may change in the future, to round both arguments.
+#
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+CREATE TABLE t2 (b DECIMAL(32,9));
+INSERT INTO t2 VALUES
+(0),
+(0.9),
+(0.999999),
+(0.9999999);
+SELECT a, b, DATE_ADD(a, INTERVAL b SECOND) FROM t1,t2 ORDER BY a,b;
+a	b	DATE_ADD(a, INTERVAL b SECOND)
+2017-12-31 23:59:59	0.000000000	2017-12-31 23:59:59.000000
+2017-12-31 23:59:59	0.900000000	2017-12-31 23:59:59.900000
+2017-12-31 23:59:59	0.999999000	2017-12-31 23:59:59.999999
+2017-12-31 23:59:59	0.999999900	2017-12-31 23:59:59.999999
+2017-12-31 23:59:59.9	0.000000000	2017-12-31 23:59:59.900000
+2017-12-31 23:59:59.9	0.900000000	2018-01-01 00:00:00.800000
+2017-12-31 23:59:59.9	0.999999000	2018-01-01 00:00:00.899999
+2017-12-31 23:59:59.9	0.999999900	2018-01-01 00:00:00.899999
+2017-12-31 23:59:59.999999	0.000000000	2017-12-31 23:59:59.999999
+2017-12-31 23:59:59.999999	0.900000000	2018-01-01 00:00:00.899999
+2017-12-31 23:59:59.999999	0.999999000	2018-01-01 00:00:00.999998
+2017-12-31 23:59:59.999999	0.999999900	2018-01-01 00:00:00.999998
+2017-12-31 23:59:59.9999999	0.000000000	2018-01-01 00:00:00.000000
+2017-12-31 23:59:59.9999999	0.900000000	2018-01-01 00:00:00.900000
+2017-12-31 23:59:59.9999999	0.999999000	2018-01-01 00:00:00.999999
+2017-12-31 23:59:59.9999999	0.999999900	2018-01-01 00:00:00.999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
+DROP TABLE t1, t2;
diff --git a/mysql-test/main/func_time_round.test b/mysql-test/main/func_time_round.test
new file mode 100644
index 00000000000..12d3a50a10f
--- /dev/null
+++ b/mysql-test/main/func_time_round.test
@@ -0,0 +1,461 @@
+SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
+SET @default_sql_mode=@@sql_mode;
+
+CREATE TABLE t1_datetime_in_varchar (id SERIAL, a VARCHAR(64));
+INSERT INTO t1_datetime_in_varchar (a) VALUES
+('2000-12-31 23:59:59'),
+('2000-12-31 23:59:59.9'),
+('2000-12-31 23:59:59.99'),
+('2000-12-31 23:59:59.999'),
+('2000-12-31 23:59:59.9999'),
+('2000-12-31 23:59:59.99999'),
+('2000-12-31 23:59:59.999999'),
+('2000-12-31 23:59:59.9999999');
+
+CREATE TABLE t1_datetime_in_decimal (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_datetime_in_decimal (a) VALUES
+(20001231235959),
+(20001231235959.9),
+(20001231235959.99),
+(20001231235959.999),
+(20001231235959.9999),
+(20001231235959.99999),
+(20001231235959.999999),
+(20001231235959.9999999);
+
+
+CREATE TABLE t1_time_in_varchar (id SERIAL, a VARCHAR(64));
+INSERT INTO t1_time_in_varchar (a) VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.99'),
+('00:00:00.999'),
+('00:00:00.9999'),
+('00:00:00.99999'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+INSERT INTO t1_time_in_varchar (a) VALUES
+('837:59:59.9999999'),
+('838:59:59'),
+('838:59:59.9'),
+('838:59:59.99'),
+('838:59:59.999'),
+('838:59:59.9999'),
+('838:59:59.99999'),
+('838:59:59.999999'),
+('838:59:59.9999999'),
+('839:59:59.9999999'),
+('87649414:59:59.999999'),
+('87649414:59:59.9999999'),
+('87649415:59:59.999999'),
+('87649415:59:59.9999999');
+
+
+CREATE TABLE t1_time_in_decimal (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_time_in_decimal (a) VALUES
+(0),
+(0.9),
+(0.99),
+(0.999),
+(0.9999),
+(0.99999),
+(0.999999),
+(0.9999999);
+INSERT INTO t1_time_in_decimal (a) VALUES
+(8375959.9999999),
+(8385959),
+(8385959.9),
+(8385959.99),
+(8385959.999),
+(8385959.9999),
+(8385959.99999),
+(8385959.999999),
+(8385959.9999999),
+(8395959.9999999),
+(876494145959.999999),
+(876494145959.9999999),
+(876494155959.999999),
+(876494155959.9999999);
+
+--echo #
+--echo # TIME: LEAST/GREATEST
+--echo #
+
+SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_varchar;
+SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_decimal;
+
+SELECT GREATEST(TIME'00:00:00', '00:00:00.0000004');
+SELECT GREATEST(TIME'00:00:00', 0.0000004);
+
+SELECT GREATEST(TIME'00:00:00', '00:00:00.0000005');
+SELECT GREATEST(TIME'00:00:00', 0.0000005);
+
+
+--echo #
+--echo # Functions with a single TIME input, conversion from DATETIME-in-VARCHAR
+--echo #
+
+SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single TIME input, conversion from DATETIME-in-DECIMAL
+--echo #
+
+
+SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single TIME interval input, conversion from TIME-interval-in-VARCHAR
+--echo #
+
+SELECT
+  EXTRACT(DAY FROM a),
+  EXTRACT(HOUR FROM a),
+  EXTRACT(MINUTE FROM a),
+  EXTRACT(SECOND FROM a),
+  EXTRACT(MICROSECOND FROM a),
+  CAST(a AS INTERVAL DAY_SECOND(6)),
+  a
+FROM t1_time_in_varchar ORDER BY id;
+
+SELECT
+  TIME_TO_SEC(a),
+  CAST(a AS TIME(6)),
+  a
+FROM t1_time_in_varchar ORDER BY id;
+
+--echo #
+--echo # Functions with a single TIME interval input, conversion from TIME-interval-in-DECIMAL
+--echo #
+
+SELECT
+  EXTRACT(DAY FROM a),
+  EXTRACT(HOUR FROM a),
+  EXTRACT(MINUTE FROM a),
+  EXTRACT(SECOND FROM a),
+  EXTRACT(MICROSECOND FROM a),
+  CAST(a AS INTERVAL DAY_SECOND(6)),
+  a
+FROM t1_time_in_decimal ORDER BY id;
+
+SELECT
+  TIME_TO_SEC(a),
+  CAST(a AS TIME(6)),
+  a
+FROM t1_time_in_decimal ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single DATE input, conversion from DATETIME-in-VARCHAR
+--echo #
+
+SELECT QUARTER(a), CAST(a AS DATE),  a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+
+
+CREATE TABLE t1 (a VARCHAR(32));
+INSERT INTO t1 VALUES
+('2002-01-05 23:59:59'),
+('2002-01-05 23:59:59.999999'),
+('2002-01-05 23:59:59.9999999');
+SELECT YEARWEEK(a), a FROM t1;
+SELECT WEEK(a), a FROM t1;
+SELECT WEEKDAY(a), a FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(32,9));
+INSERT INTO t1 VALUES
+(20020105235959),
+(20020105235959.999999),
+(20020105235959.9999999);
+SELECT YEARWEEK(a), a FROM t1;
+SELECT WEEK(a), a FROM t1;
+SELECT WEEKDAY(a), a FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Functions with a single DATE input, conversion from DATETIME-in-DECIMAL
+--echo #
+
+SELECT QUARTER(a), CAST(a AS DATE),  a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT YEARWEEK(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT DAYOFMONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single DATETIME input, conversion from DATETIME-in-VARCHAR
+--echo #
+
+SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SET time_zone='+00:00';
+SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SET time_zone=DEFAULT;
+
+SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single DATETIME input, conversion from DATETIME-in-DECIMAL
+--echo #
+
+SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SET time_zone='+00:00';
+SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SET time_zone=DEFAULT;
+
+SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+DROP TABLE t1_datetime_in_varchar;
+DROP TABLE t1_datetime_in_decimal;
+DROP TABLE t1_time_in_varchar;
+DROP TABLE t1_time_in_decimal;
+
+
+--echo #
+--echo # Functions that construct DATETIME
+--echo #
+
+SET time_zone='+00:00';
+CREATE TABLE t1_unix_timestamp (id SERIAL, a DECIMAL(30,10));
+INSERT INTO t1_unix_timestamp (a) VALUES
+(980639999),
+(980639999.9),
+(980639999.999999),
+(980639999.9999999),
+(2147483647),
+(2147483647.9),
+(2147483647.999999),
+(2147483647.9999999);
+SELECT a, FROM_UNIXTIME(a) FROM t1_unix_timestamp ORDER BY id;
+DROP TABLE t1_unix_timestamp;
+SET time_zone=DEFAULT;
+
+
+--echo #
+--echo # Functions that construct TIME
+--echo #
+
+CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_sec (a) VALUES
+(59),
+(59.9),
+(59.999999),
+(59.9999999),
+(3020398),
+(3020398.999999),
+(3020398.9999999),
+(3020399),
+(3020399.999999),
+(3020399.9999999),
+(9223372036854775807),
+(9223372036854775807.9),
+(9223372036854775807.999999),
+(9223372036854775807.9999999),
+(18446744073709551615),
+(18446744073709551615.9),
+(18446744073709551615.999999),
+(18446744073709551615.9999999);
+SELECT a, SEC_TO_TIME(a) FROM t1_sec ORDER BY id;
+DROP TABLE t1_sec;
+
+
+CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_sec (a) VALUES
+(0),
+(0.9),
+(0.999999),
+(0.9999999);
+SELECT a, MAKETIME(0, 0, a) FROM t1_sec ORDER BY id;
+DROP TABLE t1_sec;
+
+
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+--vertical_results
+SELECT
+  '----',
+  a,
+  DATE_FORMAT(a, '%Y') AS yyyy,
+  DATE_FORMAT(a, '%Y-%m-%d') AS d,
+  DATE_FORMAT(a, '%H:%i:%s') AS t0,
+  DATE_FORMAT(a, '%H:%i:%s.%f') AS t6,
+  DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0,
+  DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6
+FROM t1;
+--horizontal_results
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(32,9));
+INSERT INTO t1 VALUES
+(20171231235959),
+(20171231235959.9),
+(20171231235959.999999),
+(20171231235959.9999999);
+--vertical_results
+SELECT
+  '----',
+  a,
+  DATE_FORMAT(a, '%Y') AS yyyy,
+  DATE_FORMAT(a, '%Y-%m-%d') AS d,
+  DATE_FORMAT(a, '%H:%i:%s') AS t0,
+  DATE_FORMAT(a, '%H:%i:%s.%f') AS t6,
+  DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0,
+  DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6
+FROM t1;
+--horizontal_results
+DROP TABLE t1;
+
+
+--echo #
+--echo # Functions with two temporal parameters that round nanoseconds in both parameters in MySQL
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+
+SELECT TIMESTAMP(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+SELECT ADDTIME(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+
+DROP TABLE t1, t2;
+
+
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('23:59:59'),
+('23:59:59.9'),
+('23:59:59.999999'),
+('23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+DROP TABLE t1, t2;
+
+
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2001-12-31 23:59:59'),
+('2001-12-31 23:59:59.9'),
+('2001-12-31 23:59:59.999999'),
+('2001-12-31 23:59:59.9999999');
+INSERT INTO t2 VALUES
+('2001-12-31 23:59:59'),
+('2001-12-31 23:59:59.9'),
+('2001-12-31 23:59:59.999999'),
+('2001-12-31 23:59:59.9999999');
+SELECT TIMESTAMPDIFF(MICROSECOND,t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('23:59:59'),
+('23:59:59.9'),
+('23:59:59.999999'),
+('23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+DROP TABLE t1, t2;
+
+
+--echo #
+--echo # STR_TO_DATE behaviour is questionable in MySQL 5.6 (MySQL Bug #92474)
+--echo #
+
+--echo # It truncates nanoseconds, but this may change in the future.
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+
+SELECT
+  a,
+  STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s') AS c0,
+  STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s.%f') AS c6
+FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # DATE_ADD behaviour is questionable in MySQL 5.6 (MySQL Bug#92473)
+--echo # It rounds nanoseconds in the first argument, but truncates nanoseconds in the second argument.
+--echo # This may change in the future, to round both arguments.
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+CREATE TABLE t2 (b DECIMAL(32,9));
+INSERT INTO t2 VALUES
+(0),
+(0.9),
+(0.999999),
+(0.9999999);
+SELECT a, b, DATE_ADD(a, INTERVAL b SECOND) FROM t1,t2 ORDER BY a,b;
+DROP TABLE t1, t2;
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index b6e331a0382..4a39eea6115 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -664,7 +664,7 @@ proc	body	longblob
 proc	definer	char(141)
 proc	created	timestamp
 proc	modified	timestamp
-proc	sql_mode	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT')
+proc	sql_mode	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL')
 proc	comment	text
 proc	character_set_client	char(32)
 proc	collation_connection	char(32)
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index 5ee85eee7de..cf25f377142 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -1209,7 +1209,8 @@ The following specify which files/extra groups are read (specified before remain
  ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, 
  NO_AUTO_CREATE_USER, HIGH_NOT_PRECEDENCE, 
  NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH, 
- EMPTY_STRING_IS_NULL, SIMULTANEOUS_ASSIGNMENT
+ EMPTY_STRING_IS_NULL, SIMULTANEOUS_ASSIGNMENT, 
+ TIME_ROUND_FRACTIONAL
  --sql-safe-updates  If set to 1, UPDATEs and DELETEs need either a key in the
  WHERE clause, or a LIMIT clause, or else they will
  aborted. Prevents the common mistake of accidentally
diff --git a/mysql-test/main/sql_mode.result b/mysql-test/main/sql_mode.result
index 238bae2efd8..25a90703bf5 100644
--- a/mysql-test/main/sql_mode.result
+++ b/mysql-test/main/sql_mode.result
@@ -476,10 +476,14 @@ select @@sql_mode;
 @@sql_mode
 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI
 set sql_mode=2147483648*2*2*2;
-ERROR 42000: Variable 'sql_mode' can't be set to the value of '17179869184'
 select @@sql_mode;
 @@sql_mode
-REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI
+TIME_ROUND_FRACTIONAL
+set sql_mode=2147483648*2*2*2*2;
+ERROR 42000: Variable 'sql_mode' can't be set to the value of '34359738368'
+select @@sql_mode;
+@@sql_mode
+TIME_ROUND_FRACTIONAL
 set sql_mode=PAD_CHAR_TO_FULL_LENGTH;
 create table t1 (a int auto_increment primary key, b char(5));
 insert into t1 (b) values('a'),('b\t'),('c ');
diff --git a/mysql-test/main/sql_mode.test b/mysql-test/main/sql_mode.test
index 8cf50f73f6f..97f5cf42791 100644
--- a/mysql-test/main/sql_mode.test
+++ b/mysql-test/main/sql_mode.test
@@ -263,8 +263,10 @@ set sql_mode=4194304;
 select @@sql_mode;
 set sql_mode=16384+(65536*4);
 select @@sql_mode;
+set sql_mode=2147483648*2*2*2;
+select @@sql_mode;
 --error 1231
-set sql_mode=2147483648*2*2*2; # that mode does not exist
+set sql_mode=2147483648*2*2*2*2; # that mode does not exist
 select @@sql_mode;
 
 #
diff --git a/mysql-test/main/system_mysql_db.result b/mysql-test/main/system_mysql_db.result
index a4c414888ca..bdc1df85131 100644
--- a/mysql-test/main/system_mysql_db.result
+++ b/mysql-test/main/system_mysql_db.result
@@ -191,7 +191,7 @@ proc	CREATE TABLE `proc` (
   `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NOT NULL DEFAULT '',
+  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
   `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
   `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
   `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -217,7 +217,7 @@ event	CREATE TABLE `event` (
   `ends` datetime DEFAULT NULL,
   `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
   `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
-  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NOT NULL DEFAULT '',
+  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
   `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `originator` int(10) unsigned NOT NULL,
   `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff --git a/mysql-test/main/system_mysql_db_fix40123.result b/mysql-test/main/system_mysql_db_fix40123.result
index d76a2ef923e..abb6fbb38e1 100644
--- a/mysql-test/main/system_mysql_db_fix40123.result
+++ b/mysql-test/main/system_mysql_db_fix40123.result
@@ -192,7 +192,7 @@ proc	CREATE TABLE `proc` (
   `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NOT NULL DEFAULT '',
+  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
   `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
   `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
   `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -218,7 +218,7 @@ event	CREATE TABLE `event` (
   `ends` datetime DEFAULT NULL,
   `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
   `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
-  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NOT NULL DEFAULT '',
+  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
   `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `originator` int(10) unsigned NOT NULL,
   `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff --git a/mysql-test/main/system_mysql_db_fix50030.result b/mysql-test/main/system_mysql_db_fix50030.result
index fdc708db313..b3a0caa282f 100644
--- a/mysql-test/main/system_mysql_db_fix50030.result
+++ b/mysql-test/main/system_mysql_db_fix50030.result
@@ -192,7 +192,7 @@ proc	CREATE TABLE `proc` (
   `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NOT NULL DEFAULT '',
+  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
   `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
   `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
   `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -218,7 +218,7 @@ event	CREATE TABLE `event` (
   `ends` datetime DEFAULT NULL,
   `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
   `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
-  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NOT NULL DEFAULT '',
+  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
   `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `originator` int(10) unsigned NOT NULL,
   `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff --git a/mysql-test/main/system_mysql_db_fix50117.result b/mysql-test/main/system_mysql_db_fix50117.result
index d76a2ef923e..abb6fbb38e1 100644
--- a/mysql-test/main/system_mysql_db_fix50117.result
+++ b/mysql-test/main/system_mysql_db_fix50117.result
@@ -192,7 +192,7 @@ proc	CREATE TABLE `proc` (
   `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NOT NULL DEFAULT '',
+  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
   `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
   `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
   `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -218,7 +218,7 @@ event	CREATE TABLE `event` (
   `ends` datetime DEFAULT NULL,
   `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
   `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
-  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NOT NULL DEFAULT '',
+  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
   `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
   `originator` int(10) unsigned NOT NULL,
   `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff --git a/mysql-test/main/type_date_round.result b/mysql-test/main/type_date_round.result
new file mode 100644
index 00000000000..0da78c6afe5
--- /dev/null
+++ b/mysql-test/main/type_date_round.result
@@ -0,0 +1,174 @@
+SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
+SET @default_sql_mode=@@sql_mode;
+#
+# DATE: SET
+#
+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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SELECT a FROM t1;
+a
+2000-12-31
+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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+Note	1265	Data truncated for column 'a' at row 2
+SELECT a FROM t1;
+a
+2000-12-31
+2000-12-31
+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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+Note	1265	Data truncated for column 'a' at row 2
+SELECT a FROM t1;
+a
+2000-12-31
+2000-12-31
+DROP TABLE t1;
+#
+# DATE: ALTER
+#
+CREATE TABLE t1 (a DATETIME(4));
+INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
+ALTER TABLE t1 MODIFY a DATE;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SELECT a FROM t1;
+a
+2000-12-31
+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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+Note	1265	Data truncated for column 'a' at row 2
+SELECT a FROM t1;
+a
+2000-12-31
+2000-12-31
+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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+Note	1265	Data truncated for column 'a' at row 2
+SELECT a FROM t1;
+a
+2000-12-31
+2000-12-31
+DROP TABLE t1;
+#
+# DATE: CAST
+#
+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;
+a	CAST(a AS DATE)
+2000-12-31 23:59:59.9999	2000-12-31
+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 DATE) FROM t1;
+a	CAST(a AS DATE)
+2000-12-31 23:59:59.9999	2000-12-31
+2000-12-31 23:59:59.9999999	2000-12-31
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+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 DATE) FROM t1;
+a	CAST(a AS DATE)
+20001231235959.9999000000	2000-12-31
+20001231235959.9999999000	2000-12-31
+DROP TABLE t1;
+#
+# Equal field propagation
+#
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES (20010101);
+INSERT INTO t1 VALUES (20010102);
+SELECT * FROM t1 WHERE a= 20010101235959.9999999;
+a
+2001-01-02
+SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999';
+a
+2001-01-02
+Warnings:
+Note	1292	Truncated incorrect datetime value: '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';
+a
+2001-01-02
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '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';
+a
+2001-01-02
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.9999999';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-02'
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-02'
+DROP TABLE t1;
+#
+# Comparing non-temporal to DATE
+#
+# Although conversion from non-temporal to DATE (e.g. on SET) does not round,
+# comparison between non-temporal to DATE is performed as DATETIME.
+# 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';
+a
+2001-01-01 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+SELECT * FROM t1 WHERE CONCAT(a)=DATE'2001-01-02';
+a
+2001-01-01 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-02';
+a
+2001-01-01 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(32,7));
+INSERT t1 VALUES (20010101235959.9999999);
+SELECT * FROM t1 WHERE a=DATE'2001-01-02';
+a
+20010101235959.9999999
+SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-02';
+a
+20010101235959.9999999
+DROP TABLE t1;
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;
diff --git a/mysql-test/main/type_datetime_round.result b/mysql-test/main/type_datetime_round.result
new file mode 100644
index 00000000000..c6584223268
--- /dev/null
+++ b/mysql-test/main/type_datetime_round.result
@@ -0,0 +1,205 @@
+SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
+SET @default_sql_mode=@@sql_mode;
+#
+# DATETIME: SET
+#
+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;
+a
+2001-01-01 00:00:00.000
+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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 2
+SELECT a FROM t1;
+a
+2001-01-01 00:00:00.000
+2001-01-01 00:00:00.000
+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;
+a
+2001-01-01 00:00:00.000
+2001-01-01 00:00:00.000
+DROP TABLE t1;
+#
+# DATETIME: ALTER
+#
+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;
+a
+2001-01-01 00:00:00.000
+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);
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT a FROM t1;
+a
+2001-01-01 00:00:00.000
+2001-01-01 00:00:00.000
+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;
+a
+2001-01-01 00:00:00.000
+2001-01-01 00:00:00.000
+DROP TABLE t1;
+#
+# Corner case:
+# ALTER DATETIME to a shorter DATETIME
+# All values round, maximum possible value truncates.
+#
+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);
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 4
+SELECT * FROM t1;
+ID	a	comment
+0	9999-12-30 23:59:59.00000	Should round
+1	9999-12-31 23:00:00.00000	Should round
+2	9999-12-31 23:59:59.00000	Should round
+3	9999-12-31 23:59:59.99999	Should truncate
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+#
+# NOW
+#
+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;
+id	a
+1	2011-01-01 00:00:00.0000
+2	2011-01-01 00:00:00.0000
+3	2011-01-01 00:00:00.0000
+DROP TABLE t1;
+SET timestamp=DEFAULT;
+SET time_zone=DEFAULT;
+#
+# DATETIME: CAST
+#
+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;
+a	CAST(a AS DATETIME(3))
+2000-12-31 23:59:59.9999	2001-01-01 00:00:00.000
+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;
+a	CAST(a AS DATETIME(3))
+2000-12-31 23:59:59.9999	2001-01-01 00:00:00.000
+2000-12-31 23:59:59.9999999	2001-01-01 00:00:00.000
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+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;
+a	CAST(a AS DATETIME(3))
+20001231235959.9999000000	2001-01-01 00:00:00.000
+20001231235959.9999999000	2001-01-01 00:00:00.000
+DROP TABLE t1;
+#
+# Equal field propagation
+#
+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;
+a
+2001-01-02 00:00:00.000000
+SELECT * FROM t1 WHERE a='20010101235959.9999999';
+a
+2001-01-02 00:00:00.000000
+Warnings:
+Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
+SELECT * FROM t1 WHERE a='20010101235959.9999999' AND a>='20010101235959.9999999';
+a
+2001-01-02 00:00:00.000000
+Warnings:
+Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
+Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
+SELECT * FROM t1 WHERE a='20010101235959.9999999' AND CONCAT(a)='2001-01-02 00:00:00.000000';
+a
+2001-01-02 00:00:00.000000
+Warnings:
+Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='20010101235959.9999999' AND a>='20010101235959.9999999';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
+Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-02 00:00:00'
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='20010101235959.9999999' AND CONCAT(a)='2001-01-02 00:00:00.000000';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-02 00:00:00'
+DROP TABLE t1;
+#
+# Comparing non-temporal to DATETIME
+#
+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';
+a
+2001-01-01 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+SELECT * FROM t1 WHERE CONCAT(a)=TIMESTAMP'2001-01-02 00:00:00';
+a
+2001-01-01 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+SELECT * FROM t1 WHERE COALESCE(a)=TIMESTAMP'2001-01-02 00:00:00';
+a
+2001-01-01 23:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+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';
+a
+20010101235959.9999999
+SELECT * FROM t1 WHERE COALESCE(a)=TIMESTAMP'2001-01-02 00:00:00';
+a
+20010101235959.9999999
+DROP TABLE t1;
+#
+# Literal corner case
+#
+SELECT TIMESTAMP'9999-12-31 23:59:59.999999';
+TIMESTAMP'9999-12-31 23:59:59.999999'
+9999-12-31 23:59:59.999999
+SELECT TIME'9999-12-31 23:59:59.9999999';
+ERROR HY000: Incorrect TIME value: '9999-12-31 23:59:59.9999999'
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';
diff --git a/mysql-test/main/type_time_round.result b/mysql-test/main/type_time_round.result
new file mode 100644
index 00000000000..31e97c888db
--- /dev/null
+++ b/mysql-test/main/type_time_round.result
@@ -0,0 +1,260 @@
+SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
+SET @default_sql_mode=@@sql_mode;
+#
+# TIME: SET
+#
+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;
+a
+00:00:01.000
+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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 2
+SELECT a FROM t1;
+a
+00:00:01.000
+00:00:01.000
+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;
+a
+00:00:01.000
+00:00:01.000
+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;
+a
+00:00:01.000
+00:00:01.000
+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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SELECT a FROM t1;
+a
+00:00:01.000000
+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;
+a
+00:00:01.000000
+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;
+a
+00:00:01.000000
+DROP TABLE t1;
+#
+# TIME: ALTER
+#
+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;
+a
+00:00:01.000
+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);
+Warnings:
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+SELECT a FROM t1;
+a
+00:00:01.000
+00:00:01.000
+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;
+a
+00:00:01.000
+00:00:01.000
+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);
+Warnings:
+Note	1265	Data truncated for column 'a' at row 2
+SELECT a FROM t1;
+a
+00:00:01.000
+00:00:01.000
+DROP TABLE t1;
+#
+# TIME: CAST
+#
+CREATE TABLE t1 (a TIME(4));
+INSERT INTO t1 VALUES('00:00:00.9999');
+SELECT a, CAST(a AS TIME(3)) FROM t1;
+a	CAST(a AS TIME(3))
+00:00:00.9999	00:00:01.000
+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;
+a	CAST(a AS TIME(3))
+00:00:00.9999	00:00:01.000
+00:00:00.9999999	00:00:01.000
+Warnings:
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+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;
+a	CAST(a AS TIME(3))
+0.9999000000	00:00:01.000
+0.9999999000	00:00:01.000
+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;
+a	CAST(a AS TIME(3))
+0.9999	00:00:01.000
+0.9999999	00:00:01.000
+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;
+a	CAST(a AS TIME(6))
+00:00:00.9999999	00:00:01.000000
+Warnings:
+Note	1292	Truncated incorrect time value: '00:00:00.9999999'
+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;
+a	CAST(a AS TIME(6))
+0.9999999000	00:00:01.000000
+DROP TABLE t1;
+CREATE TABLE t1 (a DOUBLE);
+INSERT INTO t1 VALUES(0.9999999);
+SELECT a, CAST(a AS TIME(6)) FROM t1;
+a	CAST(a AS TIME(6))
+0.9999999	00:00:01.000000
+DROP TABLE t1;
+#
+# NOW
+#
+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));
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+INSERT INTO t1 (a) VALUES (CURRENT_TIMESTAMP(6));
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+INSERT INTO t1 (a) VALUES (CURRENT_TIME(6));
+SELECT * FROM t1;
+id	a
+1	24:00:00.0000
+2	24:00:00.0000
+3	24:00:00.0000
+DROP TABLE t1;
+SET timestamp=DEFAULT;
+SET time_zone=DEFAULT;
+#
+# Equal field propagation
+#
+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;
+a
+00:00:01.000000
+SELECT * FROM t1 WHERE a='0.9999999';
+a
+00:00:01.000000
+Warnings:
+Note	1292	Truncated incorrect time value: '0.9999999'
+SELECT * FROM t1 WHERE a='0.9999999' AND a>='0.9999999';
+a
+00:00:01.000000
+Warnings:
+Note	1292	Truncated incorrect time value: '0.9999999'
+Note	1292	Truncated incorrect time value: '0.9999999'
+SELECT * FROM t1 WHERE a='0.9999999' AND CONCAT(a)='00:00:01.000000';
+a
+00:00:01.000000
+Warnings:
+Note	1292	Truncated incorrect time value: '0.9999999'
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0.9999999' AND a>='0.9999999';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1292	Truncated incorrect time value: '0.9999999'
+Note	1292	Truncated incorrect time value: '0.9999999'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:01'
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0.9999999' AND CONCAT(a)='00:00:01.000000';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1292	Truncated incorrect time value: '0.9999999'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:01'
+DROP TABLE t1;
+#
+# Comparing non-temporal to TIME
+#
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT t1 VALUES ('22:59:59.9999999');
+SELECT * FROM t1 WHERE a=TIME'23:00:00';
+a
+22:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect time value: '22:59:59.9999999'
+SELECT * FROM t1 WHERE CONCAT(a)=TIME'23:00:00';
+a
+22:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect time value: '22:59:59.9999999'
+SELECT * FROM t1 WHERE COALESCE(a)=TIME'23:00:00';
+a
+22:59:59.9999999
+Warnings:
+Note	1292	Truncated incorrect time value: '22:59:59.9999999'
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(32,7));
+INSERT t1 VALUES (225959.9999999);
+SELECT * FROM t1 WHERE a=TIME'23:00:00';
+a
+225959.9999999
+SELECT * FROM t1 WHERE COALESCE(a)=TIME'23:00:00';
+a
+225959.9999999
+DROP TABLE t1;
+#
+# Literal corner case
+#
+SELECT TIME'838:59:59.999999';
+TIME'838:59:59.999999'
+838:59:59.999999
+SELECT TIME'838:59:59.9999999';
+ERROR HY000: Incorrect TIME value: '838:59:59.9999999'
+SELECT TIME'839:00:00';
+ERROR HY000: Incorrect TIME value: '839:00:00'
diff --git a/mysql-test/main/type_time_round.test b/mysql-test/main/type_time_round.test
new file mode 100644
index 00000000000..6d4b2d8947a
--- /dev/null
+++ b/mysql-test/main/type_time_round.test
@@ -0,0 +1,184 @@
+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';
diff --git a/mysql-test/main/type_timestamp_round.result b/mysql-test/main/type_timestamp_round.result
new file mode 100644
index 00000000000..69b2c8fdeba
--- /dev/null
+++ b/mysql-test/main/type_timestamp_round.result
@@ -0,0 +1,164 @@
+SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
+SET @default_sql_mode=@@sql_mode;
+#
+# TIMESTAMP: SET
+#
+CREATE TABLE t1 (a TIMESTAMP(3) NULL DEFAULT NULL, b TIMESTAMP(4) NULL DEFAULT NULL);
+INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999');
+UPDATE t1 SET a=b;
+SELECT a FROM t1;
+a
+2001-01-01 00:00:00.000
+DROP TABLE t1;
+CREATE TABLE t1 (a TIMESTAMP(3) NULL DEFAULT NULL, 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;
+Warnings:
+Note	1265	Data truncated for column 'a' at row 2
+SELECT a FROM t1;
+a
+2001-01-01 00:00:00.000
+2001-01-01 00:00:00.000
+DROP TABLE t1;
+CREATE TABLE t1 (a TIMESTAMP(3) NULL DEFAULT NULL, 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;
+a
+2001-01-01 00:00:00.000
+2001-01-01 00:00:00.000
+DROP TABLE t1;
+#
+# TIMESTAMP: ALTER
+#
+CREATE TABLE t1 (a TIMESTAMP(4) NULL DEFAULT NULL);
+INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
+ALTER TABLE t1 MODIFY a TIMESTAMP(3) NULL DEFAULT NULL;
+SELECT a FROM t1;
+a
+2001-01-01 00:00:00.000
+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 TIMESTAMP(3) NULL DEFAULT NULL;
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
+SELECT a FROM t1;
+a
+2001-01-01 00:00:00.000
+2001-01-01 00:00:00.000
+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 TIMESTAMP(3) NULL DEFAULT NULL;
+SELECT a FROM t1;
+a
+2001-01-01 00:00:00.000
+2001-01-01 00:00:00.000
+DROP TABLE t1;
+#
+# Corner case:
+# ALTER TIMESTAMP to a shorter TIMESTAMP
+# All values round, maximum possible value truncates.
+#
+SET time_zone='+00:00';
+CREATE TABLE t1 (ID INT, a TIMESTAMP(6), comment VARCHAR(64));
+INSERT INTO t1 VALUES (0, '2038-01-18 23:59:59.999999', 'Should round');
+INSERT INTO t1 VALUES (1, '2038-01-19 03:14:06.999999', 'Should round');
+INSERT INTO t1 VALUES (2, '2038-01-19 03:14:07.999999', 'Should truncate');
+ALTER TABLE t1 MODIFY a TIMESTAMP(5);
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 3
+SELECT * FROM t1;
+ID	a	comment
+0	2038-01-19 00:00:00.00000	Should round
+1	2038-01-19 03:14:07.00000	Should round
+2	2038-01-19 03:14:07.99999	Should truncate
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+#
+# NOW
+#
+SET time_zone='+00:00';
+SET timestamp=UNIX_TIMESTAMP('2010-12-31 23:59:59.999999');
+CREATE OR REPLACE TABLE t1 (id SERIAL, a TIMESTAMP(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;
+id	a
+1	2011-01-01 00:00:00.0000
+2	2011-01-01 00:00:00.0000
+3	2011-01-01 00:00:00.0000
+DROP TABLE t1;
+SET timestamp=DEFAULT;
+SET time_zone=DEFAULT;
+#
+# DATETIME to TIMESTAMP conversion with DST change
+#
+SET sql_mode=IF(@@version LIKE '%MariaDB%',
+'STRICT_ALL_TABLES,TIME_ROUND_FRACTIONAL',
+'STRICT_ALL_TABLES');
+SET time_zone='Europe/Moscow';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES ('2010-03-28 01:59:59.0' /* Winter time */);
+INSERT INTO t1 VALUES ('2010-03-28 01:59:59.9' /* Rounds to the DST gap */);
+ERROR 22007: Incorrect datetime value: '2010-03-28 01:59:59.9' for column 'a' at row 1
+SELECT * FROM t1;
+a
+2010-03-28 01:59:59
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+SET sql_mode=@default_sql_mode;
+SET sql_mode=IF(@@version LIKE '%MariaDB%','TIME_ROUND_FRACTIONAL','');
+SET time_zone='Europe/Moscow';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES ('2010-03-28 01:59:59.0' /* Winter time */);
+INSERT INTO t1 VALUES ('2010-03-28 01:59:59.9' /* Rounds to the DST gap, then gets fixed to the first second of the summer time */);
+Warnings:
+Warning	1299	Invalid TIMESTAMP value in column 'a' at row 1
+SELECT a, UNIX_TIMESTAMP(a) FROM t1;
+a	UNIX_TIMESTAMP(a)
+2010-03-28 01:59:59	1269730799
+2010-03-28 03:00:00	1269730800
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+SET sql_mode=@default_sql_mode;
+#
+# Comparing non-temporal to TIMESTAMP
+#
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT t1 VALUES ('2001-01-01 23:59:59.9999999');
+CREATE TABLE t2 (a TIMESTAMP);
+INSERT INTO t2 VALUES ('2001-01-02 00:00:00');
+SELECT * FROM t1,t2 WHERE t1.a=t2.a;
+a	a
+2001-01-01 23:59:59.9999999	2001-01-02 00:00:00
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+SELECT * FROM t1,t2 WHERE CONCAT(t1.a)=t2.a;
+a	a
+2001-01-01 23:59:59.9999999	2001-01-02 00:00:00
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+SELECT * FROM t1,t2 WHERE COALESCE(t1.a)=t2.a;
+a	a
+2001-01-01 23:59:59.9999999	2001-01-02 00:00:00
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a DECIMAL(32,7));
+INSERT t1 VALUES (20010101235959.9999999);
+CREATE TABLE t2 (a TIMESTAMP);
+INSERT INTO t2 VALUES ('2001-01-02 00:00:00');
+SELECT * FROM t1,t2 WHERE t1.a=t2.a;
+a	a
+20010101235959.9999999	2001-01-02 00:00:00
+SELECT * FROM t1,t2 WHERE COALESCE(t1.a)=t2.a;
+a	a
+20010101235959.9999999	2001-01-02 00:00:00
+DROP TABLE t1,t2;
diff --git a/mysql-test/main/type_timestamp_round.test b/mysql-test/main/type_timestamp_round.test
new file mode 100644
index 00000000000..2ed01cc2a82
--- /dev/null
+++ b/mysql-test/main/type_timestamp_round.test
@@ -0,0 +1,138 @@
+SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
+SET @default_sql_mode=@@sql_mode;
+
+--echo #
+--echo # TIMESTAMP: SET
+--echo #
+
+CREATE TABLE t1 (a TIMESTAMP(3) NULL DEFAULT NULL, b TIMESTAMP(4) NULL DEFAULT NULL);
+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 TIMESTAMP(3) NULL DEFAULT NULL, 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 TIMESTAMP(3) NULL DEFAULT NULL, 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 # TIMESTAMP: ALTER
+--echo #
+
+CREATE TABLE t1 (a TIMESTAMP(4) NULL DEFAULT NULL);
+INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
+ALTER TABLE t1 MODIFY a TIMESTAMP(3) NULL DEFAULT NULL;
+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 TIMESTAMP(3) NULL DEFAULT NULL;
+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 TIMESTAMP(3) NULL DEFAULT NULL;
+SELECT a FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Corner case:
+--echo # ALTER TIMESTAMP to a shorter TIMESTAMP
+--echo # All values round, maximum possible value truncates.
+--echo #
+
+SET time_zone='+00:00';
+CREATE TABLE t1 (ID INT, a TIMESTAMP(6), comment VARCHAR(64));
+INSERT INTO t1 VALUES (0, '2038-01-18 23:59:59.999999', 'Should round');
+INSERT INTO t1 VALUES (1, '2038-01-19 03:14:06.999999', 'Should round');
+INSERT INTO t1 VALUES (2, '2038-01-19 03:14:07.999999', 'Should truncate');
+ALTER TABLE t1 MODIFY a TIMESTAMP(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 TIMESTAMP(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 to TIMESTAMP conversion with DST change
+--echo #
+
+--disable_warnings
+SET sql_mode=IF(@@version LIKE '%MariaDB%',
+               'STRICT_ALL_TABLES,TIME_ROUND_FRACTIONAL',
+               'STRICT_ALL_TABLES');
+--enable_warnings
+SET time_zone='Europe/Moscow';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES ('2010-03-28 01:59:59.0' /* Winter time */); 
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES ('2010-03-28 01:59:59.9' /* Rounds to the DST gap */);
+SELECT * FROM t1;
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+--disable_warnings
+SET sql_mode=@default_sql_mode;
+--enable_warnings
+
+SET sql_mode=IF(@@version LIKE '%MariaDB%','TIME_ROUND_FRACTIONAL','');
+SET time_zone='Europe/Moscow';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES ('2010-03-28 01:59:59.0' /* Winter time */);
+INSERT INTO t1 VALUES ('2010-03-28 01:59:59.9' /* Rounds to the DST gap, then gets fixed to the first second of the summer time */);
+SELECT a, UNIX_TIMESTAMP(a) FROM t1;
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+--disable_warnings
+SET sql_mode=@default_sql_mode;
+--enable_warnings
+
+
+--echo #
+--echo # Comparing non-temporal to TIMESTAMP
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT t1 VALUES ('2001-01-01 23:59:59.9999999');
+CREATE TABLE t2 (a TIMESTAMP);
+INSERT INTO t2 VALUES ('2001-01-02 00:00:00');
+SELECT * FROM t1,t2 WHERE t1.a=t2.a;
+SELECT * FROM t1,t2 WHERE CONCAT(t1.a)=t2.a;
+SELECT * FROM t1,t2 WHERE COALESCE(t1.a)=t2.a;
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (a DECIMAL(32,7));
+INSERT t1 VALUES (20010101235959.9999999);
+CREATE TABLE t2 (a TIMESTAMP);
+INSERT INTO t2 VALUES ('2001-01-02 00:00:00');
+SELECT * FROM t1,t2 WHERE t1.a=t2.a;
+SELECT * FROM t1,t2 WHERE COALESCE(t1.a)=t2.a;
+DROP TABLE t1,t2;
diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result
index 5a7fdbd63c9..e682a8599c4 100644
--- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result
+++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result
@@ -61,7 +61,7 @@ def	mysql	event	modified	9	'0000-00-00 00:00:00'	NO	timestamp	NULL	NULL	NULL	NUL
 def	mysql	event	name	2	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)	PRI		select,insert,update,references		NEVER	NULL
 def	mysql	event	on_completion	14	'DROP'	NO	enum	8	24	NULL	NULL	NULL	utf8	utf8_general_ci	enum('DROP','PRESERVE')			select,insert,update,references		NEVER	NULL
 def	mysql	event	originator	17	NULL	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(10) unsigned			select,insert,update,references		NEVER	NULL
-def	mysql	event	sql_mode	15	''	NO	set	539	1617	NULL	NULL	NULL	utf8	utf8_general_ci	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT')			select,insert,update,references		NEVER	NULL
+def	mysql	event	sql_mode	15	''	NO	set	561	1683	NULL	NULL	NULL	utf8	utf8_general_ci	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL')			select,insert,update,references		NEVER	NULL
 def	mysql	event	starts	11	NULL	YES	datetime	NULL	NULL	NULL	NULL	0	NULL	NULL	datetime			select,insert,update,references		NEVER	NULL
 def	mysql	event	status	13	'ENABLED'	NO	enum	18	54	NULL	NULL	NULL	utf8	utf8_general_ci	enum('ENABLED','DISABLED','SLAVESIDE_DISABLED')			select,insert,update,references		NEVER	NULL
 def	mysql	event	time_zone	18	'SYSTEM'	NO	char	64	64	NULL	NULL	NULL	latin1	latin1_swedish_ci	char(64)			select,insert,update,references		NEVER	NULL
@@ -133,7 +133,7 @@ def	mysql	proc	returns	10	NULL	NO	longblob	4294967295	4294967295	NULL	NULL	NULL
 def	mysql	proc	security_type	8	'DEFINER'	NO	enum	7	21	NULL	NULL	NULL	utf8	utf8_general_ci	enum('INVOKER','DEFINER')			select,insert,update,references		NEVER	NULL
 def	mysql	proc	specific_name	4	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)			select,insert,update,references		NEVER	NULL
 def	mysql	proc	sql_data_access	6	'CONTAINS_SQL'	NO	enum	17	51	NULL	NULL	NULL	utf8	utf8_general_ci	enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA')			select,insert,update,references		NEVER	NULL
-def	mysql	proc	sql_mode	15	''	NO	set	539	1617	NULL	NULL	NULL	utf8	utf8_general_ci	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT')			select,insert,update,references		NEVER	NULL
+def	mysql	proc	sql_mode	15	''	NO	set	561	1683	NULL	NULL	NULL	utf8	utf8_general_ci	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL')			select,insert,update,references		NEVER	NULL
 def	mysql	proc	type	3	NULL	NO	enum	12	36	NULL	NULL	NULL	utf8	utf8_general_ci	enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')	PRI		select,insert,update,references		NEVER	NULL
 def	mysql	procs_priv	Db	2	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_bin	char(64)	PRI		select,insert,update,references		NEVER	NULL
 def	mysql	procs_priv	Grantor	6	''	NO	char	141	423	NULL	NULL	NULL	utf8	utf8_bin	char(141)	MUL		select,insert,update,references		NEVER	NULL
@@ -380,7 +380,7 @@ NULL	mysql	event	starts	datetime	NULL	NULL	NULL	NULL	datetime
 NULL	mysql	event	ends	datetime	NULL	NULL	NULL	NULL	datetime
 3.0000	mysql	event	status	enum	18	54	utf8	utf8_general_ci	enum('ENABLED','DISABLED','SLAVESIDE_DISABLED')
 3.0000	mysql	event	on_completion	enum	8	24	utf8	utf8_general_ci	enum('DROP','PRESERVE')
-3.0000	mysql	event	sql_mode	set	539	1617	utf8	utf8_general_ci	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT')
+3.0000	mysql	event	sql_mode	set	561	1683	utf8	utf8_general_ci	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL')
 3.0000	mysql	event	comment	char	64	192	utf8	utf8_bin	char(64)
 NULL	mysql	event	originator	int	NULL	NULL	NULL	NULL	int(10) unsigned
 1.0000	mysql	event	time_zone	char	64	64	latin1	latin1_swedish_ci	char(64)
@@ -451,7 +451,7 @@ NULL	mysql	innodb_table_stats	sum_of_other_index_sizes	bigint	NULL	NULL	NULL	NUL
 3.0000	mysql	proc	definer	char	141	423	utf8	utf8_bin	char(141)
 NULL	mysql	proc	created	timestamp	NULL	NULL	NULL	NULL	timestamp
 NULL	mysql	proc	modified	timestamp	NULL	NULL	NULL	NULL	timestamp
-3.0000	mysql	proc	sql_mode	set	539	1617	utf8	utf8_general_ci	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT')
+3.0000	mysql	proc	sql_mode	set	561	1683	utf8	utf8_general_ci	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL')
 1.0000	mysql	proc	comment	text	65535	65535	utf8	utf8_bin	text
 3.0000	mysql	proc	character_set_client	char	32	96	utf8	utf8_bin	char(32)
 3.0000	mysql	proc	collation_connection	char	32	96	utf8	utf8_bin	char(32)
diff --git a/mysql-test/suite/rpl/r/rpl_temporal_round.result b/mysql-test/suite/rpl/r/rpl_temporal_round.result
new file mode 100644
index 00000000000..df8cc431a74
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_temporal_round.result
@@ -0,0 +1,50 @@
+include/master-slave.inc
+[connection master]
+SET sql_mode=TIME_ROUND_FRACTIONAL;
+SET time_zone='+00:00';
+SET timestamp=UNIX_TIMESTAMP('2010-12-31 23:59:59.999999');
+CREATE TABLE t1 (id SERIAL, a TIMESTAMP(4));
+INSERT INTO t1 (a) VALUES (now(6));
+INSERT INTO t1 (a) VALUES ('2011-01-01 23:59:59.999999');
+CREATE TABLE t2 (id SERIAL, a DATETIME(4));
+INSERT INTO t2 (a) VALUES (now(6));
+INSERT INTO t2 (a) VALUES ('2011-01-01 23:59:59.999999');
+CREATE TABLE t3 (id SERIAL, a TIME(4));
+INSERT INTO t3 (a) VALUES (now(6));
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+INSERT INTO t3 (a) VALUES ('2011-01-01 23:59:59.999999');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SELECT * FROM t1;
+id	a
+1	2011-01-01 00:00:00.0000
+2	2011-01-02 00:00:00.0000
+SELECT * FROM t2;
+id	a
+1	2011-01-01 00:00:00.0000
+2	2011-01-02 00:00:00.0000
+SELECT * FROM t3;
+id	a
+1	24:00:00.0000
+2	24:00:00.0000
+connection slave;
+connection slave;
+SET time_zone='+00:00';
+SELECT * FROM t1;
+id	a
+1	2011-01-01 00:00:00.0000
+2	2011-01-02 00:00:00.0000
+SELECT * FROM t2;
+id	a
+1	2011-01-01 00:00:00.0000
+2	2011-01-02 00:00:00.0000
+SELECT * FROM t3;
+id	a
+1	24:00:00.0000
+2	24:00:00.0000
+connection master;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_temporal_round.test b/mysql-test/suite/rpl/t/rpl_temporal_round.test
new file mode 100644
index 00000000000..c13c18bddb5
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_temporal_round.test
@@ -0,0 +1,35 @@
+--source include/master-slave.inc
+
+SET sql_mode=TIME_ROUND_FRACTIONAL;
+SET time_zone='+00:00';
+SET timestamp=UNIX_TIMESTAMP('2010-12-31 23:59:59.999999');
+
+CREATE TABLE t1 (id SERIAL, a TIMESTAMP(4));
+INSERT INTO t1 (a) VALUES (now(6));
+INSERT INTO t1 (a) VALUES ('2011-01-01 23:59:59.999999');
+
+CREATE TABLE t2 (id SERIAL, a DATETIME(4));
+INSERT INTO t2 (a) VALUES (now(6));
+INSERT INTO t2 (a) VALUES ('2011-01-01 23:59:59.999999');
+
+CREATE TABLE t3 (id SERIAL, a TIME(4));
+INSERT INTO t3 (a) VALUES (now(6));
+INSERT INTO t3 (a) VALUES ('2011-01-01 23:59:59.999999');
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+
+sync_slave_with_master;
+connection slave;
+SET time_zone='+00:00';
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+
+connection master;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/sys_vars/r/sql_mode_basic.result b/mysql-test/suite/sys_vars/r/sql_mode_basic.result
index a200f620a7c..d911e80b780 100644
--- a/mysql-test/suite/sys_vars/r/sql_mode_basic.result
+++ b/mysql-test/suite/sys_vars/r/sql_mode_basic.result
@@ -367,7 +367,15 @@ SELECT @@global.sql_mode;
 @@global.sql_mode
 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT
 SET @@global.sql_mode = 17179869184;
-ERROR 42000: Variable 'sql_mode' can't be set to the value of '17179869184'
+SELECT @@global.sql_mode;
+@@global.sql_mode
+TIME_ROUND_FRACTIONAL
+SET @@global.sql_mode = 34359738367;
+SELECT @@global.sql_mode;
+@@global.sql_mode
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL
+SET @@global.sql_mode = 34359738368;
+ERROR 42000: Variable 'sql_mode' can't be set to the value of '34359738368'
 SET @@global.sql_mode = 0.4;
 ERROR 42000: Incorrect argument type to variable 'sql_mode'
 '#---------------------FN_DYNVARS_152_08----------------------#'
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 26acbfad9c6..56f7a136983 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -4755,7 +4755,7 @@ VARIABLE_COMMENT	Sets the sql mode
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT
+ENUM_VALUE_LIST	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	SQL_NOTES
diff --git a/mysql-test/suite/sys_vars/t/sql_mode_basic.test b/mysql-test/suite/sys_vars/t/sql_mode_basic.test
index b4841ecd3ff..68be8ba969b 100644
--- a/mysql-test/suite/sys_vars/t/sql_mode_basic.test
+++ b/mysql-test/suite/sys_vars/t/sql_mode_basic.test
@@ -310,8 +310,14 @@ SELECT @@global.sql_mode;
 SET @@global.sql_mode = 17179869183;
 SELECT @@global.sql_mode;
 
---Error ER_WRONG_VALUE_FOR_VAR
 SET @@global.sql_mode = 17179869184;
+SELECT @@global.sql_mode;
+
+SET @@global.sql_mode = 34359738367;
+SELECT @@global.sql_mode;
+
+--Error ER_WRONG_VALUE_FOR_VAR
+SET @@global.sql_mode = 34359738368;
 
 # use of decimal values
 
diff --git a/mysql-test/suite/versioning/r/sysvars.result b/mysql-test/suite/versioning/r/sysvars.result
index b23742462d1..acc5d62e069 100644
--- a/mysql-test/suite/versioning/r/sysvars.result
+++ b/mysql-test/suite/versioning/r/sysvars.result
@@ -130,3 +130,14 @@ show status like "Feature_system_versioning";
 Variable_name	Value
 Feature_system_versioning	2
 drop table t;
+#
+# MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP
+#
+SET sql_mode=TIME_ROUND_FRACTIONAL;
+SET @@global.system_versioning_asof= timestamp'2001-12-31 23:59:59.9999999';
+Warnings:
+Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
+SELECT @@global.system_versioning_asof;
+@@global.system_versioning_asof
+2002-01-01 00:00:00.000000
+SET @@global.system_versioning_asof= DEFAULT;
diff --git a/mysql-test/suite/versioning/t/sysvars.test b/mysql-test/suite/versioning/t/sysvars.test
index 160af12fe02..52fab81b8e6 100644
--- a/mysql-test/suite/versioning/t/sysvars.test
+++ b/mysql-test/suite/versioning/t/sysvars.test
@@ -87,3 +87,13 @@ select * from t for system_time between '0-0-0' and current_timestamp(6);
 show status like "Feature_system_versioning";
 
 drop table t;
+
+
+--echo #
+--echo # MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP
+--echo #
+
+SET sql_mode=TIME_ROUND_FRACTIONAL;
+SET @@global.system_versioning_asof= timestamp'2001-12-31 23:59:59.9999999';
+SELECT @@global.system_versioning_asof;
+SET @@global.system_versioning_asof= DEFAULT;