# TestInMeetsPrepareAndExecute
prepare pr1 from 'select ? in (1,?,?)';
set @a=1, @b=2, @c=3;
execute pr1 using @a,@b,@c;
prepare pr2 from 'select 3 in (1,?,?)';
set @a=2, @b=3;
execute pr2 using @a,@b;
prepare pr3 from 'select ? in (1,2,3)';
set @a=4;
execute pr3 using @a;
prepare pr4 from 'select ? in (?,?,?)';
set @a=1, @b=2, @c=3, @d=4;
execute pr4 using @a,@b,@c,@d;

# TestOrderByFuncPlanCache
drop table if exists t;
create table t(a int);
prepare stmt from 'SELECT * FROM t order by rand()';
execute stmt;
prepare stmt from 'SELECT * FROM t order by now()';
execute stmt;

# TestSelectLimitPlanCache
drop table if exists t;
create table t(a int);
insert into t values(1), (2), (3);
set @@session.sql_select_limit = 1;
prepare stmt from 'SELECT * FROM t';
execute stmt;
set @@session.sql_select_limit = default;
execute stmt;
set @@session.sql_select_limit = 2;
execute stmt;
set @@session.sql_select_limit = 1;
execute stmt;
set @@session.sql_select_limit = default;
execute stmt;
set @@session.sql_select_limit = 2;
execute stmt;
set sql_select_limit=default;

# TestPreparePlanCache
set tidb_enable_prepared_plan_cache=ON;
drop table if exists t;
create table t(a int);
prepare stmt from 'select * from t where a = ?';
set @a = 1;
execute stmt using @a;
select @@last_plan_from_cache;
execute stmt using @a;
select @@last_plan_from_cache;
set tidb_enable_prepared_plan_cache=default;

# TestRowCountPlanCache
set tidb_enable_prepared_plan_cache=ON;
drop table if exists t;
create table t(a int auto_increment primary key);
prepare stmt from 'select row_count()';
insert into t values();
execute stmt;
insert into t values(),(),();
execute stmt;
set tidb_enable_prepared_plan_cache=default;

# TestCacheRegexpr
set tidb_enable_prepared_plan_cache=ON;
drop table if exists t1;
create table t1 (a varchar(40));
insert into t1 values ('C1'),('R1');
insert into mysql.expr_pushdown_blacklist values('regexp_like', 'tikv,tiflash,tidb', 'for test');
insert into mysql.expr_pushdown_blacklist values('regexp_substr', 'tikv,tiflash,tidb', 'for test');
insert into mysql.expr_pushdown_blacklist values('regexp_instr', 'tikv,tiflash,tidb', 'for test');
insert into mysql.expr_pushdown_blacklist values('regexp_replace', 'tikv,tiflash,tidb', 'for test');
admin reload expr_pushdown_blacklist;
set tidb_enable_vectorized_expression=ON;
prepare stmt1 from 'select a from t1 where a rlike ?';
prepare stmt2 from 'select a, regexp_substr(a, ?) from t1 order by a asc';
prepare stmt3 from 'select a, regexp_instr(a, ?) from t1 order by a asc';
prepare stmt4 from 'select a, regexp_replace(a, ?, ?) from t1 order by a asc';
set @a='^C.*';
set @r='xxxx';
execute stmt1 using @a;
execute stmt2 using @a;
execute stmt3 using @a;
execute stmt4 using @a, @r;
set @r='yyyy';
execute stmt4 using @a, @r;
set @a='^R.*';
execute stmt1 using @a;
execute stmt2 using @a;
execute stmt3 using @a;
execute stmt4 using @a, @r;
set tidb_enable_vectorized_expression=OFF;
set @a='^C.*';
set @r='xxxx';
execute stmt1 using @a;
execute stmt2 using @a;
execute stmt3 using @a;
execute stmt4 using @a, @r;
set @r='yyyy';
execute stmt4 using @a, @r;
set @a='^R.*';
execute stmt1 using @a;
execute stmt2 using @a;
execute stmt3 using @a;
execute stmt4 using @a, @r;
delete from mysql.expr_pushdown_blacklist where name like 'regexp%' and store_type = 'tikv,tiflash,tidb' and reason = 'for test';
admin reload expr_pushdown_blacklist;
set tidb_enable_prepared_plan_cache=default;
set tidb_enable_vectorized_expression=default;

