summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--include/my_time.h7
-rw-r--r--mysql-test/main/type_datetime_hires.result165
-rw-r--r--mysql-test/main/type_datetime_hires.test98
-rw-r--r--mysql-test/main/type_time_hires.result237
-rw-r--r--mysql-test/main/type_time_hires.test123
-rw-r--r--mysql-test/main/type_timestamp_hires.result12
-rw-r--r--mysql-test/main/type_timestamp_hires.test10
-rw-r--r--sql/item_func.cc51
-rw-r--r--sql/item_func.h27
-rw-r--r--sql/item_timefunc.cc2
-rw-r--r--sql/protocol.cc2
-rw-r--r--sql/sql_prepare.cc2
-rw-r--r--sql/sql_type.cc42
-rw-r--r--sql/sql_type.h61
14 files changed, 827 insertions, 12 deletions
diff --git a/include/my_time.h b/include/my_time.h
index bad2b8bf4b0..b7b54db5586 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -233,9 +233,16 @@ static inline long my_time_fraction_remainder(long nr, uint decimals)
DBUG_ASSERT(decimals <= TIME_SECOND_PART_DIGITS);
return nr % (long) log_10_int[TIME_SECOND_PART_DIGITS - decimals];
}
+static inline void my_datetime_trunc(MYSQL_TIME *ltime, uint decimals)
+{
+ ltime->second_part-= my_time_fraction_remainder(ltime->second_part, decimals);
+}
static inline void my_time_trunc(MYSQL_TIME *ltime, uint decimals)
{
ltime->second_part-= my_time_fraction_remainder(ltime->second_part, decimals);
+ if (!ltime->second_part && ltime->neg &&
+ !ltime->hour && !ltime->minute && !ltime->second)
+ ltime->neg= FALSE;
}
static inline void my_timeval_trunc(struct timeval *tv, uint decimals)
{
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 #
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 45a7b155481..424437ead07 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2218,8 +2218,7 @@ bool Item_func_int_val::fix_length_and_dec()
{
DBUG_ENTER("Item_func_int_val::fix_length_and_dec");
DBUG_PRINT("info", ("name %s", func_name()));
- if (args[0]->cast_to_int_type_handler()->
- Item_func_int_val_fix_length_and_dec(this))
+ if (args[0]->type_handler()->Item_func_int_val_fix_length_and_dec(this))
DBUG_RETURN(TRUE);
DBUG_PRINT("info", ("Type: %s", type_handler()->name().ptr()));
DBUG_RETURN(FALSE);
@@ -2262,6 +2261,28 @@ my_decimal *Item_func_ceiling::decimal_op(my_decimal *decimal_value)
}
+bool Item_func_ceiling::date_op(THD *thd, MYSQL_TIME *to, date_mode_t fuzzydate)
+{
+ Datetime::Options opt(thd, TIME_FRAC_TRUNCATE);
+ Datetime *tm= new (to) Datetime(thd, args[0], opt);
+ tm->ceiling(thd);
+ null_value= !tm->is_valid_datetime();
+ DBUG_ASSERT(maybe_null || !null_value);
+ return null_value;
+}
+
+
+bool Item_func_ceiling::time_op(THD *thd, MYSQL_TIME *to)
+{
+ static const Time::Options_for_round opt;
+ Time *tm= new (to) Time(thd, args[0], opt);
+ tm->ceiling();
+ null_value= !tm->is_valid_time();
+ DBUG_ASSERT(maybe_null || !null_value);
+ return null_value;
+}
+
+
longlong Item_func_floor::int_op()
{
switch (args[0]->result_type()) {
@@ -2302,6 +2323,28 @@ my_decimal *Item_func_floor::decimal_op(my_decimal *decimal_value)
}
+bool Item_func_floor::date_op(THD *thd, MYSQL_TIME *to, date_mode_t fuzzydate)
+{
+ // DATETIME is not negative, so FLOOR means just truncation
+ Datetime::Options opt(thd, TIME_FRAC_TRUNCATE);
+ Datetime *tm= new (to) Datetime(thd, args[0], opt, 0);
+ null_value= !tm->is_valid_datetime();
+ DBUG_ASSERT(maybe_null || !null_value);
+ return null_value;
+}
+
+
+bool Item_func_floor::time_op(THD *thd, MYSQL_TIME *to)
+{
+ static const Time::Options_for_round opt;
+ Time *tm= new (to) Time(thd, args[0], opt);
+ tm->floor();
+ null_value= !tm->is_valid_time();
+ DBUG_ASSERT(maybe_null || !null_value);
+ return null_value;
+}
+
+
void Item_func_round::fix_length_and_dec_decimal(uint decimals_to_set)
{
int decimals_delta= args[0]->decimals - decimals_to_set;
@@ -2540,9 +2583,7 @@ bool Item_func_round::time_op(THD *thd, MYSQL_TIME *to)
{
DBUG_ASSERT(args[0]->type_handler()->mysql_timestamp_type() ==
MYSQL_TIMESTAMP_TIME);
- Time::Options opt(Time::default_flags_for_get_date(),
- truncate ? TIME_FRAC_TRUNCATE : TIME_FRAC_ROUND,
- Time::DATETIME_TO_TIME_DISALLOW);
+ Time::Options_for_round opt(truncate ? TIME_FRAC_TRUNCATE : TIME_FRAC_ROUND);
Longlong_hybrid_null dec= args[1]->to_longlong_hybrid_null();
Time *tm= new (to) Time(thd, args[0], opt,
dec.to_uint(TIME_SECOND_PART_DIGITS));
diff --git a/sql/item_func.h b/sql/item_func.h
index a8727272adc..dc9ec9be742 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1666,13 +1666,32 @@ public:
};
-class Item_func_int_val :public Item_func_num1
+class Item_func_int_val :public Item_func_hybrid_field_type
{
public:
- Item_func_int_val(THD *thd, Item *a): Item_func_num1(thd, a) {}
+ Item_func_int_val(THD *thd, Item *a): Item_func_hybrid_field_type(thd, a) {}
+ bool check_partition_func_processor(void *int_arg) { return FALSE; }
+ bool check_vcol_func_processor(void *arg) { return FALSE; }
void fix_length_and_dec_double();
void fix_length_and_dec_int_or_decimal();
+ void fix_length_and_dec_time()
+ {
+ fix_attributes_time(0);
+ set_handler(&type_handler_time2);
+ }
+ void fix_length_and_dec_datetime()
+ {
+ fix_attributes_datetime(0);
+ set_handler(&type_handler_datetime2);
+ maybe_null= true; // E.g. CEILING(TIMESTAMP'0000-01-01 23:59:59.9')
+ }
bool fix_length_and_dec();
+ String *str_op(String *str) { DBUG_ASSERT(0); return 0; }
+ bool native_op(THD *thd, Native *to)
+ {
+ DBUG_ASSERT(0);
+ return true;
+ }
};
@@ -1684,6 +1703,8 @@ public:
longlong int_op();
double real_op();
my_decimal *decimal_op(my_decimal *);
+ bool date_op(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate);
+ bool time_op(THD *thd, MYSQL_TIME *ltime);
Item *get_copy(THD *thd)
{ return get_item_copy<Item_func_ceiling>(thd, this); }
};
@@ -1697,6 +1718,8 @@ public:
longlong int_op();
double real_op();
my_decimal *decimal_op(my_decimal *);
+ bool date_op(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate);
+ bool time_op(THD *thd, MYSQL_TIME *ltime);
Item *get_copy(THD *thd)
{ return get_item_copy<Item_func_floor>(thd, this); }
};
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 924fdecffa9..d10c00aa325 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1580,7 +1580,7 @@ static void set_sec_part(ulong sec_part, MYSQL_TIME *ltime, Item *item)
{
ltime->second_part= sec_part;
if (item->decimals < TIME_SECOND_PART_DIGITS)
- my_time_trunc(ltime, item->decimals);
+ my_datetime_trunc(ltime, item->decimals);
}
}
diff --git a/sql/protocol.cc b/sql/protocol.cc
index 8c7eeaec90c..947ddd33d4c 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -1524,7 +1524,7 @@ bool Protocol_binary::store(MYSQL_TIME *tm, int decimals)
DBUG_ASSERT(decimals == AUTO_SEC_PART_DIGITS ||
(decimals >= 0 && decimals <= TIME_SECOND_PART_DIGITS));
if (decimals != AUTO_SEC_PART_DIGITS)
- my_time_trunc(tm, decimals);
+ my_datetime_trunc(tm, decimals);
int4store(pos+7, tm->second_part);
if (tm->second_part)
length=11;
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 8ec6ef96511..e5b178bd641 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -5330,7 +5330,7 @@ bool Protocol_local::store(const char *str, size_t length,
bool Protocol_local::store(MYSQL_TIME *time, int decimals)
{
if (decimals != AUTO_SEC_PART_DIGITS)
- my_time_trunc(time, decimals);
+ my_datetime_trunc(time, decimals);
return store_column(time, sizeof(MYSQL_TIME));
}
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index b5711f6b41c..8b6d99de621 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -980,7 +980,7 @@ bool Temporal::datetime_round_or_invalidate(THD *thd, uint dec, int *warn, ulong
DBUG_ASSERT(dec <= TIME_SECOND_PART_DIGITS);
if (datetime_add_nanoseconds_or_invalidate(thd, warn, nsec))
return true;
- my_time_trunc(this, dec);
+ my_datetime_trunc(this, dec);
return false;
}
@@ -5732,6 +5732,22 @@ bool Type_handler_int_result::
}
+bool Type_handler_typelib::
+ Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
+{
+ item->fix_length_and_dec_int_or_decimal();
+ return false;
+}
+
+
+bool Type_handler_hex_hybrid::
+ Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
+{
+ item->fix_length_and_dec_int_or_decimal();
+ return false;
+}
+
+
bool Type_handler_real_result::
Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
{
@@ -5756,6 +5772,30 @@ bool Type_handler_temporal_result::
}
+bool Type_handler_time_common::
+ Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
+{
+ item->fix_length_and_dec_time();
+ return false;
+}
+
+
+bool Type_handler_datetime_common::
+ Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
+{
+ item->fix_length_and_dec_datetime();
+ return false;
+}
+
+
+bool Type_handler_timestamp_common::
+ Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
+{
+ item->fix_length_and_dec_datetime();
+ return false;
+}
+
+
bool Type_handler_string_result::
Item_func_int_val_fix_length_and_dec(Item_func_int_val *item) const
{
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 2be651a2f2f..efc8e94e454 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -1358,6 +1358,14 @@ public:
{ }
};
+ class Options_for_round: public Options
+ {
+ public:
+ Options_for_round(time_round_mode_t round_mode= TIME_FRAC_TRUNCATE)
+ :Options(Time::default_flags_for_get_date(), round_mode,
+ Time::DATETIME_TO_TIME_DISALLOW)
+ { }
+ };
class Options_cmp: public Options
{
public:
@@ -1709,6 +1717,40 @@ public:
DBUG_ASSERT(is_valid_value_slow());
return *this;
}
+ Time &ceiling(int *warn)
+ {
+ if (is_valid_time())
+ {
+ if (neg)
+ my_time_trunc(this, 0);
+ else if (second_part)
+ round_or_set_max(0, warn, 999999999);
+ }
+ DBUG_ASSERT(is_valid_value_slow());
+ return *this;
+ }
+ Time &ceiling()
+ {
+ int warn= 0;
+ return ceiling(&warn);
+ }
+ Time &floor(int *warn)
+ {
+ if (is_valid_time())
+ {
+ if (!neg)
+ my_time_trunc(this, 0);
+ else if (second_part)
+ round_or_set_max(0, warn, 999999999);
+ }
+ DBUG_ASSERT(is_valid_value_slow());
+ return *this;
+ }
+ Time &floor()
+ {
+ int warn= 0;
+ return floor(&warn);
+ }
Time &round(uint dec, int *warn)
{
if (is_valid_time())
@@ -2276,10 +2318,22 @@ public:
Datetime &trunc(uint dec)
{
if (is_valid_datetime())
- my_time_trunc(this, dec);
+ my_datetime_trunc(this, dec);
+ DBUG_ASSERT(is_valid_value_slow());
+ return *this;
+ }
+ Datetime &ceiling(THD *thd, int *warn)
+ {
+ if (is_valid_datetime() && second_part)
+ round_or_invalidate(thd, 0, warn, 999999999);
DBUG_ASSERT(is_valid_value_slow());
return *this;
}
+ Datetime &ceiling(THD *thd)
+ {
+ int warn= 0;
+ return ceiling(thd, &warn);
+ }
Datetime &round(THD *thd, uint dec, int *warn)
{
if (is_valid_datetime())
@@ -5305,6 +5359,7 @@ public:
MYSQL_TIME *, date_mode_t fuzzydate) const;
longlong Item_func_between_val_int(Item_func_between *func) const;
bool Item_func_round_fix_length_and_dec(Item_func_round *) const;
+ bool Item_func_int_val_fix_length_and_dec(Item_func_int_val *) const;
Item *make_const_item_for_comparison(THD *, Item *src, const Item *cmp) const;
bool set_comparator_func(Arg_comparator *cmp) const;
cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) const;
@@ -5532,6 +5587,7 @@ public:
my_decimal *Item_func_min_max_val_decimal(Item_func_min_max *,
my_decimal *) const;
bool Item_func_round_fix_length_and_dec(Item_func_round *) const;
+ bool Item_func_int_val_fix_length_and_dec(Item_func_int_val *) const;
bool Item_hybrid_func_fix_attributes(THD *thd,
const char *name,
Type_handler_hybrid_field_type *,
@@ -5634,6 +5690,7 @@ public:
int cmp_native(const Native &a, const Native &b) const;
longlong Item_func_between_val_int(Item_func_between *func) const;
bool Item_func_round_fix_length_and_dec(Item_func_round *) const;
+ bool Item_func_int_val_fix_length_and_dec(Item_func_int_val *) const;
cmp_item *make_cmp_item(THD *thd, CHARSET_INFO *cs) const;
in_vector *make_in_vector(THD *thd, const Item_func_in *f, uint nargs) const;
void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field,
@@ -5977,6 +6034,7 @@ public:
const Name name() const { return m_name_hex_hybrid; }
const Type_handler *cast_to_int_type_handler() const;
const Type_handler *type_handler_for_system_time() const;
+ bool Item_func_int_val_fix_length_and_dec(Item_func_int_val *) const;
};
@@ -6212,6 +6270,7 @@ public:
enum_field_types field_type() const { return MYSQL_TYPE_STRING; }
const Type_handler *type_handler_for_item_field() const;
const Type_handler *cast_to_int_type_handler() const;
+ bool Item_func_int_val_fix_length_and_dec(Item_func_int_val *) const;
bool Item_hybrid_func_fix_attributes(THD *thd,
const char *name,
Type_handler_hybrid_field_type *,
View Lorry Controller Status