Bug #104713 Functional Index not working with LIKE operator
Submitted: 24 Aug 2021 22:56 Modified: 25 Aug 2021 6:27
Reporter: Juan Arruti Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.25, 8.0.26 OS:Any
Assigned to: CPU Architecture:Any

[24 Aug 2021 22:56] Juan Arruti
Description:
When querying based on a function MySQL does not use the available functional index when using the LIKE operator.

How to repeat:
mysql [localhost:5679] {msandbox} (test) > select @@version ;
+-----------+
| @@version |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:5679] {msandbox} (test) > create table t1 (c1 varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql [localhost:5679] {msandbox} (test) > alter table t1 add index ((lower(c1)));
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) = 'A' ;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | functional_index | functional_index | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) LIKE 'A' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) LIKE 'A%' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Suggested fix:
There should be a similar behavior between normal indexes and functionals.
[25 Aug 2021 6:27] MySQL Verification Team
Hello Juan Arruti,

Thank you for the report and test case.
Verified as described.

regards,
Umesh