1 files changed, 446 insertions, 0 deletions
diff --git a/mysql-test/main/sp-prelocking.test b/mysql-test/main/sp-prelocking.test
new file mode 100644
index 00000000000..38cbd5aa110
--- /dev/null
+++ b/mysql-test/main/sp-prelocking.test
@@ -0,0 +1,446 @@
+#
+# 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_warnings
+drop 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#8766
+
+delimiter //;
+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 int
+begin 
+  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 tests
+create 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 int
+begin
+  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_warnings
+drop view if exists v1;
+--enable_warnings
+delimiter //;
+
+create function f1(ab int) returns int
+begin
+  declare i int;
+  set i= (select max(a) from t1 where a < ab) ;
+  return i;
+end //
+
+create function f2(ab int) returns int
+begin
+  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 int
+begin
+  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 int
+begin
+  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_warnings
+drop table if exists t1;
+drop view if exists v1, v2, v3;
+drop function if exists bug15683;
+--enable_warnings
+create 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 bigint
+begin
+return (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_warnings
+drop table if exists t1, t2, t3;
+drop function if exists bug19634;
+--enable_warnings
+create 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 server
+execute 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_warnings
+drop table if exists bug_27907_logs;
+drop table if exists bug_27907_t1;
+--enable_warnings
+
+create 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_t1
+for each row
+begin
+  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_TABLE
+insert 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_warnings
+drop table if exists t1;
+drop function if exists f_bug22427;
+--enable_warnings
+create 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 lock
+create table if not exists t1 select f_bug22427() as i;
+--error ER_TABLE_EXISTS_ERROR
+create 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_warnings
+DROP table IF EXISTS t1,t2;
+--enable_warnings
+CREATE 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 ROW
+BEGIN
+UPDATE 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_LOCKED
+INSERT 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
+
+--echo #
+--echo # Bug#21142859: FUNCTION UPDATING A VIEW FAILS TO FIND TABLE THAT ACTUALLY EXISTS
+--echo #
+
+CREATE TABLE t1 SELECT 1 AS fld1, 'A' AS fld2;
+CREATE TABLE t2 (fld3 INT, fld4 CHAR(1));
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+CREATE TRIGGER t1_au AFTER UPDATE ON t1
+FOR EACH ROW INSERT INTO t2 VALUES (new.fld1, new.fld2);
+
+DELIMITER !;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+ UPDATE v1 SET fld2='B' WHERE fld1=1;
+ RETURN row_count();
+END !
+DELIMITER ;!
+
+--echo # Without the patch, an error was getting reported.
+SELECT f1();
+
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug #16672723 "CAN'T FIND TEMPORARY TABLE".
+--echo #
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+CREATE TEMPORARY TABLE tmp1(a INT);
+PREPARE stmt1 FROM "CREATE TEMPORARY TABLE tmp2 AS SELECT b FROM (SELECT f1() AS b FROM tmp1) AS t";
+--echo # The below statement failed before the fix.
+EXECUTE stmt1;
+DROP TEMPORARY TABLES tmp1, tmp2;
+DEALLOCATE PREPARE stmt1;
+DROP FUNCTION f1;
+
+#
+# MDEV-9084 Calling a stored function from a nested select from temporary table causes unpredictable behavior
+#
+delimiter $$;
+create procedure sp1()
+begin
+  drop table if exists t1, t2;
+  create temporary table t1 select 1 v;
+  create table t2 (col varchar(45)) select distinct col from (select sf1() as col from t1) t;
+end$$
+delimiter ;$$
+create function sf1() returns text return 'blah';
+call test.sp1();
+call test.sp1();
+drop procedure sp1;
+drop function sf1;
+drop table t2;