Description:
MySQL does not utilise functional indexes for operators like `LIKE'.
How to repeat:
All of the below are executed under:
```
SET SESSION debug="+d,show_hidden_columns";
```
01) Table schema:
CREATE TABLE `a` (
`id` int NOT NULL AUTO_INCREMENT,
`a` varchar(100) DEFAULT NULL,
`!hidden!test01!0!0` varchar(100) GENERATED ALWAYS AS (lower(`a`)) VIRTUAL,
PRIMARY KEY (`id`),
KEY `test01` ((lower(`a`)))
) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
02) Implicit usage does not use the `test01` index:
mysql> explain select * from a where lower(a) like 'a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 96 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
03) Explicit usage of the virtually generated columns makes use of that index:
mysql> explain select * from a where `!hidden!test01!0!0` like 'a';
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | range | test01 | tets01 | 403 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
Suggested fix:
The optimizer should use the functional indexes more consistently.
