# 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;