[MCOL-4991] TRUNCATE/ROUND/CEILING functions on TIMESTAMP/DATETIME columns with microsecond precision produce incorrect results. - Jira

XML

Word

Printable

+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)

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.