Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1374 insertions, 0 deletions

diff --git a/mysql-test/main/func_time_round.result b/mysql-test/main/func_time_round.result
new file mode 100644
index 00000000..b335cf39
--- /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 `test`.`t1_time_in_decimal`.`a` at row 17
+Warning	1292	Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18
+Warning	1292	Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19
+Warning	1292	Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20
+Warning	1292	Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`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 `test`.`t1_time_in_decimal`.`a` at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`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 `test`.`t1_time_in_decimal`.`a` at row 17
+Warning	1292	Incorrect time value: '8385959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 17
+Warning	1292	Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18
+Warning	1292	Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18
+Warning	1292	Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19
+Warning	1292	Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19
+Warning	1292	Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20
+Warning	1292	Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20
+Warning	1292	Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21
+Warning	1292	Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
+Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`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;