Optimizer should use index when comparing with user-defined variables

[sgykfjsm]

Bug Report

Please answer these questions before submitting your issue. Thanks!

When I set user-defined variables in the WHERE clause, the optimizer doesn't use the index. Current behavior is different from MySQL.

1. Minimal reproduce step (Required)

CREATE TABLE IF NOT EXISTS t (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    dt DATE,
    INDEX idx_dt (dt)
);
INSERT INTO t (dt) VALUES ('2024-01-01');
INSERT INTO t (dt) VALUES ('2024-01-02');
INSERT INTO t (dt) VALUES ('2024-01-03');
INSERT INTO t (dt) VALUES ('2024-01-04');
INSERT INTO t (dt) VALUES ('2024-01-05');

SET @a := '2024-01-01';
SET @b := '2024-01-03';
EXPLAIN SELECT * FROM t WHERE dt BETWEEN @a AND @b;

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

The EXPLAIN should output the plan using the index on the dt column, as same as using literal values.

+------------------------+---------+-----------+---------------------------+---------------------------------------------------------------+
| id                     | estRows | task      | access object             | operator info                                                 |
+------------------------+---------+-----------+---------------------------+---------------------------------------------------------------+
| IndexReader_6          | 250.00  | root      |                           | index:IndexRangeScan_5                                        |
| └─IndexRangeScan_5     | 250.00  | cop[tikv] | table:t, index:idx_dt(dt) | range:[2024-01-01,2024-01-03], keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------------------+---------------------------------------------------------------+

3. What did you see instead (Required)

+-------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------+
| id                      | estRows  | task      | access object | operator info                                                                                              |
+-------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------+
| Selection_5             | 8000.00  | root      |               | ge(test.t.dt, cast(getvar("a"), datetime(6) BINARY)), le(test.t.dt, cast(getvar("b"), datetime(6) BINARY)) |
| └─TableReader_7         | 10000.00 | root      |               | data:TableFullScan_6                                                                                       |
|   └─TableFullScan_6     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                             |
+-------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------+

4. What is your TiDB version? (Required)

tidb:4000 > SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.5.0
Edition: Community
Git Commit Hash: 069631e2ecfedc000ffb92c67207bea81380f020
Git Branch: heads/refs/tags/v7.5.0
UTC Build Time: 2023-11-24 08:41:10
GoVersion: go1.21.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)