MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()

Fixing functions CEILING and FLOOR to return
- TIME for TIME input
- DATETIME for DATETIME and TIMESTAMP input

6 files changed, 645 insertions, 0 deletions

diff --git a/mysql-test/main/type_datetime_hires.result b/mysql-test/main/type_datetime_hires.result
index 5211515ea77..02adadd16c5 100644
--- a/mysql-test/main/type_datetime_hires.result
+++ b/mysql-test/main/type_datetime_hires.result
@@ -900,5 +900,170 @@ f
 NULL
 DROP TABLE t1;
 #
+# MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
+#
+CREATE TABLE t1 AS SELECT
+FLOOR(TIMESTAMP'2001-01-01 00:00:00.999999'),
+CEILING(TIMESTAMP'2001-01-01 00:00:00.999999');
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `FLOOR(TIMESTAMP'2001-01-01 00:00:00.999999')` datetime DEFAULT NULL,
+  `CEILING(TIMESTAMP'2001-01-01 00:00:00.999999')` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999999');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99999');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9999');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.1');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.0');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999999');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99999');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9999');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.1');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.0');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999999');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99999');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9999');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.1');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.0');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999999');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99999');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9999');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.1');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.0');
+CREATE FUNCTION FLOOR_SP(a DATETIME(6)) RETURNS DATETIME
+BEGIN
+RETURN
+CASE
+WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a
+ELSE TRUNCATE(a,0)
+END;
+END;
+$$
+CREATE FUNCTION CEILING_SP(a DATETIME(6)) RETURNS DATETIME
+BEGIN
+RETURN
+CASE
+WHEN TRUNCATE(a,0)=TIMESTAMP'9999-12-31 23:59:59' THEN a
+WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a
+ELSE TRUNCATE(a,0)+INTERVAL 1 SECOND
+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)
+0001-12-31 23:59:59.000000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0001-12-31 23:59:59.100000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0001-12-31 23:59:59.900000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0001-12-31 23:59:59.990000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0001-12-31 23:59:59.999000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0001-12-31 23:59:59.999900	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0001-12-31 23:59:59.999990	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0001-12-31 23:59:59.999999	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0999-12-31 23:59:59.000000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+0999-12-31 23:59:59.100000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+0999-12-31 23:59:59.900000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+0999-12-31 23:59:59.990000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+0999-12-31 23:59:59.999000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+0999-12-31 23:59:59.999900	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+0999-12-31 23:59:59.999990	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+0999-12-31 23:59:59.999999	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+9999-12-30 23:59:59.000000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-30 23:59:59.100000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-30 23:59:59.900000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-30 23:59:59.990000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-30 23:59:59.999000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-30 23:59:59.999900	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-30 23:59:59.999990	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-30 23:59:59.999999	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-31 23:59:59.000000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.100000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.900000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.990000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.999000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.999900	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.999990	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.999999	9999-12-31 23:59:59	9999-12-31 23: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)
+0001-12-31 23:59:59.000000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
+0001-12-31 23:59:59.100000	0002-01-01 00:00:00	0002-01-01 00:00:00	1
+0001-12-31 23:59:59.900000	0002-01-01 00:00:00	0002-01-01 00:00:00	1
+0001-12-31 23:59:59.990000	0002-01-01 00:00:00	0002-01-01 00:00:00	1
+0001-12-31 23:59:59.999000	0002-01-01 00:00:00	0002-01-01 00:00:00	1
+0001-12-31 23:59:59.999900	0002-01-01 00:00:00	0002-01-01 00:00:00	1
+0001-12-31 23:59:59.999990	0002-01-01 00:00:00	0002-01-01 00:00:00	1
+0001-12-31 23:59:59.999999	0002-01-01 00:00:00	0002-01-01 00:00:00	1
+0999-12-31 23:59:59.000000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
+0999-12-31 23:59:59.100000	1000-01-01 00:00:00	1000-01-01 00:00:00	1
+0999-12-31 23:59:59.900000	1000-01-01 00:00:00	1000-01-01 00:00:00	1
+0999-12-31 23:59:59.990000	1000-01-01 00:00:00	1000-01-01 00:00:00	1
+0999-12-31 23:59:59.999000	1000-01-01 00:00:00	1000-01-01 00:00:00	1
+0999-12-31 23:59:59.999900	1000-01-01 00:00:00	1000-01-01 00:00:00	1
+0999-12-31 23:59:59.999990	1000-01-01 00:00:00	1000-01-01 00:00:00	1
+0999-12-31 23:59:59.999999	1000-01-01 00:00:00	1000-01-01 00:00:00	1
+9999-12-30 23:59:59.000000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
+9999-12-30 23:59:59.100000	9999-12-31 00:00:00	9999-12-31 00:00:00	1
+9999-12-30 23:59:59.900000	9999-12-31 00:00:00	9999-12-31 00:00:00	1
+9999-12-30 23:59:59.990000	9999-12-31 00:00:00	9999-12-31 00:00:00	1
+9999-12-30 23:59:59.999000	9999-12-31 00:00:00	9999-12-31 00:00:00	1
+9999-12-30 23:59:59.999900	9999-12-31 00:00:00	9999-12-31 00:00:00	1
+9999-12-30 23:59:59.999990	9999-12-31 00:00:00	9999-12-31 00:00:00	1
+9999-12-30 23:59:59.999999	9999-12-31 00:00:00	9999-12-31 00:00:00	1
+9999-12-31 23:59:59.000000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.100000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.900000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.990000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.999000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.999900	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.999990	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+9999-12-31 23:59:59.999999	9999-12-31 23:59:59	9999-12-31 23:59:59	1
+DROP FUNCTION FLOOR_SP;
+DROP FUNCTION CEILING_SP;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('0000-00-00 23:59:59.999999');
+INSERT INTO t1 VALUES ('0000-00-01 23:59:59.999999');
+INSERT INTO t1 VALUES ('0000-01-01 23:59:59.999999');
+INSERT INTO t1 VALUES ('0001-00-00 23:59:59.999999');
+SELECT a, FLOOR(a), CEILING(a) FROM t1;
+a	FLOOR(a)	CEILING(a)
+0000-00-00 23:59:59.999999	0000-00-00 23:59:59	NULL
+0000-00-01 23:59:59.999999	0000-00-01 23:59:59	NULL
+0000-01-01 23:59:59.999999	0000-01-01 23:59:59	NULL
+0001-00-00 23:59:59.999999	0001-00-00 23:59:59	NULL
+Warnings:
+Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
+Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
+Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
+Warning	1411	Incorrect date value: '0001-00-00' for function round(datetime)
+DROP TABLE t1;
+SET sql_mode=ALLOW_INVALID_DATES;
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('2001-02-28 23:59:59.999999');
+INSERT INTO t1 VALUES ('2001-02-29 23:59:59.999999');
+SELECT a, FLOOR(a), CEILING(a) FROM t1;
+a	FLOOR(a)	CEILING(a)
+2001-02-28 23:59:59.999999	2001-02-28 23:59:59	2001-03-01 00:00:00
+2001-02-29 23:59:59.999999	2001-02-29 23:59:59	NULL
+Warnings:
+Warning	1411	Incorrect date value: '2001-02-29' for function round(datetime)
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/type_datetime_hires.test b/mysql-test/main/type_datetime_hires.test
index 7b3b37560a3..6476d7fb182 100644
--- a/mysql-test/main/type_datetime_hires.test
+++ b/mysql-test/main/type_datetime_hires.test
@@ -157,5 +157,103 @@ DROP TABLE t1;
 
 
 --echo #
