Dropping session binding affects global binding

[yi888long]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> explain select * from test where a>23;
+-------------------------+---------+-----------+---------------+---------------------+
| id                      | estRows | task      | access object | operator info       |
+-------------------------+---------+-----------+---------------+---------------------+
| TableReader_7           | 1977.00 | root      |               | data:Selection_6    |
| └─Selection_6           | 1977.00 | cop[tikv] |               | gt(test.test.a, 23) |
|   └─TableFullScan_5     | 2000.00 | cop[tikv] | table:test    | keep order:false    |
+-------------------------+---------+-----------+---------------+---------------------+
3 rows in set (0.00 sec)


mysql> CREATE global BINDING FOR SELECT * FROM test WHERE a>23 USING SELECT /*+ USE_INDEX(test, idx_a) */ * FROM test WHERE a>23;
Query OK, 0 rows affected (0.01 sec)


mysql> explain select * from test where a>23;
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| id                            | estRows | task      | access object              | operator info                     |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| IndexLookUp_7                 | 1977.00 | root      |                            |                                   |
| ├─IndexRangeScan_5(Build)     | 1977.00 | cop[tikv] | table:test, index:idx_a(a) | range:(23,+inf], keep order:false |
| └─TableRowIDScan_6(Probe)     | 1977.00 | cop[tikv] | table:test                 | keep order:false                  |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
3 rows in set (0.00 sec)


