Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33651

timestamp and datetime round is done wrong

    XMLWordPrintable

Details

    Description

      When we run these queries against a specified data set we expect 2 times the same result for each value:

      select * from test where ts = STR_TO_DATE('2024-02-12 17:19:39.123', '%Y-%m-%d %H:%i:%S.%f');
      select * from test where ts = '2024-02-12 17:19:39.123';
      select * from test where ts = STR_TO_DATE('2024-02-12 17:19:39.000', '%Y-%m-%d %H:%i:%S.%f');
      select * from test where ts = '2024-02-12 17:19:39.000';
      

      But we get:
      Empty set (0.003 sec)
      10483 rows in set (0.031 sec)
      10483 rows in set (0.036 sec)
      10483 rows in set (0.033 sec)

      So case #2 IMHO is wrong. The round is done inappropriately.

      It does not matter if timestamp or datetime is used. But if datetime(3) timestamp(3) is used the result becomes correct.

      Data set is attached to this issue.

      Bonus Info: Query execution plans are also different:

      STR_TO_DATE()

      +------+-------------+-------+------+---------------+------+---------+-------+-------+-----------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                 |
      +------+-------------+-------+------+---------------+------+---------+-------+-------+-----------------------+
      |    1 | SIMPLE      | test  | ref  | ts            | ts   | 6       | const | 20964 | Using index condition |
      +------+-------------+-------+------+---------------+------+---------+-------+-------+-----------------------+
      

      'timestamp'

      +------+-------------+-------+------+---------------+------+---------+-------+-------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra |
      +------+-------------+-------+------+---------------+------+---------+-------+-------+-------+
      |    1 | SIMPLE      | test  | ref  | ts            | ts   | 6       | const | 20964 |       |
      +------+-------------+-------+------+---------------+------+---------+-------+-------+-------+
      

      Which leads theoretically to a better performance in the STR_TO_DATE case but in practice it is about <5 % slower.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            oli Oli Sennhauser
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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