+--echo # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
+--echo #
+
+CREATE TABLE t1 AS SELECT
+  FLOOR(TIMESTAMP'2001-01-01 00:00:00.999999'),
+  CEILING(TIMESTAMP'2001-01-01 00:00:00.999999');
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATETIME(6));
+
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999999');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99999');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9999');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.1');
+INSERT INTO t1 VALUES ('9999-12-31 23:59:59.0');
+
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999999');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99999');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9999');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.1');
+INSERT INTO t1 VALUES ('9999-12-30 23:59:59.0');
+
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999999');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99999');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9999');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.1');
+INSERT INTO t1 VALUES ('0999-12-31 23:59:59.0');
+
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999999');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99999');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9999');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.1');
+INSERT INTO t1 VALUES ('0001-12-31 23:59:59.0');
+
+
+DELIMITER $$;
+CREATE FUNCTION FLOOR_SP(a DATETIME(6)) RETURNS DATETIME
+BEGIN
+  RETURN
+    CASE
+      WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a
+      ELSE TRUNCATE(a,0)
+    END;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+CREATE FUNCTION CEILING_SP(a DATETIME(6)) RETURNS DATETIME
+BEGIN
+  RETURN
+    CASE
+      WHEN TRUNCATE(a,0)=TIMESTAMP'9999-12-31 23:59:59' THEN a
+      WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a
+      ELSE TRUNCATE(a,0)+INTERVAL 1 SECOND
+    END;
+END;
+$$
+DELIMITER ;$$
+
+SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a;
+SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a;
+
+DROP FUNCTION FLOOR_SP;
+DROP FUNCTION CEILING_SP;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('0000-00-00 23:59:59.999999');
+INSERT INTO t1 VALUES ('0000-00-01 23:59:59.999999');
+INSERT INTO t1 VALUES ('0000-01-01 23:59:59.999999');
+INSERT INTO t1 VALUES ('0001-00-00 23:59:59.999999');
+SELECT a, FLOOR(a), CEILING(a) FROM t1;
+DROP TABLE t1;
+
+SET sql_mode=ALLOW_INVALID_DATES;
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('2001-02-28 23:59:59.999999');
+INSERT INTO t1 VALUES ('2001-02-29 23:59:59.999999');
+SELECT a, FLOOR(a), CEILING(a) FROM t1;
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+
+--echo #
 --echo # End of 10.4 tests
 --echo #
