Bug #101207 Function index on REVERSE(column) does not use index for LIKE
Submitted: 16 Oct 2020 11:27 Modified: 16 Oct 2020 12:11
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
Tags: function index

[16 Oct 2020 11:27] Justin Swanhart
Description:
I created a simple table and sample data to test a function index using the REVERSE function to support wildcard prefix LIKE search using reversed values trick.  

I inserted the values 'abcdef' and 'xyzabc' and provided skew such that only 'abcdef' is selective. But querying it with the REVERSE function does not work.  When querying for REVERSE(c1) LIKE 'def%' the functional index does not work.

When doing exact match on reverse, it does work, but that is not very useful for this use case.

How to repeat:
mysql> create table Z(id bigint auto_increment primary key, c1 varchar(30), c2 int, key((reverse(c1)),c2)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into Z values (1, 'abcdef', 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Z values (2,'xyzabc', 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 128 rows affected (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 256 rows affected (0.01 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 512 rows affected (0.01 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 1024 rows affected (0.02 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 2048 rows affected (0.04 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 4096 rows affected (0.08 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
^[[AQuery OK, 8192 rows affected (0.17 sec)
Records: 8192  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 16384 rows affected (0.21 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 32768 rows affected (0.39 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 65536 rows affected (0.77 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql> explain select c2 from Z where reverse(c1) like 'fed%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | Z     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 131202 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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)

Suggested fix:
Support LIKE with functional indexes?
[16 Oct 2020 12:11] MySQL Verification Team
Hi 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.