Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
6.2.1
-
2021-17, 2022-22, 2022-23, 2023-4, 2023-5, 2023-6
Description
+underlined text+
MariaDB [test]> create table t1 (a timestamp(6), b datetime(6))engine=columnstore; |
Query OK, 0 rows affected (0.233 sec) |
|
MariaDB [test]> insert into t1 values ('2022-01-02 11:13:15.123456', '2022-01-01 12:14:16.789456'); |
Query OK, 1 row affected (0.195 sec)
|
|
MariaDB [test]> insert into t1 values ('2022-01-01 12:14:16.789456', '2022-01-02 11:13:15.123456'); |
Query OK, 1 row affected (0.167 sec)
|
|
MariaDB [test]> create table i1 as select * from t1; |
Query OK, 2 rows affected (0.055 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
CEILING on TIMESTAMP/DATETIME columns in CS:
MariaDB [test]> select a, b, ceiling(a), ceiling(b) from t1; |
+----------------------------+----------------------------+---------------------+---------------------+ |
| a | b | ceiling(a) | ceiling(b) |
|
+----------------------------+----------------------------+---------------------+---------------------+ |
| 2022-01-02 11:13:15.123456 | 2022-01-01 12:14:16.789456 | 2022-01-02 11:13:15 | 2022-01-01 12:14:16 |
|
| 2022-01-01 12:14:16.789456 | 2022-01-02 11:13:15.123456 | 2022-01-01 12:14:16 | 2022-01-02 11:13:15 |
|
+----------------------------+----------------------------+---------------------+---------------------+ |
2 rows in set (0.027 sec) |
CEILING on TIMESTAMP/DATETIME columns in InnoDB:
MariaDB [test]> select a, b, ceiling(a), ceiling(b) from i1; |
+----------------------------+----------------------------+---------------------+---------------------+ |
| a | b | ceiling(a) | ceiling(b) |
|
+----------------------------+----------------------------+---------------------+---------------------+ |
| 2022-01-02 11:13:15.123456 | 2022-01-01 12:14:16.789456 | 2022-01-02 11:13:16 | 2022-01-01 12:14:17 |
|
| 2022-01-01 12:14:16.789456 | 2022-01-02 11:13:15.123456 | 2022-01-01 12:14:17 | 2022-01-02 11:13:16 |
|
+----------------------------+----------------------------+---------------------+---------------------+ |
2 rows in set (0.001 sec) |
TRUNCATE on DATETIME column in CS:
MariaDB [test]> select b, truncate(b, -2), truncate(b, 0), truncate(b, 2) from t1; |
+----------------------------+---------------------+---------------------+------------------------+ |
| b | truncate(b, -2) | truncate(b, 0) | truncate(b, 2) | |
+----------------------------+---------------------+---------------------+------------------------+ |
| 2022-01-01 12:14:16.789456 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00.00 |
|
| 2022-01-02 11:13:15.123456 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00.00 |
|
+----------------------------+---------------------+---------------------+------------------------+ |
2 rows in set (0.024 sec) |
TRUNCATE on DATETIME column in InnoDB:
MariaDB [test]> select b, truncate(b, -2), truncate(b, 0), truncate(b, 2) from i1; |
+----------------------------+---------------------+---------------------+------------------------+ |
| b | truncate(b, -2) | truncate(b, 0) | truncate(b, 2) | |
+----------------------------+---------------------+---------------------+------------------------+ |
| 2022-01-01 12:14:16.789456 | 2022-01-01 12:14:16 | 2022-01-01 12:14:16 | 2022-01-01 12:14:16.78 |
|
| 2022-01-02 11:13:15.123456 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15.12 |
|
+----------------------------+---------------------+---------------------+------------------------+ |
2 rows in set (0.001 sec) |
ROUND on TIMESTAMP column in CS:
MariaDB [test]> select a, round(a, -2), round(a, 0), round(a, 2) from t1; |
+----------------------------+---------------------+---------------------+------------------------+ |
| a | round(a, -2) | round(a, 0) | round(a, 2) |
|
+----------------------------+---------------------+---------------------+------------------------+ |
| 2022-01-02 11:13:15.123456 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15.12 |
|
| 2022-01-01 12:14:16.789456 | 2022-01-01 12:14:16 | 2022-01-01 12:14:16 | 2022-01-01 12:14:16.78 |
|
+----------------------------+---------------------+---------------------+------------------------+ |
2 rows in set (0.021 sec) |
ROUND on TIMESTAMP column in InnoDB:
MariaDB [test]> select a, round(a, -2), round(a, 0), round(a, 2) from i1; |
+----------------------------+---------------------+---------------------+------------------------+ |
| a | round(a, -2) | round(a, 0) | round(a, 2) |
|
+----------------------------+---------------------+---------------------+------------------------+ |
| 2022-01-02 11:13:15.123456 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15.12 |
|
| 2022-01-01 12:14:16.789456 | 2022-01-01 12:14:17 | 2022-01-01 12:14:17 | 2022-01-01 12:14:16.79 |
|
+----------------------------+---------------------+---------------------+------------------------+ |
2 rows in set (0.001 sec) |
ROUND on DATETIME column in CS:
MariaDB [test]> select b, round(b, -2), round(b, 0), round(b, 2) from t1; |
+----------------------------+---------------------+---------------------+------------------------+ |
| b | round(b, -2) | round(b, 0) | round(b, 2) |
|
+----------------------------+---------------------+---------------------+------------------------+ |
| 2022-01-01 12:14:16.789456 | 2022-01-01 12:14:16 | 2022-01-01 12:14:16 | 2022-01-01 12:14:16.78 |
|
| 2022-01-02 11:13:15.123456 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15.12 |
|
+----------------------------+---------------------+---------------------+------------------------+ |
2 rows in set (0.020 sec) |
ROUND on DATETIME column in InnoDB:
MariaDB [test]> select b, round(b, -2), round(b, 0), round(b, 2) from i1; |
+----------------------------+---------------------+---------------------+------------------------+ |
| b | round(b, -2) | round(b, 0) | round(b, 2) |
|
+----------------------------+---------------------+---------------------+------------------------+ |
| 2022-01-01 12:14:16.789456 | 2022-01-01 12:14:17 | 2022-01-01 12:14:17 | 2022-01-01 12:14:16.79 |
|
| 2022-01-02 11:13:15.123456 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15 | 2022-01-02 11:13:15.12 |
|
+----------------------------+---------------------+---------------------+------------------------+ |
2 rows in set (0.001 sec) |
In the ceil function, the original code do nothing to ceil it, and could be solved by adding a value according to the storing format of the different datatypes.
In the truncate function, a wrong returning format had been returned and causing all 0 output.
In the round function, a string like execution had been used which was slow and could cause bugs. And I refactored it by only using numeral calculations which could also do round function correctly.
Btw, the bug exists not only in datetime and timestamp, but also in time, which also has been solved.