You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 

69 lines
3.6 KiB

use test;
drop table if exists t1, t2, t3;
create table t1 (c1 int primary key, c2 int, c3 int, index c2 (c2));
load stats 's/explain_easy_stats_t1.json';
create table t2 (c1 int unique, c2 int);
load stats 's/explain_easy_stats_t2.json';
create table t3 (a bigint, b bigint, c bigint, d bigint);
load stats 's/explain_easy_stats_t3.json';
create table index_prune(a bigint(20) NOT NULL, b bigint(20) NOT NULL, c tinyint(4) NOT NULL, primary key(a, b), index idx_b_c_a(b, c, a));
load stats 's/explain_easy_stats_index_prune.json';
set @@session.tidb_opt_agg_push_down = 1;
set @@session.tidb_opt_insubq_to_join_and_agg=1;
set @@session.tidb_hashagg_partial_concurrency = 1;
set @@session.tidb_hashagg_final_concurrency = 1;
explain select * from t3 where exists (select s.a from t3 s having sum(s.a) = t3.a );
explain select * from t1;
explain select * from t1 order by c2;
explain select * from t2 order by c2;
explain select * from t1 where t1.c1 > 0;
explain select t1.c1, t1.c2 from t1 where t1.c2 = 1;
explain select * from t1 left join t2 on t1.c2 = t2.c1 where t1.c1 > 1;
explain update t1 set t1.c2 = 2 where t1.c1 = 1;
explain delete from t1 where t1.c2 = 1;
explain select count(b.c2) from t1 a, t2 b where a.c1 = b.c2 group by a.c1;
explain select * from t2 order by t2.c2 limit 0, 1;
explain select * from t1 where c1 > 1 and c2 = 1 and c3 < 1;
explain select * from t1 where c1 = 1 and c2 > 1;
# TODO: Add this test after support stream agg on TiKV.
# explain select sum(t1.c1 in (select c1 from t2)) from t1;
# id parents children task operator info count
# TableScan_20 StreamAgg_13 cop table:t1, range:(-inf,+inf), keep order:false 8000
# StreamAgg_13 TableScan_20 cop , funcs:sum(in(test.t1.c1, 1, 2)) 1
# TableReader_22 StreamAgg_21 root data:StreamAgg_13 1
# StreamAgg_21 TableReader_22 root , funcs:sum(col_0) 1
explain select c1 from t1 where c1 in (select c2 from t2);
# explain select (select count(1) k from t1 s where s.c1 = t1.c1 having k != 0) from t1;
explain select * from information_schema.columns;
explain select c2 = (select c2 from t2 where t1.c1 = t2.c1 order by c1 limit 1) from t1;
explain select * from t1 order by c1 desc limit 1;
set @@session.tidb_opt_insubq_to_join_and_agg=0;
# explain select sum(t1.c1 in (select c1 from t2)) from t1;
explain select 1 in (select c2 from t2) from t1;
# explain select sum(6 in (select c2 from t2)) from t1;
# explain format="dot" select sum(t1.c1 in (select c1 from t2)) from t1;
explain format="dot" select 1 in (select c2 from t2) from t1;
explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 1;
explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 0;
explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 1, 1;
explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 1, 0;
explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 0, 1;
explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 ORDER BY a;
explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 GROUP BY b;
explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 GROUP BY b ORDER BY a limit 1;
drop table if exists t1, t2, t3, index_prune;
set @@session.tidb_opt_insubq_to_join_and_agg=1;
drop table if exists tbl;
create table tbl(column1 int, column2 int, index idx(column1, column2));
load stats 's/explain_easy_stats_tbl_dnf.json';
explain select * from tbl where (column1=0 and column2=1) or (column1=1 and column2=3) or (column1=2 and column2=5);