706 lines
29 KiB
Plaintext
706 lines
29 KiB
Plaintext
set tidb_cost_model_version=1;
|
|
set @@sql_mode='STRICT_TRANS_TABLES';
|
|
set @@tidb_enable_outer_join_reorder=true;
|
|
DROP TABLE IF EXISTS t;
|
|
CREATE TABLE t (
|
|
c1 int,
|
|
c2 int,
|
|
c3 int,
|
|
PRIMARY KEY (c1)
|
|
);
|
|
INSERT INTO t VALUES (1,2,3);
|
|
set session tidb_hashagg_partial_concurrency = 1;
|
|
set session tidb_hashagg_final_concurrency = 1;
|
|
SELECT * from t;
|
|
c1 c2 c3
|
|
1 2 3
|
|
SELECT c1, c2, c3 from t;
|
|
c1 c2 c3
|
|
1 2 3
|
|
SELECT c1, c1 from t;
|
|
c1 c1
|
|
1 1
|
|
SELECT c1 as a, c2 as a from t;
|
|
a a
|
|
1 2
|
|
SELECT 1;
|
|
1
|
|
1
|
|
SELECT 1, 1;
|
|
1 1
|
|
1 1
|
|
SET @@autocommit = 1;
|
|
SELECT @@autocommit;
|
|
@@autocommit
|
|
1
|
|
SELECT @@autocommit, @@autocommit;
|
|
@@autocommit @@autocommit
|
|
1 1
|
|
SET @a = 10;
|
|
SET @b = 11;
|
|
SELECT @a, @@autocommit;
|
|
@a @@autocommit
|
|
10 1
|
|
SELECT @a, @b;
|
|
@a @b
|
|
10 11
|
|
SELECT 1, @a;
|
|
1 @a
|
|
1 10
|
|
SELECT 1, @a as a;
|
|
1 a
|
|
1 10
|
|
SELECT 1, @a, @@autocommit as a, c1 from t;
|
|
1 @a a c1
|
|
1 10 1 1
|
|
SET @b = "123";
|
|
SELECT @b + "123";
|
|
@b + "123"
|
|
246
|
|
SELECT 1 + 1;
|
|
1 + 1
|
|
2
|
|
SELECT 1 a, 1 as a, 1 + 1 a;
|
|
a a a
|
|
1 1 2
|
|
SELECT c1 a, c1 as a from t;
|
|
a a
|
|
1 1
|
|
SELECT * from t LIMIT 0,1;
|
|
c1 c2 c3
|
|
1 2 3
|
|
SELECT * from t LIMIT 1;
|
|
c1 c2 c3
|
|
1 2 3
|
|
SELECT * from t LIMIT 1,1;
|
|
c1 c2 c3
|
|
SELECT * from t LIMIT 1 OFFSET 0;
|
|
c1 c2 c3
|
|
1 2 3
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t2 (
|
|
c1 int,
|
|
c2 int,
|
|
PRIMARY KEY (c1)
|
|
);
|
|
INSERT INTO t2 VALUES (1,2);
|
|
SELECT * from t a;
|
|
c1 c2 c3
|
|
1 2 3
|
|
SELECT * from t a, t2 b;
|
|
c1 c2 c3 c1 c2
|
|
1 2 3 1 2
|
|
SELECT * from t as a, t2 as b;
|
|
c1 c2 c3 c1 c2
|
|
1 2 3 1 2
|
|
SELECT * from t a left join t2 b on a.c1 = b.c1;
|
|
c1 c2 c3 c1 c2
|
|
1 2 3 1 2
|
|
SELECT * from (SELECT 1, 1) as a;
|
|
Error 1060 (42S21): Duplicate column name '1'
|
|
SELECT * from (SELECT * FROM t, t2) as a;
|
|
Error 1060 (42S21): Duplicate column name 'c1'
|
|
DROP TABLE IF EXISTS t;
|
|
CREATE TABLE t (c1 INT, c2 INT);
|
|
INSERT INTO t VALUES (1, 2), (1, 1), (1, 3);
|
|
SELECT c1=c2 FROM t;
|
|
c1=c2
|
|
0
|
|
1
|
|
0
|
|
SELECT 1=1;
|
|
1=1
|
|
1
|
|
SELECT t.c1 + t.c2 from t limit 1;
|
|
t.c1 + t.c2
|
|
3
|
|
SELECT t.c1 from t limit 1;
|
|
c1
|
|
1
|
|
SELECT t.c1 + c2 from t limit 1;
|
|
t.c1 + c2
|
|
3
|
|
SELECT c1 + 10 from t limit 1;
|
|
c1 + 10
|
|
11
|
|
SELECT t.c1 + 10 from t limit 1;
|
|
t.c1 + 10
|
|
11
|
|
SELECT all c1, c2 from t limit 1;
|
|
c1 c2
|
|
1 2
|
|
SELECT distinct c1, c2 from t order by c1, c2 limit 1;
|
|
c1 c2
|
|
1 1
|
|
SELECT c2 from t where not (c2 > 2);
|
|
c2
|
|
2
|
|
1
|
|
select c2 from t where not null is null;
|
|
c2
|
|
select !(1 + 2);
|
|
!(1 + 2)
|
|
0
|
|
select + - 1, --1, +-+-+1, + "123";
|
|
+ - 1 --1 +-+-+1 123
|
|
-1 1 1 123
|
|
select --------------------1, ++++++++++++++++++++1;
|
|
--------------------1 1
|
|
1 1
|
|
select +(+(1)), (-+1), ((+1)), +1.23, +1e23, +1E23, +null, +true, +false, + ( ( 1 ) );
|
|
1 (-+1) 1 1.23 1e23 1E23 NULL TRUE FALSE 1
|
|
1 -1 1 1.23 1e23 1e23 NULL 1 0 1
|
|
select +
|
|
(
|
|
+
|
|
(
|
|
1
|
|
)
|
|
)
|
|
;
|
|
1
|
|
1
|
|
select + ( + 1 );
|
|
1
|
|
1
|
|
select --+(1 + 1), +-+-(1 * 1);
|
|
--+(1 + 1) +-+-(1 * 1)
|
|
2 1
|
|
select * from t where null;
|
|
c1 c2
|
|
select * from t where 1;
|
|
c1 c2
|
|
1 2
|
|
1 1
|
|
1 3
|
|
select * from t where 0;
|
|
c1 c2
|
|
select * from t where 0 * 10;
|
|
c1 c2
|
|
select * from t where null is not null;
|
|
c1 c2
|
|
select * from t where !1;
|
|
c1 c2
|
|
select * from t where 1 && 0 || 3 && null;
|
|
c1 c2
|
|
select * from t as a, t2 as b;
|
|
c1 c2 c1 c2
|
|
1 2 1 2
|
|
1 1 1 2
|
|
1 3 1 2
|
|
select * from t as a cross join t2 as b;
|
|
c1 c2 c1 c2
|
|
1 2 1 2
|
|
1 1 1 2
|
|
1 3 1 2
|
|
select * from t as a join t2 as b;
|
|
c1 c2 c1 c2
|
|
1 2 1 2
|
|
1 1 1 2
|
|
1 3 1 2
|
|
select * from t as a join t2 as b on a.c2 = b.c2;
|
|
c1 c2 c1 c2
|
|
1 2 1 2
|
|
select * from (t);
|
|
c1 c2
|
|
1 2
|
|
1 1
|
|
1 3
|
|
select * from (t as a, t2 as b);
|
|
c1 c2 c1 c2
|
|
1 2 1 2
|
|
1 1 1 2
|
|
1 3 1 2
|
|
select * from (t as a cross join t2 as b);
|
|
c1 c2 c1 c2
|
|
1 2 1 2
|
|
1 1 1 2
|
|
1 3 1 2
|
|
select 1 as a from t;
|
|
a
|
|
1
|
|
1
|
|
1
|
|
select count(*), 1 from t;
|
|
count(*) 1
|
|
3 1
|
|
select *, 1 from t;
|
|
c1 c2 1
|
|
1 2 1
|
|
1 1 1
|
|
1 3 1
|
|
select 1, count(1), sum(1);
|
|
1 count(1) sum(1)
|
|
1 1 1
|
|
drop table if exists t1;
|
|
create table t1(a int primary key, b int, c int, index idx(b, c));
|
|
insert into t1 values(1, 2, 3);
|
|
insert into t1 values(2, 3, 4);
|
|
insert into t1 values(3 ,4, 5);
|
|
insert into t1 values(4, 5, 6);
|
|
insert into t1 values(5, 6, 7);
|
|
insert into t1 values(6, 7, 8);
|
|
insert into t1 values(7, 8, 9);
|
|
insert into t1 values(9, 10, 11);
|
|
explain format = 'plan_tree' select a, c from t1 use index(idx) order by a limit 5;
|
|
id task access object operator info
|
|
TopN root select.t1.a, offset:0, count:5
|
|
└─IndexReader root index:TopN
|
|
└─TopN cop[tikv] select.t1.a, offset:0, count:5
|
|
└─IndexFullScan cop[tikv] table:t1, index:idx(b, c) keep order:false, stats:pseudo
|
|
select c, a from t1 use index(idx) order by a limit 5;
|
|
c a
|
|
3 1
|
|
4 2
|
|
5 3
|
|
6 4
|
|
7 5
|
|
drop table if exists t;
|
|
create table t (a int, b int, c int, key idx(a, b, c));
|
|
explain format = 'plan_tree' select count(a) from t;
|
|
id task access object operator info
|
|
StreamAgg root funcs:count(Column)->Column
|
|
└─TableReader root data:StreamAgg
|
|
└─StreamAgg cop[tikv] funcs:count(select.t.a)->Column
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
select count(a) from t;
|
|
count(a)
|
|
0
|
|
insert t values(0,0,0);
|
|
explain format = 'plan_tree' select distinct b from t group by a;
|
|
id task access object operator info
|
|
HashAgg root group by:select.t.b, funcs:firstrow(select.t.b)->select.t.b
|
|
└─StreamAgg root group by:select.t.a, funcs:firstrow(Column)->select.t.b
|
|
└─IndexReader root index:StreamAgg
|
|
└─StreamAgg cop[tikv] group by:select.t.a, funcs:firstrow(select.t.b)->Column
|
|
└─IndexFullScan cop[tikv] table:t, index:idx(a, b, c) keep order:true, stats:pseudo
|
|
select distinct b from t group by a;
|
|
b
|
|
0
|
|
explain format = 'plan_tree' select count(b) from t group by a;
|
|
id task access object operator info
|
|
StreamAgg root group by:select.t.a, funcs:count(Column)->Column
|
|
└─IndexReader root index:StreamAgg
|
|
└─StreamAgg cop[tikv] group by:select.t.a, funcs:count(select.t.b)->Column
|
|
└─IndexFullScan cop[tikv] table:t, index:idx(a, b, c) keep order:true, stats:pseudo
|
|
select count(b) from t group by a;
|
|
count(b)
|
|
1
|
|
insert t values(1,1,1),(3,3,6),(3,2,5),(2,1,4),(1,1,3),(1,1,2);
|
|
explain format = 'plan_tree' select count(a) from t where b>0 group by a, b;
|
|
id task access object operator info
|
|
StreamAgg root group by:select.t.a, select.t.b, funcs:count(Column)->Column
|
|
└─IndexReader root index:StreamAgg
|
|
└─StreamAgg cop[tikv] group by:select.t.a, select.t.b, funcs:count(select.t.a)->Column
|
|
└─Selection cop[tikv] gt(select.t.b, 0)
|
|
└─IndexFullScan cop[tikv] table:t, index:idx(a, b, c) keep order:true, stats:pseudo
|
|
select count(a) from t where b>0 group by a, b;
|
|
count(a)
|
|
3
|
|
1
|
|
1
|
|
1
|
|
explain format = 'plan_tree' select count(a) from t where b>0 group by a, b order by a;
|
|
id task access object operator info
|
|
Projection root Column
|
|
└─StreamAgg root group by:select.t.a, select.t.b, funcs:count(Column)->Column, funcs:firstrow(select.t.a)->select.t.a
|
|
└─IndexReader root index:StreamAgg
|
|
└─StreamAgg cop[tikv] group by:select.t.a, select.t.b, funcs:count(select.t.a)->Column
|
|
└─Selection cop[tikv] gt(select.t.b, 0)
|
|
└─IndexFullScan cop[tikv] table:t, index:idx(a, b, c) keep order:true, stats:pseudo
|
|
select count(a) from t where b>0 group by a, b order by a;
|
|
count(a)
|
|
3
|
|
1
|
|
1
|
|
1
|
|
explain format = 'plan_tree' select count(a) from t where b>0 group by a, b order by a limit 1;
|
|
id task access object operator info
|
|
Limit root offset:0, count:1
|
|
└─StreamAgg root group by:select.t.a, select.t.b, funcs:count(Column)->Column, funcs:firstrow(select.t.a)->select.t.a
|
|
└─IndexReader root index:StreamAgg
|
|
└─StreamAgg cop[tikv] group by:select.t.a, select.t.b, funcs:count(select.t.a)->Column
|
|
└─Selection cop[tikv] gt(select.t.b, 0)
|
|
└─IndexFullScan cop[tikv] table:t, index:idx(a, b, c) keep order:true, stats:pseudo
|
|
select count(a) from t where b>0 group by a, b order by a limit 1;
|
|
count(a)
|
|
3
|
|
drop table if exists t;
|
|
create table t (id int primary key, a int, b int);
|
|
explain format = 'plan_tree' select * from (t t1 left join t t2 on t1.a = t2.a) left join (t t3 left join t t4 on t3.a = t4.a) on t2.b = 1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:HashJoin, left cond:[eq(select.t.b, 1)]
|
|
├─HashJoin(Build) root left outer join, left side:TableReader, equal:[eq(select.t.a, select.t.a)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t.a))
|
|
│ │ └─TableFullScan cop[tikv] table:t4 keep order:false, stats:pseudo
|
|
│ └─TableReader(Probe) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t3 keep order:false, stats:pseudo
|
|
└─HashJoin(Probe) root left outer join, left side:TableReader, equal:[eq(select.t.a, select.t.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
drop table if exists t;
|
|
create table t(a bigint primary key, b bigint);
|
|
desc select * from t where a = 1;
|
|
id estRows task access object operator info
|
|
Point_Get_1 1.00 root table:t handle:1
|
|
desc select * from t where a = '1';
|
|
id estRows task access object operator info
|
|
Point_Get_1 1.00 root table:t handle:1
|
|
desc select sysdate(), sleep(1), sysdate();
|
|
id estRows task access object operator info
|
|
Projection_3 1.00 root sysdate()->Column#1, sleep(1)->Column#2, sysdate()->Column#3
|
|
└─TableDual_4 1.00 root rows:1
|
|
drop table if exists th;
|
|
set @@session.tidb_partition_prune_mode = 'static';
|
|
create table th (a int, b int) partition by hash(a) partitions 3;
|
|
insert into th values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
|
|
insert into th values (-1,-1),(-2,-2),(-3,-3),(-4,-4),(-5,-5),(-6,-6),(-7,-7),(-8,-8);
|
|
desc select * from th where a=-2;
|
|
id estRows task access object operator info
|
|
TableReader_10 10.00 root data:Selection_9
|
|
└─Selection_9 10.00 cop[tikv] eq(select.th.a, -2)
|
|
└─TableFullScan_8 10000.00 cop[tikv] table:th, partition:p2 keep order:false, stats:pseudo
|
|
desc select * from th;
|
|
id estRows task access object operator info
|
|
PartitionUnion_10 30000.00 root
|
|
├─TableReader_13 10000.00 root data:TableFullScan_12
|
|
│ └─TableFullScan_12 10000.00 cop[tikv] table:th, partition:p0 keep order:false, stats:pseudo
|
|
├─TableReader_15 10000.00 root data:TableFullScan_14
|
|
│ └─TableFullScan_14 10000.00 cop[tikv] table:th, partition:p1 keep order:false, stats:pseudo
|
|
└─TableReader_17 10000.00 root data:TableFullScan_16
|
|
└─TableFullScan_16 10000.00 cop[tikv] table:th, partition:p2 keep order:false, stats:pseudo
|
|
desc select * from th partition (p2,p1);
|
|
id estRows task access object operator info
|
|
PartitionUnion_9 20000.00 root
|
|
├─TableReader_12 10000.00 root data:TableFullScan_11
|
|
│ └─TableFullScan_11 10000.00 cop[tikv] table:th, partition:p1 keep order:false, stats:pseudo
|
|
└─TableReader_14 10000.00 root data:TableFullScan_13
|
|
└─TableFullScan_13 10000.00 cop[tikv] table:th, partition:p2 keep order:false, stats:pseudo
|
|
set @@session.tidb_partition_prune_mode = 'dynamic';
|
|
analyze table th all columns;
|
|
desc select * from th where a=-2;
|
|
id estRows task access object operator info
|
|
TableReader_8 1.00 root partition:p2 data:Selection_7
|
|
└─Selection_7 1.00 cop[tikv] eq(select.th.a, -2)
|
|
└─TableFullScan_6 17.00 cop[tikv] table:th keep order:false
|
|
desc select * from th;
|
|
id estRows task access object operator info
|
|
TableReader_6 17.00 root partition:all data:TableFullScan_5
|
|
└─TableFullScan_5 17.00 cop[tikv] table:th keep order:false
|
|
desc select * from th partition (p2,p1);
|
|
id estRows task access object operator info
|
|
TableReader_6 17.00 root partition:p1,p2 data:TableFullScan_5
|
|
└─TableFullScan_5 17.00 cop[tikv] table:th keep order:false
|
|
set @@session.tidb_partition_prune_mode = DEFAULT;
|
|
drop table if exists t;
|
|
create table t(a int, b int);
|
|
explain format = 'plan_tree' select a != any (select a from t t2) from t t1;
|
|
id task access object operator info
|
|
Projection root and(or(or(gt(Column, 1), ne(select.t.a, Column)), if(ne(Column, 0), <nil>, 0)), and(ne(Column, 0), if(isnull(select.t.a), <nil>, 1)))->Column
|
|
└─HashJoin root CARTESIAN inner join
|
|
├─StreamAgg(Build) root funcs:max(Column)->Column, funcs:count(distinct Column)->Column, funcs:sum(Column)->Column, funcs:count(1)->Column
|
|
│ └─Projection root select.t.a->Column, select.t.a->Column, cast(isnull(select.t.a), decimal(20,0) BINARY)->Column
|
|
│ └─TableReader root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select a = all (select a from t t2) from t t1;
|
|
id task access object operator info
|
|
Projection root or(and(and(le(Column, 1), eq(select.t.a, Column)), if(ne(Column, 0), <nil>, 1)), or(eq(Column, 0), if(isnull(select.t.a), <nil>, 0)))->Column
|
|
└─HashJoin root CARTESIAN inner join
|
|
├─StreamAgg(Build) root funcs:max(Column)->Column, funcs:count(distinct Column)->Column, funcs:sum(Column)->Column, funcs:count(1)->Column
|
|
│ └─Projection root select.t.a->Column, select.t.a->Column, cast(isnull(select.t.a), decimal(20,0) BINARY)->Column
|
|
│ └─TableReader root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
drop table if exists t;
|
|
create table t(a int, b int);
|
|
drop table if exists s;
|
|
create table s(a varchar(20), b varchar(20));
|
|
explain format = 'plan_tree' select a in (select a from s where s.b = t.b) from t;
|
|
id task access object operator info
|
|
HashJoin root left outer semi join, left side:Projection, equal:[eq(Column, Column)], other cond:eq(cast(select.t.a, double BINARY), cast(select.s.a, double BINARY))
|
|
├─Projection(Build) root select.s.a, cast(select.s.b, double BINARY)->Column
|
|
│ └─TableReader root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:s keep order:false, stats:pseudo
|
|
└─Projection(Probe) root select.t.a, cast(select.t.b, double BINARY)->Column
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select a in (select a+b from t t2 where t2.b = t1.b) from t t1;
|
|
id task access object operator info
|
|
HashJoin root left outer semi join, left side:TableReader, equal:[eq(select.t.b, select.t.b)], other cond:eq(select.t.a, plus(select.t.a, select.t.b))
|
|
├─TableReader(Build) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
drop table t;
|
|
create table t(a int not null, b int);
|
|
explain format = 'plan_tree' select a in (select a from t t2 where t2.b = t1.b) from t t1;
|
|
id task access object operator info
|
|
HashJoin root left outer semi join, left side:TableReader, equal:[eq(select.t.b, select.t.b) eq(select.t.a, select.t.a)]
|
|
├─TableReader(Build) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select 1 from (select sleep(1)) t;
|
|
id task access object operator info
|
|
Projection root 1->Column
|
|
└─Projection root sleep(1)->Column
|
|
└─TableDual root rows:1
|
|
drop table if exists t;
|
|
create table t(a int, b int);
|
|
explain format = 'plan_tree' select a from t order by rand();
|
|
id task access object operator info
|
|
Projection root select.t.a
|
|
└─Sort root Column
|
|
└─Projection root select.t.a, rand()->Column
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select a, b from t order by abs(2);
|
|
id task access object operator info
|
|
TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
explain format = 'plan_tree' select a from t order by abs(rand())+1;
|
|
id task access object operator info
|
|
Projection root select.t.a
|
|
└─Sort root Column
|
|
└─Projection root select.t.a, plus(abs(rand()), 1)->Column
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
drop table if exists t1;
|
|
create table t1(a int, b int);
|
|
drop table if exists t2;
|
|
create table t2(a int, b int);
|
|
explain format = 'plan_tree' select * from t1 where t1.a in (select t2.a as a from t2 where t2.b > t1.b order by t1.b);
|
|
id task access object operator info
|
|
HashJoin root semi join, left side:TableReader, equal:[eq(select.t1.a, select.t2.a)], other cond:gt(select.t2.b, select.t1.b)
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t2.a)), not(isnull(select.t2.b))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] not(isnull(select.t1.a)), not(isnull(select.t1.b))
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
drop table t;
|
|
CREATE TABLE t (id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
i int(10) unsigned DEFAULT NULL,
|
|
x int(10) unsigned DEFAULT '0',
|
|
PRIMARY KEY (`id`)
|
|
);
|
|
explain format = 'plan_tree' select row_number() over( partition by i ) - x as rnk from t;
|
|
id task access object operator info
|
|
Projection root minus(Column, select.t.x)->Column
|
|
└─Shuffle root execution info: concurrency:5, data sources:[TableReader]
|
|
└─Window root row_number()->Column#5 over(partition by select.t.i rows between current row and current row)
|
|
└─Sort root select.t.i
|
|
└─ShuffleReceiver root
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
create table precise_types (
|
|
a BIGINT UNSIGNED NOT NULL,
|
|
b BIGINT NOT NULL,
|
|
c DECIMAL(21,1) NOT NULL,
|
|
d DOUBLE(21,1) NOT NULL
|
|
);
|
|
insert into precise_types values (
|
|
18446744073709551614,
|
|
-9223372036854775806,
|
|
99999999999999999999,
|
|
18446744073709551614
|
|
);
|
|
SELECT a, b, c, d FROM precise_types;
|
|
a b c d
|
|
18446744073709551614 -9223372036854775806 99999999999999999999.0 1.8446744073709552e19
|
|
drop table if exists t1;
|
|
create table t1(a int, b int);
|
|
insert into t1 values (1,1),(2,2),(1,3),(2,3);
|
|
drop table if exists t2;
|
|
create table t2(a int, b int);
|
|
insert into t2 values (1,1),(2,2),(3,4);
|
|
drop table if exists t3;
|
|
create table t3(a int, b int);
|
|
insert into t3 values (1,1),(2,3);
|
|
drop table if exists t4;
|
|
create table t4(a int, b int);
|
|
analyze table t1,t2,t3 all columns;
|
|
explain format = 'plan_tree' select * from t2 left join t1 on t2.a=t1.a left join t3 on t2.a=t3.a;
|
|
id task access object operator info
|
|
Projection root select.t2.a, select.t2.b, select.t1.a, select.t1.b, select.t3.a, select.t3.b
|
|
└─HashJoin root left outer join, left side:HashJoin, equal:[eq(select.t2.a, select.t1.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t1.a))
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false
|
|
└─HashJoin(Probe) root left outer join, left side:TableReader, equal:[eq(select.t2.a, select.t3.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t3.a))
|
|
│ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false
|
|
explain format = 'plan_tree' select * from t2 left join (t1 left join t3 on t1.a=t3.a) on t2.a=1;
|
|
id task access object operator info
|
|
HashJoin root CARTESIAN left outer join, left side:TableReader, left cond:[eq(select.t2.a, 1)]
|
|
├─HashJoin(Build) root left outer join, left side:TableReader, equal:[eq(select.t1.a, select.t3.a)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t3.a))
|
|
│ │ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
│ └─TableReader(Probe) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false
|
|
explain format = 'plan_tree' select * from t2 left join (t1 left join t3 on t1.a=t3.a) on t2.a=t3.a;
|
|
id task access object operator info
|
|
HashJoin root left outer join, left side:TableReader, equal:[eq(select.t2.a, select.t3.a)]
|
|
├─Projection(Build) root select.t1.a, select.t1.b, select.t3.a, select.t3.b
|
|
│ └─HashJoin root inner join, equal:[eq(select.t3.a, select.t1.a)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t3.a))
|
|
│ │ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
│ └─TableReader(Probe) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t1.a))
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:false
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:false
|
|
explain format = 'plan_tree' select * from t2 left join t1 on t1.a=t2.a join t3 on t2.b=t3.b;
|
|
id task access object operator info
|
|
Projection root select.t2.a, select.t2.b, select.t1.a, select.t1.b, select.t3.a, select.t3.b
|
|
└─HashJoin root left outer join, left side:HashJoin, equal:[eq(select.t2.a, select.t1.a)]
|
|
├─HashJoin(Build) root inner join, equal:[eq(select.t3.b, select.t2.b)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t3.b))
|
|
│ │ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
│ └─TableReader(Probe) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t2.b))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] not(isnull(select.t1.a))
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false
|
|
explain format = 'plan_tree' select * from t1 right join t2 on t1.a=t2.a join t3 on t2.b=t3.b;
|
|
id task access object operator info
|
|
Projection root select.t1.a, select.t1.b, select.t2.a, select.t2.b, select.t3.a, select.t3.b
|
|
└─HashJoin root right outer join, left side:TableReader, equal:[eq(select.t1.a, select.t2.a)]
|
|
├─HashJoin(Build) root inner join, equal:[eq(select.t3.b, select.t2.b)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t3.b))
|
|
│ │ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
│ └─TableReader(Probe) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t2.b))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] not(isnull(select.t1.a))
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false
|
|
explain format = 'plan_tree' select * from t2 right join t3 on t3.a=t2.a right join t1 on t2.a=t1.a;
|
|
id task access object operator info
|
|
HashJoin root right outer join, left side:Projection, equal:[eq(select.t2.a, select.t1.a)]
|
|
├─Projection(Build) root select.t2.a, select.t2.b, select.t3.a, select.t3.b
|
|
│ └─HashJoin root inner join, equal:[eq(select.t3.a, select.t2.a)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t3.a))
|
|
│ │ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
│ └─TableReader(Probe) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t2.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false
|
|
explain format = 'plan_tree' select * from (t1 left join t2 on t1.a=t2.a) left join (t3 left join t4 on t3.a=t4.a) on t2.a=t4.a;
|
|
id task access object operator info
|
|
HashJoin root left outer join, left side:HashJoin, equal:[eq(select.t2.a, select.t4.a)]
|
|
├─HashJoin(Build) root inner join, equal:[eq(select.t3.a, select.t4.a)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t3.a))
|
|
│ │ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
│ └─TableReader(Probe) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t4.a))
|
|
│ └─TableFullScan cop[tikv] table:t4 keep order:false, stats:pseudo
|
|
└─HashJoin(Probe) root left outer join, left side:TableReader, equal:[eq(select.t1.a, select.t2.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t2.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false
|
|
explain format = 'plan_tree' select * from (t1 left join t2 on t1.a=t2.a) left join (t3 left join t4 on t3.a=t4.a) on t2.a=t3.a;
|
|
id task access object operator info
|
|
HashJoin root left outer join, left side:HashJoin, equal:[eq(select.t2.a, select.t3.a)]
|
|
├─HashJoin(Build) root left outer join, left side:TableReader, equal:[eq(select.t3.a, select.t4.a)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t3.a))
|
|
│ │ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
│ └─TableReader(Probe) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t4.a))
|
|
│ └─TableFullScan cop[tikv] table:t4 keep order:false, stats:pseudo
|
|
└─HashJoin(Probe) root left outer join, left side:TableReader, equal:[eq(select.t1.a, select.t2.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t2.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false
|
|
explain format = 'plan_tree' select * from (t1 left join t2 on t1.a=t2.a) left join (t3 left join t4 on t3.a=t4.a) on t1.a=t4.a;
|
|
id task access object operator info
|
|
HashJoin root left outer join, left side:HashJoin, equal:[eq(select.t1.a, select.t4.a)]
|
|
├─HashJoin(Build) root inner join, equal:[eq(select.t3.a, select.t4.a)]
|
|
│ ├─TableReader(Build) root data:Selection
|
|
│ │ └─Selection cop[tikv] not(isnull(select.t3.a))
|
|
│ │ └─TableFullScan cop[tikv] table:t3 keep order:false
|
|
│ └─TableReader(Probe) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t4.a))
|
|
│ └─TableFullScan cop[tikv] table:t4 keep order:false, stats:pseudo
|
|
└─HashJoin(Probe) root left outer join, left side:TableReader, equal:[eq(select.t1.a, select.t2.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(select.t2.a))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false
|
|
drop table if exists t3;
|
|
create table t3(a char(10), primary key (a));
|
|
insert into t3 values ('a');
|
|
select * from t3 where a > 0x80;
|
|
Error 1105 (HY000): Cannot convert string '\x80' from binary to utf8mb4
|
|
set @@tidb_enable_outer_join_reorder=false;
|
|
set @@sql_mode=default;
|
|
SELECT 1 FROM (SELECT 1 x) a STRAIGHT_JOIN (SELECT 1 x) b USING (x);
|
|
1
|
|
1
|
|
SELECT 1 FROM (SELECT 1 x) a STRAIGHT_JOIN (SELECT 2 x) b USING (x);
|
|
1
|
|
SELECT 1 FROM (SELECT 1 x UNION ALL SELECT 3) a STRAIGHT_JOIN (SELECT 1 x UNION ALL SELECT 2) b USING (x);
|
|
1
|
|
1
|
|
DROP TABLE IF EXISTS t1,t2,t3,t4;
|
|
CREATE TABLE t1 (id INT PRIMARY KEY);
|
|
CREATE TABLE t2 (id INT PRIMARY KEY);
|
|
CREATE TABLE t3 (pk INT PRIMARY KEY);
|
|
CREATE TABLE t4 (id INT PRIMARY KEY, v VARCHAR(255));
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
INSERT INTO t2 VALUES (2);
|
|
INSERT INTO t4 VALUES (1,"first"),(2,"second"),(3,"third");
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 USING(id);
|
|
id
|
|
2
|
|
EXPLAIN format='plan_tree' SELECT * FROM t1 STRAIGHT_JOIN t2 USING(id);
|
|
id task access object operator info
|
|
MergeJoin root inner join, left key:select.t1.id, right key:select.t2.id
|
|
├─TableReader(Build) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:true, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:true, stats:pseudo
|
|
SELECT * FROM t2 STRAIGHT_JOIN t1 USING(id);
|
|
id
|
|
2
|
|
EXPLAIN format='plan_tree' SELECT * FROM t2 STRAIGHT_JOIN t1 USING(id);
|
|
id task access object operator info
|
|
MergeJoin root inner join, left key:select.t2.id, right key:select.t1.id
|
|
├─TableReader(Build) root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t1 keep order:true, stats:pseudo
|
|
└─TableReader(Probe) root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t2 keep order:true, stats:pseudo
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 USING(id) STRAIGHT_JOIN t4 USING(id);
|
|
id v
|
|
2 second
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 USING(i);
|
|
Error 1054 (42S22): Unknown column 'i' in 'from clause'
|
|
SELECT * FROM t1 STRAIGHT_JOIN t3 USING(id);
|
|
Error 1054 (42S22): Unknown column 'id' in 'from clause'
|