the optimizer should treat predicate 'like' the same way as predicate '=' when it is an exact match

[tshqin]

there is a multi-column index (a,c,b) on the table, when using 'like' on a condition which is an exact match, the next column in multi-column index could not use index scan.

create table t3 (a int, b decimal(10,2), c varchar(10), d double, e timestamp);
desc select * from t3 where a=5 and b=5.2 and c like 'ok';
desc select * from t3 where a=5 and b=5.2 and c = 'ok';

mysql> desc select * from t3 where a=5 and b=5.2 and c like 'ok';
+-----------------------+-------+------+--------------------------------------------------------------------------------+
| id                    | count | task | operator info                                                                  |
+-----------------------+-------+------+--------------------------------------------------------------------------------+
| IndexLookUp_11        | 0.10  | root |                                                                                |
| ├─Selection_10        | 0.10  | cop  | eq(gin.t3.b, 5.2)                                                              |
| │ └─IndexScan_8       | 0.10  | cop  | table:t3, index:a, c, b, range:[5 "ok",5 "ok"], keep order:false, stats:pseudo |
| └─TableScan_9         | 0.10  | cop  | table:t3, keep order:false                                                     |
+-----------------------+-------+------+--------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> desc select * from t3 where a=5 and b=5.2 and c = 'ok';
+-------------------+-------+------+------------------------------------------------------------------------------------------+
| id                | count | task | operator info                                                                            |
+-------------------+-------+------+------------------------------------------------------------------------------------------+
| IndexLookUp_10    | 0.00  | root |                                                                                          |
| ├─IndexScan_8     | 0.00  | cop  | table:t3, index:a, c, b, range:[5 "ok" 5.20,5 "ok" 5.20], keep order:false, stats:pseudo |
| └─TableScan_9     | 0.00  | cop  | table:t3, keep order:false, stats:pseudo                                                 |
+-------------------+-------+------+------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

tidb version:

Release Version: v2.1.2-1-g8ba8096
Git Commit Hash: 8ba8096
Git Branch: release-2.1
UTC Build Time: 2018-12-21 03:45:55
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

[winoros]

The best solution is that we rewrite such kind of like function to equal function.

[arnkore]

Can I work on this issue?

[winoros]

@arnkore
Oh, thanks for your attention. There's already one #9071 about this.

[winoros]

At this moment in time, we found a new case that LIKE don't behave like EQ when the expression is unicode collation.
Simply rewriting LIKE to EQ would cause correctness problem. We need to handle it in a new way.