276 lines
8.4 KiB
Plaintext
276 lines
8.4 KiB
Plaintext
# TestPreparedNullParam
|
|
set @@tidb_enable_prepared_plan_cache=true;
|
|
drop table if exists t;
|
|
create table t (id int, KEY id (id));
|
|
insert into t values (1), (2), (3);
|
|
prepare stmt from 'select * from t use index(id) where id = ?';
|
|
execute stmt using @id;
|
|
execute stmt using @id;
|
|
set @id="1";
|
|
execute stmt using @id;
|
|
execute stmt using @id2;
|
|
execute stmt using @id;
|
|
set @@tidb_enable_prepared_plan_cache=false;
|
|
drop table if exists t;
|
|
create table t (id int, KEY id (id));
|
|
insert into t values (1), (2), (3);
|
|
prepare stmt from 'select * from t use index(id) where id = ?';
|
|
execute stmt using @id;
|
|
execute stmt using @id;
|
|
set @id="1";
|
|
execute stmt using @id;
|
|
execute stmt using @id2;
|
|
execute stmt using @id;
|
|
set @@tidb_enable_prepared_plan_cache=default;
|
|
|
|
# TestPreparedIssue7579
|
|
set @@tidb_enable_prepared_plan_cache=true;
|
|
drop table if exists t;
|
|
create table t (a int, b int, index a_idx(a));
|
|
insert into t values (1,1), (2,2), (null,3);
|
|
select a, b from t order by b asc;
|
|
prepare stmt from 'select a, b from t where ? order by b asc';
|
|
execute stmt using @param;
|
|
set @param = true;
|
|
execute stmt using @param;
|
|
set @param = false;
|
|
execute stmt using @param;
|
|
set @param = 1;
|
|
execute stmt using @param;
|
|
set @param = 0;
|
|
execute stmt using @param;
|
|
set @@tidb_enable_prepared_plan_cache=false;
|
|
drop table if exists t;
|
|
create table t (a int, b int, index a_idx(a));
|
|
insert into t values (1,1), (2,2), (null,3);
|
|
select a, b from t order by b asc;
|
|
prepare stmt from 'select a, b from t where ? order by b asc';
|
|
execute stmt using @param;
|
|
set @param = true;
|
|
execute stmt using @param;
|
|
set @param = false;
|
|
execute stmt using @param;
|
|
set @param = 1;
|
|
execute stmt using @param;
|
|
set @param = 0;
|
|
execute stmt using @param;
|
|
set @@tidb_enable_prepared_plan_cache=default;
|
|
|
|
# TestPreparedIssue8644
|
|
set @@tidb_enable_prepared_plan_cache=true;
|
|
drop table if exists t;
|
|
create table t(data mediumblob);
|
|
prepare stmt from 'insert t (data) values (?)';
|
|
set @a = 'a';
|
|
execute stmt using @a;
|
|
set @b = 'aaaaaaaaaaaaaaaaaa';
|
|
execute stmt using @b;
|
|
select * from t;
|
|
drop table if exists t;
|
|
create table t(data decimal);
|
|
prepare stmt from 'insert t (data) values (?)';
|
|
set @a = '1';
|
|
execute stmt using @a;
|
|
set @b = '11111.11111';
|
|
execute stmt using @b;
|
|
select * from t;
|
|
drop table if exists t;
|
|
create table t(data decimal(10,3));
|
|
prepare stmt from 'insert t (data) values (?)';
|
|
set @a = 1.1;
|
|
execute stmt using @a;
|
|
set @b = 11.11;
|
|
execute stmt using @b;
|
|
select * from t;
|
|
set @@tidb_enable_prepared_plan_cache=false;
|
|
drop table if exists t;
|
|
create table t(data mediumblob);
|
|
prepare stmt from 'insert t (data) values (?)';
|
|
set @a = 'a';
|
|
execute stmt using @a;
|
|
set @b = 'aaaaaaaaaaaaaaaaaa';
|
|
execute stmt using @b;
|
|
select * from t;
|
|
drop table if exists t;
|
|
create table t(data decimal);
|
|
prepare stmt from 'insert t (data) values (?)';
|
|
set @a = '1';
|
|
execute stmt using @a;
|
|
set @b = '11111.11111';
|
|
execute stmt using @b;
|
|
select * from t;
|
|
drop table if exists t;
|
|
create table t(data decimal(10,3));
|
|
prepare stmt from 'insert t (data) values (?)';
|
|
set @a = 1.1;
|
|
execute stmt using @a;
|
|
set @b = 11.11;
|
|
execute stmt using @b;
|
|
select * from t;
|
|
set @@tidb_enable_prepared_plan_cache=default;
|
|
|
|
# TestPreparedNameResolver
|
|
drop table if exists t;
|
|
create table t (id int, KEY id (id));
|
|
prepare stmt from 'select * from t limit ? offset ?';
|
|
-- error 1054
|
|
prepare stmt from 'select b from t';
|
|
-- error 1054
|
|
prepare stmt from '(select * FROM t) union all (select * FROM t) order by a limit ?';
|
|
|
|
# TestPreparedDDL
|
|
# a 'create table' DDL statement should be accepted if it has no parameters.
|
|
drop table if exists t;
|
|
prepare stmt from 'create table t (id int, KEY id (id))';
|
|
|
|
# TestUnsupportedStmtForPrepare
|
|
# https://github.com/pingcap/tidb/issues/17412
|
|
prepare stmt0 from "create table t0(a int primary key)";
|
|
-- error 1295
|
|
prepare stmt1 from "execute stmt0";
|
|
-- error 1295
|
|
prepare stmt2 from "deallocate prepare stmt0";
|
|
-- error 1295
|
|
prepare stmt4 from "prepare stmt3 from 'create table t1(a int, b int)'";
|
|
|
|
# TestIgnorePlanCache
|
|
drop table if exists t;
|
|
create table t (id int primary key, num int);
|
|
insert into t values (1, 1);
|
|
insert into t values (2, 2);
|
|
insert into t values (3, 3);
|
|
prepare stmt from 'select /*+ IGNORE_PLAN_CACHE() */ * from t where id=?';
|
|
set @ignore_plan_doma = 1;
|
|
execute stmt using @ignore_plan_doma;
|
|
select @@last_plan_from_cache;
|
|
|
|
# TestPreparedStmtWithHint
|
|
## https://github.com/pingcap/tidb/issues/18535
|
|
prepare stmt from "select /*+ max_execution_time(10) */ sleep(3)";
|
|
set @a=now();
|
|
execute stmt;
|
|
select timediff(now(), @a) < 3;
|
|
set @a=now();
|
|
select /*+ max_execution_time(10) */ sleep(3);
|
|
select timediff(now(), @a) < 3;
|
|
|
|
## see https://github.com/pingcap/tidb/issues/46817
|
|
drop table if exists t;
|
|
create table t (i int);
|
|
prepare stmt from 'with a as (select /*+ qb_name(qb1) */ * from t) select /*+ leading(@qb1)*/ * from a;';
|
|
|
|
# TestIssue28782
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
prepare stmt from 'SELECT IF(?, 1, 0);';
|
|
set @a=1, @b=null, @c=0;
|
|
execute stmt using @a;
|
|
execute stmt using @b;
|
|
## TODO(Reminiscent): Support cache more tableDual plan.
|
|
select @@last_plan_from_cache;
|
|
execute stmt using @c;
|
|
select @@last_plan_from_cache;
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
|
|
# TestIssue28087And28162
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
## issue 28087
|
|
drop table if exists IDT_26207;
|
|
CREATE TABLE IDT_26207 (col1 bit(1));
|
|
insert into IDT_26207 values(0x0), (0x1);
|
|
prepare stmt from 'select hex(t1.col1) from IDT_26207 as t1 left join IDT_26207 as t2 on t1.col1 = t2.col1 where t1.col1 in (?, ?, ?)';
|
|
set @a=0x01, @b=0x01, @c=0x01;
|
|
execute stmt using @a,@b,@c;
|
|
set @a=0x00, @b=0x00, @c=0x01;
|
|
execute stmt using @a,@b,@c;
|
|
select @@last_plan_from_cache;
|
|
|
|
## issue 28162
|
|
drop table if exists IDT_MC21780;
|
|
CREATE TABLE IDT_MC21780 (
|
|
COL1 timestamp NULL DEFAULT NULL,
|
|
COL2 timestamp NULL DEFAULT NULL,
|
|
COL3 timestamp NULL DEFAULT NULL,
|
|
KEY U_M_COL (COL1,COL2)
|
|
);
|
|
insert into IDT_MC21780 values("1970-12-18 10:53:28", "1970-12-18 10:53:28", "1970-12-18 10:53:28");
|
|
prepare stmt from 'select/*+ hash_join(t1) */ * from IDT_MC21780 t1 join IDT_MC21780 t2 on t1.col1 = t2.col1 where t1. col1 < ? and t2. col1 in (?, ?, ?);';
|
|
set @a="2038-01-19 03:14:07", @b="2038-01-19 03:14:07", @c="2038-01-19 03:14:07", @d="2038-01-19 03:14:07";
|
|
execute stmt using @a,@b,@c,@d;
|
|
set @a="1976-09-09 20:21:11", @b="2021-07-14 09:28:16", @c="1982-01-09 03:36:39", @d="1970-12-18 10:53:28";
|
|
execute stmt using @a,@b,@c,@d;
|
|
select @@last_plan_from_cache;
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
|
|
# TestTemporaryTable4PlanCache
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
drop table if exists tmp2;
|
|
create temporary table tmp2 (a int, b int, key(a), key(b));
|
|
prepare stmt from 'select * from tmp2;';
|
|
execute stmt;
|
|
execute stmt;
|
|
select @@last_plan_from_cache;
|
|
drop table if exists tmp_t;
|
|
create global temporary table tmp_t (id int primary key, a int, b int, index(a)) on commit delete rows;
|
|
prepare stmt from 'select * from tmp_t;';
|
|
execute stmt;
|
|
execute stmt;
|
|
select @@last_plan_from_cache;
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
|
|
# TestIssue31141
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_txn_mode = 'pessimistic';
|
|
prepare stmt1 from 'do 1';
|
|
set @@tidb_txn_mode = 'optimistic';
|
|
prepare stmt1 from 'do 1';
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_txn_mode=default;
|
|
|
|
# TestLimitUnsupportedCase
|
|
drop table if exists t;
|
|
create table t(a int, key(a));
|
|
prepare stmt from 'select * from t limit ?';
|
|
set @a = 1.2;
|
|
-- error 1210
|
|
execute stmt using @a;
|
|
set @a = 1.;
|
|
-- error 1210
|
|
execute stmt using @a;
|
|
set @a = '0';
|
|
-- error 1210
|
|
execute stmt using @a;
|
|
set @a = '1';
|
|
-- error 1210
|
|
execute stmt using @a;
|
|
set @a = 1_2;
|
|
-- error 1210
|
|
execute stmt using @a;
|
|
|
|
# TestIssue38323
|
|
drop table if exists t;
|
|
create table t(id int, k int);
|
|
prepare stmt from 'explain select * from t where id = ? and k = ? group by id, k';
|
|
show warnings;
|
|
set @a = 1;
|
|
execute stmt using @a, @a;
|
|
explain select * from t where id = 1 and k = 1 group by id, k;
|
|
prepare stmt from 'explain select * from t where ? = id and ? = k group by id, k';
|
|
show warnings;
|
|
set @a = 1;
|
|
execute stmt using @a, @a;
|
|
explain select * from t where 1 = id and 1 = k group by id, k;
|
|
|
|
# TestIssue59275
|
|
-- error 1064
|
|
PREPARE stmt FROM 'VALUES ( ("foo"), ROW("bar") )';
|
|
SHOW ERRORS;
|
|
-- error 1064
|
|
PREPARE stmt FROM 'VALUES ( ("foo"), ROW("bar") )';
|
|
SHOW ERRORS;
|
|
-- error 1064
|
|
PREPARE stmt FROM 'VALUES ( ("foo"), ROW("bar") )';
|
|
SHOW ERRORS; |