187 lines
6.8 KiB
Plaintext
187 lines
6.8 KiB
Plaintext
set tidb_enable_parallel_apply=0;
|
|
select @@tidb_enable_parallel_apply;
|
|
@@tidb_enable_parallel_apply
|
|
0
|
|
set tidb_enable_parallel_apply=1;
|
|
select @@tidb_enable_parallel_apply;
|
|
@@tidb_enable_parallel_apply
|
|
1
|
|
set tidb_enable_parallel_apply=on;
|
|
select @@tidb_enable_parallel_apply;
|
|
@@tidb_enable_parallel_apply
|
|
1
|
|
set tidb_enable_parallel_apply=off;
|
|
select @@tidb_enable_parallel_apply;
|
|
@@tidb_enable_parallel_apply
|
|
0
|
|
set tidb_enable_parallel_apply=-1;
|
|
Error 1231 (42000): Variable 'tidb_enable_parallel_apply' can't be set to the value of '-1'
|
|
set tidb_enable_parallel_apply=2;
|
|
Error 1231 (42000): Variable 'tidb_enable_parallel_apply' can't be set to the value of '2'
|
|
set tidb_enable_parallel_apply=1000;
|
|
Error 1231 (42000): Variable 'tidb_enable_parallel_apply' can't be set to the value of '1000'
|
|
set tidb_enable_parallel_apply='onnn';
|
|
Error 1231 (42000): Variable 'tidb_enable_parallel_apply' can't be set to the value of 'onnn'
|
|
set tidb_enable_parallel_apply=default;
|
|
set tidb_enable_parallel_apply=true;
|
|
drop table if exists t, t1;
|
|
create table t(a varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, b int);
|
|
create table t1(a varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, b int);
|
|
insert into t values ('a', 1), ('A', 2), ('a', 3), ('A', 4);
|
|
insert into t1 values ('a', 1), ('A', 2), ('a', 3), ('A', 4);
|
|
select (select min(t1.b) from t1 where t1.a >= t.a), (select sum(t1.b) from t1 where t1.a >= t.a) from t;
|
|
(select min(t1.b) from t1 where t1.a >= t.a) (select sum(t1.b) from t1 where t1.a >= t.a)
|
|
1 10
|
|
1 10
|
|
1 10
|
|
1 10
|
|
select (select min(t1.b) from t1 where t1.a >= t.a and t1.b >= t.b), (select sum(t1.b) from t1 where t1.a >= t.a and t1.b >= t.b) from t;
|
|
(select min(t1.b) from t1 where t1.a >= t.a and t1.b >= t.b) (select sum(t1.b) from t1 where t1.a >= t.a and t1.b >= t.b)
|
|
1 10
|
|
2 9
|
|
3 7
|
|
4 4
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
drop table if exists t1, t2;
|
|
create table t1(a int, b int);
|
|
create table t2(a int, b int);
|
|
insert into t1 values (1, 1), (1, 5), (2, 3), (2, 4), (3, 3);
|
|
insert into t2 values (0, 1), (2, -1), (3, 2);
|
|
prepare stmt from "select * from t1 where t1.b >= (select sum(t2.b) from t2 where t2.a > t1.a and t2.a > ?)";
|
|
set @a=1;
|
|
execute stmt using @a;
|
|
a b
|
|
1 1
|
|
1 5
|
|
2 3
|
|
2 4
|
|
set @a=2;
|
|
execute stmt using @a;
|
|
a b
|
|
1 5
|
|
2 3
|
|
2 4
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
set tidb_enable_clustered_index=ON;
|
|
drop table if exists t, t2;
|
|
create table t(a int, b int, c int, primary key(a, b));
|
|
create table t2(a int, b int, c int, primary key(a, c));
|
|
insert into t values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
|
|
insert into t2 values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
|
|
select * from t where (select min(t2.b) from t2 where t2.a > t.a) > 0;
|
|
a b c
|
|
1 1 1
|
|
2 2 2
|
|
3 3 3
|
|
set tidb_enable_clustered_index=INT_ONLY;
|
|
drop table if exists t1, t2;
|
|
create table t1(a int, b int) partition by range(a) (partition p0 values less than(10), partition p1 values less than(20), partition p2 values less than(30), partition p3 values less than(40));
|
|
create table t2(a int, b int) partition by hash(a) partitions 4;
|
|
insert into t1 values (5, 5), (15, 15), (25, 25), (35, 35);
|
|
insert into t2 values (5, 5), (15, 15), (25, 25), (35, 35);
|
|
select (select count(*) from t2 where t2.a > t1.b and t2.a=20), (select max(t2.b) from t2 where t2.a between t1.a and 20) from t1 where t1.a > 10;
|
|
(select count(*) from t2 where t2.a > t1.b and t2.a=20) (select max(t2.b) from t2 where t2.a between t1.a and 20)
|
|
0 NULL
|
|
0 NULL
|
|
0 15
|
|
set tidb_enable_parallel_apply=default;
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set tidb_enable_clustered_index=default;
|
|
set tidb_enable_parallel_apply=true;
|
|
drop table if exists t, t2;
|
|
create table t(a bigint, b int);
|
|
create table t2(a int, b int);
|
|
insert into t values (1, 1), (2, 2), (3, 3), (4, 4), (1, 1), (2, 2), (3, 3), (4, 4);
|
|
insert into t2 values (1, 1), (2, 2), (3, 3), (4, 4), (1, 1), (2, 2), (3, 3), (4, 4);
|
|
delete from t where (select min(t2.a) * 2 from t2 where t2.a < t.a) > 1;
|
|
select * from t;
|
|
a b
|
|
1 1
|
|
1 1
|
|
explain format = 'brief' delete from t where (select min(t2.a) * 2 from t2 where t2.a < t.a) > 1;
|
|
id estRows task access object operator info
|
|
Delete N/A root N/A
|
|
└─Projection 10000.00 root executor__parallel_apply.t._tidb_rowid
|
|
└─Apply 10000.00 root CARTESIAN inner join
|
|
├─TableReader(Build) 10000.00 root data:TableFullScan
|
|
│ └─TableFullScan 10000.00 cop[tikv] table:t keep order:false, stats:pseudo
|
|
└─Selection(Probe) 8000.00 root gt(mul(Column#7, 2), 1)
|
|
└─StreamAgg 10000.00 root funcs:min(executor__parallel_apply.t2.a)->Column#7
|
|
└─TopN 10000.00 root executor__parallel_apply.t2.a, offset:0, count:1
|
|
└─TableReader 10000.00 root data:TopN
|
|
└─TopN 10000.00 cop[tikv] executor__parallel_apply.t2.a, offset:0, count:1
|
|
└─Selection 79920000.00 cop[tikv] lt(executor__parallel_apply.t2.a, executor__parallel_apply.t.a), not(isnull(executor__parallel_apply.t2.a))
|
|
└─TableFullScan 100000000.00 cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
drop table if exists t;
|
|
create table t(a int, b int, c int);
|
|
insert into t values (1, 1, 1), (2, 2, 2), (3, 3, 3), (1, 1, 1), (2, 2, 2), (3, 3, 3);
|
|
insert into t (select * from t where (select count(*) from t t1 where t1.b > t.a) > 2);
|
|
select * from t;
|
|
a b c
|
|
1 1 1
|
|
1 1 1
|
|
1 1 1
|
|
1 1 1
|
|
2 2 2
|
|
2 2 2
|
|
3 3 3
|
|
3 3 3
|
|
drop table if exists t, t2;
|
|
create table t(a smallint, b int);
|
|
create table t2(a int, b int);
|
|
insert into t values (1, 1), (2, 2), (3, 3), (1, 1), (2, 2), (3, 3);
|
|
insert into t2 values (1, 1), (2, 2), (3, 3), (1, 1), (2, 2), (3, 3);
|
|
update t set a = a + 1 where (select count(*) from t2 where t2.a <= t.a) in (1, 2);
|
|
select * from t;
|
|
a b
|
|
2 1
|
|
2 1
|
|
2 2
|
|
2 2
|
|
3 3
|
|
3 3
|
|
drop table if exists t, t2;
|
|
create table t(a tinyint, b int, unique index idx(a));
|
|
create table t2(a tinyint, b int);
|
|
insert into t values (1, 1), (2, 2), (3, 3), (4, 4);
|
|
insert into t2 values (1, 1), (2, 2), (3, 3), (1, 1), (2, 2), (3, 3);
|
|
replace into t (select pow(t2.a, 2), t2.b from t2 where (select min(t.a) from t where t.a > t2.a) between 1 and 5);
|
|
select * from t;
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 2
|
|
9 3
|
|
drop table if exists t1, t2;
|
|
create table t1(a int, b int);
|
|
create table t2(a int, b int);
|
|
insert into t1 values (1, 2), (1, 3);
|
|
begin;
|
|
insert into t1 values (1, 4), (2, 3), (2, 5);
|
|
insert into t2 values (2, 3), (3, 4);
|
|
select * from t1 where t1.b > any (select t2.b from t2 where t2.b < t1.b);
|
|
a b
|
|
1 4
|
|
2 5
|
|
delete from t1 where a = 1;
|
|
select * from t1 where t1.b > any (select t2.b from t2 where t2.b < t1.b);
|
|
a b
|
|
2 5
|
|
commit;
|
|
select * from t1 where t1.b > any (select t2.b from t2 where t2.b < t1.b);
|
|
a b
|
|
2 5
|
|
set tidb_enable_parallel_apply=default;
|
|
set tidb_enable_parallel_apply=true;
|
|
drop table if exists t1, t2;
|
|
create table t1(a int);
|
|
create table t2(a int);
|
|
select case when t1.a is null
|
|
then (select t2.a from t2 where t2.a = t1.a limit 1) else t1.a end a
|
|
from t1 where t1.a=1 order by a limit 1;
|
|
a
|
|
set tidb_enable_parallel_apply=default;
|