## Tests of prelocking-free execution of stored procedures.# Currently two properties of prelocking-free SP execution are checked:#  - It is possible to execute DDL statements in prelocking-free stored#    procedure#  - The same procedure can be called in prelocking-free mode and #    in prelocked mode (from within a function).--disable_warningsdrop database if exists mysqltest;drop table if exists t1, t2, t3, t4;drop procedure if exists sp1;drop procedure if exists sp2;drop procedure if exists sp3;drop procedure if exists sp4;drop function if exists f1;drop function if exists f2;drop function if exists f3;--enable_warnings# BUG#8072 create database mysqltest;delimiter //;use mysqltest//create procedure sp1 () begin  drop table if exists t1;  select 1 as "my-col";end;//delimiter ;//select database();call sp1();select database();use test;select database();call mysqltest.sp1();select database();drop procedure mysqltest.sp1;drop database mysqltest;# BUG#8766delimiter //;create procedure sp1() begin   create table t1 (a int);   insert into t1 values (10); end//create procedure sp2()begin  create table t2(a int);  insert into t2 values(1);  call sp1();end//create function f1() returns intbegin   return (select max(a) from t1);end//create procedure sp3()begin   call sp1();  select 'func', f1();end//delimiter ;//call sp1();select 't1',a from t1;drop table t1;call sp2();select 't1',a from t1;select 't2',a from t2;drop table t1, t2;call sp3();select 't1',a from t1;drop table t1;drop procedure sp1;drop procedure sp2;drop procedure sp3;drop function f1;delimiter //;create procedure sp1()begin  create temporary table t2(a int);  insert into t2 select * from t1;end//create procedure sp2()begin  create temporary table t1 (a int);  insert into t1 values(1);  call sp1();  select 't1', a from t1;  select 't2', a from t2;  drop table t1;  drop table t2;end//delimiter ;//call sp2();drop procedure sp1;drop procedure sp2;# Miscelaneous testscreate table t1 (a int);insert into t1 values(1),(2);create table t2 as select * from t1;create table t3 as select * from t1;create table t4 as select * from t1;delimiter //;create procedure sp1(a int)begin  select a;end //create function f1() returns intbegin  return (select max(a) from t1);end //delimiter ;//CALL sp1(f1());#############delimiter //;create procedure sp2(a int)begin  select * from t3;  select a;end //create procedure sp3()begin   select * from t1;  call sp2(5);end //create procedure sp4()begin   select * from t2;  call sp3();end //delimiter ;//call sp4();drop procedure sp1;drop procedure sp2;drop procedure sp3;drop procedure sp4;drop function f1;# Test that prelocking state restoration works with cursors--disable_warningsdrop view if exists v1;--enable_warningsdelimiter //;create function f1(ab int) returns intbegin  declare i int;  set i= (select max(a) from t1 where a < ab) ;  return i;end //create function f2(ab int) returns intbegin  declare i int;  set i= (select max(a) from t2 where a < ab) ;  return i;end //create view v1 as   select t3.a as x, t4.a as y, f2(3) as z  from t3, t4 where t3.a = t4.a //create procedure sp1()begin  declare a int;  set a= (select f1(4) + count(*) A from t1, v1);end //create function f3() returns intbegin  call sp1();  return 1;end //call sp1() //select f3() //select f3() //call sp1() //# ---------------drop procedure sp1//drop function f3//create procedure sp1() begin   declare x int;  declare c cursor for select f1(3) + count(*) from v1;  open c;  fetch c into x;end;//create function f3() returns intbegin  call sp1();  return 1;end //call sp1() //call sp1() //select f3() //call sp1() //delimiter ;//drop view v1;drop table t1,t2,t3,t4;drop function f1;drop function f2;drop function f3;drop procedure sp1;## Bug#15683 "crash, Function on nested VIEWs, Prepared statement"# Check that when creating the prelocking list a nested view # is not merged until it's used.#--disable_warningsdrop table if exists t1;drop view if exists v1, v2, v3;drop function if exists bug15683;--enable_warningscreate table t1 (f1 bigint, f2 varchar(20), f3 bigint);insert into t1 set f1 = 1, f2 = 'schoenenbourg', f3 = 1;create view v1 as select 1 from t1 union all select 1;create view v2 as select 1 from v1;create view v3 as select 1 as f1 from v2;delimiter |;create function bug15683() returns bigintbeginreturn (select count(*) from v3);end|delimiter ;|prepare stmt from "select bug15683()";execute stmt;execute stmt;deallocate prepare stmt;drop table t1;drop view v1, v2, v3;drop function bug15683;## Bug#19634 "Re-execution of multi-delete which involve trigger/stored #            function crashes server"#--disable_warningsdrop table if exists t1, t2, t3;drop function if exists bug19634;--enable_warningscreate table t1 (id int, data int);create table t2 (id int);create table t3 (data int);create function bug19634() returns int return (select count(*) from t3);prepare stmt from "delete t1 from t1, t2 where t1.id = t2.id and bug19634()";# This should not crash serverexecute stmt;execute stmt;deallocate prepare stmt;create trigger t1_bi before delete on t1 for each row insert into t3 values (old.data);prepare stmt from "delete t1 from t1, t2 where t1.id = t2.id";execute stmt;execute stmt;deallocate prepare stmt;drop function bug19634;drop table t1, t2, t3;## Bug #27907 Misleading error message when opening/locking tables#--disable_warningsdrop table if exists bug_27907_logs;drop table if exists bug_27907_t1;--enable_warningscreate table bug_27907_logs (a int);create table bug_27907_t1 (a int);delimiter |;create trigger bug_27907_t1_ai after insert on bug_27907_t1for each rowbegin  insert into bug_27907_logs (a) values (1);end|delimiter ;|drop table bug_27907_logs;## was failing before with error ER_NOT_LOCKED#--error ER_NO_SUCH_TABLEinsert into bug_27907_t1(a) values (1);drop table bug_27907_t1;--echo--echo Bug#22427 create table if not exists + stored function results in--echo inconsistent behavior--echo--echo Add a test case, the bug itself was fixed by the patch for--echo Bug#20662--echo--disable_warningsdrop table if exists t1;drop function if exists f_bug22427;--enable_warningscreate table t1 (i int);insert into t1 values (1);create function f_bug22427() returns int return (select max(i) from t1);select f_bug22427();# Until this bug was fixed, the following emitted error# ERROR 1213: Deadlock found when trying to get lockcreate table if not exists t1 select f_bug22427() as i;--error ER_TABLE_EXISTS_ERRORcreate table t1 select f_bug22427() as i;drop table t1;drop function f_bug22427;--echo #--echo # Bug #29929 LOCK TABLES does not pre-lock tables used in triggers of the locked tables--echo #--disable_warningsDROP table IF EXISTS t1,t2;--enable_warningsCREATE TABLE t1 (c1 INT);CREATE TABLE t2 (c2 INT);INSERT INTO t1 VALUES (1);INSERT INTO t2 VALUES (2);DELIMITER //;CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROWBEGINUPDATE t2 SET c2= c2 + 1;END//DELIMITER ;//--echo # Take a table lock on t1.--echo # This should pre-lock t2 through the trigger.LOCK TABLE t1 WRITE;INSERT INTO t1 VALUES (3);UNLOCK TABLES;LOCK TABLE t1 READ;--error ER_TABLE_NOT_LOCKEDINSERT INTO t2 values(4);UNLOCK TABLES;SELECT * FROM t1;SELECT * FROM t2;DROP TRIGGER t1_ai;DROP TABLE t1, t2;--echo End of 5.0 tests