1 files changed, 557 insertions, 2 deletions
diff --git a/mysql-test/main/type_time_hires.result b/mysql-test/main/type_time_hires.result
index 47185116bea..c7fecbef76b 100644
--- a/mysql-test/main/type_time_hires.result
+++ b/mysql-test/main/type_time_hires.result
@@ -25,8 +25,8 @@ a
 03:04:05.789
 15:47:11.123
 838:59:59.999
-select truncate(a, 6) from t1;
-truncate(a, 6)
+select cast(a AS double(30,6)) from t1;
+cast(a AS double(30,6))
 2003.123000
 10203.456000
 30405.789062
@@ -67,8 +67,10 @@ a
 01:02:03.4567
 select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456';
 extract(microsecond from a + interval 100 microsecond)
+456800
 select a from t1 where a>'2010-11-12 01:02:03.456' group by a;
 a
+01:02:03.4567
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -115,6 +117,11 @@ NULL
 delete from t1 where a < 20110101;
 select * from t1;
 a
+01:02:13.3332
+NULL
+delete from t1 where a is not null;
+select * from t1;
+a
 NULL
 create table t2 select * from t1;
 create table t3 like t1;
@@ -352,3 +359,551 @@ drop table t1;
 select cast(1e-6 as time(6));
 cast(1e-6 as time(6))
 00:00:00.000001
