pingcap / pingcap/tidb

the global binding is not working when using Prepared Statement with "select ... cast(.. as ..) from..."

Open
#62,329 1 comment 0 reactions 0 assignees View on GitHub
component/spm type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
CREATE TABLE t(id varchar(10) DEFAULT NULL,name varchar(10) DEFAULT NULL,KEY idx_id(id));
CREATE TABLE t1(id int DEFAULT NULL,name varchar(10) DEFAULT NULL,KEY idx_id(id));
explain analyze select * from t where id in(select cast(t1.id as char) id from t1);

create global binding for select * from t where id in(select cast(t1.id as char) id from t1) using select * from t where id in(select cast(t1.id as char) id from t1 use index());
prepare mystmt from "explain analyze select * from t where id in(select cast(t1.id as char) id from t1)";
execute mystmt;

### 2. What did you expect to see? (Required)
+--------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+-----------+---------+
| HashJoin_8 | 7992.00 | 0 | root | | time:961.3µs, loops:1, RU:1.032227, build_hash_table:{total:825µs, fetch:825µs, build:0s} | semi join, equal:[eq(wdhis.t.id, Column#7)] | 0 Bytes | 0 Bytes |
| ├─Projection_15(Build) | 8000.00 | 0 | root | | time:744.6µs, loops:1, Concurrency:5 | cast(wdhis.t1.id, var_string(11))->Column#7 | 20.2 KB | N/A |
| │ └─TableReader_18 | 8000.00 | 0 | root | | time:704.7µs, loops:1, cop_task: {num: 1, max: 830.9µs, proc_keys: 0, tot_proc: 129.6µs, tot_wait: 90.8µs, copr_cache_hit_ratio: 0.00, build_task_duration: 2.36µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:800.3µs}} | data:Selection_17 | 236 Bytes | N/A |
| │ └─Selection_17 | 8000.00 | 0 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 33.7µs, rocksdb: {block: {cache_hit_count: 4}}}, time_detail: {total_process_time: 129.6µs, total_wait_time: 90.8µs, tikv_wall_time: 441.8µs} | not(isnull(cast(wdhis.t1.id, var_string(11)))) | N/A | N/A |
| │ └─TableFullScan_16 | 10000.00 | 0 | cop[tikv] | table:t1 | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_11(Probe) | 9990.00 | 0 | root | | time:768.4µs, loops:1, cop_task: {num: 1, max: 887µs, proc_keys: 0, tot_proc: 117.1µs, tot_wait: 82.7µs, copr_cache_hit_ratio: 0.00, build_task_duration: 16.3µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:836.1µs}} | data:Selection_10 | 235 Bytes | N/A |
| └─Selection_10 | 9990.00 | 0 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 39.1µs, rocksdb: {block: {cache_hit_count: 4}}}, time_detail: {total_process_time: 117.1µs, total_wait_time: 82.7µs, tikv_wall_time: 449µs} | not(isnull(wdhis.t.id)) | N/A | N/A |
| └─TableFullScan_9 | 10000.00 | 0 | cop[tikv] | table:t | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+-----------+---------+

### 3. What did you see instead (Required)
mysql> execute mystmt;
+--------------------------------+----------+---------+-----------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------+----------+---------+-----------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+-----------+---------+
| HashJoin_8 | 7992.00 | 0 | root | | time:2.29ms, loops:1, RU:1.090269, build_hash_table:{total:2.07ms, fetch:2.07ms, build:0s} | semi join, equal:[eq(wdhis.t.id, Column#7)] | 0 Bytes | 0 Bytes |
| ├─Projection_15(Build) | 8000.00 | 0 | root | | time:1.92ms, loops:1, Concurrency:5 | cast(wdhis.t1.id, var_string(11))->Column#7 | 20.2 KB | N/A |
| │ └─IndexReader_21 | 8000.00 | 0 | root | | time:1.85ms, loops:1, cop_task: {num: 1, max: 1.9ms, proc_keys: 0, tot_proc: 266.3µs, tot_wait: 393.8µs, copr_cache_hit_ratio: 0.00, build_task_duration: 2.1µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:1.74ms}} | index:Selection_20 | 245 Bytes | N/A |
| │ └─Selection_20 | 8000.00 | 0 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 334.6µs, rocksdb: {block: {cache_hit_count: 4}}}, time_detail: {total_process_time: 266.3µs, total_wait_time: 393.8µs, tikv_wall_time: 1.12ms} | not(isnull(cast(wdhis.t1.id, var_string(11)))) | N/A | N/A |
| │ └─IndexFullScan_19 | 10000.00 | 0 | cop[tikv] | table:t1, index:idx_id(id) | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_11(Probe) | 9990.00 | 0 | root | | time:2ms, loops:1, cop_task: {num: 1, max: 2.01ms, proc_keys: 0, tot_proc: 154.5µs, tot_wait: 721.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 21.5µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:1.94ms}} | data:Selection_10 | 243 Bytes | N/A |
| └─Selection_10 | 9990.00 | 0 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 662.6µs, rocksdb: {block: {cache_hit_count: 4}}}, time_detail: {total_process_time: 154.5µs, total_wait_time: 721.6µs, tikv_wall_time: 1.1ms} | not(isnull(wdhis.t.id)) | N/A | N/A |
| └─TableFullScan_9 | 10000.00 | 0 | cop[tikv] | table:t | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------+----------+---------+-----------+----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+-----------+---------+

### 4. What is your TiDB version? (Required)
v7.1

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.