read from global temporary table generate unexpected cop request #58875
Description
Bug Report
mysql> CREATE GLOBAL TEMPORARY TABLE users ( id BIGINT, v1 int, v2 int, v3 int, v4 int, PRIMARY KEY(id), index v1_index(v1,v2,v3) ) ON COMMIT DELETE ROWS;
Query OK, 0 rows affected (0.51 sec)
mysql> create table users1(id int, value int, index index_value(value)); Query OK, 0 rows affected (0.51 sec)
mysql> insert into users1 values(1,2);
Query OK, 1 row affected (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users values(1,2,3,4,5); Query OK, 1 row affected (0.00 sec)
mysql> explain analyze select /*+ inl_join(users) */ * from users use index(v1_index) where v1 in (select value from users1);
+------------------------------------+---------+---------+-----------+-----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+---------+---------+-----------+-----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| IndexJoin_18 | 1.25 | 1 | root | | time:3.96ms, open:271.8µs, close:13.1µs, loops:2, RU:0.96, inner:{total:1.01ms, concurrency:5, task:1, construct:58µs, fetch:947.5µs, build:3.13µs}, probe:33.9µs | inner join, inner:UnionScan_17, outer key:test.users1.value, inner key:test.users.v1, equal cond:eq(test.users1.value, test.users.v1) | 49.8 KB | N/A |
| ├─StreamAgg_47(Build) | 1.00 | 1 | root | | time:2.55ms, open:196.6µs, close:10.1µs, loops:3 | group by:test.users1.value, funcs:firstrow(test.users1.value)->test.users1.value | 396 Bytes | N/A |
| │ └─IndexReader_48 | 1.00 | 1 | root | | time:2.42ms, open:137.8µs, close:8.29µs, loops:2, cop_task: {num: 1, max: 2.16ms, proc_keys: 1, tot_proc: 34.1µs, tot_wait: 18.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 35.1µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:2.12ms}} | index:StreamAgg_39 | 262 Bytes | N/A |
| │ └─StreamAgg_39 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 46, total_keys: 2, get_snapshot_time: 9.54µs, rocksdb: {key_skipped_count: 1, block: {}}}, time_detail: {total_process_time: 34.1µs, total_suspend_time: 1.17µs, total_wait_time: 18.6µs, tikv_wall_time: 1.69ms} | group by:test.users1.value, | N/A | N/A |
| │ └─IndexFullScan_46 | 1.00 | 1 | cop[tikv] | table:users1, index:index_value(value) | tikv_task:{time:0s, loops:1} | keep order:true, stats:partial[value:unInitialized] | N/A | N/A |
| └─UnionScan_17(Probe) | 1.25 | 1 | root | | time:756.5µs, open:0s, close:5µs, loops:2 | not(isnull(test.users.v1)) | N/A | N/A |
| └─IndexLookUp_16 | 1.25 | 0 | root | | time:721.5µs, open:0s, close:4µs, loops:3 | | 246 Bytes | N/A |
| ├─Selection_15(Build) | 1.25 | 0 | cop[tikv] | | time:573.3µs, open:0s, close:0s, loops:1, cop_task: {num: 1, max: 532.2µs, proc_keys: 0, tot_proc: 7.88µs, tot_wait: 90.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 35µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:512.3µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 79.8µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 7.88µs, total_wait_time: 90.3µs, tikv_wall_time: 165.3µs} | not(isnull(test.users.v1)) | N/A | N/A |
| │ └─IndexRangeScan_13 | 1.25 | 0 | cop[tikv] | table:users, index:v1_index(v1, v2, v3) | tikv_task:{time:0s, loops:1} | range: decided by [eq(test.users.v1, test.users1.value)], keep order:false, stats:pseudo | N/A | N/A |
| └─TableRowIDScan_14(Probe) | 1.25 | 0 | cop[tikv] | table:users | | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------------+---------+---------+-----------+-----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
10 rows in set (0.01 sec)
users is a global temporary table, so all its data is saved in tidb server, when accessing users, there should no cop request. But in Selection_15 it actually generate a cop request, which is not expected.
Note this bug seems can not be reproduced using simple query like this
mysql> explain analyze select * from users use index(v1_index);
+---------------------------------+----------+---------+-----------+-----------------------------------------+-------------------------------------------------------------+--------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+----------+---------+-----------+-----------------------------------------+-------------------------------------------------------------+--------------------------------+----------+------+
| UnionScan_5 | 10000.00 | 1 | root | | time:98.9µs, open:62.8µs, close:2.75µs, loops:2, RU:0.00 | | N/A | N/A |
| └─IndexLookUp_8 | 10000.00 | 0 | root | | time:21.7µs, open:15.5µs, close:1.63µs, loops:3 | | 90 Bytes | N/A |
| ├─IndexFullScan_6(Build) | 10000.00 | 0 | cop[tikv] | table:users, index:v1_index(v1, v2, v3) | | keep order:false, stats:pseudo | N/A | N/A |
| └─TableRowIDScan_7(Probe) | 10000.00 | 0 | cop[tikv] | table:users | | keep order:false, stats:pseudo | N/A | N/A |
+---------------------------------+----------+---------+-----------+-----------------------------------------+-------------------------------------------------------------+--------------------------------+----------+------+
Please answer these questions before submitting your issue. Thanks!