diff options
Diffstat (limited to 'mysql-test/main/ps.test')
| -rw-r--r-- | mysql-test/main/ps.test | 4674 |
1 files changed, 4674 insertions, 0 deletions
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test new file mode 100644 index 00000000000..59b157e2393 --- /dev/null +++ b/mysql-test/main/ps.test @@ -0,0 +1,4674 @@ +-- source include/not_embedded.inc +-- source include/have_log_bin.inc + +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); +# +# SQL Syntax for Prepared Statements test +# +--disable_warnings +drop table if exists t1,t2,t3,t4; + +drop database if exists mysqltest1; +# Avoid wrong warnings if mysql_client_test fails +drop database if exists client_test_db; +--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 ER_UNSUPPORTED_PS +prepare stmt2 from 'prepare nested_stmt from "select 1"'; + +--error ER_UNSUPPORTED_PS +prepare stmt2 from 'execute stmt1'; + +--error ER_UNSUPPORTED_PS +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; +deallocate prepare stmt3; +deallocate prepare stmt4; +deallocate prepare stmt5; + +# +# 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, 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 @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + +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; + +set optimizer_switch=@tmp_optimizer_switch; + +drop tables t1,t2; + +set @@optimizer_switch=@save_optimizer_switch; + +# +# 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 +# + +--let $outfile=$MYSQLTEST_VARDIR/tmp/f1.txt +--error 0,1 +--remove_file $outfile + +prepare stmt1 from "select 1 into @var"; +execute stmt1; +execute stmt1; +prepare stmt1 from "create table t1 select 1 as i"; +--disable_warnings ONCE +execute stmt1; +drop table t1; +--disable_warnings ONCE +execute stmt1; +prepare stmt1 from "insert into t1 select i from t1"; +execute stmt1; +execute stmt1; +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval prepare stmt1 from "select * from t1 into outfile '$outfile'"; +execute stmt1; +deallocate prepare stmt1; +drop table t1; + +--remove_file $outfile + +# +# 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 `ü`; +deallocate prepare `ü`; +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 + ?"; +set @v=5; +--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - +execute stmt using @v; +set @v=0; +--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - +execute stmt using @v; +set @v=5; +--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - +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; +--disable_warnings +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; +--enable_warnings +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#19399 "Stored Procedures 'Lost Connection' when dropping/creating +# tables" +# Check that multi-delete tables are also cleaned up before re-execution. +# +--disable_warnings +drop table if exists t1; +create temporary table if not exists t1 (a1 int); +--enable_warnings +# exact delete syntax is essential +prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1"; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +# the server crashed on the next statement without the fix +execute stmt; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +# the problem was in memory corruption: repeat the test just in case +execute stmt; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +execute stmt; +drop temporary table t1; +deallocate prepare stmt; + +# Bug#6102 "Server crash with prepared statement and blank after +# function name" +# ensure that stored functions are cached when preparing a statement +# before we open tables +# +create table t1 (a varchar(20)); +insert into t1 values ('foo'); +prepare stmt FROM 'SELECT char_length (a) FROM t1'; +-- error ER_SP_DOES_NOT_EXIST +prepare stmt2 FROM 'SELECT not_a_function (a) FROM t1'; +drop table t1; + +# +# 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', + preceding_id int(11) not null default '0', + primary key (id,preceding_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.preceding_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#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; + +# +# A test case for Bug#12734 "prepared statement may return incorrect result +# set for a select SQL request": test that canDoTurboBM is reset for each +# execute of a prepared statement. +# +create table t1 ( + word_id mediumint(8) unsigned not null default '0', + formatted varchar(20) not null default '' +); + +insert into t1 values + (80,'pendant'), (475,'pretendants'), (989,'tendances'), + (1019,'cependant'),(1022,'abondance'),(1205,'independants'), + (13,'lessiver'),(25,'lambiner'),(46,'situer'),(71,'terminer'), + (82,'decrocher'); + +select count(*) from t1 where formatted like '%NDAN%'; +select count(*) from t1 where formatted like '%ER'; +prepare stmt from "select count(*) from t1 where formatted like ?"; +set @like="%NDAN%"; +execute stmt using @like; +set @like="%ER"; +execute stmt using @like; +set @like="%NDAN%"; +execute stmt using @like; +set @like="%ER"; +execute stmt using @like; +deallocate prepare stmt; +drop table t1; + +# +# Bug#13134 "Length of VARCHAR() utf8 column is increasing when table is +# recreated with PS/SP" +# + +prepare stmt from 'create table t1 (a varchar(10) character set utf8)'; +execute stmt; +--disable_warnings +insert ignore into t1 (a) values (repeat('a', 20)); +--enable_warnings +select length(a) from t1; +drop table t1; +execute stmt; +--disable_warnings +insert ignore into t1 (a) values (repeat('a', 20)); +--enable_warnings +# Check that the data is truncated to the same length +select length(a) from t1; +drop table t1; +deallocate prepare stmt; + +# +# Bug#16248 "WHERE (col1,col2) IN ((?,?)) gives wrong results": +# check that ROW implementation is reexecution-friendly. +# +create table t1 (col1 integer, col2 integer); +insert into t1 values(100,100),(101,101),(102,102),(103,103); +prepare stmt from 'select col1, col2 from t1 where (col1, col2) in ((?,?))'; +set @a=100, @b=100; +execute stmt using @a,@b; +set @a=101, @b=101; +execute stmt using @a,@b; +set @a=102, @b=102; +execute stmt using @a,@b; +set @a=102, @b=103; +execute stmt using @a,@b; +deallocate prepare stmt; +drop table t1; + +# +# Bug#16365 Prepared Statements: DoS with too many open statements +# Check that the limit @@max_prpeared_stmt_count works. +# +# This is also the test for bug#23159 prepared_stmt_count should be +# status variable. +# +# Save the old value +set @old_max_prepared_stmt_count= @@max_prepared_stmt_count; +# +# Disable prepared statement protocol: in this test we set +# @@max_prepared_stmt_count to 0 or 1 and would like to test the limit +# manually. +# +--disable_ps_protocol +# +# A. Check that the new variables are present in SHOW VARIABLES and +# SHOW STATUS lists. +# +show variables like 'max_prepared_stmt_count'; +show status like 'prepared_stmt_count'; +# +# B. Check that the new system variable is selectable. +# +select @@max_prepared_stmt_count; +# +# C. Check that max_prepared_stmt_count is settable (global only). +# +set global max_prepared_stmt_count=-1; +select @@max_prepared_stmt_count; +set global max_prepared_stmt_count=10000000000000000; +select @@max_prepared_stmt_count; +set global max_prepared_stmt_count=default; +select @@max_prepared_stmt_count; +--error ER_GLOBAL_VARIABLE +set @@max_prepared_stmt_count=1; +--error ER_GLOBAL_VARIABLE +set max_prepared_stmt_count=1; +--error ER_GLOBAL_VARIABLE +set local max_prepared_stmt_count=1; +# set to a reasonable limit works +set global max_prepared_stmt_count=1; +select @@max_prepared_stmt_count; +# +# D. Check that the variables actually work. +# +set global max_prepared_stmt_count=0; +select @@max_prepared_stmt_count; +show status like 'prepared_stmt_count'; +--error ER_MAX_PREPARED_STMT_COUNT_REACHED +prepare stmt from "select 1"; +show status like 'prepared_stmt_count'; +set global max_prepared_stmt_count=1; +prepare stmt from "select 1"; +show status like 'prepared_stmt_count'; +--error ER_MAX_PREPARED_STMT_COUNT_REACHED +prepare stmt1 from "select 1"; +show status like 'prepared_stmt_count'; +deallocate prepare stmt; +show status like 'prepared_stmt_count'; +# +# E. Check that we can prepare a statement with the same name +# successfully, without hitting the limit. +# +prepare stmt from "select 1"; +show status like 'prepared_stmt_count'; +prepare stmt from "select 2"; +show status like 'prepared_stmt_count'; +# +# F. We can set the max below the current count. In this case no new +# statements should be allowed to prepare. +# +show status like 'prepared_stmt_count'; +select @@max_prepared_stmt_count; +set global max_prepared_stmt_count=0; +--error ER_MAX_PREPARED_STMT_COUNT_REACHED +prepare stmt from "select 1"; +# Result: the old statement is deallocated, the new is not created. +--error ER_UNKNOWN_STMT_HANDLER +execute stmt; +show status like 'prepared_stmt_count'; +--error ER_MAX_PREPARED_STMT_COUNT_REACHED +prepare stmt from "select 1"; +show status like 'prepared_stmt_count'; +# +# G. Show that the variables are up to date even after a connection with all +# statements in it was terminated. +# +set global max_prepared_stmt_count=3; +select @@max_prepared_stmt_count; +show status like 'prepared_stmt_count'; +prepare stmt from "select 1"; + +connect (con1,localhost,root,,); + +# Switch to connection con1 +connection con1; +let $con1_id=`SELECT CONNECTION_ID()`; + +prepare stmt from "select 2"; +prepare stmt1 from "select 3"; +--error ER_MAX_PREPARED_STMT_COUNT_REACHED +prepare stmt2 from "select 4"; +connection default; +--error ER_MAX_PREPARED_STMT_COUNT_REACHED +prepare stmt2 from "select 4"; +select @@max_prepared_stmt_count; +show status like 'prepared_stmt_count'; + +# Disconnect connection con1 and switch to default connection +disconnect con1; +connection default; + +# Wait for the connection con1 to die +let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist WHERE id=$con1_id; +--source include/wait_condition.inc + +deallocate prepare stmt; + +select @@max_prepared_stmt_count; +show status like 'prepared_stmt_count'; +# +# Restore the old value. +# +set global max_prepared_stmt_count= @old_max_prepared_stmt_count; +--enable_ps_protocol + + +# +# Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating +# tables" +# Check that multi-delete tables are also cleaned up before re-execution. +# +--disable_warnings +drop table if exists t1; +create temporary table if not exists t1 (a1 int); +--enable_warnings +# exact delete syntax is essential +prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1"; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +# the server crashed on the next statement without the fix +execute stmt; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +# the problem was in memory corruption: repeat the test just in case +execute stmt; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +execute stmt; +drop temporary table t1; +deallocate prepare stmt; + + +# +# BUG#22085: Crash on the execution of a prepared statement that +# uses an IN subquery with aggregate functions in HAVING +# + +CREATE TABLE t1( + ID int(10) unsigned NOT NULL auto_increment, + Member_ID varchar(15) NOT NULL default '', + Action varchar(12) NOT NULL, + Action_Date datetime NOT NULL, + Track varchar(15) default NULL, + User varchar(12) default NULL, + Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update + CURRENT_TIMESTAMP, + PRIMARY KEY (ID), + KEY Action (Action), + KEY Action_Date (Action_Date) +); + +INSERT INTO t1(Member_ID, Action, Action_Date, Track) VALUES + ('111111', 'Disenrolled', '2006-03-01', 'CAD' ), + ('111111', 'Enrolled', '2006-03-01', 'CAD' ), + ('111111', 'Disenrolled', '2006-07-03', 'CAD' ), + ('222222', 'Enrolled', '2006-03-07', 'CAD' ), + ('222222', 'Enrolled', '2006-03-07', 'CHF' ), + ('222222', 'Disenrolled', '2006-08-02', 'CHF' ), + ('333333', 'Enrolled', '2006-03-01', 'CAD' ), + ('333333', 'Disenrolled', '2006-03-01', 'CAD' ), + ('444444', 'Enrolled', '2006-03-01', 'CAD' ), + ('555555', 'Disenrolled', '2006-03-01', 'CAD' ), + ('555555', 'Enrolled', '2006-07-21', 'CAD' ), + ('555555', 'Disenrolled', '2006-03-01', 'CHF' ), + ('666666', 'Enrolled', '2006-02-09', 'CAD' ), + ('666666', 'Enrolled', '2006-05-12', 'CHF' ), + ('666666', 'Disenrolled', '2006-06-01', 'CAD' ); + +PREPARE STMT FROM +"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1 + WHERE Member_ID=? AND Action='Enrolled' AND + (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1 + WHERE Member_ID=? + GROUP BY Track + HAVING Track>='CAD' AND + MAX(Action_Date)>'2006-03-01')"; +SET @id='111111'; +EXECUTE STMT USING @id,@id; +SET @id='222222'; +EXECUTE STMT USING @id,@id; + +DEALLOCATE PREPARE STMT; +DROP TABLE t1; + +# +# BUG#21354: (COUNT(*) = 1) not working in SELECT inside prepared +# statement +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i INT, INDEX(i)); +INSERT INTO t1 VALUES (1); + +PREPARE stmt FROM "SELECT (COUNT(i) = 1), COUNT(i) FROM t1 WHERE i = ?"; +SET @a = 0; +EXECUTE stmt USING @a; +SET @a = 1; +EXECUTE stmt USING @a; +SET @a = 0; +EXECUTE stmt USING @a; + +PREPARE stmt FROM "SELECT (AVG(i) = 1), AVG(i) FROM t1 WHERE i = ?"; +SET @a = 0; +EXECUTE stmt USING @a; +SET @a = 1; +EXECUTE stmt USING @a; +SET @a = 0; +EXECUTE stmt USING @a; + +PREPARE stmt FROM "SELECT (VARIANCE(i) = 1), VARIANCE(i) FROM t1 WHERE i = ?"; +SET @a = 0; +EXECUTE stmt USING @a; +SET @a = 1; +EXECUTE stmt USING @a; +SET @a = 0; +EXECUTE stmt USING @a; + +PREPARE stmt FROM "SELECT (STDDEV(i) = 1), STDDEV(i) FROM t1 WHERE i = ?"; +SET @a = 0; +EXECUTE stmt USING @a; +SET @a = 1; +EXECUTE stmt USING @a; +SET @a = 0; +EXECUTE stmt USING @a; + +PREPARE stmt FROM "SELECT (BIT_OR(i) = 1), BIT_OR(i) FROM t1 WHERE i = ?"; +SET @a = 0; +EXECUTE stmt USING @a; +SET @a = 1; +EXECUTE stmt USING @a; +SET @a = 0; +EXECUTE stmt USING @a; + +PREPARE stmt FROM "SELECT (BIT_AND(i) = 1), BIT_AND(i) FROM t1 WHERE i = ?"; +SET @a = 0; +EXECUTE stmt USING @a; +SET @a = 1; +EXECUTE stmt USING @a; +SET @a = 0; +EXECUTE stmt USING @a; + +PREPARE stmt FROM "SELECT (BIT_XOR(i) = 1), BIT_XOR(i) FROM t1 WHERE i = ?"; +SET @a = 0; +EXECUTE stmt USING @a; +SET @a = 1; +EXECUTE stmt USING @a; +SET @a = 0; +EXECUTE stmt USING @a; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + +# +# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work +# from stored procedure. +# +# The cause of a bug was that cached LEX::create_list was modified, +# and then together with LEX::key_list was reset. +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (i INT); + +PREPARE st_19182 +FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1"; + +EXECUTE st_19182; +DESC t2; + +DROP TABLE t2; + +# Check that on second execution we don't loose 'j' column and the keys +# on 'i' and 'j' columns. +EXECUTE st_19182; +DESC t2; + +DEALLOCATE PREPARE st_19182; +DROP TABLE t2, t1; + +# +# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# +# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE +# statement modified HA_CREATE_INFO structure in LEX, making these +# statements PS/SP-unsafe (their re-execution might have resulted +# in incorrect results). +# +--disable_warnings +drop database if exists mysqltest; +drop table if exists t1, t2; +--enable_warnings +# CREATE TABLE and CREATE TABLE ... SELECT +create database mysqltest character set utf8; +prepare stmt1 from "create table mysqltest.t1 (c char(10))"; +prepare stmt2 from "create table mysqltest.t2 select 'test'"; +execute stmt1; +--disable_warnings ONCE +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop table mysqltest.t1; +drop table mysqltest.t2; +alter database mysqltest character set latin1; +execute stmt1; +--disable_warnings ONCE +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop database mysqltest; +deallocate prepare stmt1; +deallocate prepare stmt2; +# +# CREATE TABLE with DATA DIRECTORY option +# +--disable_warnings +--disable_query_log +eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'"; +--enable_query_log +execute stmt; +# +# DATA DIRECTORY option does not always work: if the operating +# system does not support symlinks, have_symlinks option is automatically +# disabled. +# In this case DATA DIRECTORY is silently ignored when +# creating a table, and is not output by SHOW CREATE TABLE. +# +--disable_result_log +show create table t1; +--enable_result_log +drop table t1; +execute stmt; +--disable_result_log +show create table t1; +--enable_result_log +--enable_warnings +drop table t1; +deallocate prepare stmt; +# + +# +# Bug #27937: crash on the second execution for prepared statement +# from UNION with ORDER BY an expression containing RAND() +# + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (2), (3), (1); + +PREPARE st1 FROM + '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; + +EXECUTE st1; +EXECUTE st1; + +DEALLOCATE PREPARE st1; +DROP TABLE t1; + + +# +# Bug #32137: prepared statement crash with str_to_date in update clause +# +create table t1 (a int, b tinyint); +prepare st1 from 'update t1 set b= (str_to_date(a, a))'; +execute st1; +deallocate prepare st1; +drop table t1; + +--echo End of 4.1 tests. + +############################# 5.0 tests start ################################ +# +# +# Bug#6102 "Server crash with prepared statement and blank after +# function name" +# ensure that stored functions are cached when preparing a statement +# before we open tables +# +create table t1 (a varchar(20)); +insert into t1 values ('foo'); +prepare stmt FROM 'SELECT char_length (a) FROM t1'; +-- error ER_SP_DOES_NOT_EXIST +prepare stmt2 FROM 'SELECT not_a_function (a) FROM t1'; +drop table t1; + +# +# Bug#8115: equality propagation and prepared statements +# + +create table t1 (a char(3) not null, b char(3) not null, + c char(3) not null, primary key (a, b, c)); +create table t2 like t1; + +# reduced query +prepare stmt from + "select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b) + where t1.a=1"; +execute stmt; +execute stmt; +execute stmt; + +# original query +prepare stmt from +"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from +(t1 left outer join t2 on t2.a=? and t1.b=t2.b) +left outer join t2 t3 on t3.a=? where t1.a=?"; + +set @a:=1, @b:=1, @c:=1; + +execute stmt using @a, @b, @c; +execute stmt using @a, @b, @c; +execute stmt using @a, @b, @c; + +deallocate prepare stmt; + +drop table t1,t2; + + +# +# Bug#9383: INFORMATION_SCHEMA.COLUMNS, JOIN, Crash, prepared statement +# + +eval SET @aux= "SELECT COUNT(*) + FROM INFORMATION_SCHEMA.COLUMNS A, + INFORMATION_SCHEMA.COLUMNS B + WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA + AND A.TABLE_NAME = B.TABLE_NAME + AND A.COLUMN_NAME = B.COLUMN_NAME AND + A.TABLE_NAME = 'user'"; + +let $exec_loop_count= 3; +eval prepare my_stmt from @aux; +while ($exec_loop_count) +{ + eval execute my_stmt; + dec $exec_loop_count; +} +deallocate prepare my_stmt; + +# Test CALL in prepared mode +delimiter |; +--disable_warnings +drop procedure if exists p1| +drop table if exists t1| +--enable_warnings +create table t1 (id int)| +insert into t1 values(1)| +create procedure p1(a int, b int) +begin + declare c int; + select max(id)+1 into c from t1; + insert into t1 select a+b; + insert into t1 select a-b; + insert into t1 select a-c; +end| +set @a= 3, @b= 4| +prepare stmt from "call p1(?, ?)"| +execute stmt using @a, @b| +execute stmt using @a, @b| +select * from t1| +deallocate prepare stmt| +drop procedure p1| +drop table t1| +delimiter ;| + + +# +# Bug#7306 LIMIT ?, ? and also WL#1785 " Prepared statements: implement +# support for placeholders in LIMIT clause." +# Add basic test coverage for the feature. +# +create table t1 (a int); +insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +prepare stmt from "select * from t1 limit ?, ?"; +set @offset=0, @limit=1; +execute stmt using @offset, @limit; +select * from t1 limit 0, 1; +set @offset=3, @limit=2; +execute stmt using @offset, @limit; +select * from t1 limit 3, 2; +prepare stmt from "select * from t1 limit ?"; +execute stmt using @limit; +--error 1235 +prepare stmt from "select * from t1 where a in (select a from t1 limit ?)"; +prepare stmt from "select * from t1 union all select * from t1 limit ?, ?"; +set @offset=9; +set @limit=2; +execute stmt using @offset, @limit; +prepare stmt from "(select * from t1 limit ?, ?) union all + (select * from t1 limit ?, ?) order by a limit ?"; +execute stmt using @offset, @limit, @offset, @limit, @limit; + +drop table t1; +deallocate prepare stmt; + +# +# Bug#12651 +# (Crash on a PS including a subquery which is a select from a simple view) +# +CREATE TABLE b12651_T1(a int) ENGINE=MYISAM; +CREATE TABLE b12651_T2(b int) ENGINE=MYISAM; +CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2; + +PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)'; +EXECUTE b12651; + +DROP VIEW b12651_V1; +DROP TABLE b12651_T1, b12651_T2; +DEALLOCATE PREPARE b12651; + + + +# +# Bug #14956: ROW_COUNT() returns incorrect result after EXECUTE of prepared +# statement +# +create table t1 (id int); +prepare ins_call from "insert into t1 (id) values (1)"; +execute ins_call; +select row_count(); +drop table t1; + +# +# BUG#16474: SP crashed MySQL +# (when using "order by localvar", where 'localvar' is just that. +# The actual bug test is in sp.test, this is just testing that we get the +# expected result for prepared statements too, i.e. place holders work as +# textual substitution. If it's a single integer, it works as the (deprecated) +# "order by column#", otherwise it's an expression. +# +create table t1 (a int, b int); +insert into t1 (a,b) values (2,8),(1,9),(3,7); + +# Will order by index +prepare stmt from "select * from t1 order by ?"; +set @a=NULL; +execute stmt using @a; +set @a=1; +execute stmt using @a; +set @a=2; +execute stmt using @a; +deallocate prepare stmt; +# For reference: +select * from t1 order by 1; + +# Will not order by index. +prepare stmt from "select * from t1 order by ?+1"; +set @a=0; +execute stmt using @a; +set @a=1; +execute stmt using @a; +deallocate prepare stmt; +# For reference: +select * from t1 order by 1+1; + +drop table t1; + +# +# Bug#19308 "REPAIR/OPTIMIZE/ANALYZE supported in SP but not in PS". +# Add test coverage for the added commands. +# +create table t1 (a int); +create table t2 like t1; +create table t3 like t2; +prepare stmt from "repair table t1"; +execute stmt; +execute stmt; +prepare stmt from "optimize table t1"; +execute stmt; +execute stmt; +prepare stmt from "analyze table t1"; +execute stmt; +execute stmt; +prepare stmt from "repair table t1, t2, t3"; +execute stmt; +execute stmt; +prepare stmt from "optimize table t1, t2, t3"; +execute stmt; +execute stmt; +prepare stmt from "analyze table t1, t2, t3"; +execute stmt; +execute stmt; +prepare stmt from "repair table t1, t4, t3"; +execute stmt; +execute stmt; +prepare stmt from "optimize table t1, t3, t4"; +execute stmt; +execute stmt; +prepare stmt from "analyze table t4, t1"; +execute stmt; +execute stmt; +deallocate prepare stmt; +drop table t1, t2, t3; + +# +# Bug#17199 "Table not found" error occurs if the query contains a call +# to a function from another database. +# Test prepared statements- related behaviour. +# +# +# ALTER TABLE RENAME and Prepared Statements: wrong DB name buffer was used +# in ALTER ... RENAME which caused memory corruption in prepared statements. +# No need to fix this problem in 4.1 as ALTER TABLE is not allowed in +# Prepared Statements in 4.1. +# +create database mysqltest_long_database_name_to_thrash_heap; +use test; +create table t1 (i int); +prepare stmt from "alter table test.t1 rename t1"; +use mysqltest_long_database_name_to_thrash_heap; +execute stmt; +show tables like 't1'; +prepare stmt from "alter table test.t1 rename t1"; +use test; +execute stmt; +show tables like 't1'; +use mysqltest_long_database_name_to_thrash_heap; +show tables like 't1'; +deallocate prepare stmt; +# +# Check that a prepared statement initializes its current database at +# PREPARE, and then works correctly even if the current database has been +# changed. +# +use mysqltest_long_database_name_to_thrash_heap; +# Necessary for preparation of INSERT/UPDATE/DELETE to succeed +prepare stmt_create from "create table t1 (i int)"; +prepare stmt_insert from "insert into t1 (i) values (1)"; +prepare stmt_update from "update t1 set i=2"; +prepare stmt_delete from "delete from t1 where i=2"; +prepare stmt_select from "select * from t1"; +prepare stmt_alter from "alter table t1 add column (b int)"; +prepare stmt_alter1 from "alter table t1 drop column b"; +prepare stmt_analyze from "analyze table t1"; +prepare stmt_optimize from "optimize table t1"; +prepare stmt_show from "show tables like 't1'"; +prepare stmt_truncate from "truncate table t1"; +prepare stmt_drop from "drop table t1"; +# Drop the table that was used to prepare INSERT/UPDATE/DELETE: we will +# create a new one by executing stmt_create +drop table t1; +# Switch the current database +use test; +# Check that all prepared statements operate on the database that was +# active at PREPARE +execute stmt_create; +# should return empty set +show tables like 't1'; +use mysqltest_long_database_name_to_thrash_heap; +show tables like 't1'; +use test; +execute stmt_insert; +select * from mysqltest_long_database_name_to_thrash_heap.t1; +execute stmt_update; +select * from mysqltest_long_database_name_to_thrash_heap.t1; +execute stmt_delete; +execute stmt_select; +execute stmt_alter; +show columns from mysqltest_long_database_name_to_thrash_heap.t1; +execute stmt_alter1; +show columns from mysqltest_long_database_name_to_thrash_heap.t1; +execute stmt_analyze; +execute stmt_optimize; +execute stmt_show; +execute stmt_truncate; +execute stmt_drop; +show tables like 't1'; +use mysqltest_long_database_name_to_thrash_heap; +show tables like 't1'; +# +# Attempt a statement PREPARE when there is no current database: +# is expected to return an error. +# +drop database mysqltest_long_database_name_to_thrash_heap; +--error ER_NO_DB_ERROR +prepare stmt_create from "create table t1 (i int)"; +--error ER_NO_DB_ERROR +prepare stmt_insert from "insert into t1 (i) values (1)"; +--error ER_NO_DB_ERROR +prepare stmt_update from "update t1 set i=2"; +--error ER_NO_DB_ERROR +prepare stmt_delete from "delete from t1 where i=2"; +--error ER_NO_DB_ERROR +prepare stmt_select from "select * from t1"; +--error ER_NO_DB_ERROR +prepare stmt_alter from "alter table t1 add column (b int)"; +--error ER_NO_DB_ERROR +prepare stmt_alter1 from "alter table t1 drop column b"; +--error ER_NO_DB_ERROR +prepare stmt_analyze from "analyze table t1"; +--error ER_NO_DB_ERROR +prepare stmt_optimize from "optimize table t1"; +--error ER_NO_DB_ERROR +prepare stmt_show from "show tables like 't1'"; +--error ER_NO_DB_ERROR +prepare stmt_truncate from "truncate table t1"; +--error ER_NO_DB_ERROR +prepare stmt_drop from "drop table t1"; +# +# The above has automatically deallocated all our statements. +# +# Attempt to CREATE a temporary table when no DB used: it should fail +# This proves that no table can be used without explicit specification of +# its database if there is no current database. +# +--error ER_NO_DB_ERROR +create temporary table t1 (i int); +# +# Restore the old environemnt +# +use test; + + +# +# BUG#21166: Prepared statement causes signal 11 on second execution +# +# Changes in an item tree done by optimizer weren't properly +# registered and went unnoticed, which resulted in preliminary freeing +# of used memory. +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2, t3; +--enable_warnings + +CREATE TABLE t1 (i BIGINT, j BIGINT); +CREATE TABLE t2 (i BIGINT); +CREATE TABLE t3 (i BIGINT, j BIGINT); + +PREPARE stmt FROM "SELECT * FROM t1 JOIN t2 ON (t2.i = t1.i) + LEFT JOIN t3 ON ((t3.i, t3.j) = (t1.i, t1.j)) + WHERE t1.i = ?"; + +SET @a= 1; +EXECUTE stmt USING @a; +EXECUTE stmt USING @a; + +DEALLOCATE PREPARE stmt; +DROP TABLE IF EXISTS t1, t2, t3; + + +# +# BUG#21081: SELECT inside stored procedure returns wrong results +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (i INT KEY); +CREATE TABLE t2 (i INT); + +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (1); + +PREPARE stmt FROM "SELECT t2.i FROM t1 LEFT JOIN t2 ON t2.i = t1.i + WHERE t1.i = ?"; + +SET @arg= 1; +EXECUTE stmt USING @arg; +SET @arg= 2; +EXECUTE stmt USING @arg; +SET @arg= 1; +EXECUTE stmt USING @arg; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2; + + +# +# BUG#20327: Marking of a wrong field leads to a wrong result on select with +# view, prepared statement and subquery. +# +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; + +INSERT INTO t1 VALUES (1), (2); + +let $query = SELECT t1.i FROM t1 JOIN v1 ON t1.i = v1.i + WHERE EXISTS (SELECT * FROM t1 WHERE v1.i = 1); +eval $query; +eval PREPARE stmt FROM "$query"; +# Statement execution should return '1'. +EXECUTE stmt; +# Check re-execution. +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1; + + +# +# BUG#21856: Prepared Statments: crash if bad create +# +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +let $iterations= 100; +--disable_query_log +--disable_result_log +while ($iterations) +{ + --error ER_PARSE_ERROR + PREPARE stmt FROM "CREATE PROCEDURE p1()"; + dec $iterations; +} +--enable_query_log +--enable_result_log + +# +# Bug 19764: SHOW commands end up in the slow log as table scans +# + +flush status; +prepare sq from 'show status like "slow_queries"'; +execute sq; +prepare no_index from 'select 1 from information_schema.tables limit 1'; +execute sq; +execute no_index; +execute sq; +deallocate prepare no_index; +deallocate prepare sq; + + +# +# Bug 25027: query with a single-row non-correlated subquery +# and IS NULL predicate +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (NULL); + +SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL; +PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL'; + +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL'; +SET @arg=1; +EXECUTE stmt USING @arg; +DEALLOCATE PREPARE stmt; + +DROP TABLE t1,t2; +# +# Bug#4968 "Stored procedure crash if cursor opened on altered table" +# The bug is not repeatable any more after the fix for +# Bug#15217 "Bug #15217 Using a SP cursor on a table created with PREPARE +# fails with weird error", however ALTER TABLE is not re-execution friendly +# and that caused a valgrind warning. Check that the warning is gone. +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (s1 char(20)); +prepare stmt from "alter table t1 modify s1 int"; +execute stmt; +execute stmt; +drop table t1; +deallocate prepare stmt; + +# +# Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing" +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, b int); +prepare s_6895 from "alter table t1 drop column b"; +execute s_6895; +show columns from t1; +drop table t1; +create table t1 (a int, b int); +execute s_6895; +show columns from t1; +drop table t1; +create table t1 (a int, b int); +execute s_6895; +show columns from t1; +deallocate prepare s_6895; +drop table t1; + +# +# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# +# 5.0 part of the test. +# + +# ALTER TABLE +create table t1 (i int primary key auto_increment) comment='comment for table t1'; +create table t2 (i int, j int, k int); +prepare stmt from "alter table t1 auto_increment=100"; +execute stmt; +show create table t1; +# Let us trash table-cache's memory +flush tables; +select * from t2; +execute stmt; +show create table t1; +deallocate prepare stmt; +drop table t1, t2; +# 5.1 part of the test. +# CREATE DATABASE +set @old_character_set_server= @@character_set_server; +set @@character_set_server= latin1; +prepare stmt from "create database mysqltest_1"; +execute stmt; +show create database mysqltest_1; +drop database mysqltest_1; +set @@character_set_server= utf8; +execute stmt; +show create database mysqltest_1; +drop database mysqltest_1; +deallocate prepare stmt; +set @@character_set_server= @old_character_set_server; + + +# +# BUG#24491 "using alias from source table in insert ... on duplicate key" +# +--disable_warnings +drop tables if exists t1; +--enable_warnings +create table t1 (id int primary key auto_increment, value varchar(10)); +insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD'); +# Let us prepare INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement +# which in its ON DUPLICATE KEY clause erroneously tries to assign value +# to a column which is mentioned only in SELECT part. +prepare stmt from "insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP'"; +# Both first and second attempts to execute it should fail +--error ER_BAD_FIELD_ERROR +execute stmt; +--error ER_BAD_FIELD_ERROR +execute stmt; +deallocate prepare stmt; +# And now the same test for more complex case which is more close +# to the one that was reported originally. +prepare stmt from "insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP'"; +--error ER_BAD_FIELD_ERROR +execute stmt; +--error ER_BAD_FIELD_ERROR +execute stmt; +deallocate prepare stmt; +drop tables t1; + +# +# Bug #28509: strange behaviour: passing a decimal value to PS +# +prepare stmt from "create table t1 select ?"; +set @a=1.0; +execute stmt using @a; +show create table t1; +drop table t1; + +# +# Bug#33798: prepared statements improperly handle large unsigned ints +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a bigint unsigned, b bigint(20) unsigned); +prepare stmt from "insert into t1 values (?,?)"; +set @a= 9999999999999999; +set @b= 14632475938453979136; +insert into t1 values (@a, @b); +select * from t1 where a = @a and b = @b; +execute stmt using @a, @b; +select * from t1 where a = @a and b = @b; +deallocate prepare stmt; +drop table t1; + +# +# Bug#32890 Crash after repeated create and drop of tables and views +# + +--disable_warnings +drop view if exists v1; +drop table if exists t1; +--enable_warnings + +create table t1 (a int, b int); +insert into t1 values (1,1), (2,2), (3,3); +insert into t1 values (3,1), (1,2), (2,3); + +prepare stmt from "create view v1 as select * from t1"; +execute stmt; +drop table t1; +create table t1 (a int, b int); +drop view v1; +execute stmt; +show create view v1; +drop view v1; + +prepare stmt from "create view v1 (c,d) as select a,b from t1"; +execute stmt; +show create view v1; +select * from v1; +drop view v1; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from "create view v1 (c) as select b+1 from t1"; +execute stmt; +show create view v1; +select * from v1; +drop view v1; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from "create view v1 (c,d,e,f) as select a,b,a in (select a+2 from t1), a = all (select a from t1) from t1"; +execute stmt; +show create view v1; +select * from v1; +drop view v1; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from "create or replace view v1 as select 1"; +execute stmt; +show create view v1; +select * from v1; +execute stmt; +show create view v1; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from "create view v1 as select 1, 1"; +execute stmt; +show create view v1; +select * from v1; +drop view v1; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from "create view v1 (x) as select a from t1 where a > 1"; +execute stmt; +show create view v1; +select * from v1; +drop view v1; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from "create view v1 as select * from `t1` `b`"; +execute stmt; +show create view v1; +select * from v1; +drop view v1; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from "create view v1 (a,b,c) as select * from t1"; +--error ER_VIEW_WRONG_LIST +execute stmt; +--error ER_VIEW_WRONG_LIST +execute stmt; +deallocate prepare stmt; + +drop table t1; +create temporary table t1 (a int, b int); + +prepare stmt from "create view v1 as select * from t1"; +--error ER_VIEW_SELECT_TMPTABLE +execute stmt; +--error ER_VIEW_SELECT_TMPTABLE +execute stmt; +deallocate prepare stmt; + +drop table t1; + +--error ER_NO_SUCH_TABLE +prepare stmt from "create view v1 as select * from t1"; +--error ER_NO_SUCH_TABLE +prepare stmt from "create view v1 as select * from `t1` `b`"; + +# +# Bug#33851: Passing UNSIGNED param to EXECUTE returns ERROR 1210 +# + +prepare stmt from "select ?"; +set @arg= 123456789.987654321; +select @arg; +execute stmt using @arg; +set @arg= "string"; +select @arg; +execute stmt using @arg; +set @arg= 123456; +select @arg; +execute stmt using @arg; +set @arg= cast(-12345.54321 as decimal(20, 10)); +select @arg; +execute stmt using @arg; +deallocate prepare stmt; + +--echo # +--echo # Bug#48508: Crash on prepared statement re-execution. +--echo # +create table t1(b int); +insert into t1 values (0); +create view v1 AS select 1 as a from t1 where b; +prepare stmt from "select * from v1 where a"; +execute stmt; +execute stmt; +deallocate prepare stmt; +drop table t1; +drop view v1; + +create table t1(a bigint); +create table t2(b tinyint); +insert into t2 values (null); +prepare stmt from "select 1 from t1 join t2 on a xor b where b > 1 and a =1"; +execute stmt; +execute stmt; +deallocate prepare stmt; +drop table t1,t2; +--echo # + + +--echo # +--echo # Bug #49570: Assertion failed: !(order->used & map) +--echo # on re-execution of prepared statement +--echo # +CREATE TABLE t1(a INT PRIMARY KEY); +INSERT INTO t1 VALUES(0), (1); +PREPARE stmt FROM + "SELECT 1 FROM t1 JOIN t1 t2 USING(a) GROUP BY t2.a, t1.a"; +EXECUTE stmt; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + + +--echo End of 5.0 tests. + +# +# Bug #20665: All commands supported in Stored Procedures should work in +# Prepared Statements +# +create procedure proc_1() reset query cache; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int deterministic begin reset query cache; return 1; end| +create function func_1() returns int deterministic begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "reset query cache"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() reset master; +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin reset master; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "reset master"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() reset slave; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin reset slave; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "reset slave"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1(a integer) kill a; +--error ER_NO_SUCH_THREAD +call proc_1(0); +--error ER_NO_SUCH_THREAD +call proc_1(0); +--error ER_NO_SUCH_THREAD +call proc_1(0); +drop procedure proc_1; +delimiter |; +create function func_1() returns int begin kill 0; return 1; end| +delimiter ;| +--error ER_NO_SUCH_THREAD +select func_1() from dual; +--error ER_NO_SUCH_THREAD +select func_1() from dual; +--error ER_NO_SUCH_THREAD +select func_1() from dual; +drop function func_1; +prepare abc from "kill 0"; +--error ER_NO_SUCH_THREAD +execute abc; +--error ER_NO_SUCH_THREAD +execute abc; +--error ER_NO_SUCH_THREAD +execute abc; +deallocate prepare abc; + + +create procedure proc_1() flush hosts; +call proc_1(); +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush hosts; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush hosts"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() flush privileges; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush privileges; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush privileges"; +deallocate prepare abc; + + +create procedure proc_1() flush tables with read lock; +call proc_1(); +unlock tables; +call proc_1(); +unlock tables; +call proc_1(); +unlock tables; +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush tables with read lock; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush tables with read lock"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; +unlock tables; + + +create procedure proc_1() flush tables; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush tables; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush tables"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() flush tables; +flush tables; +--sorted_result +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +--sorted_result +show open tables from mysql; +call proc_1(); +--sorted_result +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +--sorted_result +show open tables from mysql; +call proc_1(); +--sorted_result +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +--sorted_result +show open tables from mysql; +call proc_1(); +--sorted_result +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +--sorted_result +show open tables from mysql; +flush tables; +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush tables; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; + +# make the output deterministic: +# the order used in SHOW OPEN TABLES +# is too much implementation dependent +--disable_ps_protocol +flush tables; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +--sorted_result +show open tables from mysql; +--enable_ps_protocol + +prepare abc from "flush tables"; +execute abc; +--sorted_result +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +--sorted_result +show open tables from mysql; +execute abc; +--sorted_result +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +--sorted_result +show open tables from mysql; +execute abc; +--sorted_result +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +--sorted_result +show open tables from mysql; +flush tables; +deallocate prepare abc; + + +create procedure proc_1() flush logs; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush logs; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush logs"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() flush status; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush status; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush status"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() flush slave; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush slave; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush slave"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() flush master; +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush master; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush master"; +deallocate prepare abc; + + +create procedure proc_1() flush des_key_file; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush des_key_file; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush des_key_file"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() flush user_resources; +call proc_1(); +call proc_1(); +call proc_1(); +delimiter |; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin flush user_resources; return 1; end| +create function func_1() returns int begin call proc_1(); return 1; end| +delimiter ;| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select func_1(), func_1(), func_1() from dual; +drop function func_1; +drop procedure proc_1; +prepare abc from "flush user_resources"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() start slave; +drop procedure proc_1; +delimiter |; +create function func_1() returns int begin start slave; return 1; end| +delimiter ;| +drop function func_1; +prepare abc from "start slave"; +deallocate prepare abc; + + +create procedure proc_1() stop slave; +drop procedure proc_1; +delimiter |; +create function func_1() returns int begin stop slave; return 1; end| +delimiter ;| +drop function func_1; +prepare abc from "stop slave"; +deallocate prepare abc; + + +create procedure proc_1() show binlog events; +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show binlog events; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show binlog events"; +deallocate prepare abc; + + +create procedure proc_1() show slave status; +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show slave status; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show slave status"; +deallocate prepare abc; + + +create procedure proc_1() show master status; +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show master status; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show master status"; +deallocate prepare abc; + + +create procedure proc_1() show master logs; +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show master logs; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show master logs"; +deallocate prepare abc; + + +create procedure proc_1() show events; +call proc_1(); +call proc_1(); +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show events; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show events"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +--disable_warnings +drop procedure if exists a; +--enable_warnings +create procedure a() select 42; +create procedure proc_1(a char(2)) show create procedure a; +call proc_1("bb"); +call proc_1("bb"); +call proc_1("bb"); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show create procedure a; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show create procedure a"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; +drop procedure a; + + +--disable_warnings +drop function if exists a; +--enable_warnings +create function a() returns int return 42+13; +create procedure proc_1(a char(2)) show create function a; +call proc_1("bb"); +call proc_1("bb"); +call proc_1("bb"); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show create function a; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show create function a"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; +drop function a; + + +--disable_warnings +drop table if exists tab1; +--enable_warnings +create table tab1(a int, b char(1), primary key(a,b)); +create procedure proc_1() show create table tab1; +call proc_1(); +call proc_1(); +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show create table tab1; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show create table tab1"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; +drop table tab1; + + +--disable_warnings +drop view if exists v1; +drop table if exists t1; +--enable_warnings +create table t1(a int, b char(5)); +insert into t1 values (1, "one"), (1, "edno"), (2, "two"), (2, "dve"); +create view v1 as + (select a, count(*) from t1 group by a) + union all + (select b, count(*) from t1 group by b); +create procedure proc_1() show create view v1; +call proc_1(); +call proc_1(); +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show create view v1; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "show create view v1"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; +drop view v1; +drop table t1; + + +create procedure proc_1() install plugin my_plug soname 'some_plugin.so'; +--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED +call proc_1(); +--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED +call proc_1(); +--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin install plugin my_plug soname '/tmp/plugin'; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "install plugin my_plug soname 'some_plugin.so'"; +deallocate prepare abc; + + +create procedure proc_1() uninstall plugin my_plug; +--error ER_SP_DOES_NOT_EXIST +call proc_1(); +--error ER_SP_DOES_NOT_EXIST +call proc_1(); +--error ER_SP_DOES_NOT_EXIST +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin uninstall plugin my_plug; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "uninstall plugin my_plug"; +--error ER_SP_DOES_NOT_EXIST +execute abc; +--error ER_SP_DOES_NOT_EXIST +execute abc; +--error ER_SP_DOES_NOT_EXIST +execute abc; +deallocate prepare abc; + + +--disable_warnings +drop database if exists mysqltest_xyz; +--enable_warnings +create procedure proc_1() create database mysqltest_xyz; +call proc_1(); +drop database if exists mysqltest_xyz; +call proc_1(); +--error ER_DB_CREATE_EXISTS +call proc_1(); +drop database if exists mysqltest_xyz; +call proc_1(); +drop database if exists mysqltest_xyz; +drop procedure proc_1; +delimiter |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin create database mysqltest_xyz; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "create database mysqltest_xyz"; +execute abc; +drop database if exists mysqltest_xyz; +execute abc; +--error ER_DB_CREATE_EXISTS +execute abc; +drop database if exists mysqltest_xyz; +execute abc; +drop database if exists mysqltest_xyz; +deallocate prepare abc; + + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, b char(5)); +insert into t1 values (1, "one"), (2, "two"), (3, "three"); +create procedure proc_1() checksum table xyz; +call proc_1(); +call proc_1(); +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin checksum table t1; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "checksum table t1"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; + + +create procedure proc_1() create user pstest_xyz@localhost; +call proc_1(); +drop user pstest_xyz@localhost; +call proc_1(); +--error ER_CANNOT_USER +call proc_1(); +drop user pstest_xyz@localhost; +call proc_1(); +drop user pstest_xyz@localhost; +drop procedure proc_1; +delimiter |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin create user pstest_xyz@localhost; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +prepare abc from "create user pstest_xyz@localhost"; +execute abc; +drop user pstest_xyz@localhost; +execute abc; +--error ER_CANNOT_USER +execute abc; +drop user pstest_xyz@localhost; +execute abc; +drop user pstest_xyz@localhost; +deallocate prepare abc; + + +--disable_warnings +drop event if exists xyz; +--enable_warnings +#create procedure proc_1() create event xyz on schedule every 5 minute disable do select 123; +#call proc_1(); +#drop event xyz; +#call proc_1(); +#--error ER_EVENT_ALREADY_EXISTS +#call proc_1(); +#drop event xyz; +#call proc_1(); +#drop event xyz; +#drop procedure proc_1; +delimiter |; +--error ER_EVENT_RECURSION_FORBIDDEN +create function func_1() returns int begin create event xyz on schedule at now() do select 123; return 1; end| +delimiter ;| +--error ER_SP_DOES_NOT_EXIST +select func_1(), func_1(), func_1() from dual; +--error ER_SP_DOES_NOT_EXIST +drop function func_1; +--error ER_UNSUPPORTED_PS +prepare abc from "create event xyz on schedule at now() do select 123"; +--error ER_UNKNOWN_STMT_HANDLER +deallocate prepare abc; + + +--disable_warnings +drop event if exists xyz; +create event xyz on schedule every 5 minute disable do select 123; +--enable_warnings +create procedure proc_1() alter event xyz comment 'xyz'; +call proc_1(); +drop event xyz; +create event xyz on schedule every 5 minute disable do select 123; +call proc_1(); +drop event xyz; +create event xyz on schedule every 5 minute disable do select 123; +call proc_1(); +drop event xyz; +drop procedure proc_1; +delimiter |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin alter event xyz comment 'xyz'; return 1; end| +delimiter ;| +--error ER_UNSUPPORTED_PS +prepare abc from "alter event xyz comment 'xyz'"; +--error ER_UNKNOWN_STMT_HANDLER +deallocate prepare abc; + + +--disable_warnings +drop event if exists xyz; +create event xyz on schedule every 5 minute disable do select 123; +--enable_warnings +create procedure proc_1() drop event xyz; +call proc_1(); +create event xyz on schedule every 5 minute disable do select 123; +call proc_1(); +--error ER_EVENT_DOES_NOT_EXIST +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create function func_1() returns int begin drop event xyz; return 1; end| +delimiter ;| +--error ER_UNSUPPORTED_PS +prepare abc from "drop event xyz"; +--error ER_UNKNOWN_STMT_HANDLER +deallocate prepare abc; + + +--disable_warnings +drop table if exists t1; +create table t1 (a int, b char(5)) engine=myisam; +insert into t1 values (1, "one"), (2, "two"), (3, "three"); +--enable_warnings +SET GLOBAL new_cache.key_buffer_size=128*1024; +create procedure proc_1() cache index t1 in new_cache; +call proc_1(); +call proc_1(); +call proc_1(); +drop procedure proc_1; +SET GLOBAL second_cache.key_buffer_size=128*1024; +prepare abc from "cache index t1 in second_cache"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; +drop table t1; + +--disable_warnings +drop table if exists t1; +drop table if exists t2; +create table t1 (a int, b char(5)) engine=myisam; +insert into t1 values (1, "one"), (2, "two"), (3, "three"); +create table t2 (a int, b char(5)) engine=myisam; +insert into t2 values (1, "one"), (2, "two"), (3, "three"); +--enable_warnings +create procedure proc_1() load index into cache t1 ignore leaves; +call proc_1(); +call proc_1(); +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin load index into cache t1 ignore leaves; return 1; end| +delimiter ;| +prepare abc from "load index into cache t2 ignore leaves"; +execute abc; +execute abc; +execute abc; +deallocate prepare abc; +drop table t1, t2; + +# +# Bug #21422: GRANT/REVOKE possible inside stored function, probably in a trigger +# This is disabled for now till it is resolved in 5.0 +# + +#create procedure proc_1() grant all on *.* to abc@host; +#drop procedure proc_1; +#delimiter |; +#--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +#create function func_1() returns int begin grant all on *.* to abc@host; return 1; end| +#delimiter ;| +#prepare abc from "grant all on *.* to abc@host"; +# +#create procedure proc_1() revoke all on *.* from abc@host; +#drop procedure proc_1; +#delimiter |;#--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +#create function func_1() returns int begin revoke all on *.* from abc@host; return 1; end| +#delimiter ;| +#prepare abc from "revoke all on *.* from abc@host"; + +create procedure proc_1() show errors; +call proc_1(); +call proc_1(); +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show errors; return 1; end| +delimiter ;| +prepare abc from "show errors"; +deallocate prepare abc; + +--disable_warnings +drop table if exists t1; +drop table if exists t2; +--enable_warnings +create procedure proc_1() show warnings; +drop table if exists t1; +call proc_1(); +drop table if exists t2; +call proc_1(); +drop table if exists t1, t2; +call proc_1(); +drop procedure proc_1; +delimiter |; +--error ER_SP_NO_RETSET +create function func_1() returns int begin show warnings; return 1; end| +delimiter ;| +prepare abc from "show warnings"; +drop table if exists t1; +execute abc; +drop table if exists t2; +execute abc; +drop table if exists t1, t2; +execute abc; +deallocate prepare abc; + +# +# Bug#22684: The Functions ENCODE, DECODE and FORMAT are not real functions +# + +set @my_password="password"; +set @my_data="clear text to encode"; + +prepare stmt1 from 'select decode(encode(?, ?), ?)'; +execute stmt1 using @my_data, @my_password, @my_password; +set @my_data="more text to encode"; +execute stmt1 using @my_data, @my_password, @my_password; +set @my_password="new password"; +execute stmt1 using @my_data, @my_password, @my_password; +deallocate prepare stmt1; + +set @to_format="123456789.123456789"; +set @dec=0; + +prepare stmt2 from 'select format(?, ?)'; +execute stmt2 using @to_format, @dec; +set @dec=4; +execute stmt2 using @to_format, @dec; +set @dec=6; +execute stmt2 using @to_format, @dec; +set @dec=2; +execute stmt2 using @to_format, @dec; +set @to_format="100"; +execute stmt2 using @to_format, @dec; +set @to_format="1000000"; +execute stmt2 using @to_format, @dec; +set @to_format="10000"; +execute stmt2 using @to_format, @dec; +deallocate prepare stmt2; + + +# +# BUG#18326: Do not lock table for writing during prepare of statement +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (i INT); +INSERT INTO t2 VALUES (2); + +LOCK TABLE t1 READ, t2 WRITE; + +connect (conn1, localhost, root, , ); + +# Prepare never acquires the lock, and thus should not block. +PREPARE stmt1 FROM "SELECT i FROM t1"; +PREPARE stmt2 FROM "INSERT INTO t2 (i) VALUES (3)"; + +# This should not block because READ lock on t1 is shared. +EXECUTE stmt1; + +# This should block because WRITE lock on t2 is exclusive. +send EXECUTE stmt2; + +connection default; + +SELECT * FROM t2; +UNLOCK TABLES; +let $wait_condition= SELECT COUNT(*) = 2 FROM t2; +--source include/wait_condition.inc +SELECT * FROM t2; + +# DDL and DML works even if some client have a prepared statement +# referencing the table. +ALTER TABLE t1 ADD COLUMN j INT; +ALTER TABLE t2 ADD COLUMN j INT; +INSERT INTO t1 VALUES (4, 5); +INSERT INTO t2 VALUES (4, 5); + +connection conn1; + +reap; +EXECUTE stmt1; +EXECUTE stmt2; +SELECT * FROM t2; + +disconnect conn1; + +connection default; + +DROP TABLE t1, t2; + +# +# Bug #24879 Prepared Statements: CREATE TABLE (UTF8 KEY) produces a growing +# key length +# +# Test that parse information is not altered by subsequent executions of a +# prepared statement +# +drop table if exists t1; +prepare stmt +from "create table t1 (c char(100) character set utf8, key (c(10)))"; +execute stmt; +show create table t1; +drop table t1; +execute stmt; +show create table t1; +drop table t1; + +# +# Bug #32030 DELETE does not return an error and deletes rows if error +# evaluating WHERE +# +# Test that there is an error for prepared delete just like for the normal +# one. +# +--disable_warnings +drop table if exists t1, t2; +--enable_warnings +create table t1 (a int, b int); +create table t2 like t1; + +insert into t1 (a, b) values (1,1), (1,2), (1,3), (1,4), (1,5), + (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); + +insert into t2 select a, max(b) from t1 group by a; + +prepare stmt from "delete from t2 where (select (select max(b) from t1 group +by a having a < 2) x from t1) > 10000"; + +--error ER_SUBQUERY_NO_1_ROW +delete from t2 where (select (select max(b) from t1 group +by a having a < 2) x from t1) > 10000; +--error ER_SUBQUERY_NO_1_ROW +execute stmt; +--error ER_SUBQUERY_NO_1_ROW +execute stmt; + +deallocate prepare stmt; +drop table t1, t2; + +--echo # +--echo # Bug#27430 Crash in subquery code when in PS and table DDL changed +--echo # after PREPARE +--echo # +--echo # This part of the test doesn't work in embedded server, this is +--echo # why it's here. For the main test see ps_ddl*.test +--echo +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int); +prepare stmt from "show events where (1) in (select * from t1)"; +execute stmt; +drop table t1; +create table t1 (x int); +execute stmt; +drop table t1; +deallocate prepare stmt; + +--echo # +--echo # Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0 +--echo # + +prepare encode from "select encode(?, ?) into @ciphertext"; +prepare decode from "select decode(?, ?) into @plaintext"; +set @str="abc", @key="cba"; +execute encode using @str, @key; +execute decode using @ciphertext, @key; +select @plaintext; +set @str="bcd", @key="dcb"; +execute encode using @str, @key; +execute decode using @ciphertext, @key; +select @plaintext; +deallocate prepare encode; +deallocate prepare decode; + +--echo # +--echo # Bug#52124 memory leaks like a sieve in datetime, timestamp, time, date fields + warnings +--echo # +CREATE TABLE t1 (a DATETIME NOT NULL, b TINYINT); +INSERT INTO t1 VALUES (0, 0),(0, 0); +PREPARE stmt FROM "SELECT 1 FROM t1 WHERE +ROW(a, b) >= ROW('1', (SELECT 1 FROM t1 WHERE a > 1234))"; +--disable_warnings +EXECUTE stmt; +EXECUTE stmt; +--enable_warnings +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + +########################################################################### + +--echo # +--echo # Bug#54494 crash with explain extended and prepared statements +--echo # +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1),(2); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; +PREPARE stmt FROM 'EXPLAIN EXTENDED SELECT 1 FROM t1 RIGHT JOIN t1 t2 ON 1'; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +SET optimizer_switch=@save_optimizer_switch; +DROP TABLE t1; + +--echo # +--echo # Bug#54488 crash when using explain and prepared statements with subqueries +--echo # +CREATE TABLE t1(f1 INT); +INSERT INTO t1 VALUES (1),(1); +PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1))'; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + +--echo +--echo End of 5.1 tests. + +--echo # +--echo # lp:1001500 Crash on the second execution of the PS for +--echo # a query with degenerated conjunctive condition +--echo # (see also mysql bug#12582849) +--echo # + +CREATE TABLE t1 ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER, + col_int_key INTEGER, + + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + + PRIMARY KEY (pk), + KEY (col_int_key), + KEY (col_varchar_key, col_int_key) +); + +INSERT INTO t1 ( + col_int_key, col_int_nokey, + col_varchar_key, col_varchar_nokey +) VALUES +(4, 2, 'v', 'v'), +(62, 150, 'v', 'v'); + +CREATE TABLE t2 ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER, + col_int_key INTEGER, + + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + + PRIMARY KEY (pk), + KEY (col_int_key), + KEY (col_varchar_key, col_int_key) +); + +INSERT INTO t2 ( + col_int_key, col_int_nokey, + col_varchar_key, col_varchar_nokey +) VALUES +(8, NULL, 'x', 'x'), +(7, 8, 'd', 'd'); + +PREPARE stmt FROM ' +SELECT + ( SELECT MAX( SQ1_alias2 .col_int_nokey ) AS SQ1_field1 + FROM ( t2 AS SQ1_alias1 RIGHT JOIN t1 AS SQ1_alias2 + ON ( SQ1_alias2.col_varchar_key = SQ1_alias1.col_varchar_nokey ) + ) + WHERE SQ1_alias2.pk < alias1.col_int_nokey OR alias1.pk + ) AS field1 +FROM ( t1 AS alias1 JOIN t2 AS alias2 ON alias2.pk ) +GROUP BY field1 +'; + +EXECUTE stmt; +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; + +DROP TABLE t1, t2; + +########################################################################### + +--echo +--echo # +--echo # WL#4435: Support OUT-parameters in prepared statements. +--echo # +--echo + +# The idea of this test case is to check that +# - OUT-parameters of four allowed types (string, double, int, decimal) work +# properly; +# - INOUT and OUT parameters work properly; +# - A mix of IN and OUT parameters work properly; + +--disable_warnings +DROP PROCEDURE IF EXISTS p_string; +DROP PROCEDURE IF EXISTS p_double; +DROP PROCEDURE IF EXISTS p_int; +DROP PROCEDURE IF EXISTS p_decimal; +--enable_warnings + +delimiter |; + +--echo +CREATE PROCEDURE p_string( + IN v0 INT, + OUT v1 CHAR(32), + IN v2 CHAR(32), + INOUT v3 CHAR(32)) +BEGIN + SET v0 = -1; + SET v1 = 'test_v1'; + SET v2 = 'n/a'; + SET v3 = 'test_v3'; +END| + +--echo +CREATE PROCEDURE p_double( + IN v0 INT, + OUT v1 DOUBLE(4, 2), + IN v2 DOUBLE(4, 2), + INOUT v3 DOUBLE(4, 2)) +BEGIN + SET v0 = -1; + SET v1 = 12.34; + SET v2 = 98.67; + SET v3 = 56.78; +END| + +--echo +CREATE PROCEDURE p_int( + IN v0 CHAR(10), + OUT v1 INT, + IN v2 INT, + INOUT v3 INT) +BEGIN + SET v0 = 'n/a'; + SET v1 = 1234; + SET v2 = 9876; + SET v3 = 5678; +END| + +--echo +CREATE PROCEDURE p_decimal( + IN v0 INT, + OUT v1 DECIMAL(4, 2), + IN v2 DECIMAL(4, 2), + INOUT v3 DECIMAL(4, 2)) +BEGIN + SET v0 = -1; + SET v1 = 12.34; + SET v2 = 98.67; + SET v3 = 56.78; +END| + +delimiter ;| + +--echo +PREPARE stmt_str FROM 'CALL p_string(?, ?, ?, ?)'; +PREPARE stmt_dbl FROM 'CALL p_double(?, ?, ?, ?)'; +PREPARE stmt_int FROM 'CALL p_int(?, ?, ?, ?)'; +PREPARE stmt_dec FROM 'CALL p_decimal(?, ?, ?, ?)'; + +--echo +SET @x_str_1 = NULL; +SET @x_str_2 = NULL; +SET @x_str_3 = NULL; +SET @x_dbl_1 = NULL; +SET @x_dbl_2 = NULL; +SET @x_dbl_3 = NULL; +SET @x_int_1 = NULL; +SET @x_int_2 = NULL; +SET @x_int_3 = NULL; +SET @x_dec_1 = NULL; +SET @x_dec_2 = NULL; +SET @x_dec_3 = NULL; + +--echo +--echo -- Testing strings... + +--echo +EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3; +SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3; + +--echo +EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3; +SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3; + +--echo +--echo -- Testing doubles... + +--echo +EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; +SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; + +--echo +EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; +SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; + +--echo +--echo -- Testing ints... + +--echo +EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3; +SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3; + +--echo +EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3; +SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3; + +--echo +--echo -- Testing decs... + +--echo +EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; +SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; + +--echo +EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; +SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; + +--echo +DEALLOCATE PREPARE stmt_str; +DEALLOCATE PREPARE stmt_dbl; +DEALLOCATE PREPARE stmt_int; +DEALLOCATE PREPARE stmt_dec; + +--echo +DROP PROCEDURE p_string; +DROP PROCEDURE p_double; +DROP PROCEDURE p_int; +DROP PROCEDURE p_decimal; + +# +# Another test case for WL#4435: check out parameters in Dynamic SQL. +# + +--echo +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +--echo + +CREATE PROCEDURE p1(OUT v1 CHAR(10)) + SET v1 = 'test1'; + +--echo + +delimiter |; +CREATE PROCEDURE p2(OUT v2 CHAR(10)) +BEGIN + SET @query = 'CALL p1(?)'; + PREPARE stmt1 FROM @query; + EXECUTE stmt1 USING @u1; + DEALLOCATE PREPARE stmt1; + + SET v2 = @u1; +END| +delimiter ;| + +--echo + +CALL p2(@a); +SELECT @a; + +--echo + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +########################################################################### + +--source wl4435_generated.inc + +########################################################################### + +--echo +--echo # End of WL#4435. + +########################################################################### + + +--echo # +--echo # WL#4284: Transactional DDL locking +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 (a INT); +BEGIN; +SELECT * FROM t1; +--echo # Test that preparing a CREATE TABLE does not take a exclusive metdata lock. +PREPARE stmt1 FROM "CREATE TABLE t1 AS SELECT 1"; +--error ER_TABLE_EXISTS_ERROR +EXECUTE stmt1; +DEALLOCATE PREPARE stmt1; +DROP TABLE t1; + +--echo # +--echo # WL#4284: Transactional DDL locking +--echo # +--echo # Test that metadata locks taken during prepare are released. +--echo # + +connect(con1,localhost,root,,); +connection default; +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 (a INT); +connection con1; +BEGIN; +PREPARE stmt1 FROM "SELECT * FROM t1"; +connection default; +DROP TABLE t1; +disconnect con1; + +--echo +--echo # +--echo # Bug#56115: invalid memory reads when PS selecting from +--echo # information_schema tables +--echo # Bug#58701: crash in Field::make_field, cursor-protocol +--echo # +--echo # NOTE: MTR should be run both with --ps-protocol and --cursor-protocol. +--echo # +--echo + +SELECT * +FROM (SELECT 1 UNION SELECT 2) t; + +--echo +--echo # Bug#13805127: Stored program cache produces wrong result in same THD +--echo + +PREPARE s1 FROM +" +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2 +"; + +--echo +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +--echo +SET @x = 2; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +--echo +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +DEALLOCATE PREPARE s1; + +########################################################################### +# +# restoring of the Item tree in BETWEEN with dates +# +prepare stmt from "select date('2010-10-10') between '2010-09-09' and ?"; +set @a='2010-11-11'; +execute stmt using @a; +execute stmt using @a; +set @a='2010-08-08'; +execute stmt using @a; +execute stmt using @a; + +--echo # +--echo # Bug #892725: look-up is changed for a full scan when executing PS +--echo # + +create table t1 (a int primary key, b int); +insert into t1 values + (7,70), (3,40), (4,40), (8,70), (1,70), (9,50), (2,70); + +prepare st from 'select * from t1 where a=8'; + +flush status; +execute st; +show status like '%Handler_read%'; +flush status; +execute st; +show status like '%Handler_read%'; +flush status; +select * from t1 use index() where a=3; +show status like '%Handler_read%'; +flush status; +execute st; +show status like '%Handler_read%'; + +deallocate prepare st; +drop table t1; + +--echo # +--echo # Bug mdev-5410: crash at the execution of PS with subselect +--echo # formed by UNION with global ORDER BY +--echo # + +CREATE TABLE t1 (a int DEFAULT NULL); +INSERT INTO t1 VALUES (2), (4); +CREATE TABLE t2 (b int DEFAULT NULL); +INSERT INTO t2 VALUES (1), (3); + +PREPARE stmt FROM " +SELECT c1 FROM (SELECT (SELECT a FROM t1 WHERE t1.a <= t2.b + UNION ALL + SELECT a FROM t1 WHERE t1.a+3<= t2.b + ORDER BY a DESC) AS c1 FROM t2) t3; +"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1,t2; + + +--echo # +--echo # MDEV-5369: Wrong result (0 instead of NULL) on 2nd execution of +--echo # PS with LEFT JOIN, TEMPTABLE view +--echo # + + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0),(8); + +CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk; + +PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +DROP VIEW v2; +DROP TABLE t1, t2; + +--echo # End of 5.3 tests + +--echo # +--echo # MDEV-5505: Assertion `! is_set()' fails on PREPARE SELECT +--echo # with out of range in GROUP BY +--echo # +CREATE TABLE t1 (a INT); + +--error ER_DATA_OUT_OF_RANGE +PREPARE stmt FROM "SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1"; +--error ER_DATA_OUT_OF_RANGE +SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1; + +drop table t1; + +--echo # End of 5.3 tests + +--echo # +--echo # MDEV-8756: MariaDB 10.0.21 crashes during PREPARE +--echo # + +CREATE TABLE t1 ( id INT(10), value INT(10) ); +CREATE TABLE t2 ( id INT(10) ); +SET @save_sql_mode= @@sql_mode; +SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'; + +PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)'; +execute stmt; +insert into t1 values (1,10),(2,10),(3,10); +insert into t2 values (1),(2); +execute stmt; +select * from t1; +deallocate prepare stmt; +SET SESSION sql_mode = @save_sql_mode; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-8833: Crash of server on prepared statement with +--echo # conversion to semi-join +--echo # + +CREATE TABLE t1 (column1 INT); +INSERT INTO t1 VALUES (3),(9); + +CREATE TABLE t2 (column2 INT); +INSERT INTO t2 VALUES (1),(4); + +CREATE TABLE t3 (column3 INT); +INSERT INTO t3 VALUES (6),(8); + +CREATE TABLE t4 (column4 INT); +INSERT INTO t4 VALUES (2),(5); + +PREPARE stmt FROM "SELECT ( SELECT MAX( table1.column1 ) AS field1 +FROM t1 AS table1 +WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4"; +EXECUTE stmt; +EXECUTE stmt; +deallocate prepare stmt; +drop table t1,t2,t3,t4; + +--echo # +--echo # MDEV-11859: the plans for the first and the second executions +--echo # of PS are not the same +--echo # + +create table t1 (id int, c varchar(3), key idx(c))engine=myisam; +insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy'); + +prepare stmt1 from +"explain extended + select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from +"select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; +flush status; +execute stmt1; +show status like '%Handler_read%'; +flush status; +execute stmt1; +show status like '%Handler_read%'; +deallocate prepare stmt1; + +prepare stmt2 from +"explain extended + select * from t1 where (1, 2) in ( select 3, 4 )"; +execute stmt2; +execute stmt2; +deallocate prepare stmt2; + +drop table t1; + +--echo # +--echo # MDEV-9208: Function->Function->View = Mysqld segfault +--echo # (Server crashes in Dependency_marker::visit_field on 2nd +--echo # execution with merged subquery) +--echo # + +CREATE TABLE t1 (i1 INT); +insert into t1 values(1),(2); + +CREATE TABLE t2 (i2 INT); +insert into t2 values(1),(2); + +prepare stmt from " + select 1 from ( + select + if (i1<0, 0, 0) as f1, + (select f1) as f2 + from t1, t2 + ) sq +"; + +execute stmt; +execute stmt; + +drop table t1,t2; + +--echo # +--echo # MDEV-9619: Assertion `null_ref_table' failed in virtual +--echo # table_map Item_direct_view_ref::used_tables() const on 2nd +--echo # execution of PS +--echo # + +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES ('a'),('b'); + +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'),('d'); + +PREPARE stmt FROM "SELECT * FROM v1 WHERE f1 = SOME ( SELECT f2 FROM t2 )"; +EXECUTE stmt; +EXECUTE stmt; +insert into t1 values ('c'); +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; +drop view v1; +drop table t1,t2; + +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES ('a'),('b'); + +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'),('d'); + +PREPARE stmt FROM "SELECT * FROM v1 WHERE (f1,f1) = SOME ( SELECT f2,f2 FROM t2 )"; +EXECUTE stmt; +EXECUTE stmt; +insert into t1 values ('c'); +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; +drop view v1; +drop table t1,t2; + + + +CREATE TABLE t1 (column1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(9); + +CREATE TABLE t2 (column2 INT) ENGINE=MyISAM; + +INSERT INTO t2 VALUES (1),(4); + +CREATE TABLE t3 (column3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6),(8); + +CREATE TABLE t4 (column4 INT) ENGINE=MyISAM; +INSERT INTO t4 VALUES (2),(5); + +PREPARE stmt FROM " +SELECT ( + SELECT MAX( table1.column1 ) AS field1 + FROM t1 AS table1 + WHERE (111,table3.column3) IN ( SELECT 111,table2.column2 AS field2 FROM t2 AS table2 ) +) AS sq +FROM t3 AS table3, t4 AS table4 GROUP BY sq +"; + +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; +drop table t1,t2,t3,t4; + +create table t1 (a int, b int, c int); +create table t2 (x int, y int, z int); +create table t3 as select * from t1; +insert into t1 values (1,2,3),(4,5,6),(100,200,300),(400,500,600); +insert into t2 values (1,2,3),(7,8,9),(100,200,300),(400,500,600); +insert into t3 values (1,2,3),(11,12,13),(100,0,0),(400,500,600); + + +set @optimizer_switch_save=@@optimizer_switch; +set @join_cache_level_save=@@join_cache_level; +set optimizer_switch='materialization=off'; +set join_cache_level=0; +select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z); +prepare stmt from "select * from t1 where (select a,b from t3 where t3.c=t1.c) in (select x,y from t2 where t1.c= t2.z)"; +EXECUTE stmt; +EXECUTE stmt; + +create view v1 as select * from t1; +create view v2 as select * from t2; +create view v3 as select * from t3; +select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z); +prepare stmt from "select * from v1 where (select a,b from v3 where v3.c=v1.c) in (select x,y from v2 where v1.c= v2.z)"; +EXECUTE stmt; +EXECUTE stmt; +set optimizer_switch=@optimizer_switch_save; +set join_cache_level=@join_cache_level_save; + +deallocate prepare stmt; +drop view v1,v2,v3; +drop table t1,t2,t3; + +--echo # +--echo # MDEV-10657: incorrect result returned with binary protocol +--echo # (prepared statements) +--echo # + +create table t1 (code varchar(10) primary key); +INSERT INTO t1(code) VALUES ('LINE1'), ('LINE2'), ('LINE3'); +SELECT X.* +FROM + (SELECT CODE, RN + FROM + (SELECT A.CODE, @cnt := @cnt + 1 AS RN + FROM t1 A, (SELECT @cnt := 0) C) T + ) X; +drop table t1; +--echo # End of 5.5 tests + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-10709 Expressions as parameters to Dynamic SQL +--echo # + +--echo # +--echo # Using a simple expressions as an EXECUTE parameter +--echo # + +PREPARE stmt FROM 'SELECT ? FROM DUAL'; +EXECUTE stmt USING 10; +DEALLOCATE PREPARE stmt; + +PREPARE stmt FROM 'SELECT ? FROM DUAL'; +EXECUTE stmt USING TO_BASE64('xxx'); +DEALLOCATE PREPARE stmt; + +PREPARE stmt FROM 'SELECT ?+? FROM DUAL'; +EXECUTE stmt USING 10, 10 + 10; +DEALLOCATE PREPARE stmt; + +PREPARE stmt FROM 'SELECT CONCAT(?,?) FROM DUAL'; +EXECUTE stmt USING 'xxx', CONCAT('yyy','zzz'); +DEALLOCATE PREPARE stmt; + +--echo # +--echo # Testing disallowed expressions in USING +--echo # + +PREPARE stmt FROM 'SELECT ? FROM DUAL'; +--error ER_PARSE_ERROR +EXECUTE stmt USING (SELECT 1); +DEALLOCATE PREPARE stmt; + +CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; +PREPARE stmt FROM 'SELECT ? FROM DUAL'; +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE stmt USING f1(); +DEALLOCATE PREPARE stmt; +DROP FUNCTION f1; + +--echo # +--echo # Testing erroneous expressions in USING +--echo # + +PREPARE stmt FROM 'SELECT ?'; +--error ER_CANT_AGGREGATE_2COLLATIONS +EXECUTE stmt USING _latin1'a'=_latin2'a'; +DEALLOCATE PREPARE stmt; + +PREPARE stmt FROM 'SELECT ?'; +--error ER_OPERAND_COLUMNS +EXECUTE stmt USING ROW(1,2); +DEALLOCATE PREPARE stmt; + +--echo # +--echo # Creating tables from EXECUTE parameters +--echo # + +PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ? AS c1 FROM DUAL'; +EXECUTE stmt USING 10; +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING 10.123; +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING 10.123e0; +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING CURRENT_DATE; +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING CURRENT_TIMESTAMP; +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING CURRENT_TIMESTAMP(3); +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING CURRENT_TIMESTAMP(6); +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING CURRENT_TIME; +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING CURRENT_TIME(3); +SHOW CREATE TABLE t1; +DROP TABLE t1; +EXECUTE stmt USING CURRENT_TIME(6); +SHOW CREATE TABLE t1; +DROP TABLE t1; +DEALLOCATE PREPARE stmt; + + +--echo # +--echo # Using a user variable as an EXECUTE..USING out parameter +--echo # + +DELIMITER /; +CREATE PROCEDURE p1(OUT a INT) +BEGIN + SET a:= 10; +END; +/ +DELIMITER ;/ +SET @a=1; +CALL p1(@a); +SELECT @a; +SET @a=2; +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @a; +SELECT @a; +DROP PROCEDURE p1; + + +--echo # +--echo # Using an SP variable as an EXECUTE..USING out parameter +--echo # + +DELIMITER /; +CREATE PROCEDURE p1 (OUT a INT) +BEGIN + SET a=10; +END; +/ +CREATE PROCEDURE p2 (OUT a INT) +BEGIN + PREPARE stmt FROM 'CALL p1(?)'; + EXECUTE stmt USING a; +END; +/ +DELIMITER ;/ +SET @a= 1; +CALL p2(@a); +SELECT @a; +DROP PROCEDURE p2; +DROP PROCEDURE p1; + + +--echo # +--echo # Testing re-prepare on a table metadata update between PREPARE and EXECUTE +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER /; +CREATE PROCEDURE p1(a INT) +BEGIN + INSERT INTO t1 VALUES (a); +END; +/ +DELIMITER ;/ +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING 10; +SELECT * FROM t1; +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a+1; +EXECUTE stmt USING 20; +SELECT * FROM t1; +DEALLOCATE PREPARE stmt; +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # End of MDEV-10709 Expressions as parameters to Dynamic SQL +--echo # + +--echo # +--echo # MDEV-10585 EXECUTE IMMEDIATE statement +--echo # + +EXECUTE IMMEDIATE 'SELECT 1 AS a'; +SET @a=10; +EXECUTE IMMEDIATE 'SELECT ? AS a' USING @a; +EXECUTE IMMEDIATE 'SELECT ? AS a' USING 20; + + +--echo # +--echo # Erroneous queries +--echo # + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE 'xxx'; + +--error ER_WRONG_ARGUMENTS +EXECUTE IMMEDIATE 'SELECT 1' USING @a; + +--error ER_WRONG_ARGUMENTS +EXECUTE IMMEDIATE 'SELECT ?'; + +--error ER_UNSUPPORTED_PS +EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"'; + +--error ER_UNSUPPORTED_PS +EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"'; + +--error ER_UNSUPPORTED_PS +EXECUTE IMMEDIATE 'EXECUTE stmt'; + +--error ER_UNSUPPORTED_PS +EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt'; + +--error ER_CANT_AGGREGATE_2COLLATIONS +EXECUTE IMMEDIATE 'SELECT ?' USING _latin1'a'=_latin2'a'; + +--error ER_OPERAND_COLUMNS +EXECUTE IMMEDIATE 'SELECT ?' USING ROW(1,2); + +--echo # +--echo # Testing disallowed expressions in USING +--echo # + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING (SELECT 1); + +CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1(); +DROP FUNCTION f1; + +--echo # +--echo # DDL +--echo # + +EXECUTE IMMEDIATE 'CREATE TABLE t1 (a INT)'; +EXECUTE IMMEDIATE 'SHOW CREATE TABLE t1'; +EXECUTE IMMEDIATE 'DROP TABLE t1'; + +SET @stmt= 'CREATE TABLE t1 (a INT)'; +EXECUTE IMMEDIATE @stmt; +SET @stmt= 'SHOW CREATE TABLE t1'; +EXECUTE IMMEDIATE @stmt; +SET @stmt= 'DROP TABLE t1'; +EXECUTE IMMEDIATE @stmt; + + +--echo # +--echo # DDL with parameters +--echo # + +SET @a= 10, @b= 10.1, @c= 10e0, @d='str'; +EXECUTE IMMEDIATE + 'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d' + USING @a,@b,@c,@d; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +EXECUTE IMMEDIATE + 'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d' + USING 10, 10.1, 10e0, 'str'; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +EXECUTE IMMEDIATE + 'CREATE TABLE t1 AS SELECT ? AS t1,? AS t2, ? AS d1,? AS dt1, ? AS dt2' + USING TIME'10:20:30', + TIME'10:20:30.123', + DATE'2001-01-01', + TIMESTAMP'2001-01-01 10:20:30', + TIMESTAMP'2001-01-01 10:20:30.123'; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # Using a user variable as an EXECUTE IMMEDIATE..USING out parameter +--echo # + +DELIMITER /; +CREATE PROCEDURE p1(OUT a INT) +BEGIN + SET a:= 10; +END; +/ +DELIMITER ;/ +SET @a=1; +CALL p1(@a); +SELECT @a; +SET @a=2; +EXECUTE IMMEDIATE 'CALL p1(?)' USING @a; +SELECT @a; +DROP PROCEDURE p1; + + +--echo # +--echo # Using an SP variable as an EXECUTE IMMEDIATE..USING out parameter +--echo # + +DELIMITER /; +CREATE PROCEDURE p1 (OUT a INT) +BEGIN + SET a=10; +END; +/ +CREATE PROCEDURE p2 (OUT a INT) +BEGIN + EXECUTE IMMEDIATE 'CALL p1(?)' USING a; +END; +/ +DELIMITER ;/ +SET @a= 1; +CALL p2(@a); +SELECT @a; +DROP PROCEDURE p2; +DROP PROCEDURE p1; + + +--echo # +--echo # Changing user variables +--echo # + +SET @a=10; +EXECUTE IMMEDIATE 'SET @a=@a+1'; +SELECT @a; + + +--echo # +--echo # SET STATEMENT +--echo # + +SET @@max_sort_length=1024; +EXECUTE IMMEDIATE 'SET STATEMENT max_sort_length=1025 FOR SELECT @@max_sort_length'; +SELECT @@max_sort_length; +SET @@max_sort_length=DEFAULT; + + +--echo # +--echo # Similar to prepared EXECUTE, IMMEDIATE is not allowed in stored functions +--echo # +DELIMITER $$; +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION f1() RETURNS INT +BEGIN + EXECUTE IMMEDIATE 'DO 1'; + RETURN 1; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Status variables +--echo # +CREATE FUNCTION get_status_var(name TEXT) RETURNS INT + RETURN (SELECT CAST(VARIABLE_VALUE AS INT) + FROM INFORMATION_SCHEMA.SESSION_STATUS + WHERE VARIABLE_NAME=name); +DELIMITER $$; +CREATE PROCEDURE test_status_var(name TEXT) +BEGIN + SET @cnt0=get_status_var(name); + EXECUTE IMMEDIATE 'DO 1'; + SET @cnt1=get_status_var(name); + SELECT @cnt1-@cnt0 AS increment; +END; +$$ +DELIMITER ;$$ +--echo # Note, EXECUTE IMMEDIATE does not increment COM_EXECUTE_SQL +--echo # It increments COM_EXECUTE_IMMEDIATE instead. +CALL test_status_var('COM_EXECUTE_SQL'); +CALL test_status_var('COM_EXECUTE_IMMEDIATE'); +CALL test_status_var('COM_STMT_PREPARE'); +CALL test_status_var('COM_STMT_EXECUTE'); +CALL test_status_var('COM_STMT_CLOSE'); + +DROP PROCEDURE test_status_var; +DROP FUNCTION get_status_var; + +--echo # +--echo # End of MDEV-10585 EXECUTE IMMEDIATE statement +--echo # + +--echo # +--echo # MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions +--echo # + +--echo # +--echo # Testing erroneous and diallowed prepare source +--echo # + +--error ER_CANT_AGGREGATE_2COLLATIONS +EXECUTE IMMEDIATE CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL'); +--error ER_CANT_AGGREGATE_2COLLATIONS +PREPARE stmt FROM CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL'); + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE (SELECT 'SELECT 1'); +--error ER_PARSE_ERROR +PREPARE stmt FROM (SELECT 'SELECT 1'); + +--error ER_BAD_FIELD_ERROR +EXECUTE IMMEDIATE a; +--error ER_BAD_FIELD_ERROR +PREPARE stmt FROM a; + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE NULL; +--error ER_PARSE_ERROR +PREPARE stmt FROM NULL; + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE CONCAT(NULL); +--error ER_PARSE_ERROR +PREPARE stmt FROM CONCAT(NULL); + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE ? USING 'SELECT 1'; + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE 10; + +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE TIME'10:20:30'; + +--error ER_OPERAND_COLUMNS +EXECUTE IMMEDIATE ROW('SELECT 1','SELECT 2'); + +--error ER_INVALID_GROUP_FUNC_USE +EXECUTE IMMEDIATE MAX('SELECT 1 AS c'); + +--error ER_BAD_FIELD_ERROR +EXECUTE IMMEDIATE DEFAULT(a); + +--error ER_BAD_FIELD_ERROR +EXECUTE IMMEDIATE VALUE(a); + + +CREATE FUNCTION f1() RETURNS VARCHAR(64) RETURN 't1'; +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE IMMEDIATE f1(); +--error ER_SUBQUERIES_NOT_SUPPORTED +PREPARE stmt FROM f1(); +DROP FUNCTION f1; + +--error ER_SUBQUERIES_NOT_SUPPORTED +EXECUTE IMMEDIATE non_existent(); + + +--echo # +--echo # Testing literals in prepare source +--echo # +EXECUTE IMMEDIATE N'SELECT 1 AS c'; +EXECUTE IMMEDIATE _latin1'SELECT 1 AS c'; +EXECUTE IMMEDIATE 'SELECT ' '1' ' AS c' ' FROM ' 'DUAL'; +EXECUTE IMMEDIATE 0x53454C4543542031 /*This is 'SELECT 1'*/; + +--echo # +--echo # Testing user variables in prepare source +--echo # + +SET @stmt='SELECT 1 AS c FROM DUAL'; +EXECUTE IMMEDIATE @stmt; +PREPARE stmt FROM @stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +SET @table_name='DUAL'; +EXECUTE IMMEDIATE CONCAT('SELECT 1 AS a FROM ', @table_name); +PREPARE stmt FROM CONCAT('SELECT 1 AS a FROM ', @table_name); +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # +--echo # Testing SP parameters and variables in prepare source +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(table_name VARCHAR(64)) +BEGIN + EXECUTE IMMEDIATE CONCAT('SELECT 1 AS c FROM ', table_name); +END; +$$ +DELIMITER ;$$ +CALL p1('DUAL'); +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE table_name VARCHAR(64) DEFAULT 'DUAL'; + EXECUTE IMMEDIATE CONCAT('SELECT 1 AS c FROM ', table_name); +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; + + +--echo # +--echo # Testing complex expressions +--echo # +EXECUTE IMMEDIATE CONVERT('SELECT 1 AS c' USING utf8); +EXECUTE IMMEDIATE CAST('SELECT 1 AS c' AS CHAR); +EXECUTE IMMEDIATE _latin1'SELECT 1 AS c' COLLATE latin1_bin; +EXECUTE IMMEDIATE (((('SELECT 1 AS c')))); +EXECUTE IMMEDIATE CASE WHEN 1>2 THEN 'SELECT 1 AS c' ELSE 'SELECT 2 AS c' END; +EXECUTE IMMEDIATE TRIM('SELECT 1 AS c'); +EXECUTE IMMEDIATE SUBSTRING('SELECT 1 AS c' FROM 1); +EXECUTE IMMEDIATE COALESCE(NULL, 'SELECT 1 AS c'); + +--echo # +--echo # Testing SET STATEMENT and system variables +--echo # +CREATE TABLE t1 (a INT); +SET STATEMENT max_sort_length=1025 FOR EXECUTE IMMEDIATE CONCAT('INSERT INTO t1 VALUES (', @@max_sort_length, ')'); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions +--echo # + +--echo # +--echo # End of 10.2 tests +--echo # + + +--echo # +--echo # MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter +--echo # + +# Correct usage +CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL); +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING DEFAULT, DEFAULT; +SELECT * FROM t1; +UPDATE t1 SET a=20, b=30; +SELECT * FROM t1; +EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING DEFAULT, DEFAULT; +SELECT * FROM t1; +DROP TABLE t1; + +# Incorrect usage in a expression in INSERT..VALUES +CREATE TABLE t1 (a INT DEFAULT 10); +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING DEFAULT, 'test'; +DROP TABLE t1; + +# Incorrect usage in UPDATE..SET +CREATE TABLE t1 (a INT DEFAULT 10); +INSERT INTO t1 VALUES (20); +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING DEFAULT, 'test'; +DROP TABLE t1; + +# Incorrect usage in not an UPDATE/INSERT query at all +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING DEFAULT; + +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT ?+1' USING DEFAULT; +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING DEFAULT,'test'; + +# Incorrect usage in the LIMIT clause +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING DEFAULT; +CREATE TABLE t1 (a INT DEFAULT 10); +INSERT INTO t1 VALUES (1),(2),(3); +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING DEFAULT; +DROP TABLE t1; + +--echo # The output of this query in 'Note' is a syntactically incorrect query. +--echo # But as it's never logged, it's ok. It should be human readable only. +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING DEFAULT; + +# This tests Item_param::eq() for DEFAULT as a bound value +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +--error ER_INVALID_DEFAULT_PARAM +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT; +DROP TABLE t1; + +--echo # +--echo # MDEV-11780 Crash with PREPARE + SP out parameter + literal +--echo # + +DELIMITER $$; +CREATE OR REPLACE PROCEDURE p1(OUT a INT) +BEGIN + SET a=10; +END; +$$ +DELIMITER ;$$ + +PREPARE stmt FROM 'CALL p1(?)'; +--error ER_SP_NOT_VAR_ARG +EXECUTE stmt USING 10; +--error ER_SP_NOT_VAR_ARG +EXECUTE stmt USING DEFAULT; +--error ER_SP_NOT_VAR_ARG +EXECUTE stmt USING IGNORE; +DEALLOCATE PREPARE stmt; + +--error ER_SP_NOT_VAR_ARG +EXECUTE IMMEDIATE 'CALL p1(?)' USING 10; +--error ER_SP_NOT_VAR_ARG +EXECUTE IMMEDIATE 'CALL p1(?)' USING DEFAULT; +--error ER_SP_NOT_VAR_ARG +EXECUTE IMMEDIATE 'CALL p1(?)' USING IGNORE; +DROP PROCEDURE p1; + + +--echo # +--echo # MDEV-14434 Wrong result for CHARSET(CONCAT(?,const)) +--echo # + +SET NAMES utf8; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(5,_latin1'a'))"; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5e0; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIME'10:20:30'; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIMESTAMP'2001-01-01 10:20:30'; + +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5e0; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIME'10:20:30'; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIMESTAMP'2001-01-01 10:20:30'; + +--echo # +--echo # MDEV-14435 Different UNSIGNED flag of out user variable for YEAR parameter for direct vs prepared CALL +--echo # + +CREATE PROCEDURE p1(OUT v INT UNSIGNED) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +CREATE PROCEDURE p1(OUT v YEAR) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +CREATE PROCEDURE p1(OUT v BIT(16)) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # MDEV-14426 Assertion in Diagnostics_area::set_error_status when using a bad datetime with PS and SP +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(OUT a VARCHAR(20)) +BEGIN + SET a=10; +END; +$$ +--error ER_TRUNCATED_WRONG_VALUE +BEGIN NOT ATOMIC + DECLARE a DATETIME; + CALL p1(a); +END; +$$ +--error ER_TRUNCATED_WRONG_VALUE +BEGIN NOT ATOMIC + DECLARE a DATETIME; + EXECUTE IMMEDIATE 'CALL p1(?)' USING a; +END; +$$ +--error ER_TRUNCATED_WRONG_VALUE +BEGIN NOT ATOMIC + DECLARE a DATETIME; + PREPARE stmt FROM 'CALL p1(?)'; + EXECUTE stmt USING a; + DEALLOCATE PREPARE stmt; +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + +--echo # +--echo # MDEV-14454 Binary protocol returns wrong collation ID for SP OUT parameters +--echo # + +CREATE PROCEDURE p1(OUT v CHAR(32) CHARACTER SET utf8) SET v='aaa'; +PREPARE stmt1 FROM 'CALL p1(?)'; +EXECUTE stmt1 USING @a; +CREATE TABLE t1 AS SELECT @a AS c1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler +--echo # + +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1e0; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING '10'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING TIME'10:10:10'; + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a,? AS b' USING 1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 10 AS a,? AS b' USING 10; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 999999999 AS a,? AS b' USING 999999999; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 2147483647 AS a,? AS b' USING 2147483647; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-14603 signal 11 with short stacktrace +--echo # + +SET NAMES utf8; +CREATE TABLE t1(i INT); +CREATE PROCEDURE p1(tn VARCHAR(32)) + EXECUTE IMMEDIATE CONCAT('ANALYZE TABLE ',tn); +CALL p1('t1'); +DROP PROCEDURE p1; +DROP TABLE t1; + +SET NAMES utf8; +CREATE PROCEDURE p1() + EXECUTE IMMEDIATE CONCAT('SELECT ',CONVERT(RAND() USING latin1)); +--disable_result_log +CALL p1(); +--enable_result_log +DROP PROCEDURE p1; + +SET NAMES utf8; +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + PREPARE stmt FROM CONCAT('SELECT ',CONVERT(RAND() USING latin1)); + EXECUTE stmt; + DEALLOCATE PREPARE stmt; +END; +$$ +DELIMITER ;$$ +--disable_result_log +CALL p1(); +--enable_result_log +DROP PROCEDURE p1; + +SET NAMES utf8; +CREATE PROCEDURE p1(a VARCHAR(10) CHARACTER SET utf8) + EXECUTE IMMEDIATE 'SELECT ?' USING CONCAT(a, CONVERT(RAND() USING latin1)); +--disable_result_log +CALL p1('x'); +--enable_result_log +DROP PROCEDURE p1; + +SET NAMES utf8; +DELIMITER $$; +CREATE PROCEDURE p1(a VARCHAR(10) CHARACTER SET utf8) +BEGIN + PREPARE stmt FROM 'SELECT ?'; + EXECUTE stmt USING CONCAT(a, CONVERT(RAND() USING latin1)); + DEALLOCATE PREPARE stmt; +END; +$$ +DELIMITER ;$$ +--disable_result_log +CALL p1('x'); +DROP PROCEDURE p1; |