Details
Major Description
We stumbled upon a strange problem which seems to be related to either an index or an optimizer error which is repeatable but not straightforward to reproduce. For purpose of this bugreport we were able to bring the case down to a simple table:
CREATE TABLE `rt_sum_fm23` (
`route` varchar(20) NOT NULL DEFAULT '',
`timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`route`,`timestamp`)
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=1
which contains a little short of 1.900.000 records. This is relevant as the experienced behaviour seems related to having a certain number of records. This table contains records with 364 distinct entries for 'route' and per route a number of timestamped records. Now see this:
> select route, min(timestamp), max(timestamp) from rt_sum_fm23 where route = 'N201__N205_ZAV';
| route | min(timestamp) | max(timestamp) |
|---|---|---|
| N201__N205_ZAV | 2020-06-07 17:59:00 | 2020-06-12 23:59:00 |
So there is (at least) one record with a timestamp on June 7th, which is supported by the following:
> select * from rt_sum_fm23 where route = 'N201__N205_ZAV' order by timestamp asc limit 5;
| route | timestamp |
|---|---|
| N201__N205_ZAV | 2020-06-07 17:59:00 |
| N201__N205_ZAV | 2020-06-07 18:00:00 |
| N201__N205_ZAV | 2020-06-07 18:01:00 |
| N201__N205_ZAV | 2020-06-07 18:02:00 |
| N201__N205_ZAV | 2020-06-07 18:03:00 |
> explain select * from rt_sum_fm23 where route = 'N201__N205_ZAV' order by timestamp asc limit 5;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | rt_sum_fm23 | ref | PRIMARY | PRIMARY | 22 | const | 7290 | Using where; Using index |
However - and this is the strange behaviour - this is what happens if we add a condition to that where:
> select * from rt_sum_fm23 where route = 'N201__N205_ZAV' and timestamp < 20200608000000 order by timestamp asc limit 5;
Empty set (0.00 sec)
> explain select * from rt_sum_fm23 where route = 'N201__N205_ZAV' and timestamp < 20200608000000 order by timestamp asc limit 5;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
Repairing the table results in either the problem remaining the same,, or it shifts (other 'routes' experience the same issue). We have made an export (dump to SQL) and imported that on two different hosts what resulted in the same experience (even the same route). The SQL dump is attached for your benefit.
We have done numerous tests and the behavior is repeatable.
edit: removed some incorrect markup from the text, only editorial changes