Added Non-prelocked SP execution: Now a PROCEDURE doesn't enter/leave prelocked mode for

its body, but lets each statement to get/release its own locks. This allows a broader set
of statements to be executed inside PROCEDUREs (but breaks replication)
This patch should fix BUG#8072, BUG#8766, BUG#9563, BUG#11126


mysql-test/r/sp-security.result:
  Drop tables this test attempts to create
mysql-test/r/sp-threads.result:
  Update test results
mysql-test/r/sp.result:
  Disabled a test that triggers BUG#11986, cleanup used tables when tests start.
mysql-test/r/view.result:
  Enabled a test case that now works with prelocking-free SPs
mysql-test/t/sp-security.test:
  Drop tables this test attempts to create
mysql-test/t/sp.test:
  Disabled a test that triggers BUG#11986, cleanup used tables when tests start.
mysql-test/t/view.test:
  Enabled a test case that now works with prelocking-free SPs
sql/handler.cc:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt
sql/item_func.cc:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt
sql/sp.cc:
  Non-prelocked SP execution: Added support for skipping prelocking of procedure body for
  "CALL proc(...)" statements.
sql/sp.h:
  Non-prelocked SP execution: Added support for skipping prelocking of procedure body for
  "CALL proc(...)" statements.
sql/sp_cache.h:
  Added comments
sql/sp_head.cc:
  Non-prelocked SP execution:
  * Try to unlock tables after PROCEDURE arguments have been evaluated.
  * Make sp_lex_keeper be able to execute in 2 modes: A) when already in prelocked mode
    B) when its statement enters/leaves prelocked mode itself.
sql/sp_head.h:
  Non-prelocked SP execution:  Make sp_lex_keeper to additionally keep list of tables it 
  needs to prelock when its statement enters/leaves prelocked mode on its own.
sql/sql_base.cc:
  Non-prelocked SP execution: Make open_tables() to
   * detect 'CALL proc(...)' and not to do prelocking for procedure body statements.
   * Make lex->query_tables_last to point precisely to a boundary in lex->query_tables 
     list where 'own' tables and views' tables end and added-for-prelocking tables begin.
     (it was not true before - view's tables could end up after query_tables_own_last)
sql/sql_class.cc:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt
sql/sql_class.h:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt
sql/sql_lex.cc:
  Non-prelocked SP execution: More rigourous cleanup in st_lex::cleanup_after_one_table_open()
sql/sql_parse.cc:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt, remove outdated comments
sql/sql_trigger.h:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt

1 files changed, 236 insertions, 0 deletions

diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test
new file mode 100644
index 00000000000..9dbf4f4af7e
--- /dev/null
+++ b/mysql-test/t/sp-prelocking.test
@@ -0,0 +1,236 @@
+--disable_warnings
+drop database if exists testdb;
+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 testdb;
+delimiter //;
+use testdb//
+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 testdb.sp1();
+select database();
+
+drop procedure testdb.sp1;
+drop database testdb;
+
+# 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', b 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 temporary table t1;
+drop temporary table t2;
+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 table t1,t2,t3;
+drop function f1;
+drop function f2;
+drop function f3;
+drop procedure sp1;
+