diff options
Diffstat (limited to 'mysql-test/t/ps.test')
| -rw-r--r-- | mysql-test/t/ps.test | 1039 |
1 files changed, 890 insertions, 149 deletions
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 749864c1f59..34f9a6fb699 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1,8 +1,9 @@ +-- source include/not_embedded.inc # # SQL Syntax for Prepared Statements test # --disable_warnings -drop table if exists t1,t2; +drop table if exists t1,t2,t3,t4; # Avoid wrong warnings if mysql_client_test fails drop database if exists client_test_db; @@ -523,8 +524,9 @@ deallocate prepare stmt; # create table t1 (a varchar(20)); insert into t1 values ('foo'); ---error 1305 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; # @@ -949,7 +951,18 @@ select @@max_prepared_stmt_count; show status like 'prepared_stmt_count'; disconnect con1; connection default; +# Wait for the connection to die: deal with a possible race deallocate prepare stmt; +let $query= select variable_value from information_schema.global_status + where variable_name = 'prepared_stmt_count'; +let $count= `$query`; +if ($count) +{ +--sleep 1 + let $count= `$query`; +} +select @@max_prepared_stmt_count; +show status like 'prepared_stmt_count'; # # Restore the old value. # @@ -1035,7 +1048,6 @@ EXECUTE STMT USING @id,@id; DEALLOCATE PREPARE STMT; DROP TABLE t1; - # # BUG#21354: (COUNT(*) = 1) not working in SELECT inside prepared # statement @@ -1106,97 +1118,9 @@ 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; -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; -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 -# -# Protect ourselves from data left in tmp/ by a previos possibly failed -# test ---system rm -f $MYSQLTEST_VARDIR/tmp/t1.* ---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; - --echo End of 4.1 tests. + ############################# 5.0 tests start ################################ # # @@ -1207,8 +1131,9 @@ deallocate prepare stmt; # create table t1 (a varchar(20)); insert into t1 values ('foo'); ---error 1305 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; # @@ -1671,76 +1596,892 @@ EXECUTE stmt USING @arg; DEALLOCATE PREPARE stmt; DROP TABLE t1,t2; + +--echo End of 5.0 tests. + # -# 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. +# 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; +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +show open tables from mysql; +call proc_1(); +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +show open tables from mysql; +call proc_1(); +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +show open tables from mysql; +call proc_1(); +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +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; +flush tables; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +show open tables from mysql; +prepare abc from "flush tables"; +execute abc; +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +show open tables from mysql; +execute abc; +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +show open tables from mysql; +execute abc; +show open tables from mysql; +select Host, User from mysql.user limit 0; +select Host, Db from mysql.host limit 0; +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 (s1 char(20)); -prepare stmt from "alter table t1 modify s1 int"; -execute stmt; -execute stmt; +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; -deallocate prepare stmt; -# -# Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing" -# + +create procedure proc_1() install plugin my_plug soname 'some_plugin.so'; +--replace_regex /(Can\'t open shared library).*$/\1/ +--error ER_CANT_OPEN_LIBRARY +call proc_1(); +--replace_regex /(Can\'t open shared library).*$/\1/ +--error ER_CANT_OPEN_LIBRARY +call proc_1(); +--replace_regex /(Can\'t open shared library).*$/\1/ +--error ER_CANT_OPEN_LIBRARY +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 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; +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_RECURSIVITY_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 #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# Bug #21422: GRANT/REVOKE possible inside stored function, probably in a trigger +# This is disabled for now till it is resolved in 5.0 # -# 5.0 part of the test. + +#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"; -# 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"; -#execute stmt; -#show create database mysqltest; -#drop database mysqltest; -#set @@character_set_server= utf8; -#execute stmt; -#show create database mysqltest; -#drop database mysqltest; -#deallocate prepare stmt; -#set @@character_set_server= @old_character_set_server; +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; ---echo End of 5.0 tests. |