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

optimizer fails to optimize query on timestamp column if where condition falls in the DST hour that do not exist

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.1.38
    • Fix Version/s: 10.3, 10.4
    • Component/s: Server
    • Labels:
      None

      Description

      If the condition in the WHERE statement on a timestamp column falls within that hour that does not exist (because we're switching to DST) the optimizer goes nuts and is unable to properly evaluate the row count, hence performing a table scan as a result.
      This can be seen in the explain output below, but also happens in real world i.e. relevant queries take several minutes to execute.
      This also applies if the condition uses a date/time function, i.e. if the result of such function falls within the hour which does not exist.

      In the following example, the timezone is from the UK (now BST) and the DST switch happens exactly at 2019-03-31 01:00:00. In fact, there are no records on database for the hour which doesn't exist, as expected.

      MariaDB [schema]> explain select count(*)  from message where when_updated between '2019-03-31 01:00:00' and '2019-03-31 01:00:01';
      +------+-------------+---------+-------+---------------+--------------+---------+------+----------+--------------------------+
      | id   | select_type | table   | type  | possible_keys | key          | key_len | ref  | rows     | Extra                    |
      +------+-------------+---------+-------+---------------+--------------+---------+------+----------+--------------------------+
      |    1 | SIMPLE      | message | index | when_updated  | when_updated | 4       | NULL | 17206865 | Using where; Using index |
      +------+-------------+---------+-------+---------------+--------------+---------+------+----------+--------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [schema]> explain select count(*)  from message where when_updated between '2019-03-31 00:00:59' and '2019-03-31 00:00:59';
      +------+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+
      | id   | select_type | table   | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
      +------+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+
      |    1 | SIMPLE      | message | ref  | when_updated  | when_updated | 4       | const |    1 | Using where; Using index |
      +------+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+
      1 row in set (0.02 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              rpizzi Rick Pizzi
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration