planner: binding cannot work when sql_select_limit is enabled

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t (a int, b int, key(b));
explain select * from t where b=1;
create global binding for select * from t where b=1 using select * from t ignore index(b) where b=1;

mysql> explain select * from t where b=1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t.b, 1)                |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> set @@sql_select_limit=100;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> explain select * from t where b=1;
+--------------------------------+---------+-----------+---------------------+---------------------------------------------+
| id                             | estRows | task      | access object       | operator info                               |
+--------------------------------+---------+-----------+---------------------+---------------------------------------------+
| IndexLookUp_17                 | 10.00   | root      |                     | limit embedded(offset:0, count:100)         |
| ├─Limit_16(Build)              | 10.00   | cop[tikv] |                     | offset:0, count:100                         |
| │ └─IndexRangeScan_14          | 10.00   | cop[tikv] | table:t, index:b(b) | range:[1,1], keep order:false, stats:pseudo |
| └─TableRowIDScan_15(Probe)     | 10.00   | cop[tikv] | table:t             | keep order:false, stats:pseudo              |
+--------------------------------+---------+-----------+---------------------+---------------------------------------------+
4 rows in set (0.00 sec)

The binding is not working after setting sql_select_limit.

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

The binding can work.

3. What did you see instead (Required)

The binding is not working.

4. What is your TiDB version? (Required)

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[Reminiscent]

The prepare statement can not use the binding when we set the sql_select_limit;

mysql> drop table if exists t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t(a int, index idx_a(a));
Query OK, 0 rows affected (0.01 sec)

mysql> create binding for select * from t  using select * from t use index(idx_a);
Query OK, 0 rows affected (0.00 sec)

mysql> set @@sql_select_limit = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t;
+---------------------------+---------+-----------+-------------------------+--------------------------------+
| id                        | estRows | task      | access object           | operator info                  |
+---------------------------+---------+-----------+-------------------------+--------------------------------+
| Limit_7                   | 3.00    | root      |                         | offset:0, count:3              |
| └─IndexReader_11          | 3.00    | root      |                         | index:Limit_10                 |
|   └─Limit_10              | 3.00    | cop[tikv] |                         | offset:0, count:3              |
|     └─IndexFullScan_9     | 3.00    | cop[tikv] | table:t, index:idx_a(a) | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+-------------------------+--------------------------------+
4 rows in set (0.00 sec)

mysql> select @@last_plan_from_binding;
+--------------------------+
| @@last_plan_from_binding |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

mysql> prepare stmt from 'select * from t';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt;
Empty set (0.01 sec)

mysql> select @@last_plan_from_binding;
+--------------------------+
| @@last_plan_from_binding |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

mysql> execute stmt;
Empty set (0.00 sec)

mysql> explain for connection 3;
+---------------------------+---------+-----------+---------------+--------------------------------+
| id                        | estRows | task      | access object | operator info                  |
+---------------------------+---------+-----------+---------------+--------------------------------+
| Limit_7                   | 3.00    | root      |               | offset:0, count:3              |
| └─TableReader_12          | 3.00    | root      |               | data:Limit_11                  |
|   └─Limit_11              | 3.00    | cop[tikv] |               | offset:0, count:3              |
|     └─TableFullScan_9     | 3.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+--------------------------------+
4 rows in set (0.00 sec)

I will add a PR to fix it.