Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4991

TRUNCATE/ROUND/CEILING functions on TIMESTAMP/DATETIME columns with microsecond precision produce incorrect results.

Details

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

      Attachments

        Activity

          muhe Mu He added a comment -

          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.

          muhe Mu He added a comment - 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.

          Build verified: Drone 7502

          [dlee@aloha rocky8]$ cat buildinfo.txt
          engine: 7bed08766acce775e8a3e18a8e22b146ca2793d7
          server: 11c83d9ae9eb249d00589cc6ab71e7f4e67ffa27
          buildNo: 7502

          dleeyh Daniel Lee (Inactive) added a comment - Build verified: Drone 7502 [dlee@aloha rocky8] $ cat buildinfo.txt engine: 7bed08766acce775e8a3e18a8e22b146ca2793d7 server: 11c83d9ae9eb249d00589cc6ab71e7f4e67ffa27 buildNo: 7502

          People

            drrtuy Roman
            tntnatbry Gagan Goel (Inactive)
            Andrey Piskunov Andrey Piskunov (Inactive)
            Daniel Lee Daniel Lee (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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