142 lines
5.3 KiB
Plaintext
142 lines
5.3 KiB
Plaintext
# TestSetTiDBEnableParallelApply
|
|
set tidb_enable_parallel_apply=0;
|
|
select @@tidb_enable_parallel_apply;
|
|
set tidb_enable_parallel_apply=1;
|
|
select @@tidb_enable_parallel_apply;
|
|
set tidb_enable_parallel_apply=on;
|
|
select @@tidb_enable_parallel_apply;
|
|
set tidb_enable_parallel_apply=off;
|
|
select @@tidb_enable_parallel_apply;
|
|
-- error 1231
|
|
set tidb_enable_parallel_apply=-1;
|
|
-- error 1231
|
|
set tidb_enable_parallel_apply=2;
|
|
-- error 1231
|
|
set tidb_enable_parallel_apply=1000;
|
|
-- error 1231
|
|
set tidb_enable_parallel_apply='onnn';
|
|
set tidb_enable_parallel_apply=default;
|
|
|
|
# TestApplyWithOtherFeatures
|
|
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);
|
|
--sorted_result
|
|
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;
|
|
--sorted_result
|
|
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;
|
|
|
|
## plan cache
|
|
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;
|
|
--sorted_result
|
|
execute stmt using @a;
|
|
set @a=2;
|
|
--sorted_result
|
|
execute stmt using @a;
|
|
select @@last_plan_from_cache;
|
|
|
|
## cluster index
|
|
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);
|
|
--sorted_result
|
|
select * from t where (select min(t2.b) from t2 where t2.a > t.a) > 0;
|
|
set tidb_enable_clustered_index=INT_ONLY;
|
|
|
|
## partitioning table
|
|
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);
|
|
--sorted_result
|
|
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;
|
|
|
|
set tidb_enable_parallel_apply=default;
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set tidb_enable_clustered_index=default;
|
|
|
|
# TestApplyInDML
|
|
set tidb_enable_parallel_apply=true;
|
|
|
|
## delete
|
|
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;
|
|
--sorted_result
|
|
select * from t;
|
|
explain format = 'brief' delete from t where (select min(t2.a) * 2 from t2 where t2.a < t.a) > 1;
|
|
|
|
## insert
|
|
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);
|
|
--sorted_result
|
|
select * from t;
|
|
|
|
## update
|
|
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);
|
|
--sorted_result
|
|
select * from t;
|
|
|
|
## replace
|
|
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);
|
|
--sorted_result
|
|
select * from t;
|
|
|
|
## transaction
|
|
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);
|
|
--sorted_result
|
|
select * from t1 where t1.b > any (select t2.b from t2 where t2.b < t1.b);
|
|
delete from t1 where a = 1;
|
|
--sorted_result
|
|
select * from t1 where t1.b > any (select t2.b from t2 where t2.b < t1.b);
|
|
commit;
|
|
--sorted_result
|
|
select * from t1 where t1.b > any (select t2.b from t2 where t2.b < t1.b);
|
|
set tidb_enable_parallel_apply=default;
|
|
|
|
# TestIssue24930
|
|
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;
|
|
set tidb_enable_parallel_apply=default;
|
|
|