ps.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:20k
- #
- # SQL Syntax for Prepared Statements test
- #
- --disable_warnings
- drop table if exists t1,t2;
- --enable_warnings
- create table t1
- (
- a int primary key,
- b char(10)
- );
- insert into t1 values (1,'one');
- insert into t1 values (2,'two');
- insert into t1 values (3,'three');
- insert into t1 values (4,'four');
- # basic functionality
- set @a=2;
- prepare stmt1 from 'select * from t1 where a <= ?';
- execute stmt1 using @a;
- set @a=3;
- execute stmt1 using @a;
- # non-existant statement
- --error 1243
- deallocate prepare no_such_statement;
- --error 1210
- execute stmt1;
- # Nesting ps commands is not allowed:
- --error 1064
- prepare stmt2 from 'prepare nested_stmt from "select 1"';
- --error 1064
- prepare stmt2 from 'execute stmt1';
- --error 1064
- prepare stmt2 from 'deallocate prepare z';
- # PS insert
- prepare stmt3 from 'insert into t1 values (?,?)';
- set @arg1=5, @arg2='five';
- execute stmt3 using @arg1, @arg2;
- select * from t1 where a>3;
- # PS update
- prepare stmt4 from 'update t1 set a=? where b=?';
- set @arg1=55, @arg2='five';
- execute stmt4 using @arg1, @arg2;
- select * from t1 where a>3;
- # PS create/delete
- prepare stmt4 from 'create table t2 (a int)';
- execute stmt4;
- prepare stmt4 from 'drop table t2';
- execute stmt4;
- # Do something that will cause error
- --error 1051
- execute stmt4;
- # placeholders in result field names.
- prepare stmt5 from 'select ? + a from t1';
- set @a=1;
- execute stmt5 using @a;
- execute stmt5 using @no_such_var;
- set @nullvar=1;
- set @nullvar=NULL;
- execute stmt5 using @nullvar;
- set @nullvar2=NULL;
- execute stmt5 using @nullvar2;
- # Check that multiple SQL statements are disabled inside PREPARE
- --error 1064
- prepare stmt6 from 'select 1; select2';
- --error 1064
- prepare stmt6 from 'insert into t1 values (5,"five"); select2';
- # This shouldn't parse
- --error 1064
- explain prepare stmt6 from 'insert into t1 values (5,"five"); select2';
- create table t2
- (
- a int
- );
- insert into t2 values (0);
- # parameter is NULL
- set @arg00=NULL ;
- prepare stmt1 from 'select 1 FROM t2 where a=?' ;
- execute stmt1 using @arg00 ;
- # prepare using variables:
- --error 1064
- prepare stmt1 from @nosuchvar;
- set @ivar= 1234;
- --error 1064
- prepare stmt1 from @ivar;
- set @fvar= 123.4567;
- --error 1064
- prepare stmt1 from @fvar;
- drop table t1,t2;
- #
- # Bug #4105: Server crash on attempt to prepare a statement with character
- # set introducer
- #
- PREPARE stmt1 FROM "select _utf8 'A' collate utf8_bin = ?";
- set @var='A';
- EXECUTE stmt1 USING @var;
- DEALLOCATE PREPARE stmt1;
- #
- # BUG#3486: FOUND_ROWS() fails inside stored procedure [and prepared statement]
- #
- create table t1 (id int);
- prepare stmt1 from "select FOUND_ROWS()";
- select SQL_CALC_FOUND_ROWS * from t1;
- # Expect 0
- execute stmt1;
- insert into t1 values (1);
- select SQL_CALC_FOUND_ROWS * from t1;
- # Expect 1
- execute stmt1;
- # Expect 0
- execute stmt1;
- deallocate prepare stmt1;
- drop table t1;
- #
- # prepared EXPLAIN
- #
- create table t1
- (
- c1 tinyint, c2 smallint, c3 mediumint, c4 int,
- c5 integer, c6 bigint, c7 float, c8 double,
- c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
- c13 date, c14 datetime, c15 timestamp(14), c16 time,
- c17 year, c18 bit, c19 bool, c20 char,
- c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
- c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
- c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
- c32 set('monday', 'tuesday', 'wednesday')
- ) engine = MYISAM ;
- create table t2 like t1;
- set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ;
- prepare stmt1 from @stmt ;
- execute stmt1 ;
- execute stmt1 ;
- explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25;
- deallocate prepare stmt1;
- drop tables t1,t2;
- #
- # parameters from variables (for field creation)
- #
- set @arg00=1;
- prepare stmt1 from ' create table t1 (m int) as select 1 as m ' ;
- execute stmt1 ;
- select m from t1;
- drop table t1;
- prepare stmt1 from ' create table t1 (m int) as select ? as m ' ;
- execute stmt1 using @arg00;
- select m from t1;
- deallocate prepare stmt1;
- drop table t1;
- #
- # eq() for parameters
- #
- create table t1 (id int(10) unsigned NOT NULL default '0',
- name varchar(64) NOT NULL default '',
- PRIMARY KEY (id), UNIQUE KEY `name` (`name`));
- insert into t1 values (1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5'),(6,'6'),(7,'7');
- prepare stmt1 from 'select name from t1 where id=? or id=?';
- set @id1=1,@id2=6;
- execute stmt1 using @id1, @id2;
- select name from t1 where id=1 or id=6;
- deallocate prepare stmt1;
- drop table t1;
- #
- # SHOW TABLE STATUS test
- #
- create table t1 ( a int primary key, b varchar(30)) engine = MYISAM ;
- prepare stmt1 from ' show table status from test like ''t1%'' ';
- --replace_column 8 4294967295 12 # 13 # 14 #
- execute stmt1;
- --replace_column 8 4294967295 12 # 13 # 14 #
- show table status from test like 't1%' ;
- deallocate prepare stmt1 ;
- drop table t1;
- #
- # Bug#4912 "mysqld crashs in case a statement is executed a second time":
- # negation elimination should work once and not break prepared statements
- #
- create table t1(a varchar(2), b varchar(3));
- prepare stmt1 from "select a, b from t1 where (not (a='aa' and b < 'zzz'))";
- execute stmt1;
- execute stmt1;
- deallocate prepare stmt1;
- drop table t1;
- #
- # Bug#5034 "prepared "select 1 into @arg15", second execute crashes
- # server".
- # Check that descendands of select_result can be reused in prepared
- # statements or are correctly created and deleted on each execute
- #
- prepare stmt1 from "select 1 into @var";
- execute stmt1;
- execute stmt1;
- prepare stmt1 from "create table t1 select 1 as i";
- execute stmt1;
- drop table t1;
- execute stmt1;
- prepare stmt1 from "insert into t1 select i from t1";
- execute stmt1;
- execute stmt1;
- prepare stmt1 from "select * from t1 into outfile 'f1.txt'";
- execute stmt1;
- deallocate prepare stmt1;
- drop table t1;
- #
- # BUG#5242 "Prepared statement names are case sensitive"
- #
- prepare stmt1 from 'select 1';
- prepare STMT1 from 'select 2';
- execute sTmT1;
- deallocate prepare StMt1;
- --error 1243
- deallocate prepare Stmt1;
- # also check that statement names are in right charset.
- set names utf8;
- prepare `眉` from 'select 1234';
- execute `眉` ;
- set names latin1;
- execute `黗;
- set names default;
- #
- # BUG#4368 "select * from t1 where a like ?" crashes server if a is in utf8
- # and ? is in latin1
- # Check that Item converting latin1 to utf8 (for LIKE function) is created
- # in memory of prepared statement.
- #
- create table t1 (a varchar(10)) charset=utf8;
- insert into t1 (a) values ('yahoo');
- set character_set_connection=latin1;
- prepare stmt from 'select a from t1 where a like ?';
- set @var='google';
- execute stmt using @var;
- execute stmt using @var;
- deallocate prepare stmt;
- drop table t1;
- #
- # BUG#5510 "inserting Null in AutoIncrement primary key Column Fails"
- # (prepared statements)
- # The cause: misuse of internal MySQL 'Field' API.
- #
- create table t1 (a bigint(20) not null primary key auto_increment);
- insert into t1 (a) values (null);
- select * from t1;
- prepare stmt from "insert into t1 (a) values (?)";
- set @var=null;
- execute stmt using @var;
- select * from t1;
- drop table t1;
- #
- # check the same for timestamps
- #
- create table t1 (a timestamp not null);
- prepare stmt from "insert into t1 (a) values (?)";
- execute stmt using @var;
- --disable_result_log
- select * from t1;
- --enable_result_log
- deallocate prepare stmt;
- drop table t1;
- #
- # BUG#5688 "Upgraded 4.1.5 Server seg faults" # (prepared statements)
- # The test case speaks for itself.
- # Just another place where we used wrong memory root for Items created
- # during statement prepare.
- #
- prepare stmt from "select 'abc' like convert('abc' using utf8)";
- execute stmt;
- execute stmt;
- deallocate prepare stmt;
- #
- # BUG#5748 "Prepared statement with BETWEEN and bigint values crashes
- # mysqld". Just another place where an item tree modification must be
- # rolled back.
- #
- create table t1 ( a bigint );
- prepare stmt from 'select a from t1 where a between ? and ?';
- set @a=1;
- execute stmt using @a, @a;
- execute stmt using @a, @a;
- execute stmt using @a, @a;
- drop table t1;
- deallocate prepare stmt;
- #
- # Bug #5987 subselect in bool function crashes server (prepared statements):
- # don't overwrite transformed subselects with old arguments of a bool
- # function.
- #
- create table t1 (a int);
- prepare stmt from "select * from t1 where 1 > (1 in (SELECT * FROM t1))";
- execute stmt;
- execute stmt;
- execute stmt;
- drop table t1;
- deallocate prepare stmt;
- #
- # Test case for Bug#6042 "constants propogation works only once (prepared
- # statements): check that the query plan changes whenever we change
- # placeholder value.
- #
- create table t1 (a int, b int);
- insert into t1 (a, b) values (1,1), (1,2), (2,1), (2,2);
- prepare stmt from
- "explain select * from t1 where t1.a=2 and t1.a=t1.b and t1.b > 1 + ?";
- --replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 -
- set @v=5;
- execute stmt using @v;
- --replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 -
- set @v=0;
- execute stmt using @v;
- --replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 -
- set @v=5;
- execute stmt using @v;
- drop table t1;
- deallocate prepare stmt;
- #
- # A test case for Bug#5985 prepare stmt from "select rand(?)" crashes
- # server. Check that Item_func_rand is prepared-statements friendly.
- #
- create table t1 (a int);
- insert into t1 (a) values (1), (2), (3), (4);
- set @precision=10000000000;
- --replace_column 1 - 3 -
- select rand(),
- cast(rand(10)*@precision as unsigned integer) from t1;
- prepare stmt from
- "select rand(),
- cast(rand(10)*@precision as unsigned integer),
- cast(rand(?)*@precision as unsigned integer) from t1";
- set @var=1;
- --replace_column 1 - 3 -
- execute stmt using @var;
- set @var=2;
- --replace_column 1 -
- execute stmt using @var;
- set @var=3;
- --replace_column 1 -
- execute stmt using @var;
- drop table t1;
- deallocate prepare stmt;
- #
- # A test case for Bug#6050 "EXECUTE stmt reports ambiguous fieldnames with
- # identical tables from different schemata"
- # Check that field name resolving in prepared statements works OK.
- #
- create database mysqltest1;
- create table t1 (a int);
- create table mysqltest1.t1 (a int);
- select * from t1, mysqltest1.t1;
- prepare stmt from "select * from t1, mysqltest1.t1";
- execute stmt;
- execute stmt;
- execute stmt;
- drop table t1;
- drop table mysqltest1.t1;
- drop database mysqltest1;
- deallocate prepare stmt;
- select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2';
- prepare stmt from
- "select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'";
- execute stmt;
- execute stmt;
- execute stmt;
- deallocate prepare stmt;
- #
- # Test CREATE TABLE ... SELECT (Bug #6094)
- #
- create table t1 (a int);
- insert into t1 values (1),(2),(3);
- create table t2 select * from t1;
- prepare stmt FROM 'create table t2 select * from t1';
- drop table t2;
- execute stmt;
- drop table t2;
- execute stmt;
- --error 1050
- execute stmt;
- drop table t2;
- execute stmt;
- drop table t1,t2;
- deallocate prepare stmt;
- #
- # Bug#6088 "FOUND_ROWS returns wrong values for prepared statements when
- # LIMIT is used"
- #
- create table t1 (a int);
- insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
- prepare stmt from "select sql_calc_found_rows * from t1 limit 2";
- execute stmt;
- select found_rows();
- execute stmt;
- select found_rows();
- execute stmt;
- select found_rows();
- deallocate prepare stmt;
- drop table t1;
- #
- # Bug#6047 "permission problem when executing mysql_stmt_execute with derived
- # table"
- #
- CREATE TABLE t1 (N int, M tinyint);
- INSERT INTO t1 VALUES (1,0),(1,0),(2,0),(2,0),(3,0);
- PREPARE stmt FROM 'UPDATE t1 AS P1 INNER JOIN (SELECT N FROM t1 GROUP BY N HAVING COUNT(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2';
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- DROP TABLE t1;
- #
- # Bug#6297 "prepared statement, wrong handling of <parameter> IS NULL"
- # Test that placeholders work with IS NULL/IS NOT NULL clauses.
- #
- prepare stmt from "select ? is null, ? is not null, ?";
- select @no_such_var is null, @no_such_var is not null, @no_such_var;
- execute stmt using @no_such_var, @no_such_var, @no_such_var;
- set @var='abc';
- select @var is null, @var is not null, @var;
- execute stmt using @var, @var, @var;
- set @var=null;
- select @var is null, @var is not null, @var;
- execute stmt using @var, @var, @var;
- #
- # Bug#6873 "PS, having with subquery, crash during execute"
- # check that if we modify having subtree, we update JOIN->having pointer
- #
- create table t1 (pnum char(3));
- create table t2 (pnum char(3));
- prepare stmt from "select pnum from t2 having pnum in (select 'p1' from t1)";
- execute stmt;
- execute stmt;
- execute stmt;
- deallocate prepare stmt;
- drop table t1, t2;
- #
- # Bug #6089: FOUND_ROWS returns wrong values when no table/view is used
- #
- prepare stmt from "SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0";
- execute stmt;
- SELECT FOUND_ROWS();
- execute stmt;
- SELECT FOUND_ROWS();
- deallocate prepare stmt;
- #
- # Bug#9096 "select doesn't return all matched records if prepared statements
- # is used"
- # The bug was is bad co-operation of the optimizer's algorithm which determines
- # which keys can be used to execute a query, constants propagation
- # part of the optimizer and parameter markers used by prepared statements.
- drop table if exists t1;
- create table t1 (c1 int(11) not null, c2 int(11) not null,
- primary key (c1,c2), key c2 (c2), key c1 (c1));
- insert into t1 values (200887, 860);
- insert into t1 values (200887, 200887);
- select * from t1 where (c1=200887 and c2=200887) or c2=860;
- prepare stmt from
- "select * from t1 where (c1=200887 and c2=200887) or c2=860";
- execute stmt;
- prepare stmt from
- "select * from t1 where (c1=200887 and c2=?) or c2=?";
- set @a=200887, @b=860;
- # this query did not return all matching rows
- execute stmt using @a, @b;
- deallocate prepare stmt;
- drop table t1;
- #
- # Bug#9777 - another occurrence of the problem stated in Bug#9096:
- # we can not compare basic constants by their names, because a placeholder
- # is a basic constant while his name is always '?'
- #
- create table t1 (
- id bigint(20) not null auto_increment,
- code varchar(20) character set utf8 collate utf8_bin not null default '',
- company_name varchar(250) character set utf8 collate utf8_bin default null,
- setup_mode tinyint(4) default null,
- start_date datetime default null,
- primary key (id), unique key code (code)
- );
- create table t2 (
- id bigint(20) not null auto_increment,
- email varchar(250) character set utf8 collate utf8_bin default null,
- name varchar(250) character set utf8 collate utf8_bin default null,
- t1_id bigint(20) default null,
- password varchar(250) character set utf8 collate utf8_bin default null,
- primary_contact tinyint(4) not null default '0',
- email_opt_in tinyint(4) not null default '1',
- primary key (id), unique key email (email), key t1_id (t1_id),
- constraint t2_fk1 foreign key (t1_id) references t1 (id)
- );
- insert into t1 values
- (1, 'demo', 'demo s', 0, current_date()),
- (2, 'code2', 'name 2', 0, current_date()),
- (3, 'code3', 'name 3', 0, current_date());
- insert into t2 values
- (2, 'email1', 'name1', 3, 'password1', 0, 0),
- (3, 'email2', 'name1', 1, 'password2', 1, 0),
- (5, 'email3', 'name3', 2, 'password3', 0, 0);
- prepare stmt from 'select t2.id from t2, t1 where (t1.id=? and t2.t1_id=t1.id)';
- set @a=1;
- execute stmt using @a;
- select t2.id from t2, t1 where (t1.id=1 and t2.t1_id=t1.id);
- deallocate prepare stmt;
- drop table t1, t2;
- #
- # Bug#11060 "Server crashes on calling stored procedure with INSERT SELECT
- # UNION SELECT" aka "Server crashes on re-execution of prepared INSERT ...
- # SELECT with UNION".
- #
- create table t1 (id int);
- prepare stmt from "insert into t1 (id) select id from t1 union select id from t1";
- execute stmt;
- execute stmt;
- deallocate prepare stmt;
- drop table t1;
- #
- # Bug#11458 "Prepared statement with subselects return random data":
- # drop PARAM_TABLE_BIT from the list of tables used by a subquery
- #
- create table t1 (
- id int(11) unsigned not null primary key auto_increment,
- partner_id varchar(35) not null,
- t1_status_id int(10) unsigned
- );
- insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
- ("3", "partner3", "10"), ("4", "partner4", "10");
- create table t2 (
- id int(11) unsigned not null default '0',
- t1_line_id int(11) unsigned not null default '0',
- article_id varchar(20),
- sequence int(11) not null default '0',
- primary key (id,t1_line_id)
- );
- insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
- ("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
- ("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
- ("4", "1", "sup", "0");
- create table t3 (
- id int(11) not null default '0',
- preceeding_id int(11) not null default '0',
- primary key (id,preceeding_id)
- );
- create table t4 (
- user_id varchar(50) not null,
- article_id varchar(20) not null,
- primary key (user_id,article_id)
- );
- insert into t4 values("nicke", "imp");
- prepare stmt from
- 'select distinct t1.partner_id
- from t1 left join t3 on t1.id = t3.id
- left join t1 pp on pp.id = t3.preceeding_id
- where
- exists (
- select *
- from t2 as pl_inner
- where pl_inner.id = t1.id
- and pl_inner.sequence <= (
- select min(sequence) from t2 pl_seqnr
- where pl_seqnr.id = t1.id
- )
- and exists (
- select * from t4
- where t4.article_id = pl_inner.article_id
- and t4.user_id = ?
- )
- )
- and t1.id = ?
- group by t1.id
- having count(pp.id) = 0';
- set @user_id = 'nicke';
- set @id = '2';
- execute stmt using @user_id, @id;
- execute stmt using @user_id, @id;
- deallocate prepare stmt;
- drop table t1, t2, t3, t4;
- #
- # Bug#9379: make sure that Item::collation is reset when one sets
- # a parameter marker from a string variable.
- #
- prepare stmt from 'select ?=?';
- set @a='CHRISTINE ';
- set @b='CHRISTINE';
- execute stmt using @a, @b;
- execute stmt using @a, @b;
- set @a=1, @b=2;
- execute stmt using @a, @b;
- set @a='CHRISTINE ';
- set @b='CHRISTINE';
- execute stmt using @a, @b;
- deallocate prepare stmt;
- #
- # Bug#9442 Set parameter make query fail if column character set is UCS2
- #
- create table t1 (utext varchar(20) character set ucs2);
- insert into t1 values ("lily");
- insert into t1 values ("river");
- prepare stmt from 'select utext from t1 where utext like ?';
- set @param1='%%';
- execute stmt using @param1;
- execute stmt using @param1;
- select utext from t1 where utext like '%%';
- drop table t1;
- deallocate prepare stmt;
- #
- # Bug#11299 "prepared statement makes wrong SQL syntax in binlog which stops
- # replication": check that errouneous queries with placeholders are not
- # allowed
- #
- create table t1 (a int);
- --error 1064
- prepare stmt from "select ??";
- --error 1064
- prepare stmt from "select ?FROM t1";
- --error 1064
- prepare stmt from "select FROM t1 WHERE?=1";
- --error 1064
- prepare stmt from "update t1 set a=a+?WHERE 1";
- --disable_ps_protocol
- --error 1064
- select ?;
- --error 1064
- select ??;
- --error 1064
- select ? from t1;
- --enable_ps_protocol
- drop table t1;
- #
- # Bug#9359 "Prepared statements take snapshot of system vars at PREPARE
- # time"
- #
- prepare stmt from "select @@time_zone";
- execute stmt;
- set @@time_zone:='Japan';
- execute stmt;
- prepare stmt from "select @@tx_isolation";
- execute stmt;
- set transaction isolation level read committed;
- execute stmt;
- set transaction isolation level serializable;
- execute stmt;
- set @@tx_isolation=default;
- execute stmt;
- deallocate prepare stmt;
- #
- # Bug#14410 "Crash in Enum or Set type in CREATE TABLE and PS/SP"
- #
- # Part I. Make sure the typelib for ENUM is created in the statement memory
- # root.
- prepare stmt from "create temporary table t1 (letter enum('','a','b','c')
- not null)";
- execute stmt;
- drop table t1;
- execute stmt;
- drop table t1;
- execute stmt;
- drop table t1;
- # Part II. Make sure that when the default value is converted to UTF-8,
- # the new item is # created in the statement memory root.
- set names latin1;
- prepare stmt from "create table t1 (a enum('test') default 'test')
- character set utf8";
- execute stmt;
- drop table t1;
- execute stmt;
- drop table t1;
- execute stmt;
- drop table t1;
- # Cleanup
- set names default;
- deallocate prepare stmt;
- # End of 4.1 tests