← Back to team overview

maria-discuss team mailing list archive

Re: Strange timestamp index behaviour / bug


Hi, Reinis!

On Apr 01, Reinis Rozitis wrote:
> Hello,
> before reporting it as bug I wanted to understand what's really going
> on or how to investigate it further:
> Because of the DST changes we experienced a strange issue where
> afterwards a query suddenly wouldn't use an index on a timestamp
> column anymore and would do a full tablescan and because of the table
> size literally brought down the db.
> MariaDB [db]> explain select * from tmp where `ts` > '2019-03-31 03:00:00';

This is intentional behavior. Perhaps it might be improved, but it's not
a bug. For range access to work, the literal must have the same data
type as the column or it should be possible to convert it to the column

For example, this is fine, same data type:

  explain select * from int_column > 10

this is fine too, the data type differs, but it can be converted to an

  explain select * from int_column > 1e9

But this is not, the value cannot be converted to an int:

  explain select * from int_column > 1e10

In your case you have a timestamp column and a string literal that
cannot be converted to a timestamp.

Chief Architect MariaDB
and security@xxxxxxxxxxx