← 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
type.

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
int:

  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.

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx


References