# TestCacheLikeAndIlike
set tidb_enable_prepared_plan_cache=ON;
drop table if exists t1;
create table t1 (a varchar(40));
insert into t1 values ('C1'),('R1'),('c1'),('r1');
insert into mysql.expr_pushdown_blacklist values('like', 'tikv,tiflash,tidb', 'for test');
insert into mysql.expr_pushdown_blacklist values('ilike', 'tikv,tiflash,tidb', 'for test');
admin reload expr_pushdown_blacklist;
set tidb_enable_vectorized_expression=ON;
prepare stmt1 from 'select a from t1 where a like ? order by a asc';
prepare stmt2 from 'select a from t1 where a ilike ? order by a asc';
set @a='%C%';
execute stmt1 using @a;
execute stmt2 using @a;
set @a='%R%';
execute stmt1 using @a;
execute stmt2 using @a;
set tidb_enable_vectorized_expression=OFF;
set @a='%C%';
execute stmt1 using @a;
execute stmt2 using @a;
set @a='%R%';
execute stmt1 using @a;
execute stmt2 using @a;
delete from mysql.expr_pushdown_blacklist where name in ('like', 'ilike') and store_type = 'tikv,tiflash,tidb' and reason = 'for test';
admin reload expr_pushdown_blacklist;
set tidb_enable_prepared_plan_cache=default;
set tidb_enable_vectorized_expression=default;

# TestCacheAes
set tidb_enable_prepared_plan_cache=ON;
drop table if exists t1;
create table t1 (a varchar(40));
insert into t1 values ('a'),('b');
insert into mysql.expr_pushdown_blacklist values('aes_encrypt', 'tikv,tiflash,tidb', 'for test');
admin reload expr_pushdown_blacklist;
set tidb_enable_vectorized_expression=ON;
prepare stmt1 from 'select a, hex(aes_encrypt(a, ?)) from t1 order by a asc';
set @a='xx';
execute stmt1 using @a;
set @a='yy';
execute stmt1 using @a;
execute stmt2 using @a;
set tidb_enable_vectorized_expression=OFF;
set @a='xx';
execute stmt1 using @a;
set @a='yy';
execute stmt1 using @a;
execute stmt2 using @a;
delete from mysql.expr_pushdown_blacklist where name like 'aes_%' and store_type = 'tikv,tiflash,tidb' and reason = 'for test';
admin reload expr_pushdown_blacklist;
set tidb_enable_prepared_plan_cache=default;
set tidb_enable_vectorized_expression=default;

# TestCacheRefineArgs
set tidb_enable_prepared_plan_cache=ON;
drop table if exists t;
create table t(col_int int);
insert into t values(null);
prepare stmt from 'SELECT ((col_int is true) = ?) AS res FROM t';
set @p0='0.8';
execute stmt using @p0;
set @p0='0';
execute stmt using @p0;
prepare stmt from 'SELECT UCASE(?) < col_int from t;';
set @a1 = 'xayh7vrWVNqZtzlJmdJQUwAHnkI8Ec';
execute stmt using @a1;
delete from t;
insert into t values(1);
prepare stmt from 'SELECT col_int < ? FROM t';
set @p0='-184467440737095516167.1';
execute stmt using @p0;
set tidb_enable_prepared_plan_cache=default;

# TestCacheConstEval
set tidb_enable_prepared_plan_cache=ON;
drop table if exists t;
create table t(col_double double);
insert into t values (1);
set tidb_enable_vectorized_expression=false;
insert into mysql.expr_pushdown_blacklist values('cast', 'tikv,tiflash,tidb', 'for test');
admin reload expr_pushdown_blacklist;
prepare stmt from 'SELECT * FROM (SELECT col_double AS c0 FROM t) t WHERE (ABS((REPEAT(?, ?) OR 5617780767323292672)) < LN(EXP(c0)) + (? ^ ?))';
set @a1 = 'JuvkBX7ykVux20zQlkwDK2DFelgn7';
set @a2 = 1;
set @a3 = -112990.35179796701;
set @a4 = 87997.92704840179;
execute stmt using @a1, @a2, @a3, @a4;
set tidb_enable_vectorized_expression=default;
delete from mysql.expr_pushdown_blacklist where name = 'cast' and store_type = 'tikv,tiflash,tidb' and reason = 'for test';
admin reload expr_pushdown_blacklist;
set tidb_enable_prepared_plan_cache=default;