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

1 files changed, 123 insertions, 0 deletions

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;