pingcap / pingcap/tidb

Dropping session binding affects global binding

Open
#58,883 2 comments 0 reactions 1 assignee Claimed by @qw4990 View on GitHub
affects-6.5 epic/sql-plan-management severity/minor sig/planner type/bug wontfix
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR metrics pending

Description

## 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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.