[MDEV-23311] CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE() Created: 2020-07-28  Updated: 2023-07-19  Resolved: 2020-07-29

Status: Closed
Project: MariaDB Server
Component/s: Data types, Temporal Types
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4.14, 10.5.5

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ... Closed
relates to MDEV-23032 FLOOR()/CEIL() incorrectly calculate ... Closed

 Description   

Under terms of MDEV-20397 we modified functions ROUND() and TRUNCATE() to handle the TIME, DATETIME, TIMESTAMP data types correctly:

SELECT ROUND(time'00:00:01.9'), TRUNCATE(TIMESTAMP'2001-01-01 00:00:01.9',0);


+-------------------------+----------------------------------------------+
 
| ROUND(time'00:00:01.9') | TRUNCATE(TIMESTAMP'2001-01-01 00:00:01.9',0) |
 
+-------------------------+----------------------------------------------+
 
| 00:00:02                | 2001-01-01 00:00:01                          |
 
+-------------------------+----------------------------------------------+

But the functions CEILING() and FLOOR() still convert temporal data types to numbers:

SELECT CEILING(time'00:00:01.9'), FLOOR(TIMESTAMP'2001-01-01 00:00:01.9');


+---------------------------+-----------------------------------------+
 
| CEILING(time'00:00:01.9') | FLOOR(TIMESTAMP'2001-01-01 00:00:01.9') |
 
+---------------------------+-----------------------------------------+
 
|                         2 |                          20010101000001 |
 
+---------------------------+-----------------------------------------+

CEILING() and FLOOR() should be fixed to return TIME (for TIME input) and DATETIME (for DATETIME and TIMESTAMP input), like ROUND() and TRUNCATE() do.
Generated at Thu Feb 08 09:21:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.