[#MDEV-19121] optimizer fails to optimize query on timestamp column if where condition falls in the DST hour that do not exist

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)

[MDEV-19121] optimizer fails to optimize query on timestamp column if where condition falls in the DST hour that do not exist Created: 2019-04-01 Updated: 2023-09-12
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Server
Affects Version/s:	10.1.38
Fix Version/s:	10.4

[MDEV-19121] optimizer fails to optimize query on timestamp column if where condition falls in the DST hour that do not exist Created: 2019-04-01 Updated: 2023-09-12