Bug #101208 Covering index not working with function index
Submitted: 16 Oct 2020 11:39 Modified: 16 Oct 2020 12:13
Reporter: Justin Swanhart Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[16 Oct 2020 11:39] Justin Swanhart
Description:
Same test case and example queries as 101207

The index is defined as (REVERSE(c1)),2 but there is no "using index" note in the query plan to indicate that selecting c2 with equality on reverse(c1)='fedcba' 

How to repeat:
mysql> explain select c2 from Z where reverse(c1) = 'fedcba';
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | Z     | NULL       | ref  | functional_index | functional_index | 123     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

^ should see USING INDEX in EXTRA field

Suggested fix:
?
[16 Oct 2020 12:13] MySQL Verification Team
Hello Mr. Swanhart,

Thank you for your bug report.

However, we do not think that it is a bug.

It, however, makes an excellent feature request.

Verified as a feature request.
[16 Oct 2020 12:14] MySQL Verification Team
Just setting the correct category.