binding normalize every number leads to use the expression index wrongly

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t(a int, key((a+1)));
Query OK, 0 rows affected (0.00 sec)

mysql> create global binding for select * from t where a+1 > 2 using select * from t use index(expression_index) where a+1 > 2;
Query OK, 0 rows affected (0.00 sec)

mysql> desc select * from t where a+1 > 2;
+---------------------------------+---------+-----------+------------------------------------------+------------------------------------------------+
| id                              | estRows | task      | access object                            | operator info                                  |
+---------------------------------+---------+-----------+------------------------------------------+------------------------------------------------+
| Projection_4                    | 3333.33 | root      |                                          | test.t.a                                       |
| └─IndexLookUp_7                 | 3333.33 | root      |                                          |                                                |
|   ├─IndexRangeScan_5(Build)     | 3333.33 | cop[tikv] | table:t, index:expression_index(`a` + 1) | range:(2,+inf], keep order:false, stats:pseudo |
|   └─TableRowIDScan_6(Probe)     | 3333.33 | cop[tikv] | table:t                                  | keep order:false, stats:pseudo                 |
+---------------------------------+---------+-----------+------------------------------------------+------------------------------------------------+
4 rows in set (0.00 sec)

mysql> desc select * from t where a+2 > 2;
+------------------------------+----------+-----------+------------------------------------------+--------------------------------+
| id                           | estRows  | task      | access object                            | operator info                  |
+------------------------------+----------+-----------+------------------------------------------+--------------------------------+
| IndexLookUp_8                | 8000.00  | root      |                                          |                                |
| ├─IndexFullScan_5(Build)     | 10000.00 | cop[tikv] | table:t, index:expression_index(`a` + 1) | keep order:false, stats:pseudo |
| └─Selection_7(Probe)         | 8000.00  | cop[tikv] |                                          | gt(plus(test.t.a, 2), 2)       |
|   └─TableRowIDScan_6         | 10000.00 | cop[tikv] | table:t                                  | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+------------------------------------------+--------------------------------+
4 rows in set (0.00 sec)

mysql> show global bindings;
+----------------------------------------------+---------------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-------------+--------+
| Original_sql                                 | Bind_sql                                                                  | Default_db | Status | Create_time             | Update_time             | Charset | Collation   | Source |
+----------------------------------------------+---------------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-------------+--------+
| select * from `test` . `t` where `a` + ? > ? | SELECT * FROM `test`.`t` USE INDEX (`expression_index`) WHERE `a` + 1 > 2 | test       | using  | 2021-07-09 11:25:46.465 | 2021-07-09 11:25:46.465 | utf8mb4 | utf8mb4_bin | manual |
+----------------------------------------------+---------------------------------------------------------------------------+------------+--------+-------------------------+-------------------------+---------+-------------+--------+
1 row in set (0.00 sec)

2. What did you expect to see? (Required)

For SQL select * from t where a+2 > 2;, don't use the expression index. But the hint work for it.

3. What did you see instead (Required)

Wrong plan for select * from t where a+2 > 2;

4. What is your TiDB version? (Required)

master

[Reminiscent]

/assign

[Reminiscent]

If we use the hint directly, the SQL select * from t where a+2 > 2; will get the same plan. So I think it is like an enhancement rather than a bug. And it should not be easy to fix in the short term.

[wjhuang2016]

The problem is, when we normalize the bound SQL, we should not normalize the whole expression.

[Reminiscent]

I think the result of the query is right, but the expression index should not be used. Maybe we can do some checks that when we build the full range for the expression index, we shouldn't use the expression index. The solutions need further investigation.

[wjhuang2016]

Change it to moderate since it's not likely encountered by users.