Wrong query plan for tpcc stock_level about mysql-5.6

[laurynas-biveinis]

Both EXPLAIN outputs seem identical, any chance this is the same thing pasted twice?

[rockeet]

Both EXPLAIN outputs seem identical, any chance this is the same thing pasted twice?

It's my fault, I pasted wrong text for the second one.

Now I have re-run the two explain on a larger data set and updated the above result -- the second explain result is ok, the query plan using key_len 12 and estimated rows is 13482. the first one is wrong, it using key_len 8, which causing slow index range scan.

Also I have reproduced this issue on upstream(Oracle) mysql-8.0.30, it seems this issue was inroduced in some recent revision by upstream mysql. So I worked around this issue by rewrite the relavant tpcc subquery to:

explain SELECT ol_i_id FROM bmsql_order_line WHERE (ol_w_id, ol_d_id, ol_o_id) IN
 (WITH RECURSIVE nums AS (SELECT 1 AS value UNION ALL SELECT value + 1 AS value FROM nums WHERE nums.value < 20) 
   SELECT d_w_id, d_id, d_next_o_id - value
    FROM nums cross join bmsql_district where d_w_id = 1 AND d_id = 5);

The new query force query plan to use the full 3 columns of the index(key_len = 12):

+----+-------------+------------------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key     | key_len | ref              | rows | filtered | Extra                      |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------+
|  1 | PRIMARY     | bmsql_district   | p0         | const | PRIMARY       | PRIMARY | 8       | const,const      |    1 |   100.00 | NULL                       |
|  1 | PRIMARY     | <derived3>       | NULL       | ALL   | NULL          | NULL    | NULL    | NULL             |    3 |   100.00 | Start temporary            |
|  1 | PRIMARY     | bmsql_order_line | p0         | ref   | PRIMARY       | PRIMARY | 12      | const,const,func |   10 |   100.00 | Using where; End temporary |
|  3 | DERIVED     | NULL             | NULL       | NULL  | NULL          | NULL    | NULL    | NULL             | NULL |     NULL | No tables used             |
|  4 | UNION       | nums             | NULL       | ALL   | NULL          | NULL    | NULL    | NULL             |    2 |    50.00 | Recursive; Using where     |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------+

Since this is an upstream issue, if myrocks team have no interest on tracking this issue, just close it.