diff --git a/mysql-test/main/type_time_hires.result b/mysql-test/main/type_time_hires.result
index 6122afcfc47..c7fecbef76b 100644
--- a/mysql-test/main/type_time_hires.result
+++ b/mysql-test/main/type_time_hires.result
@@ -670,3 +670,240 @@ a	ROUND(a,-6)
 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;
diff --git a/mysql-test/main/type_time_hires.test b/mysql-test/main/type_time_hires.test
index 0949738e949..f9b4a5a9f27 100644
--- a/mysql-test/main/type_time_hires.test
+++ b/mysql-test/main/type_time_hires.test
@@ -73,3 +73,126 @@ SELECT a, ROUND(a,-6) FROM t1;
 DROP TABLE t1;
 
 SET time_zone=DEFAULT;
+
+
+--echo #
+--echo # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
+--echo #
+
+CREATE TABLE t1 AS SELECT
+  FLOOR(TIME'00:00:00.999999'),
+  CEILING(TIME'00:00:00.999999');
+SHOW CREATE TABLE t1;
+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');
+
+DELIMITER $$;
+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;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+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;
+$$
+DELIMITER ;$$
+
+SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a;
+SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a;
+
+DROP FUNCTION FLOOR_SP;
+DROP FUNCTION CEILING_SP;
+
+DROP TABLE t1;
diff --git a/mysql-test/main/type_timestamp_hires.result b/mysql-test/main/type_timestamp_hires.result
index dc69aa36b0f..64675ff9973 100644
--- a/mysql-test/main/type_timestamp_hires.result
+++ b/mysql-test/main/type_timestamp_hires.result
@@ -626,5 +626,17 @@ a	ROUND(a,-6)
 DROP TABLE t1;
 SET time_zone=DEFAULT;
 #
+# MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
+#
+CREATE TABLE t1 (a TIMESTAMP NOT NULL);
+CREATE TABLE t2 AS SELECT FLOOR(a), CEILING(a) FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `FLOOR(a)` datetime DEFAULT NULL,
+  `CEILING(a)` datetime DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1, t2;
+#
 # End of 10.4 tests
 #
diff --git a/mysql-test/main/type_timestamp_hires.test b/mysql-test/main/type_timestamp_hires.test
index f2e765d09fe..27aed16d6be 100644
--- a/mysql-test/main/type_timestamp_hires.test
+++ b/mysql-test/main/type_timestamp_hires.test
@@ -107,6 +107,16 @@ DROP TABLE t1;
 
 SET time_zone=DEFAULT;
 
+
+--echo #
+--echo # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
+--echo #
+
+CREATE TABLE t1 (a TIMESTAMP NOT NULL);
+CREATE TABLE t2 AS SELECT FLOOR(a), CEILING(a) FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t1, t2;
+
 --echo #
 --echo # End of 10.4 tests
 --echo #