mysql> CREATE BINDING FOR SELECT * FROM test WHERE a>23 USING SELECT /*+ USE_INDEX(test, idx_a) */ * FROM test WHERE a>23;
Query OK, 0 rows affected (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| id                            | estRows | task      | access object              | operator info                     |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| IndexLookUp_7                 | 1977.00 | root      |                            |                                   |
| ├─IndexRangeScan_5(Build)     | 1977.00 | cop[tikv] | table:test, index:idx_a(a) | range:(23,+inf], keep order:false |
| └─TableRowIDScan_6(Probe)     | 1977.00 | cop[tikv] | table:test                 | keep order:false                  |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
3 rows in set (0.00 sec)


mysql> CREATE global BINDING FOR SELECT * FROM test WHERE a>23 USING SELECT /*+ USE_INDEX(test, idx_a) */ * FROM test WHERE a>23;
Query OK, 0 rows affected (0.01 sec)


mysql> explain select * from test where a>23;
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| id                            | estRows | task      | access object              | operator info                     |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| IndexLookUp_7                 | 1977.00 | root      |                            |                                   |
| ├─IndexRangeScan_5(Build)     | 1977.00 | cop[tikv] | table:test, index:idx_a(a) | range:(23,+inf], keep order:false |
| └─TableRowIDScan_6(Probe)     | 1977.00 | cop[tikv] | table:test                 | keep order:false                  |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
3 rows in set (0.00 sec)


mysql> drop binding for SELECT * FROM test WHERE a>23;
Query OK, 0 rows affected (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------+---------+-----------+---------------+---------------------+
| id                      | estRows | task      | access object | operator info       |
+-------------------------+---------+-----------+---------------+---------------------+
| TableReader_7           | 1977.00 | root      |               | data:Selection_6    |
| └─Selection_6           | 1977.00 | cop[tikv] |               | gt(test.test.a, 23) |
|   └─TableFullScan_5     | 2000.00 | cop[tikv] | table:test    | keep order:false    |
+-------------------------+---------+-----------+---------------+---------------------+
3 rows in set (0.00 sec)


mysql> show global bindings;
+---------------------------------------------+----------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+-----------------+--------+------------------------------------------------------------------+-------------+
| Original_sql                                | Bind_sql                                                                   | Default_db | Status  | Create_time             | Update_time             | Charset | Collation       | Source | Sql_digest                                                       | Plan_digest |
+---------------------------------------------+----------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+-----------------+--------+------------------------------------------------------------------+-------------+
| select * from `test` . `test` where `a` > ? | SELECT /*+ USE_INDEX(`test` `idx_a`)*/ * FROM `test`.`test` WHERE `a` > 23 | test       | enabled | 2025-01-08 21:15:08.332 | 2025-01-08 21:15:08.332 | utf8    | utf8_general_ci | manual | 33b1d8caf0e81f6da6a93dffac3465fe38d5cddab7d1ebf67a643b46c8073e2c |             |
+---------------------------------------------+----------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+-----------------+--------+------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------+---------+-----------+---------------+---------------------+
| id                      | estRows | task      | access object | operator info       |
+-------------------------+---------+-----------+---------------+---------------------+
| TableReader_7           | 1977.00 | root      |               | data:Selection_6    |
| └─Selection_6           | 1977.00 | cop[tikv] |               | gt(test.test.a, 23) |
|   └─TableFullScan_5     | 2000.00 | cop[tikv] | table:test    | keep order:false    |
+-------------------------+---------+-----------+---------------+---------------------+
3 rows in set (0.00 sec) 

------session 2

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> explain select * from test where a>23;
+-------------------------+---------+-----------+---------------+---------------------+
| id                      | estRows | task      | access object | operator info       |
+-------------------------+---------+-----------+---------------+---------------------+
| TableReader_7           | 1977.00 | root      |               | data:Selection_6    |
| └─Selection_6           | 1977.00 | cop[tikv] |               | gt(test.test.a, 23) |
|   └─TableFullScan_5     | 2000.00 | cop[tikv] | table:test    | keep order:false    |
+-------------------------+---------+-----------+---------------+---------------------+
3 rows in set (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| id                            | estRows | task      | access object              | operator info                     |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| IndexLookUp_7                 | 1977.00 | root      |                            |                                   |
| ├─IndexRangeScan_5(Build)     | 1977.00 | cop[tikv] | table:test, index:idx_a(a) | range:(23,+inf], keep order:false |
| └─TableRowIDScan_6(Probe)     | 1977.00 | cop[tikv] | table:test                 | keep order:false                  |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
3 rows in set (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| id                            | estRows | task      | access object              | operator info                     |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| IndexLookUp_7                 | 1977.00 | root      |                            |                                   |
| ├─IndexRangeScan_5(Build)     | 1977.00 | cop[tikv] | table:test, index:idx_a(a) | range:(23,+inf], keep order:false |
| └─TableRowIDScan_6(Probe)     | 1977.00 | cop[tikv] | table:test                 | keep order:false                  |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
3 rows in set (0.00 sec)


mysql> drop global binding for SELECT * FROM test WHERE a>23;
Query OK, 1 row affected (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------+---------+-----------+---------------+---------------------+
| id                      | estRows | task      | access object | operator info       |
+-------------------------+---------+-----------+---------------+---------------------+
| TableReader_7           | 1977.00 | root      |               | data:Selection_6    |
| └─Selection_6           | 1977.00 | cop[tikv] |               | gt(test.test.a, 23) |
|   └─TableFullScan_5     | 2000.00 | cop[tikv] | table:test    | keep order:false    |
+-------------------------+---------+-----------+---------------+---------------------+
3 rows in set (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| id                            | estRows | task      | access object              | operator info                     |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| IndexLookUp_7                 | 1977.00 | root      |                            |                                   |
| ├─IndexRangeScan_5(Build)     | 1977.00 | cop[tikv] | table:test, index:idx_a(a) | range:(23,+inf], keep order:false |
| └─TableRowIDScan_6(Probe)     | 1977.00 | cop[tikv] | table:test                 | keep order:false                  |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
3 rows in set (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| id                            | estRows | task      | access object              | operator info                     |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| IndexLookUp_7                 | 1977.00 | root      |                            |                                   |
| ├─IndexRangeScan_5(Build)     | 1977.00 | cop[tikv] | table:test, index:idx_a(a) | range:(23,+inf], keep order:false |
| └─TableRowIDScan_6(Probe)     | 1977.00 | cop[tikv] | table:test                 | keep order:false                  |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
3 rows in set (0.00 sec)


mysql> explain select * from test where a>23;
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| id                            | estRows | task      | access object              | operator info                     |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+
| IndexLookUp_7                 | 1977.00 | root      |                            |                                   |
| ├─IndexRangeScan_5(Build)     | 1977.00 | cop[tikv] | table:test, index:idx_a(a) | range:(23,+inf], keep order:false |
| └─TableRowIDScan_6(Probe)     | 1977.00 | cop[tikv] | table:test                 | keep order:false                  |
+-------------------------------+---------+-----------+----------------------------+-----------------------------------+

3 rows in set (0.00 sec)

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

session 1 should use index

3. What did you see instead (Required)

sesison 1 use fulltablescan

4. What is your TiDB version? (Required)

v6.5.6

[qw4990]

Fixed on v8.1.0

[qw4990]

Only the connection who creates the session binding would be affected by this bug, the global bindings can work well for all other connections and all following new connections, so this bug seems not serious.
And this bug has already been fixed by some refactor PR on v8.1.0, so I'll label this one as minor and won't fix, Thanks!