explain format='plan_tree' select * from METRICS_SCHEMA.tidb_query_duration where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13' ;
id	task	access object	operator info
MemTableScan	root	table:tidb_query_duration	PromQL:histogram_quantile(0.9, sum(rate(tidb_server_handle_query_duration_seconds_bucket{}[60s])) by (le,sql_type,instance)), start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13, step:1m0s
explain format='plan_tree' select * from METRICS_SCHEMA.up where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13' ;
id	task	access object	operator info
MemTableScan	root	table:up	PromQL:up{}, start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13, step:1m0s
explain format='plan_tree' select * from information_schema.cluster_log where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
id	task	access object	operator info
MemTableScan	root	table:CLUSTER_LOG	start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13
explain format='plan_tree' select * from information_schema.cluster_log where level in ('warn','error') and time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
id	task	access object	operator info
MemTableScan	root	table:CLUSTER_LOG	start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13, log_levels:["error","warn"]
explain format='plan_tree' select * from information_schema.cluster_log where type in ('high_cpu_1','high_memory_1') and time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
id	task	access object	operator info
MemTableScan	root	table:CLUSTER_LOG	start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13, node_types:["high_cpu_1","high_memory_1"]
explain format='plan_tree' select * from information_schema.slow_query;
id	task	access object	operator info
MemTableScan	root	table:SLOW_QUERY	only search in the current 'tidb-slow.log' file
explain format='plan_tree' select * from information_schema.slow_query where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
id	task	access object	operator info
MemTableScan	root	table:SLOW_QUERY	start_time:2019-12-23 16:10:13.000000, end_time:2019-12-23 16:30:13.000000
set @@time_zone = '+00:00';
explain format='plan_tree' select * from information_schema.slow_query where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
id	task	access object	operator info
MemTableScan	root	table:SLOW_QUERY	start_time:2019-12-23 16:10:13.000000, end_time:2019-12-23 16:30:13.000000
set @@time_zone = default;
explain format='plan_tree' select * from information_schema.cluster_config where type in ('tikv', 'tidb');
id	task	access object	operator info
MemTableScan	root	table:CLUSTER_CONFIG	node_types:["tidb","tikv"]
explain format='plan_tree' select * from information_schema.cluster_config where instance='192.168.1.7:2379';
id	task	access object	operator info
MemTableScan	root	table:CLUSTER_CONFIG	instances:["192.168.1.7:2379"]
explain format='plan_tree' select * from information_schema.cluster_config where type='tidb' and instance='192.168.1.7:2379';
id	task	access object	operator info
MemTableScan	root	table:CLUSTER_CONFIG	node_types:["tidb"], instances:["192.168.1.7:2379"]
explain format='plan_tree' select * from information_schema.inspection_result where rule = 'ddl' and rule = 'config';
id	task	access object	operator info
MemTableScan	root	table:INSPECTION_RESULT	skip_inspection:true
explain format='plan_tree' select * from information_schema.inspection_result where rule in ('ddl', 'config');
id	task	access object	operator info
MemTableScan	root	table:INSPECTION_RESULT	rules:["config","ddl"], items:[]
explain format='plan_tree' select * from information_schema.inspection_result where item in ('ddl.lease', 'raftstore.threadpool');
id	task	access object	operator info
MemTableScan	root	table:INSPECTION_RESULT	rules:[], items:["ddl.lease","raftstore.threadpool"]
explain format='plan_tree' select * from information_schema.inspection_result where item in ('ddl.lease', 'raftstore.threadpool') and rule in ('ddl', 'config');
id	task	access object	operator info
MemTableScan	root	table:INSPECTION_RESULT	rules:["config","ddl"], items:["ddl.lease","raftstore.threadpool"]
explain format='plan_tree' select * from information_schema.inspection_rules where type='inspection';
id	task	access object	operator info
MemTableScan	root	table:INSPECTION_RULES	node_types:["inspection"]
explain format='plan_tree' select * from information_schema.inspection_rules where type='inspection' or type='summary';
id	task	access object	operator info
MemTableScan	root	table:INSPECTION_RULES	node_types:["inspection","summary"]
explain format='plan_tree' select * from information_schema.inspection_rules where type='inspection' and type='summary';
id	task	access object	operator info
MemTableScan	root	table:INSPECTION_RULES	skip_request: true
set tidb_enable_prepared_plan_cache=1;
drop table if exists t;
create table t(a int, b int, c int generated always as (a+b) stored);
insert into t(a,b) values(1,1);
begin;
update t set b = 2 where a = 1;
prepare stmt from 'select b from t where a > ?';
set @p = 0;
execute stmt using @p;
b
2
set @p = 1;
execute stmt using @p;
b
insert into t(a,b,c) values(3,3,3);
Error 3105 (HY000): The value specified for generated column 'c' in table 't' is not allowed.
rollback;
set tidb_enable_prepared_plan_cache=default;
explain format='plan_tree' select * from information_schema.TABLE_STORAGE_STATS where TABLE_SCHEMA = 'information_schema';
id	task	access object	operator info
MemTableScan	root	table:TABLE_STORAGE_STATS	schema:["information_schema"]
explain format='plan_tree' select * from information_schema.TABLE_STORAGE_STATS where TABLE_NAME = 'schemata';
id	task	access object	operator info
MemTableScan	root	table:TABLE_STORAGE_STATS	table:["schemata"]
explain format='plan_tree' select * from information_schema.TABLE_STORAGE_STATS where TABLE_SCHEMA = 'information_schema' and TABLE_NAME = 'schemata';
id	task	access object	operator info
MemTableScan	root	table:TABLE_STORAGE_STATS	schema:["information_schema"], table:["schemata"]
explain format='plan_tree' select * from information_schema.inspection_summary where rule='ddl';
id	task	access object	operator info
Selection	root		eq(Column, "ddl")
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["ddl"]
explain format='plan_tree' select * from information_schema.inspection_summary where 'ddl'=rule or rule='config';
id	task	access object	operator info
Selection	root		or(eq("ddl", Column), eq(Column, "config"))
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["config","ddl"]
explain format='plan_tree' select * from information_schema.inspection_summary where 'ddl'=rule or rule='config' or rule='slow_query';
id	task	access object	operator info
Selection	root		or(eq("ddl", Column), or(eq(Column, "config"), eq(Column, "slow_query")))
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["config","ddl","slow_query"]
explain format='plan_tree' select * from information_schema.inspection_summary where (rule='config' or rule='slow_query') and (metrics_name='metric_name3' or metrics_name='metric_name1');
id	task	access object	operator info
Selection	root		or(eq(Column, "config"), eq(Column, "slow_query")), or(eq(Column, "metric_name3"), eq(Column, "metric_name1"))
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["config","slow_query"], metric_names:["metric_name1","metric_name3"]
explain format='plan_tree' select * from information_schema.inspection_summary where rule in ('ddl', 'slow_query');
id	task	access object	operator info
Selection	root		in(Column, "ddl", "slow_query")
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["ddl","slow_query"]
explain format='plan_tree' select * from information_schema.inspection_summary where rule in ('ddl', 'slow_query') and metrics_name='metric_name1';
id	task	access object	operator info
Selection	root		eq(Column, "metric_name1"), in(Column, "ddl", "slow_query")
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["ddl","slow_query"], metric_names:["metric_name1"]
explain format='plan_tree' select * from information_schema.inspection_summary where rule in ('ddl', 'slow_query') and metrics_name in ('metric_name1', 'metric_name2');
id	task	access object	operator info
Selection	root		in(Column, "ddl", "slow_query"), in(Column, "metric_name1", "metric_name2")
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["ddl","slow_query"], metric_names:["metric_name1","metric_name2"]
explain format='plan_tree' select * from information_schema.inspection_summary where rule='ddl' and metrics_name in ('metric_name1', 'metric_name2');
id	task	access object	operator info
Selection	root		eq(Column, "ddl"), in(Column, "metric_name1", "metric_name2")
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["ddl"], metric_names:["metric_name1","metric_name2"]
explain format='plan_tree' select * from information_schema.inspection_summary where rule='ddl' and metrics_name='metric_NAME3';
id	task	access object	operator info
Selection	root		eq(Column, "ddl"), eq(Column, "metric_NAME3")
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["ddl"], metric_names:["metric_name3"]
explain format='plan_tree' select * from information_schema.inspection_summary where rule in ('ddl', 'config') and rule in ('slow_query', 'config');
id	task	access object	operator info
Selection	root		in(Column, "ddl", "config"), in(Column, "slow_query", "config")
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["config"]
explain format='plan_tree' select * from information_schema.inspection_summary where metrics_name in ('metric_name1', 'metric_name4') and metrics_name in ('metric_name5', 'metric_name4') and rule in ('ddl', 'config') and rule in ('slow_query', 'config') and quantile in (0.80, 0.90);
id	task	access object	operator info
Selection	root		in(Column, "ddl", "config"), in(Column, "metric_name1", "metric_name4"), in(Column, "metric_name5", "metric_name4"), in(Column, "slow_query", "config")
└─MemTableScan	root	table:INSPECTION_SUMMARY	rules:["config"], metric_names:["metric_name4"], quantiles:[0.800000,0.900000]
explain format='plan_tree' select * from information_schema.inspection_summary where metrics_name in ('metric_name1', 'metric_name4') and metrics_name in ('metric_name5', 'metric_name4') and metrics_name in ('metric_name5', 'metric_name1') and metrics_name in ('metric_name1', 'metric_name3');
id	task	access object	operator info
Selection	root		in(Column, "metric_name1", "metric_name3"), in(Column, "metric_name1", "metric_name4"), in(Column, "metric_name5", "metric_name1"), in(Column, "metric_name5", "metric_name4")
└─MemTableScan	root	table:INSPECTION_SUMMARY	skip_inspection: true
explain format='plan_tree' select * from information_schema.TIFLASH_TABLES where TIFLASH_INSTANCE = '192.168.1.7:3930';
id	task	access object	operator info
MemTableScan	root	table:TIFLASH_TABLES	tiflash_instances:["192.168.1.7:3930"]
explain format='plan_tree' select * from information_schema.TIFLASH_SEGMENTS where TIFLASH_INSTANCE = '192.168.1.7:3930';
id	task	access object	operator info
MemTableScan	root	table:TIFLASH_SEGMENTS	tiflash_instances:["192.168.1.7:3930"]
explain format='plan_tree' select * from information_schema.TIFLASH_TABLES where TIDB_DATABASE = 'test';
id	task	access object	operator info
MemTableScan	root	table:TIFLASH_TABLES	tidb_databases:["test"]
explain format='plan_tree' select * from information_schema.TIFLASH_SEGMENTS where TIDB_DATABASE = 'test';
id	task	access object	operator info
MemTableScan	root	table:TIFLASH_SEGMENTS	tidb_databases:["test"]
explain format='plan_tree' select * from information_schema.TIFLASH_TABLES where TIDB_TABLE = 't';
id	task	access object	operator info
MemTableScan	root	table:TIFLASH_TABLES	tidb_tables:["t"]
explain format='plan_tree' select * from information_schema.TIFLASH_SEGMENTS where TIDB_TABLE = 't';
id	task	access object	operator info
MemTableScan	root	table:TIFLASH_SEGMENTS	tidb_tables:["t"]
explain format='plan_tree' select * from information_schema.TIFLASH_TABLES where TIFLASH_INSTANCE = '192.168.1.7:3930' and TIDB_DATABASE = 'test' and TIDB_TABLE = 't';
id	task	access object	operator info
MemTableScan	root	table:TIFLASH_TABLES	tiflash_instances:["192.168.1.7:3930"], tidb_databases:["test"], tidb_tables:["t"]
explain format='plan_tree' select * from information_schema.TIFLASH_SEGMENTS where TIFLASH_INSTANCE = '192.168.1.7:3930' and TIDB_DATABASE = 'test' and TIDB_TABLE = 't';
id	task	access object	operator info
MemTableScan	root	table:TIFLASH_SEGMENTS	tiflash_instances:["192.168.1.7:3930"], tidb_databases:["test"], tidb_tables:["t"]
set tidb_enable_prepared_plan_cache=1;
set @@tidb_enable_collect_execution_info=0;
drop table if exists t1, t2;
CREATE TABLE `t1` (a int);
CREATE TABLE `t2` (a int);
insert into t1 values(1), (2);
insert into t2 values(1), (3);
prepare stmt from 'select * from t1 where a > ? union select * from t2 where a > ?;';
set @a=0, @b=1;
execute stmt using @a, @b;
a
1
2
3
execute stmt using @b, @a;
a
1
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt using @b, @b;
a
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt using @a, @a;
a
1
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt from 'select * from t1 where a > ? union all select * from t2 where a > ?;';
set @a=0, @b=1;
execute stmt using @a, @b;
a
1
2
3
execute stmt using @b, @a;
a
1
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt using @b, @b;
a
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt using @a, @a;
a
1
1
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt from 'select * from t1 where a > ? except select * from t2 where a > ?;';
set @a=0, @b=1;
execute stmt using @a, @a;
a
2
execute stmt using @b, @a;
a
2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt using @b, @b;
a
2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt using @a, @b;
a
1
2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt from 'select * from t1 where a > ? union select * from t2 where a > ?;';
set @a=0, @b=1;
execute stmt using @a, @a;
a
1
2
3
execute stmt using @b, @a;
a
1
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt using @b, @b;
a
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt using @a, @b;
a
1
2
3
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt from 'select * from t1 union all select * from t1 intersect select * from t2;';
execute stmt;
a
1
1
2
prepare stmt from '(select * from t1 union all select * from t1) intersect select * from t2;';
execute stmt;
a
1
prepare stmt from '(select * from t1 union all select * from t1 intersect select * from t2) order by a limit 2;';
execute stmt;
a
1
1
set tidb_enable_prepared_plan_cache=default;
set @@tidb_enable_collect_execution_info=default;
set tidb_enable_prepared_plan_cache=1;
set @@tidb_enable_collect_execution_info=0;
drop table if exists t;
create table t(a int, index idx_a(a));
prepare stmt from 'select * from t;';
execute stmt;
a
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt from 'select /*+ IGNORE_PLAN_CACHE() */ * from t;';
execute stmt;
a
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
0
set tidb_enable_prepared_plan_cache=default;
set @@tidb_enable_collect_execution_info=default;
set tidb_enable_prepared_plan_cache=1;
set @@tidb_enable_collect_execution_info=0;
drop table if exists t;
CREATE TABLE t(c1 INT, index idx_c(c1));
prepare stmt from 'select * from t use index(idx_c) where c1 > 1;';
execute stmt;
c1
execute stmt;
c1
select @@last_plan_from_cache;
@@last_plan_from_cache
1
ALTER TABLE t ALTER INDEX idx_c INVISIBLE;
select * from t use index(idx_c) where c1 > 1;
Error 1176 (42000): Key 'idx_c' doesn't exist in table 't'
execute stmt;
Error 1176 (42000): Key 'idx_c' doesn't exist in table 't'
set tidb_enable_prepared_plan_cache=default;
set @@tidb_enable_collect_execution_info=default;
set tidb_enable_prepared_plan_cache=1;
set @@tidb_enable_collect_execution_info=0;
prepare stmt from 'with recursive cte1 as (select ? c1 union all select c1 + 1 c1 from cte1 where c1 < ?) select * from cte1;';
set @a=5, @b=4, @c=2, @d=1;
execute stmt using @d, @a;
c1
1
2
3
4
5
execute stmt using @d, @b;
c1
1
2
3
4
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt using @c, @b;
c1
2
3
4
select @@last_plan_from_cache;
@@last_plan_from_cache
0
prepare stmt from 'with recursive cte1 as (select 1 c1 union all select 2 c1 union all select c1 + 1 c1 from cte1 where c1 < ?) select * from cte1 order by c1;';
set @a=10, @b=2;
execute stmt using @a;
c1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
10
execute stmt using @b;
c1
1
2
2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
prepare stmt from 'with recursive cte1 as (select 1 c1 union all select 2 c1 union all select c1 + 1 c1 from cte1 where c1 < ? union all select c1 + ? c1 from cte1 where c1 < ?) select * from cte1 order by c1;';
set @a=1, @b=2, @c=3, @d=4, @e=5;
execute stmt using @c, @b, @e;
c1
1
2
2
3
3
3
4
4
5
5
5
6
6
execute stmt using @b, @a, @d;
c1
1
2
2
2
3
3
3
4
4
4
select @@last_plan_from_cache;
@@last_plan_from_cache
0
drop table if exists t1;
create table t1(a int);
insert into t1 values(1);
insert into t1 values(2);
prepare stmt from 'SELECT * FROM t1 dt WHERE EXISTS(WITH RECURSIVE qn AS (SELECT a*? AS b UNION ALL SELECT b+? FROM qn WHERE b=?) SELECT * FROM qn WHERE b=a);';
show warnings;
Level	Code	Message
Warning	1105	skip prepared plan-cache: find table executor__explainfor.qn failed: [schema:1146]Table 'executor__explainfor.qn' doesn't exist
set @a=1, @b=2, @c=3, @d=4, @e=5, @f=0;
execute stmt using @f, @a, @f;
a
1
execute stmt using @a, @b, @a;
a
1
2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt using @a, @b, @a;
a
1
2
prepare stmt from 'with recursive c(p) as (select ?), cte(a, b) as (select 1, 1 union select a+?, 1 from cte, c where a < ?)  select * from cte order by 1, 2;';
show warnings;
Level	Code	Message
Warning	1105	skip prepared plan-cache: find table executor__explainfor.cte failed: [schema:1146]Table 'executor__explainfor.cte' doesn't exist
execute stmt using @a, @a, @e;
a	b
1	1
2	1
3	1
4	1
5	1
execute stmt using @b, @b, @c;
a	b
1	1
3	1
select @@last_plan_from_cache;
@@last_plan_from_cache
0
set tidb_enable_prepared_plan_cache=default;
set @@tidb_enable_collect_execution_info=default;
set tidb_enable_prepared_plan_cache=1;
set @@tidb_enable_collect_execution_info=0;
drop table if exists t;
create table t(a int);
prepare stmt from 'select * from t;';
execute stmt;
a
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
drop database if exists plan_cache;
create database plan_cache;
use plan_cache;
create table t(a int);
insert into t values(1);
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt from 'select * from t;';
execute stmt;
a
1
execute stmt;
a
1
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt;
a
1
select @@last_plan_from_cache;
@@last_plan_from_cache
1
set tidb_enable_prepared_plan_cache=default;
set @@tidb_enable_collect_execution_info=default;
set tidb_enable_prepared_plan_cache=1;
set @@tidb_enable_collect_execution_info=0;
drop table if exists t;
create table t(a int, b int) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (4, 5, 6));
set @@tidb_partition_prune_mode='static';
prepare stmt from 'select * from t;';
execute stmt;
a	b
execute stmt;
a	b
select @@last_plan_from_cache;
@@last_plan_from_cache
0
set tidb_enable_prepared_plan_cache=default;
set @@tidb_enable_collect_execution_info=default;
set @@tidb_partition_prune_mode=default;
drop table if exists t12, t97;
CREATE TABLE t12(a INT, b INT);
CREATE TABLE t97(a INT, b INT UNIQUE NOT NULL);
EXPLAIN SELECT t12.a, t12.b FROM t12 LEFT JOIN t97 on t12.b = t97.b;
id	estRows	task	access object	operator info
TableReader_8	10000.00	root		data:TableFullScan_7
└─TableFullScan_7	10000.00	cop[tikv]	table:t12	keep order:false, stats:pseudo
EXPLAIN SELECT t12.a, t12.b FROM t12 LEFT JOIN t97 use index () on t12.b = t97.b;
id	estRows	task	access object	operator info
TableReader_8	10000.00	root		data:TableFullScan_7
└─TableFullScan_7	10000.00	cop[tikv]	table:t12	keep order:false, stats:pseudo
set tidb_enable_prepared_plan_cache=1;
set @@tidb_enable_collect_execution_info=0;
drop table if exists t;
create table t(a int);
prepare stmt from 'select * from t;';
execute stmt;
a
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
set tidb_enable_prepared_plan_cache=1;
execute stmt;
Error 8111 (HY000): Prepared statement not found
prepare stmt from 'select * from t;';
execute stmt;
a
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
set tidb_enable_prepared_plan_cache=default;
set @@tidb_enable_collect_execution_info=default;
set tidb_enable_prepared_plan_cache=1;
set @@tidb_enable_collect_execution_info=0;
drop view if exists view1, view2, view3, view4;
drop table if exists view_t;
create table view_t (a int,b int);
insert into view_t values(1,2);
create definer='root'@'localhost' view view1 as select * from view_t;
create definer='root'@'localhost' view view2(c,d) as select * from view_t;
create definer='root'@'localhost' view view3(c,d) as select a,b from view_t;
create definer='root'@'localhost' view view4 as select * from (select * from (select * from view_t) tb1) tb;
prepare stmt1 from 'select * from view1;';
execute stmt1;
a	b
1	2
execute stmt1;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt2 from 'select * from view2;';
execute stmt2;
c	d
1	2
execute stmt2;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt3 from 'select * from view3;';
execute stmt3;
c	d
1	2
execute stmt3;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
prepare stmt4 from 'select * from view4;';
execute stmt4;
a	b
1	2
execute stmt4;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
drop table view_t;
create table view_t(c int,d int);
execute stmt1;
Error 1356 (HY000): View 'plan_cache.view1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
execute stmt2;
Error 1356 (HY000): View 'plan_cache.view2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
execute stmt3;
Error 1356 (HY000): View 'plan_cache.view3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
drop table view_t;
create table view_t(a int,b int,c int);
insert into view_t values(1,2,3);
execute stmt1;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt1;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt2;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt2;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt3;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt3;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt4;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt4;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
alter table view_t drop column a;
alter table view_t add column a int after b;
update view_t set a=1;
execute stmt1;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt1;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt2;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt2;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt3;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt3;
c	d
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt4;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt4;
a	b
1	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
drop table view_t;
drop view view1,view2,view3,view4;
set @@tidb_enable_window_function = 1;
drop table if exists t;
create table t(a int, b int);
insert into t values (1,1),(1,2),(2,1),(2,2);
create definer='root'@'localhost' view v as select a, first_value(a) over(rows between 1 preceding and 1 following), last_value(a) over(rows between 1 preceding and 1 following) from t;
prepare stmt from 'select * from v;';
execute stmt;
a	first_value(a) over(rows between 1 preceding and 1 following)	last_value(a) over(rows between 1 preceding and 1 following)
1	1	1
1	1	2
2	1	2
2	2	2
execute stmt;
a	first_value(a) over(rows between 1 preceding and 1 following)	last_value(a) over(rows between 1 preceding and 1 following)
1	1	1
1	1	2
2	1	2
2	2	2
select @@last_plan_from_cache;
@@last_plan_from_cache
1
drop view v;
set @@tidb_enable_window_function = default;
set tidb_enable_prepared_plan_cache=default;
set @@tidb_enable_collect_execution_info=default;
drop table if exists t;
create table t(a int, index idx_a(a));
drop table if exists r;
create table r(a int);
prepare stmt from 'select * from t;';
create binding for select * from t using select /*+ use_index(t, idx_a) */ * from t;
execute stmt;
a
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt;
a
select @@last_plan_from_binding;
@@last_plan_from_binding
1
create binding for select * from t using select /*+ ignore_plan_cache() */ * from t;
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt;
a
select @@last_plan_from_binding;
@@last_plan_from_binding
1
create binding for select * from t using select /*+ use_index(t, idx_a) */ * from t;
execute stmt;
a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt;
a
select @@last_plan_from_binding;
@@last_plan_from_binding
1
prepare stmt_join from 'select * from t, r where r.a = t.a;';
create binding for select * from t, r where r.a = t.a using select /*+ straight_join() */* from t, r where r.a = t.a;
execute stmt_join;
a	a
execute stmt_join;
a	a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt_join;
a	a
select @@last_plan_from_binding;
@@last_plan_from_binding
1
create binding for select * from t, r where r.a = t.a using select /*+ ignore_plan_cache() */* from t, r where r.a = t.a;
execute stmt_join;
a	a
select @@last_plan_from_cache;
@@last_plan_from_cache
0
execute stmt_join;
a	a
select @@last_plan_from_binding;
@@last_plan_from_binding
1
create binding for select * from t, r where r.a = t.a using select /*+ straight_join() */* from t, r where r.a = t.a;
execute stmt_join;
a	a
select @@last_plan_from_cache;
@@last_plan_from_cache
1
execute stmt_join;
a	a
select @@last_plan_from_binding;
@@last_plan_from_binding
1
create database s1;
drop table if exists t;
create table t(a int);
drop table if exists r;
create table r(a int);
explain format='plan_tree' select * from information_schema.tables where table_name='t';
id	task	access object	operator info
MemTableScan	root	table:TABLES	table_name:["t"]
explain format='plan_tree' select * from information_schema.tables where lower(table_name)='t';
id	task	access object	operator info
MemTableScan	root	table:TABLES	table_name:["t"]
explain format='plan_tree' select * from information_schema.tables where upper(table_name)=upper('t');
id	task	access object	operator info
MemTableScan	root	table:TABLES	table_name:["t"]
explain format='plan_tree' select * from information_schema.REFERENTIAL_CONSTRAINTS where table_name='t';
id	task	access object	operator info
MemTableScan	root	table:REFERENTIAL_CONSTRAINTS	table_name:["t"]
explain format='plan_tree' select * from information_schema.KEY_COLUMN_USAGE where table_name='t';
id	task	access object	operator info
MemTableScan	root	table:KEY_COLUMN_USAGE	table_name:["t"]
explain format='plan_tree' select * from information_schema.STATISTICS where table_name='t';
id	task	access object	operator info
MemTableScan	root	table:STATISTICS	table_name:["t"]
explain format='plan_tree' select * from information_schema.STATISTICS where table_name='t' and table_name='r';
id	task	access object	operator info
MemTableScan	root	table:STATISTICS	skip_request:true
explain format='plan_tree' select * from information_schema.STATISTICS where table_name='t' or table_name='r';
id	task	access object	operator info
MemTableScan	root	table:STATISTICS	table_name:["r","t"]
explain format='plan_tree' select * from information_schema.tables where table_schema='t';
id	task	access object	operator info
MemTableScan	root	table:TABLES	table_schema:["t"]
explain format='plan_tree' select * from information_schema.KEY_COLUMN_USAGE where table_schema='s1';
id	task	access object	operator info
MemTableScan	root	table:KEY_COLUMN_USAGE	table_schema:["s1"]
explain format='plan_tree' select * from information_schema.STATISTICS where table_schema='s1';
id	task	access object	operator info
MemTableScan	root	table:STATISTICS	table_schema:["s1"]
explain format='plan_tree' select * from information_schema.STATISTICS where table_schema='s1' and table_schema='test';
id	task	access object	operator info
MemTableScan	root	table:STATISTICS	skip_request:true
explain format='plan_tree' select * from information_schema.STATISTICS where table_name='s1' and table_schema='test';
id	task	access object	operator info
MemTableScan	root	table:STATISTICS	table_name:["s1"], table_schema:["test"]
explain format='plan_tree' select * from information_schema.STATISTICS where table_schema='s1' or table_schema='test';
id	task	access object	operator info
MemTableScan	root	table:STATISTICS	table_schema:["s1","test"]
explain format='plan_tree' SELECT column_name FROM information_schema.statistics WHERE index_name = 'idx1' AND table_schema = DATABASE () AND table_name = 't' ORDER BY seq_in_index;
id	task	access object	operator info
Projection	root		Column
└─Sort	root		Column
  └─MemTableScan	root	table:STATISTICS	index_name:["idx1"], table_name:["t"], table_schema:["plan_cache"]
explain format='plan_tree' SELECT table_comment FROM information_schema.tables WHERE table_schema = DATABASE () AND table_name = 't';
id	task	access object	operator info
MemTableScan	root	table:TABLES	table_name:["t"], table_schema:["plan_cache"]
explain format='plan_tree' SELECT * FROM information_schema.referential_constraints rc JOIN information_schema.key_column_usage fk USING (constraint_schema, constraint_name) WHERE fk.referenced_column_name != 't' AND fk.table_schema = DATABASE () AND fk.table_name = 't' AND rc.constraint_schema = DATABASE () AND rc.table_name = 't';
id	task	access object	operator info
Projection	root		Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column
└─HashJoin	root		inner join, equal:[eq(Column, Column) eq(Column, Column)]
  ├─Selection(Build)	root		ne(Column, "t")
  │ └─MemTableScan	root	table:KEY_COLUMN_USAGE	constraint_schema:["plan_cache"], table_name:["t"], table_schema:["plan_cache"]
  └─MemTableScan(Probe)	root	table:REFERENTIAL_CONSTRAINTS	constraint_schema:["plan_cache"], table_name:["t"]