diff options
Diffstat (limited to 'mysql-test/t/ps.test')
| -rw-r--r-- | mysql-test/t/ps.test | 1065 |
1 files changed, 1049 insertions, 16 deletions
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 6c3f98f6a1a..3e1a41d32c8 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1,8 +1,10 @@ +-- source include/not_embedded.inc +-- source include/have_log_bin.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 +525,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 +952,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 +1049,6 @@ EXECUTE STMT USING @id,@id; DEALLOCATE PREPARE STMT; DROP TABLE t1; - # # BUG#21354: (COUNT(*) = 1) not working in SELECT inside prepared # statement @@ -1232,8 +1245,9 @@ drop table t1; # 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; # @@ -1756,18 +1770,18 @@ 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; +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; # @@ -1967,3 +1981,1022 @@ execute stmt using @arg; deallocate prepare stmt; --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; +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; + +# 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; +show open tables from mysql; +--enable_ps_protocol + +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(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'; +--replace_regex /(Can\'t open shared library).*$/\1/ +--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED +call proc_1(); +--replace_regex /(Can\'t open shared library).*$/\1/ +--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED +call proc_1(); +--replace_regex /(Can\'t open shared library).*$/\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 End of 5.1 tests. |