Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
Description
As of versions 10.3.x and 10.4.7, functions ROUND() and TRUNCATE() convert temporal input to double and then return a rounded/truncated double value.
This behaviour is quite useless, because can return '60' at the end:
- ROUND() in double format can return '60' at the end by definition
- TRUNCATE() in double format can return '60' at the end because of lack of precision:
TRUNCATE(TIMESTAMP'2001-12-31 23:59:59.999999', 1);
+----------------------------------------------------+
| TRUNCATE(TIMESTAMP'2001-12-31 23:59:59.999999', 1) |
+----------------------------------------------------+
| 20011231235960.0 |
+----------------------------------------------------+
Therefore a rounded or truncated double value cannot be converted back to TIMESTAMP, DATETIME or TIME easily.
In order to fix MDEV-18153 easier, let's implement native TIMESTAMP, DATETIME, TIME support in ROUND() and TRUNCATE().
Let's change these functions to return:
- TIME in case of a TIME input
- DATETIME in case of a DATETIME input
- DATETIME in case of a TIMESTAMP input
Note, ROUND(timestamp) will return DATETIME for now. It should be fine in 99% cases, but won't work well near DST changes. Let's implement real native TIMESTAMP support in ROUND() in a separate change later.
Note, the DATE data type is out of scope of this task. ROUND() and TRUNCATE() will still return a double result for a DATE input. Native DATE support for ROUND() and TRUNCATE() will be implemented separately, when needed.
Attachments
Issue Links
- blocks
-
MDEV-18153 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL
- Closed
- relates to
-
MDEV-23032 FLOOR()/CEIL() incorrectly calculate the precision of a DECIMAL(M,D) column.
- Closed
-
MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
- Closed