Branch: fb-mysql-8.0.28
Version: gitsha c75bf30
This bug impact both innodb and myrocks, an version of 6 months ago has no this issue.
Reproduce
Reproduce is very quickly, just prepair tpcc data with warehouses=5, loadWorkers=5, terminals=5, then:
mysql> explain SELECT ol_i_id, d_next_o_id FROM bmsql_order_line JOIN bmsql_district ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id between d_next_o_id - 20 AND d_next_o_id WHERE d_w_id = 1 AND d_id = 5;
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------------+--------+----------+-------------+
| 1 | SIMPLE | bmsql_district | p0 | const | PRIMARY | PRIMARY | 8 | const,const | 1 | 100.00 | NULL |
| 1 | SIMPLE | bmsql_order_line | p0 | ref | PRIMARY | PRIMARY | 8 | const,const | 120517 | 11.11 | Using where |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------------+--------+----------+-------------+
The rows column presented this query plain is wrong, it use range scan instead of const match. If we rewrite this sql to:
SELECT ol_i_id, d_next_o_id FROM bmsql_order_line JOIN bmsql_district
ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id in
(2980,2981,2982,2983,2984,2985,2986,2987,2988,2990,2991,2992,2993,2994,2995,2996,2997,2998,2999,3000,3001)
WHERE d_w_id = 1 AND d_id = 5;The result is correct:
explain SELECT ol_i_id FROM bmsql_order_line JOIN bmsql_district ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id in (2980,2981,2982,2983,2984,2985,2986,2987,2988,2990,2991,2992,2993,2994,2995,2996,2997,2998,2999,3000,3001) WHERE d_w_id = 1 AND d_id = 5;
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------------+-------+----------+-------------+
| 1 | SIMPLE | bmsql_district | p0 | const | PRIMARY | PRIMARY | 8 | const,const | 1 | 100.00 | Using index |
| 1 | SIMPLE | bmsql_order_line | p0 | range | PRIMARY | PRIMARY | 12 | NULL | 13482 | 100.00 | Using where |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------------+-------+----------+-------------+
Branch: fb-mysql-8.0.28
Version: gitsha c75bf30
This bug impact both innodb and myrocks, an version of 6 months ago has no this issue.
Reproduce
Reproduce is very quickly, just prepair tpcc data with
warehouses=5,loadWorkers=5,terminals=5, then:The
rowscolumn presented this query plain is wrong, it use range scan instead of const match. If we rewrite this sql to:The result is correct: