204 lines
6.8 KiB
Plaintext
204 lines
6.8 KiB
Plaintext
# 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;
|