+#
+# Start of 10.4 tests
+#
+#
+# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE()
+#
+CREATE TABLE t1 (a1 TIME(6), a2 TIME(6) NOT NULL);
+CREATE TABLE t2 AS SELECT
+ROUND(a1) AS r1,
+ROUND(a2) AS r2,
+TRUNCATE(a1,0) AS t1,
+TRUNCATE(a2,0) AS t2
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `r1` time DEFAULT NULL,
+  `r2` time NOT NULL,
+  `t1` time DEFAULT NULL,
+  `t2` time NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME(6));
+INSERT INTO t1 VALUES
+('-838:59:59.999999'),
+('-837:59:59.999999'),
+('-23:59:59.999999'),
+('-00:59:59.999999'),
+('-00:00:59.999999'),
+('00:00:00.999999'),
+('00:00:59.999999'),
+('00:59:59.999999'),
+('23:59:59.999999'),
+('837:59:59.999999'),
+('838:59:59.999999');
+SELECT a, TRUNCATE(a,0) FROM t1;
+a	TRUNCATE(a,0)
+-838:59:59.999999	-838:59:59
+-837:59:59.999999	-837:59:59
+-23:59:59.999999	-23:59:59
+-00:59:59.999999	-00:59:59
+-00:00:59.999999	-00:00:59
+00:00:00.999999	00:00:00
+00:00:59.999999	00:00:59
+00:59:59.999999	00:59:59
+23:59:59.999999	23:59:59
+837:59:59.999999	837:59:59
+838:59:59.999999	838:59:59
+SELECT a, TRUNCATE(a,1) FROM t1;
+a	TRUNCATE(a,1)
+-838:59:59.999999	-838:59:59.9
+-837:59:59.999999	-837:59:59.9
+-23:59:59.999999	-23:59:59.9
+-00:59:59.999999	-00:59:59.9
+-00:00:59.999999	-00:00:59.9
+00:00:00.999999	00:00:00.9
+00:00:59.999999	00:00:59.9
+00:59:59.999999	00:59:59.9
+23:59:59.999999	23:59:59.9
+837:59:59.999999	837:59:59.9
+838:59:59.999999	838:59:59.9
+SELECT a, TRUNCATE(a,2) FROM t1;
+a	TRUNCATE(a,2)
+-838:59:59.999999	-838:59:59.99
+-837:59:59.999999	-837:59:59.99
+-23:59:59.999999	-23:59:59.99
+-00:59:59.999999	-00:59:59.99
+-00:00:59.999999	-00:00:59.99
+00:00:00.999999	00:00:00.99
+00:00:59.999999	00:00:59.99
+00:59:59.999999	00:59:59.99
+23:59:59.999999	23:59:59.99
+837:59:59.999999	837:59:59.99
+838:59:59.999999	838:59:59.99
+SELECT a, TRUNCATE(a,3) FROM t1;
+a	TRUNCATE(a,3)
+-838:59:59.999999	-838:59:59.999
+-837:59:59.999999	-837:59:59.999
+-23:59:59.999999	-23:59:59.999
+-00:59:59.999999	-00:59:59.999
+-00:00:59.999999	-00:00:59.999
+00:00:00.999999	00:00:00.999
+00:00:59.999999	00:00:59.999
+00:59:59.999999	00:59:59.999
+23:59:59.999999	23:59:59.999
+837:59:59.999999	837:59:59.999
+838:59:59.999999	838:59:59.999
+SELECT a, TRUNCATE(a,4) FROM t1;
+a	TRUNCATE(a,4)
+-838:59:59.999999	-838:59:59.9999
+-837:59:59.999999	-837:59:59.9999
+-23:59:59.999999	-23:59:59.9999
+-00:59:59.999999	-00:59:59.9999
+-00:00:59.999999	-00:00:59.9999
+00:00:00.999999	00:00:00.9999
+00:00:59.999999	00:00:59.9999
+00:59:59.999999	00:59:59.9999
+23:59:59.999999	23:59:59.9999
+837:59:59.999999	837:59:59.9999
+838:59:59.999999	838:59:59.9999
+SELECT a, TRUNCATE(a,5) FROM t1;
+a	TRUNCATE(a,5)
+-838:59:59.999999	-838:59:59.99999
+-837:59:59.999999	-837:59:59.99999
+-23:59:59.999999	-23:59:59.99999
+-00:59:59.999999	-00:59:59.99999
+-00:00:59.999999	-00:00:59.99999
+00:00:00.999999	00:00:00.99999
+00:00:59.999999	00:00:59.99999
+00:59:59.999999	00:59:59.99999
+23:59:59.999999	23:59:59.99999
+837:59:59.999999	837:59:59.99999
+838:59:59.999999	838:59:59.99999
+SELECT a, TRUNCATE(a,6) FROM t1;
+a	TRUNCATE(a,6)
+-838:59:59.999999	-838:59:59.999999
+-837:59:59.999999	-837:59:59.999999
+-23:59:59.999999	-23:59:59.999999
+-00:59:59.999999	-00:59:59.999999
+-00:00:59.999999	-00:00:59.999999
+00:00:00.999999	00:00:00.999999
+00:00:59.999999	00:00:59.999999
+00:59:59.999999	00:59:59.999999
+23:59:59.999999	23:59:59.999999
+837:59:59.999999	837:59:59.999999
+838:59:59.999999	838:59:59.999999
+SELECT a, TRUNCATE(a,7) FROM t1;
+a	TRUNCATE(a,7)
+-838:59:59.999999	-838:59:59.999999
+-837:59:59.999999	-837:59:59.999999
+-23:59:59.999999	-23:59:59.999999
+-00:59:59.999999	-00:59:59.999999
+-00:00:59.999999	-00:00:59.999999
+00:00:00.999999	00:00:00.999999
+00:00:59.999999	00:00:59.999999
+00:59:59.999999	00:59:59.999999
+23:59:59.999999	23:59:59.999999
+837:59:59.999999	837:59:59.999999
+838:59:59.999999	838:59:59.999999
+SELECT a, TRUNCATE(a,-1) FROM t1;
+a	TRUNCATE(a,-1)
+-838:59:59.999999	-838:59:59
+-837:59:59.999999	-837:59:59
+-23:59:59.999999	-23:59:59
+-00:59:59.999999	-00:59:59
+-00:00:59.999999	-00:00:59
+00:00:00.999999	00:00:00
+00:00:59.999999	00:00:59
+00:59:59.999999	00:59:59
+23:59:59.999999	23:59:59
+837:59:59.999999	837:59:59
+838:59:59.999999	838:59:59
+SELECT a, TRUNCATE(a,-6) FROM t1;
+a	TRUNCATE(a,-6)
+-838:59:59.999999	-838:59:59
+-837:59:59.999999	-837:59:59
+-23:59:59.999999	-23:59:59
+-00:59:59.999999	-00:59:59
+-00:00:59.999999	-00:00:59
+00:00:00.999999	00:00:00
+00:00:59.999999	00:00:59
+00:59:59.999999	00:59:59
+23:59:59.999999	23:59:59
+837:59:59.999999	837:59:59
+838:59:59.999999	838:59:59
+SELECT a, ROUND(a) FROM t1;
+a	ROUND(a)
+-838:59:59.999999	-838:59:59
+-837:59:59.999999	-838:00:00
+-23:59:59.999999	-24:00:00
+-00:59:59.999999	-01:00:00
+-00:00:59.999999	-00:01:00
+00:00:00.999999	00:00:01
+00:00:59.999999	00:01:00
+00:59:59.999999	01:00:00
+23:59:59.999999	24:00:00
+837:59:59.999999	838:00:00
+838:59:59.999999	838:59:59
+SELECT a, ROUND(a,0) FROM t1;
+a	ROUND(a,0)
+-838:59:59.999999	-838:59:59
+-837:59:59.999999	-838:00:00
+-23:59:59.999999	-24:00:00
+-00:59:59.999999	-01:00:00
+-00:00:59.999999	-00:01:00
+00:00:00.999999	00:00:01
+00:00:59.999999	00:01:00
+00:59:59.999999	01:00:00
+23:59:59.999999	24:00:00
+837:59:59.999999	838:00:00
+838:59:59.999999	838:59:59
+SELECT a, ROUND(a,1) FROM t1;
+a	ROUND(a,1)
+-838:59:59.999999	-838:59:59.9
+-837:59:59.999999	-838:00:00.0
+-23:59:59.999999	-24:00:00.0
+-00:59:59.999999	-01:00:00.0
+-00:00:59.999999	-00:01:00.0
+00:00:00.999999	00:00:01.0
+00:00:59.999999	00:01:00.0
+00:59:59.999999	01:00:00.0
+23:59:59.999999	24:00:00.0
+837:59:59.999999	838:00:00.0
+838:59:59.999999	838:59:59.9
+SELECT a, ROUND(a,2) FROM t1;
+a	ROUND(a,2)
+-838:59:59.999999	-838:59:59.99
+-837:59:59.999999	-838:00:00.00
+-23:59:59.999999	-24:00:00.00
+-00:59:59.999999	-01:00:00.00
+-00:00:59.999999	-00:01:00.00
+00:00:00.999999	00:00:01.00
+00:00:59.999999	00:01:00.00
+00:59:59.999999	01:00:00.00
+23:59:59.999999	24:00:00.00
+837:59:59.999999	838:00:00.00
+838:59:59.999999	838:59:59.99
+SELECT a, ROUND(a,3) FROM t1;
+a	ROUND(a,3)
+-838:59:59.999999	-838:59:59.999
+-837:59:59.999999	-838:00:00.000
+-23:59:59.999999	-24:00:00.000
+-00:59:59.999999	-01:00:00.000
+-00:00:59.999999	-00:01:00.000
+00:00:00.999999	00:00:01.000
+00:00:59.999999	00:01:00.000
+00:59:59.999999	01:00:00.000
+23:59:59.999999	24:00:00.000
+837:59:59.999999	838:00:00.000
+838:59:59.999999	838:59:59.999
+SELECT a, ROUND(a,4) FROM t1;
+a	ROUND(a,4)
+-838:59:59.999999	-838:59:59.9999
+-837:59:59.999999	-838:00:00.0000
+-23:59:59.999999	-24:00:00.0000
+-00:59:59.999999	-01:00:00.0000
+-00:00:59.999999	-00:01:00.0000
+00:00:00.999999	00:00:01.0000
+00:00:59.999999	00:01:00.0000
+00:59:59.999999	01:00:00.0000
+23:59:59.999999	24:00:00.0000
+837:59:59.999999	838:00:00.0000
+838:59:59.999999	838:59:59.9999
+SELECT a, ROUND(a,5) FROM t1;
+a	ROUND(a,5)
+-838:59:59.999999	-838:59:59.99999
+-837:59:59.999999	-838:00:00.00000
+-23:59:59.999999	-24:00:00.00000
+-00:59:59.999999	-01:00:00.00000
+-00:00:59.999999	-00:01:00.00000
+00:00:00.999999	00:00:01.00000
+00:00:59.999999	00:01:00.00000
+00:59:59.999999	01:00:00.00000
+23:59:59.999999	24:00:00.00000
+837:59:59.999999	838:00:00.00000
+838:59:59.999999	838:59:59.99999
+SELECT a, ROUND(a,6) FROM t1;
+a	ROUND(a,6)
+-838:59:59.999999	-838:59:59.999999
+-837:59:59.999999	-837:59:59.999999
+-23:59:59.999999	-23:59:59.999999
+-00:59:59.999999	-00:59:59.999999
+-00:00:59.999999	-00:00:59.999999
+00:00:00.999999	00:00:00.999999
+00:00:59.999999	00:00:59.999999
+00:59:59.999999	00:59:59.999999
+23:59:59.999999	23:59:59.999999
+837:59:59.999999	837:59:59.999999
+838:59:59.999999	838:59:59.999999
+SELECT a, ROUND(a,7) FROM t1;
+a	ROUND(a,7)
+-838:59:59.999999	-838:59:59.999999
+-837:59:59.999999	-837:59:59.999999
+-23:59:59.999999	-23:59:59.999999
+-00:59:59.999999	-00:59:59.999999
+-00:00:59.999999	-00:00:59.999999
+00:00:00.999999	00:00:00.999999
+00:00:59.999999	00:00:59.999999
+00:59:59.999999	00:59:59.999999
+23:59:59.999999	23:59:59.999999
+837:59:59.999999	837:59:59.999999
+838:59:59.999999	838:59:59.999999
+SELECT a, ROUND(a,-1) FROM t1;
+a	ROUND(a,-1)
+-838:59:59.999999	-838:59:59
+-837:59:59.999999	-838:00:00
+-23:59:59.999999	-24:00:00
+-00:59:59.999999	-01:00:00
+-00:00:59.999999	-00:01:00
+00:00:00.999999	00:00:01
+00:00:59.999999	00:01:00
+00:59:59.999999	01:00:00
+23:59:59.999999	24:00:00
+837:59:59.999999	838:00:00
+838:59:59.999999	838:59:59
+SELECT a, ROUND(a,-6) FROM t1;
+a	ROUND(a,-6)
+-838:59:59.999999	-838:59:59
+-837:59:59.999999	-838:00:00
+-23:59:59.999999	-24:00:00
+-00:59:59.999999	-01:00:00
+-00:00:59.999999	-00:01:00
+00:00:00.999999	00:00:01
+00:00:59.999999	00:01:00
+00:59:59.999999	01:00:00
+23:59:59.999999	24:00:00
+837:59:59.999999	838:00:00
+838:59:59.999999	838:59:59
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+#
+# MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
+#
+CREATE TABLE t1 AS SELECT
+FLOOR(TIME'00:00:00.999999'),
+CEILING(TIME'00:00:00.999999');
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `FLOOR(TIME'00:00:00.999999')` time NOT NULL,
+  `CEILING(TIME'00:00:00.999999')` time NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME(6));
+INSERT INTO t1 VALUES ('838:59:59.999999');
+INSERT INTO t1 VALUES ('838:59:59.99999');
+INSERT INTO t1 VALUES ('838:59:59.9999');
+INSERT INTO t1 VALUES ('838:59:59.999');
+INSERT INTO t1 VALUES ('838:59:59.99');
+INSERT INTO t1 VALUES ('838:59:59.9');
+INSERT INTO t1 VALUES ('838:59:59.1');
+INSERT INTO t1 VALUES ('838:59:59.0');
+INSERT INTO t1 VALUES ('837:59:59.999999');
+INSERT INTO t1 VALUES ('837:59:59.99999');
+INSERT INTO t1 VALUES ('837:59:59.9999');
+INSERT INTO t1 VALUES ('837:59:59.999');
+INSERT INTO t1 VALUES ('837:59:59.99');
+INSERT INTO t1 VALUES ('837:59:59.9');
+INSERT INTO t1 VALUES ('837:59:59.1');
+INSERT INTO t1 VALUES ('837:59:59.0');
+INSERT INTO t1 VALUES ('23:59:59.999999');
+INSERT INTO t1 VALUES ('23:59:59.99999');
+INSERT INTO t1 VALUES ('23:59:59.9999');
+INSERT INTO t1 VALUES ('23:59:59.999');
+INSERT INTO t1 VALUES ('23:59:59.99');
+INSERT INTO t1 VALUES ('23:59:59.9');
+INSERT INTO t1 VALUES ('23:59:59.1');
+INSERT INTO t1 VALUES ('23:59:59.0');
+INSERT INTO t1 VALUES ('00:00:00.999999');
+INSERT INTO t1 VALUES ('00:00:00.99999');
+INSERT INTO t1 VALUES ('00:00:00.9999');
+INSERT INTO t1 VALUES ('00:00:00.999');
+INSERT INTO t1 VALUES ('00:00:00.99');
+INSERT INTO t1 VALUES ('00:00:00.9');
+INSERT INTO t1 VALUES ('00:00:00.1');
+INSERT INTO t1 VALUES ('00:00:00.0');
+INSERT INTO t1 VALUES ('-00:00:00.999999');
+INSERT INTO t1 VALUES ('-00:00:00.99999');
+INSERT INTO t1 VALUES ('-00:00:00.9999');
+INSERT INTO t1 VALUES ('-00:00:00.999');
+INSERT INTO t1 VALUES ('-00:00:00.99');
+INSERT INTO t1 VALUES ('-00:00:00.9');
+INSERT INTO t1 VALUES ('-00:00:00.1');
+INSERT INTO t1 VALUES ('-00:00:00.0');
+INSERT INTO t1 VALUES ('-23:59:59.999999');
+INSERT INTO t1 VALUES ('-23:59:59.99999');
+INSERT INTO t1 VALUES ('-23:59:59.9999');
+INSERT INTO t1 VALUES ('-23:59:59.999');
+INSERT INTO t1 VALUES ('-23:59:59.99');
+INSERT INTO t1 VALUES ('-23:59:59.9');
+INSERT INTO t1 VALUES ('-23:59:59.1');
+INSERT INTO t1 VALUES ('-23:59:59.0');
+INSERT INTO t1 VALUES ('-837:59:59.999999');
+INSERT INTO t1 VALUES ('-837:59:59.99999');
+INSERT INTO t1 VALUES ('-837:59:59.9999');
+INSERT INTO t1 VALUES ('-837:59:59.999');
+INSERT INTO t1 VALUES ('-837:59:59.99');
+INSERT INTO t1 VALUES ('-837:59:59.9');
+INSERT INTO t1 VALUES ('-837:59:59.1');
+INSERT INTO t1 VALUES ('-837:59:59.0');
+INSERT INTO t1 VALUES ('-838:59:59.999999');
+INSERT INTO t1 VALUES ('-838:59:59.99999');
+INSERT INTO t1 VALUES ('-838:59:59.9999');
+INSERT INTO t1 VALUES ('-838:59:59.999');
+INSERT INTO t1 VALUES ('-838:59:59.99');
+INSERT INTO t1 VALUES ('-838:59:59.9');
+INSERT INTO t1 VALUES ('-838:59:59.1');
+INSERT INTO t1 VALUES ('-838:59:59.0');
+CREATE FUNCTION FLOOR_SP(a TIME(6)) RETURNS TIME
+BEGIN
+RETURN
+CASE
+WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a
+WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a
+WHEN a=TRUNCATE(a,0) THEN a                    -- no fractional digits
+WHEN a<0 THEN TRUNCATE(a,0)-INTERVAL 1 SECOND  -- negative values
+WHEN a>0 THEN TRUNCATE(a,0)                    -- positive values
+END;
+END;
+$$
+CREATE FUNCTION CEILING_SP(a TIME(6)) RETURNS TIME
+BEGIN
+RETURN
+CASE
+WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a
+WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a
+WHEN a=TRUNCATE(a,0) THEN a                    -- no fractional digits
+WHEN a<0 THEN TRUNCATE(a,0)                    -- negative values
+WHEN a>0 THEN TRUNCATE(a,0)+INTERVAL 1 SECOND  -- positive values
+END;
+END;
+$$
+SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a;
+a	FLOOR(a)	FLOOR_SP(a)	FLOOR(a)=FLOOR_SP(a)
+-838:59:59.999999	-838:59:59	-838:59:59	1
+-838:59:59.999990	-838:59:59	-838:59:59	1
+-838:59:59.999900	-838:59:59	-838:59:59	1
+-838:59:59.999000	-838:59:59	-838:59:59	1
+-838:59:59.990000	-838:59:59	-838:59:59	1
+-838:59:59.900000	-838:59:59	-838:59:59	1
+-838:59:59.100000	-838:59:59	-838:59:59	1
+-838:59:59.000000	-838:59:59	-838:59:59	1
+-837:59:59.999999	-838:00:00	-838:00:00	1
+-837:59:59.999990	-838:00:00	-838:00:00	1
+-837:59:59.999900	-838:00:00	-838:00:00	1
+-837:59:59.999000	-838:00:00	-838:00:00	1
+-837:59:59.990000	-838:00:00	-838:00:00	1
+-837:59:59.900000	-838:00:00	-838:00:00	1
+-837:59:59.100000	-838:00:00	-838:00:00	1
+-837:59:59.000000	-837:59:59	-837:59:59	1
+-23:59:59.999999	-24:00:00	-24:00:00	1
+-23:59:59.999990	-24:00:00	-24:00:00	1
+-23:59:59.999900	-24:00:00	-24:00:00	1
+-23:59:59.999000	-24:00:00	-24:00:00	1
+-23:59:59.990000	-24:00:00	-24:00:00	1
+-23:59:59.900000	-24:00:00	-24:00:00	1
+-23:59:59.100000	-24:00:00	-24:00:00	1
+-23:59:59.000000	-23:59:59	-23:59:59	1
+-00:00:00.999999	-00:00:01	-00:00:01	1
+-00:00:00.999990	-00:00:01	-00:00:01	1
+-00:00:00.999900	-00:00:01	-00:00:01	1
+-00:00:00.999000	-00:00:01	-00:00:01	1
+-00:00:00.990000	-00:00:01	-00:00:01	1
+-00:00:00.900000	-00:00:01	-00:00:01	1
+-00:00:00.100000	-00:00:01	-00:00:01	1
+00:00:00.000000	00:00:00	00:00:00	1
+00:00:00.000000	00:00:00	00:00:00	1
+00:00:00.100000	00:00:00	00:00:00	1
+00:00:00.900000	00:00:00	00:00:00	1
+00:00:00.990000	00:00:00	00:00:00	1
+00:00:00.999000	00:00:00	00:00:00	1
+00:00:00.999900	00:00:00	00:00:00	1
+00:00:00.999990	00:00:00	00:00:00	1
+00:00:00.999999	00:00:00	00:00:00	1
+23:59:59.000000	23:59:59	23:59:59	1
+23:59:59.100000	23:59:59	23:59:59	1
+23:59:59.900000	23:59:59	23:59:59	1
+23:59:59.990000	23:59:59	23:59:59	1
+23:59:59.999000	23:59:59	23:59:59	1
+23:59:59.999900	23:59:59	23:59:59	1
+23:59:59.999990	23:59:59	23:59:59	1
+23:59:59.999999	23:59:59	23:59:59	1
+837:59:59.000000	837:59:59	837:59:59	1
+837:59:59.100000	837:59:59	837:59:59	1
+837:59:59.900000	837:59:59	837:59:59	1
+837:59:59.990000	837:59:59	837:59:59	1
+837:59:59.999000	837:59:59	837:59:59	1
+837:59:59.999900	837:59:59	837:59:59	1
+837:59:59.999990	837:59:59	837:59:59	1
+837:59:59.999999	837:59:59	837:59:59	1
+838:59:59.000000	838:59:59	838:59:59	1
+838:59:59.100000	838:59:59	838:59:59	1
+838:59:59.900000	838:59:59	838:59:59	1
+838:59:59.990000	838:59:59	838:59:59	1
+838:59:59.999000	838:59:59	838:59:59	1
+838:59:59.999900	838:59:59	838:59:59	1
+838:59:59.999990	838:59:59	838:59:59	1
+838:59:59.999999	838:59:59	838:59:59	1
+SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a;
+a	CEILING(a)	CEILING_SP(a)	CEILING(a)=CEILING_SP(a)
+-838:59:59.999999	-838:59:59	-838:59:59	1
+-838:59:59.999990	-838:59:59	-838:59:59	1
+-838:59:59.999900	-838:59:59	-838:59:59	1
+-838:59:59.999000	-838:59:59	-838:59:59	1
+-838:59:59.990000	-838:59:59	-838:59:59	1
+-838:59:59.900000	-838:59:59	-838:59:59	1
+-838:59:59.100000	-838:59:59	-838:59:59	1
+-838:59:59.000000	-838:59:59	-838:59:59	1
+-837:59:59.999999	-837:59:59	-837:59:59	1
+-837:59:59.999990	-837:59:59	-837:59:59	1
+-837:59:59.999900	-837:59:59	-837:59:59	1
+-837:59:59.999000	-837:59:59	-837:59:59	1
+-837:59:59.990000	-837:59:59	-837:59:59	1
+-837:59:59.900000	-837:59:59	-837:59:59	1
+-837:59:59.100000	-837:59:59	-837:59:59	1
+-837:59:59.000000	-837:59:59	-837:59:59	1
+-23:59:59.999999	-23:59:59	-23:59:59	1
+-23:59:59.999990	-23:59:59	-23:59:59	1
+-23:59:59.999900	-23:59:59	-23:59:59	1
+-23:59:59.999000	-23:59:59	-23:59:59	1
+-23:59:59.990000	-23:59:59	-23:59:59	1
+-23:59:59.900000	-23:59:59	-23:59:59	1
+-23:59:59.100000	-23:59:59	-23:59:59	1
+-23:59:59.000000	-23:59:59	-23:59:59	1
+-00:00:00.999999	00:00:00	00:00:00	1
+-00:00:00.999990	00:00:00	00:00:00	1
+-00:00:00.999900	00:00:00	00:00:00	1
+-00:00:00.999000	00:00:00	00:00:00	1
+-00:00:00.990000	00:00:00	00:00:00	1
+-00:00:00.900000	00:00:00	00:00:00	1
+-00:00:00.100000	00:00:00	00:00:00	1
+00:00:00.000000	00:00:00	00:00:00	1
+00:00:00.000000	00:00:00	00:00:00	1
+00:00:00.100000	00:00:01	00:00:01	1
+00:00:00.900000	00:00:01	00:00:01	1
+00:00:00.990000	00:00:01	00:00:01	1
+00:00:00.999000	00:00:01	00:00:01	1
+00:00:00.999900	00:00:01	00:00:01	1
+00:00:00.999990	00:00:01	00:00:01	1
+00:00:00.999999	00:00:01	00:00:01	1
+23:59:59.000000	23:59:59	23:59:59	1
+23:59:59.100000	24:00:00	24:00:00	1
+23:59:59.900000	24:00:00	24:00:00	1
+23:59:59.990000	24:00:00	24:00:00	1
+23:59:59.999000	24:00:00	24:00:00	1
+23:59:59.999900	24:00:00	24:00:00	1
+23:59:59.999990	24:00:00	24:00:00	1
+23:59:59.999999	24:00:00	24:00:00	1
+837:59:59.000000	837:59:59	837:59:59	1
+837:59:59.100000	838:00:00	838:00:00	1
+837:59:59.900000	838:00:00	838:00:00	1
+837:59:59.990000	838:00:00	838:00:00	1
+837:59:59.999000	838:00:00	838:00:00	1
+837:59:59.999900	838:00:00	838:00:00	1
+837:59:59.999990	838:00:00	838:00:00	1
+837:59:59.999999	838:00:00	838:00:00	1
+838:59:59.000000	838:59:59	838:59:59	1
+838:59:59.100000	838:59:59	838:59:59	1
+838:59:59.900000	838:59:59	838:59:59	1
+838:59:59.990000	838:59:59	838:59:59	1
+838:59:59.999000	838:59:59	838:59:59	1
+838:59:59.999900	838:59:59	838:59:59	1
+838:59:59.999990	838:59:59	838:59:59	1
+838:59:59.999999	838:59:59	838:59:59	1
+DROP FUNCTION FLOOR_SP;
+DROP FUNCTION CEILING_SP;
+DROP TABLE t1;