Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.10, 10.2(EOL), 10.3(EOL)
-
None
-
Ubuntu 18.04.1 LTS
Description
select * from days where date < '2018-10-09' order by date desc limit 1
fails if there is an index on date and a record for 2018-10-09 does not exist in the table.
To recreate ....
create table days (date date);
insert into days values ('2018-10-04'), ('2018-10-05');
select * from days where date < '2018-10-09' order by date desc limit 1; # Works as expected
alter table days add index date_index (date);
select * from days where date < '2018-10-05' order by date desc limit 1; # Works as expected
select * from days where date < '2018-10-09' order by date desc limit 1; # FAILS: Empty set (0.001 sec)
Attachments
Activity
The SE API doesn't have a direct equivalent of read_range_first/next for doing reverse scans.
Instead, the QUICK_SELECT_DESC does this:
1. Inform the SE about the other end of the range.
2. Call ha_rocksdb->ha_index_read_map(..., find_flag=HA_READ_BEFORE_KEY)
Step #2 is implemented in different ways in MariaDB and in the Upstream.
The upstream' QUICK_SELECT_DESC::get_next does it as follows:
file->set_end_range(&min_range, handler::RANGE_SCAN_DESC);
|
min_range here has
{key= SQL "NULL", flag = HA_READ_AFTER_KEY}MariaDB's QUICK_SELECT_DESC::get_next uses a different API call:
result= file->prepare_range_scan((last_range->flag & NO_MIN_RANGE) ? NULL : &start_key, |
(last_range->flag & NO_MAX_RANGE) ? NULL : &end_key); |
(gdb) x/4x start_key.key
|
0x7fff78343f48: 0x01 0x00 0x00 0x00 // HA_READ_AFTER_KEY
|
(gdb) x/4x end_key.key
|
0x7fff78343f50: 0x00 0x49 0xc5 0x0f // HA_READ_BEFORE_KEY
|
Comparing with the upstream:
(gdb) p slice
|
$79 = (const rocksdb::Slice &) @0x7ffff40b41f0: {data_ = 0x7fffcc024bf0 "", size_ = 8}
|
(gdb) x/8cx slice.data_
|
0x7fffcc024bf0: 0x00 0x00 0x01 0x04 0x01 0x0f 0xc5 0x49
|
|
|
(gdb) p *end_key
|
$80 = {key = 0x7fffcc0d3808 "\001", length = 4, keypart_map = 1, flag = HA_READ_AFTER_KEY}
|
(gdb) x/4cx end_key->key
|
0x7fffcc0d3808: 0x01 0x00 0x00 0x00
|
Inside myrocks::ha_rocksdb::calc_eq_cond_len, myrocks::ha_rocksdb::index_read_map_impl, we have:
(gdb) p slice
|
$302 = (const rocksdb::Slice &) @0x7fffd9934210: {data_ = 0x7fff783a7dd0 "", size_ = 8}
|
(gdb) x/8cx slice.data_
|
0x7fff783a7dd0: 0x00 0x00 0x01 0x07 0x01 0x0f 0xc5 0x49
|
|
|
(gdb) p *end_key
|
$307 = {key = 0x7fff78343c60 "", length = 4, keypart_map = 1, flag = HA_READ_BEFORE_KEY}
|
(gdb) x/4x end_key.key
|
0x7fff78343c60: 0x00 0x49 0xc5 0x0f
|
That is, it thinks that both start and the end keys are '2018-10-09'.
Investigation:
The range we are scanning is actually NULL < date < '2018-10-09'
Date column is 3 bytes + 1 NULL byte.
'2018-10-09' is encoded as
uint tmp= ltime->year*16*32 + ltime->month*32+ltime->day = 0x0f c5 45
|
Not reproducible on the upstream.
Well, the upstream produces a difference query plan:
MariaDB:
mysql> explain select * from days where date < '2018-10-09' order by date desc limit 1;
|
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | days | range | date_index | date_index | 4 | NULL | 1 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
Upstream, current FB/mysql-5.6
mysql> explain select * from days force index(date_index) where date < '2018-10-09' order by date desc limit 1;
|
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | days | index | date_index | date_index | 4 | NULL | 2 | Using where; Using index |
|
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
... but if I add more rows to the table I can get it to also pick the range plan, and it will produce the correct result.
Thanks for the report and test case. Reproducible as described.
Same workflow with InnoDB or MyISAM works all right.
index_read_map_impl() actually has the code to flip the bounds:
cur_end_key= m_start_range;added by
commit 4d51009a772fdfffc6cc7ae87422a3b0b19fa06aAuthor: Sergei Petrunia <psergey@askmonty.org> Tue May 8 13:00:26 2018Committer: Sergei Petrunia <psergey@askmonty.org> Tue May 8 13:00:26 2018But it only handles closed ranges, not open ones