From 965abcfd4791b400611481ac20fc120a287c20a4 Mon Sep 17 00:00:00 2001
From: unknown <sergefp@mysql.com>
Date: Sat, 30 Jul 2005 08:19:57 +0000
Subject: 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
---
 mysql-test/r/sp-prelocking.result | 219 +++++++++++++++++++++++++++++++++++
 mysql-test/r/sp-security.result   |   2 +-
 mysql-test/r/sp-threads.result    |   2 +-
 mysql-test/r/sp.result            |  29 +----
 mysql-test/r/view.result          |   5 +
 mysql-test/t/sp-prelocking.test   | 236 ++++++++++++++++++++++++++++++++++++++
 mysql-test/t/sp-security.test     |   2 +-
 mysql-test/t/sp.test              |  46 ++++----
 mysql-test/t/view.test            |  18 +--
 sql/handler.cc                    |   4 +-
 sql/item_func.cc                  |   6 +-
 sql/sp.cc                         |  84 +++++++++++---
 sql/sp.h                          |   5 +-
 sql/sp_cache.h                    |  16 ++-
 sql/sp_head.cc                    |  71 +++++++++++-
 sql/sp_head.h                     |  26 ++++-
 sql/sql_base.cc                   |  54 ++++++---
 sql/sql_class.cc                  |   2 +-
 sql/sql_class.h                   |   6 +-
 sql/sql_lex.cc                    |   1 +
 sql/sql_parse.cc                  |  22 ++--
 sql/sql_trigger.h                 |   6 +-
 22 files changed, 735 insertions(+), 127 deletions(-)
 create mode 100644 mysql-test/r/sp-prelocking.result
 create mode 100644 mysql-test/t/sp-prelocking.test

diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result
new file mode 100644
index 00000000000..da5c95cc2dd
--- /dev/null
+++ b/mysql-test/r/sp-prelocking.result
@@ -0,0 +1,219 @@
+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;
+create database testdb;
+use testdb//
+create procedure sp1 () 
+begin
+drop table if exists t1;
+select 1 as "my-col";
+end;
+//
+select database();
+database()
+testdb
+call sp1();
+my-col
+1
+Warnings:
+Note	1051	Unknown table 't1'
+select database();
+database()
+testdb
+use test;
+select database();
+database()
+test
+call testdb.sp1();
+my-col
+1
+Warnings:
+Note	1051	Unknown table 't1'
+select database();
+database()
+test
+drop procedure testdb.sp1;
+drop database testdb;
+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//
+call sp1();
+select 't1',a from t1;
+t1	a
+t1	10
+drop table t1;
+call sp2();
+select 't1',a from t1;
+t1	a
+t1	10
+select 't2',a from t2;
+t2	a
+t2	1
+drop table t1, t2;
+call sp3();
+func	f1()
+func	10
+select 't1',a from t1;
+t1	a
+t1	10
+drop table t1;
+drop procedure sp1;
+drop procedure sp2;
+drop procedure sp3;
+drop function f1;
+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//
+call sp2();
+t1	a
+t1	1
+drop procedure sp1;
+drop procedure sp2;
+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;
+create procedure sp1(a int)
+begin
+select a;
+end //
+create function f1() returns int
+begin
+return (select max(a) from t1);
+end //
+CALL sp1(f1());
+a
+2
+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 //
+call sp4();
+a
+1
+1
+1
+2
+a
+1
+1
+2
+a
+1
+1
+2
+a
+5
+drop temporary table t1;
+drop temporary table t2;
+drop procedure sp1;
+drop procedure sp2;
+drop procedure sp3;
+drop procedure sp4;
+drop function f1;
+drop view if exists v1;
+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() //
+f3()
+1
+select f3() //
+f3()
+1
+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() //
+f3()
+1
+call sp1() //
+drop table t1,t2,t3;
+drop function f1;
+drop function f2;
+drop function f3;
+drop procedure sp1;
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index 184978e4a0d..75ae19eed91 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -1,7 +1,7 @@
 use test;
 grant usage on *.* to user1@localhost;
 flush privileges;
-drop table if exists t1;
+drop table if exists t1,t2;
 drop database if exists db1_secret;
 create database db1_secret;
 create procedure db1_secret.dummy() begin end;
diff --git a/mysql-test/r/sp-threads.result b/mysql-test/r/sp-threads.result
index e6b8128c336..2f7e8021aa7 100644
--- a/mysql-test/r/sp-threads.result
+++ b/mysql-test/r/sp-threads.result
@@ -35,7 +35,7 @@ lock tables t2 write;
 show processlist;
 Id	User	Host	db	Command	Time	State	Info
 #	root	localhost	test	Sleep	#		NULL
-#	root	localhost	test	Query	#	Locked	call bug9486()
+#	root	localhost	test	Query	#	Locked	update t1, t2 set val= 1 where id1=id2
 #	root	localhost	test	Query	#	NULL	show processlist
 unlock tables;
 drop procedure bug9486;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index fd63204e32f..1d5cd689676 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1,10 +1,9 @@
 use test;
-drop table if exists t1;
+drop table if exists t1,t2,t3,t4;
 create table t1 (
 id   char(16) not null default '',
 data int not null
 );
-drop table if exists t2;
 create table t2 (
 s   char(16),
 i   int,
@@ -3042,32 +3041,6 @@ drop procedure bug11529|
 drop procedure if exists bug6063|
 drop procedure if exists bug7088_1|
 drop procedure if exists bug7088_2|
-create procedure bug6063()
-lābel: begin end|
-call bug6063()|
-show create procedure bug6063|
-Procedure	sql_mode	Create Procedure
-bug6063		CREATE PROCEDURE `test`.`bug6063`()
-l?bel: begin end
-set character set utf8|
-create procedure bug7088_1()
-label1: begin end label1|
-create procedure bug7088_2()
-lƤbel1: begin end|
-call bug7088_1()|
-call bug7088_2()|
-set character set default|
-show create procedure bug7088_1|
-Procedure	sql_mode	Create Procedure
-bug7088_1		CREATE PROCEDURE `test`.`bug7088_1`()
-label1: begin end label1
-show create procedure bug7088_2|
-Procedure	sql_mode	Create Procedure
-bug7088_2		CREATE PROCEDURE `test`.`bug7088_2`()
-läbel1: begin end
-drop procedure bug6063|
-drop procedure bug7088_1|
-drop procedure bug7088_2|
 drop procedure if exists bug9565_sub|
 drop procedure if exists bug9565|
 create procedure bug9565_sub()
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index c8078a0604e..4445756d37a 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -581,6 +581,11 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function
 drop view v1;
 create view v1 (a,a) as select 'a','a';
 ERROR 42S21: Duplicate column name 'a'
+drop procedure if exists p1;
+create procedure p1 () begin declare v int; create view v1 as select v; end;//
+call p1();
+ERROR HY000: View's SELECT contains a variable or parameter
+drop procedure p1;
 create table t1 (col1 int,col2 char(22));
 insert into t1 values(5,'Hello, world of views');
 create view v1 as select * from t1;
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;
+
diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test
index 15fcba5ebe9..69529fd1ed0 100644
--- a/mysql-test/t/sp-security.test
+++ b/mysql-test/t/sp-security.test
@@ -15,7 +15,7 @@ grant usage on *.* to user1@localhost;
 flush privileges;
 
 --disable_warnings
-drop table if exists t1;
+drop table if exists t1,t2;
 drop database if exists db1_secret;
 --enable_warnings
 # Create our secret database
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index d9e6163cbc7..d70ce702daf 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -22,15 +22,12 @@ use test;
 # t3 and up are created and dropped when needed.
 #
 --disable_warnings
-drop table if exists t1;
+drop table if exists t1,t2,t3,t4;
 --enable_warnings
 create table t1 (
 	id   char(16) not null default '',
         data int not null
 );
---disable_warnings
-drop table if exists t2;
---enable_warnings
 create table t2 (
 	s   char(16),
         i   int,
@@ -3812,26 +3809,27 @@ drop procedure if exists bug7088_1|
 drop procedure if exists bug7088_2|
 --enable_warnings
 
-create procedure bug6063()
-  lābel: begin end|
-call bug6063()|
-# QQ Known bug: this will not show the label correctly.
-show create procedure bug6063|
-
-set character set utf8|
-create procedure bug7088_1()
-  label1: begin end label1|
-create procedure bug7088_2()
-  lƤbel1: begin end|
-call bug7088_1()|
-call bug7088_2()|
-set character set default|
-show create procedure bug7088_1|
-show create procedure bug7088_2|
-
-drop procedure bug6063|
-drop procedure bug7088_1|
-drop procedure bug7088_2|
+# psergey: temporarily disabled until Bar fixes BUG#11986
+# create procedure bug6063()
+#   lābel: begin end|
+# call bug6063()|
+# # QQ Known bug: this will not show the label correctly.
+# show create procedure bug6063|
+# 
+# set character set utf8|
+# create procedure bug7088_1()
+#   label1: begin end label1|
+# create procedure bug7088_2()
+#   lƤbel1: begin end|
+# call bug7088_1()|
+# call bug7088_2()|
+# set character set default|
+# show create procedure bug7088_1|
+# show create procedure bug7088_2|
+# 
+# drop procedure bug6063|
+# drop procedure bug7088_1|
+# drop procedure bug7088_2|
 
 #
 # BUG#9565: "Wrong locking in stored procedure if a sub-sequent procedure
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 16a94820596..61695319bc1 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -490,15 +490,15 @@ create view v1 (a,a) as select 'a','a';
 #
 # SP variables inside view test
 #
-# QQ This can't be tested with the new table locking for functions,
-# QQ since views created in an SP can't be used within the same SP
-# QQ (just as for tables). Instead it fails with error 1146.
-#delimiter //;
-#create procedure p1 () begin declare v int; create view v1 as select v; end;//
-#delimiter ;//
-#-- error 1351
-#call p1();
-#drop procedure p1;
+--disable_warnings
+drop procedure if exists p1;
+--enable_warnings
+delimiter //;
+create procedure p1 () begin declare v int; create view v1 as select v; end;//
+delimiter ;//
+-- error 1351
+call p1();
+drop procedure p1;
 
 #
 # updatablity should be transitive
diff --git a/sql/handler.cc b/sql/handler.cc
index 2731cb8480d..1f13f0d5e36 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -598,7 +598,7 @@ int ha_commit_trans(THD *thd, bool all)
   my_xid xid= thd->transaction.xid.get_my_xid();
   DBUG_ENTER("ha_commit_trans");
 
-  if (thd->transaction.in_sub_stmt)
+  if (thd->in_sub_stmt)
   {
     /*
       Since we don't support nested statement transactions in 5.0,
@@ -717,7 +717,7 @@ int ha_rollback_trans(THD *thd, bool all)
   THD_TRANS *trans=all ? &thd->transaction.all : &thd->transaction.stmt;
   bool is_real_trans=all || thd->transaction.all.nht == 0;
   DBUG_ENTER("ha_rollback_trans");
-  if (thd->transaction.in_sub_stmt)
+  if (thd->in_sub_stmt)
   {
     /*
       If we are inside stored function or trigger we should not commit or
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 53895cc7331..fb39a62810c 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4844,7 +4844,7 @@ Item_func_sp::execute(Item **itp)
   THD *thd= current_thd;
   ulong old_client_capabilites;
   int res= -1;
-  bool save_in_sub_stmt= thd->transaction.in_sub_stmt;
+  bool save_in_sub_stmt= thd->in_sub_stmt;
   my_bool save_no_send_ok;
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   st_sp_security_context save_ctx;
@@ -4882,11 +4882,11 @@ Item_func_sp::execute(Item **itp)
   */
 
   tmp_disable_binlog(thd); /* don't binlog the substatements */
-  thd->transaction.in_sub_stmt= TRUE;
+  thd->in_sub_stmt= TRUE;
 
   res= m_sp->execute_function(thd, args, arg_count, itp);
 
-  thd->transaction.in_sub_stmt= save_in_sub_stmt;
+  thd->in_sub_stmt= save_in_sub_stmt;
   reenable_binlog(thd);
   if (res && mysql_bin_log.is_open() &&
       (m_sp->m_chistics->daccess == SP_CONTAINS_SQL ||
diff --git a/sql/sp.cc b/sql/sp.cc
index a277c6bd253..3d513b16d07 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -1175,6 +1175,44 @@ extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first)
 }
 
 
+/*
+  Check if routines in routines_list require sp_cache_routines_and_add_tables
+  call.
+
+  SYNOPSIS
+    sp_need_cache_routines()
+      thd
+      routines 
+      need_skip_first  OUT TRUE - don't do prelocking for the 1st element in 
+                                  routines list.
+                           FALSE- otherwise
+  NOTES 
+    This function assumes that for any "CALL proc(...)" statement routines_list 
+    will have 'proc' as first element (it may have several, consider e.g.
+    "proc(sp_func(...)))". This property is currently guaranted by the parser.
+
+  RETURN
+    TRUE  Need to sp_cache_routines_and_add_tables call for this statement.
+    FALSE Otherwise.
+*/
+
+bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, bool *need_skip_first)
+{
+  Sroutine_hash_entry *routine;
+  routine= (Sroutine_hash_entry*)routines_list->first;
+
+  *need_skip_first= FALSE;
+  if (!routine)
+    return FALSE;
+
+  if (routine->key.str[0] != TYPE_ENUM_PROCEDURE)
+    return TRUE;
+
+  *need_skip_first= TRUE;
+  return TRUE;
+}
+
+
 /*
   Auxilary function that adds new element to the set of stored routines
   used by statement.
@@ -1312,11 +1350,13 @@ static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src)
 
   SYNOPSIS
     sp_cache_routines_and_add_tables_aux()
-      thd   - thread context
-      lex   - LEX representing statement
-      start - first routine from the list of routines to be cached
-              (this list defines mentioned sub-set).
-
+      thd              - thread context
+      lex              - LEX representing statement
+      start            - first routine from the list of routines to be cached
+                         (this list defines mentioned sub-set).
+      first_no_prelock - If true, don't add tables or cache routines used by
+                         the body of the first routine (i.e. *start)
+                         will be executed in non-prelocked mode.
   NOTE
     If some function is missing this won't be reported here.
     Instead this fact will be discovered during query execution.
@@ -1328,10 +1368,11 @@ static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src)
 
 static bool
 sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
-                                     Sroutine_hash_entry *start)
+                                     Sroutine_hash_entry *start, 
+                                     bool first_no_prelock)
 {
   bool result= FALSE;
-
+  bool first= TRUE;
   DBUG_ENTER("sp_cache_routines_and_add_tables_aux");
 
   for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
@@ -1367,9 +1408,13 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
     }
     if (sp)
     {
-      sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines);
-      result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+      if (!(first && first_no_prelock))
+      {
+        sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines);
+        result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+      }
     }
+    first= FALSE;
   }
   DBUG_RETURN(result);
 }
@@ -1382,20 +1427,22 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
 
   SYNOPSIS
     sp_cache_routines_and_add_tables()
-      thd   - thread context
-      lex   - LEX representing statement
-
+      thd              - thread context
+      lex              - LEX representing statement
+      first_no_prelock - If true, don't add tables or cache routines used by
+                         the body of the first routine (i.e. *start)
+                         
   RETURN VALUE
     TRUE  - some tables were added
     FALSE - no tables were added.
 */
 
 bool
-sp_cache_routines_and_add_tables(THD *thd, LEX *lex)
+sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool first_no_prelock)
 {
-
   return sp_cache_routines_and_add_tables_aux(thd, lex,
-           (Sroutine_hash_entry *)lex->sroutines_list.first);
+           (Sroutine_hash_entry *)lex->sroutines_list.first,
+           first_no_prelock);
 }
 
 
@@ -1417,8 +1464,8 @@ sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex)
   Sroutine_hash_entry **last_cached_routine_ptr=
                           (Sroutine_hash_entry **)lex->sroutines_list.next;
   sp_update_stmt_used_routines(thd, lex, &aux_lex->sroutines);
-  (void)sp_cache_routines_and_add_tables_aux(thd, lex,
-                                             *last_cached_routine_ptr);
+  (void)sp_cache_routines_and_add_tables_aux(thd, lex, 
+                                             *last_cached_routine_ptr, FALSE);
 }
 
 
@@ -1453,7 +1500,8 @@ sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
         }
 
     (void)sp_cache_routines_and_add_tables_aux(thd, lex,
-                                               *last_cached_routine_ptr);
+                                               *last_cached_routine_ptr, 
+                                               FALSE);
   }
 }
 
diff --git a/sql/sp.h b/sql/sp.h
index b8af8d3a321..716f3d90f55 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -79,10 +79,13 @@ sp_show_status_function(THD *thd, const char *wild);
   Procedures for pre-caching of stored routines and building table list
   for prelocking.
 */
+bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, 
+                            bool *need_skip_first);
 void sp_add_used_routine(LEX *lex, Query_arena *arena,
                          sp_name *rt, char rt_type);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
-bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex);
+bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex, 
+                                      bool first_no_prelock);
 void sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
                                                LEX *aux_lex);
 void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
diff --git a/sql/sp_cache.h b/sql/sp_cache.h
index e9efe5b2a8c..5873c763289 100644
--- a/sql/sp_cache.h
+++ b/sql/sp_cache.h
@@ -22,6 +22,12 @@
 #pragma interface			/* gcc class implementation */
 #endif
 
+/*
+  Stored procedures/functions cache. This is used as follows:
+   * Each thread has its own cache.
+   * When SP is used it is always in some thread's cache.
+*/
+
 class sp_head;
 class sp_cache;
 
@@ -31,16 +37,20 @@ void sp_cache_init();
 /* Clear the cache *cp and set *cp to NULL */
 void sp_cache_clear(sp_cache **cp);
 
-/* Insert an SP to cache. If 'cp' points to NULL, it's set to a new cache */
+/* Insert an SP into cache. If 'cp' points to NULL, it's set to a new cache */
 void sp_cache_insert(sp_cache **cp, sp_head *sp);
 
 /* Lookup an SP in cache */
 sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name);
 
-/* Remove an SP from cache. Returns true if something was removed */
+/* 
+  Remove an SP from cache, and also bump the Cversion number so all other 
+  caches are invalidated. 
+  Returns true if something was removed.
+*/
 bool sp_cache_remove(sp_cache **cp, sp_name *name);
 
-/* Invalidate a cache */
+/* Invalidate all existing SP caches by bumping Cversion number. */
 void sp_cache_invalidate();
 
 
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 02c006d01ee..8d56e2a0b38 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -879,7 +879,10 @@ sp_head::execute_procedure(THD *thd, List<Item> *args)
       octx= new sp_rcontext(csize, hmax, cmax);
       tmp_octx= TRUE;
     }
+
+    /* Evaluate SP arguments (i.e. get the values passed as parameters) */
     // QQ: Should do type checking?
+    DBUG_PRINT("info",(" %.*s: eval args", m_name.length, m_name.str));
     for (i = 0 ; (it= li++) && i < params ; i++)
     {
       sp_pvar_t *pvar= m_pcont->find_pvar(i);
@@ -916,6 +919,14 @@ sp_head::execute_procedure(THD *thd, List<Item> *args)
       }
     }
 
+    /* 
+      Okay, got values for all arguments. Close tables that might be used by 
+      arguments evaluation.
+    */
+    if (!thd->in_sub_stmt)
+      close_thread_tables(thd, 0, 0, 0);
+
+    DBUG_PRINT("info",(" %.*s: eval args done", m_name.length, m_name.str));
     // The rest of the frame are local variables which are all IN.
     // Default all variables to null (those with default clauses will
     // be set by an set instruction).
@@ -1480,8 +1491,37 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
            implemented at the same time as ability not to store LEX for
            instruction if it is not really used.
   */
-  reinit_stmt_before_use(thd, m_lex);
 
+  bool collect_prelocking_tail= FALSE;
+
+  if (thd->prelocked_mode == NON_PRELOCKED)
+  {
+    /*
+      This statement will enter/leave prelocked mode on its own.
+      Entering prelocked mode changes table list and related members
+      of LEX, so we'll need to restore them.
+    */
+    if (lex_query_tables_own_last)
+    {
+      /*
+        We've already entered/left prelocked mode with this statement.
+        Attach the list of tables that need to be prelocked and mark m_lex
+        as having such list attached.
+      */
+      *lex_query_tables_own_last= prelocking_tables;
+      m_lex->mark_as_requiring_prelocking(lex_query_tables_own_last);
+    }
+    else
+    {
+      /* 
+        Let open_tables_calculate list of tables that this statement needs
+        to have prelocked.
+      */
+      collect_prelocking_tail= TRUE;
+    }
+  }
+    
+  reinit_stmt_before_use(thd, m_lex);
   /*
     If requested check whenever we have access to tables in LEX's table list
     and open and lock them before executing instructtions core function.
@@ -1499,6 +1539,35 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
   thd->proc_info="closing tables";
   close_thread_tables(thd);
 
+  if (thd->prelocked_mode == NON_PRELOCKED)
+  {
+    if (!lex_query_tables_own_last)
+      lex_query_tables_own_last= thd->lex->query_tables_own_last;
+      
+    if (lex_query_tables_own_last)
+    {
+      if (collect_prelocking_tail)
+      {
+        /*
+          This is the first time this statement has entered/left prelocked
+          mode on its own. open_tables() has calculated the set of tables this
+          statement needs to have prelocked and added them to the end of
+          m_lex->query_tables(->next_global)*.
+          Save this "tail" for subsequent calls (and restore original list 
+          below)
+        */
+        lex_query_tables_own_last= m_lex->query_tables_own_last;
+        prelocking_tables= *lex_query_tables_own_last;
+      }
+      /*
+        The table list now has list of tables that need to be prelocked
+        when this statement executes, chop it off, and mark this statement 
+        as not requiring prelocking.
+      */
+      *lex_query_tables_own_last= NULL;
+      m_lex->mark_as_requiring_prelocking(NULL);
+    }
+  }
   thd->rollback_item_tree_changes();
 
   /*
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 32dc4449174..724cbcc7fc5 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -282,6 +282,10 @@ private:
   /*
     Multi-set representing optimized list of tables to be locked by this
     routine. Does not include tables which are used by invoked routines.
+
+    Note: for prelocking-free SPs this multiset is constructed too.
+    We do so because the same instance of sp_head may be called both
+    in prelocked mode and in non-prelocked mode.
   */
   HASH m_sptabs;
 
@@ -383,7 +387,8 @@ class sp_lex_keeper
 public:
 
   sp_lex_keeper(LEX *lex, bool lex_resp)
-    : m_lex(lex), m_lex_resp(lex_resp)
+    : m_lex(lex), m_lex_resp(lex_resp), 
+      lex_query_tables_own_last(NULL)
   {
     lex->sp_lex_in_use= TRUE;
   }
@@ -418,6 +423,25 @@ private:
     for LEX deletion.
   */
   bool m_lex_resp;
+
+  /*
+    Support for being able to execute this statement in two modes:
+    a) inside prelocked mode set by the calling procedure or its ancestor.
+    b) outside of prelocked mode, when this statement enters/leaves
+       prelocked mode itself.
+  */
+  
+  /*
+    List of additional tables this statement needs to lock when it
+    enters/leaves prelocked mode on its own.
+  */
+  TABLE_LIST *prelocking_tables;
+
+  /*
+    The value m_lex->query_tables_own_last should be set to this when the
+    statement enters/leaves prelocked mode on its own.
+  */
+  TABLE_LIST **lex_query_tables_own_last;
 };
 
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 65bb0ec047b..367bd2c5ade 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -391,6 +391,8 @@ static void mark_used_tables_as_free_for_reuse(THD *thd, TABLE *table)
 			LOCK_open
     skip_derived	Set to 1 (0 = default) if we should not free derived
 			tables.
+    stopper             When closing tables from thd->open_tables(->next)*, 
+                        don't close/remove tables starting from stopper.
 
   IMPLEMENTATION
     Unlocks tables and frees derived tables.
@@ -474,6 +476,7 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived,
       We are in prelocked mode, so we have to leave it now with doing
       implicit UNLOCK TABLES if need.
     */
+    DBUG_PRINT("info",("thd->prelocked_mode= NON_PRELOCKED"));
     thd->prelocked_mode= NON_PRELOCKED;
 
     if (prelocked_mode == PRELOCKED_UNDER_LOCK_TABLES)
@@ -1792,6 +1795,7 @@ err:
   DBUG_RETURN(1);
 }
 
+
 /*
   Open all tables in list
 
@@ -1843,10 +1847,6 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
     statement for which table list for prelocking is already built, let
     us cache routines and try to build such table list.
 
-    NOTE: If we want queries with functions to work under explicit
-    LOCK TABLES we have to additionaly lock mysql.proc table in it.
-    At least until Monty will fix SP loading :)
-
     NOTE: We can't delay prelocking until we will met some sub-statement
     which really uses tables, since this will imply that we have to restore
     its table list to be able execute it in some other context.
@@ -1860,19 +1860,28 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
     mode we will have some locked tables, because queries which use only
     derived/information schema tables and views possible. Thus "counter"
     may be still zero for prelocked statement...
+
+    NOTE: The above notes may be out of date. Please wait for psergey to 
+          document new prelocked behavior.
   */
-  if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
-      thd->lex->sroutines.records)
+  
+  if (!thd->prelocked_mode && !thd->lex->requires_prelocking())
   {
-    TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
+    bool first_no_prelocking;
+    if (sp_need_cache_routines(thd, &thd->lex->sroutines_list,
+                               &first_no_prelocking))
+    {
+      TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
 
-    DBUG_ASSERT(thd->lex->query_tables == *start);
+      DBUG_ASSERT(thd->lex->query_tables == *start);
 
-    if (sp_cache_routines_and_add_tables(thd, thd->lex) ||
-        *start)
-    {
-      query_tables_last_own= save_query_tables_last;
-      *start= thd->lex->query_tables;
+      if (sp_cache_routines_and_add_tables(thd, thd->lex,
+                                           first_no_prelocking) ||
+          *start)
+      {
+        query_tables_last_own= save_query_tables_last;
+        *start= thd->lex->query_tables;
+      }
     }
   }
 
@@ -1891,14 +1900,31 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
       DBUG_RETURN(-1);
     }
     (*counter)++;
+    
     if (!tables->table &&
 	!(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, 0)))
     {
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
+
       if (tables->view)
       {
         /* VIEW placeholder */
 	(*counter)--;
+
+        /* 
+          tables->next_global list consists of two parts:
+          1) Query tables and underlying tables of views.
+          2) Tables used by all stored routines that this statement invokes on
+             execution.
+          We need to know where the bound between these two parts is. If we've
+          just opened the last table in part #1, and it added tables after
+          itself, adjust the boundary pointer accordingly.
+        */
+        if (query_tables_last_own &&
+            query_tables_last_own == &(tables->next_global) &&
+            tables->view->query_tables)
+          query_tables_last_own= tables->view->query_tables_last;
+        
         /*
           Again if needed we have to get cache all routines used by this view
           and add tables used by them to table list.
@@ -2323,6 +2349,7 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
         and was marked as occupied during open_tables() as free for reuse.
       */
       mark_real_tables_as_free_for_reuse(first_not_own);
+      DBUG_PRINT("info",("prelocked_mode= PRELOCKED"));
       thd->prelocked_mode= PRELOCKED;
     }
   }
@@ -2346,6 +2373,7 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
     if (thd->lex->requires_prelocking())
     {
       mark_real_tables_as_free_for_reuse(first_not_own);
+      DBUG_PRINT("info", ("thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES"));
       thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES;
     }
   }
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 89d5b543dfc..81cdc6562e9 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -178,7 +178,7 @@ THD::THD()
    rand_used(0), time_zone_used(0),
    last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0),
    in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE),
-   spcont(NULL)
+   spcont(NULL), in_sub_stmt(FALSE)
 {
   current_arena= this;
   host= user= priv_user= db= ip= 0;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index d6847f5fb35..26a861bcc5d 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1132,6 +1132,10 @@ public:
   thr_lock_type update_lock_default;
   delayed_insert *di;
   my_bool    tablespace_op;	/* This is TRUE in DISCARD/IMPORT TABLESPACE */
+  
+  /* TRUE if we are inside of trigger or stored function. */
+  bool in_sub_stmt;
+  
   /* container for handler's private per-connection data */
   void *ha_data[MAX_HA];
   struct st_transactions {
@@ -1139,8 +1143,6 @@ public:
     THD_TRANS all;			// Trans since BEGIN WORK
     THD_TRANS stmt;			// Trans for current statement
     bool on;                            // see ha_enable_transaction()
-    /* TRUE if we are inside of trigger or stored function. */
-    bool in_sub_stmt;
     XID  xid;                           // transaction identifier
     enum xa_states xa_state;            // used by external XA only
     /*
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 630a7e950f7..c66bfcbe78d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2008,6 +2008,7 @@ void st_lex::cleanup_after_one_table_open()
   time_zone_tables_used= 0;
   if (sroutines.records)
     my_hash_reset(&sroutines);
+  sroutines_list.empty();
 }
 
 
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 23403e6e00a..6a0b5586598 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -27,6 +27,7 @@
 
 #include "sp_head.h"
 #include "sp.h"
+#include "sp_cache.h"
 
 #ifdef HAVE_OPENSSL
 /*
@@ -124,7 +125,7 @@ static bool end_active_trans(THD *thd)
 {
   int error=0;
   DBUG_ENTER("end_active_trans");
-  if (unlikely(thd->transaction.in_sub_stmt))
+  if (unlikely(thd->in_sub_stmt))
   {
     my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
     DBUG_RETURN(1);
@@ -147,11 +148,7 @@ static bool end_active_trans(THD *thd)
 static bool begin_trans(THD *thd)
 {
   int error=0;
-  /*
-    QQ: May be it is better to simply prohibit COMMIT and ROLLBACK in
-        stored routines as SQL2003 suggests?
-  */
-  if (unlikely(thd->transaction.in_sub_stmt))
+  if (unlikely(thd->in_sub_stmt))
   {
     my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
     return 1;
@@ -1340,11 +1337,7 @@ int end_trans(THD *thd, enum enum_mysql_completiontype completion)
   int res= 0;
   DBUG_ENTER("end_trans");
 
-  /*
-    QQ: May be it is better to simply prohibit COMMIT and ROLLBACK in
-        stored routines as SQL2003 suggests?
-  */
-  if (unlikely(thd->transaction.in_sub_stmt))
+  if (unlikely(thd->in_sub_stmt))
   {
     my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
     DBUG_RETURN(1);
@@ -4128,9 +4121,8 @@ end_with_restore_list:
        goto error;
 
       /*
-        By this moment all needed SPs should be in cache so no need
-        to look into DB. Moreover we may be unable to do it becuase
-        we may don't have read lock on mysql.proc
+        By this moment all needed SPs should be in cache so no need to look 
+        into DB. 
       */
       if (!(sp= sp_find_procedure(thd, lex->spname, TRUE)))
       {
@@ -4195,7 +4187,7 @@ end_with_restore_list:
 	select_limit= thd->variables.select_limit;
 	thd->variables.select_limit= HA_POS_ERROR;
 
-	thd->row_count_func= 0;
+        thd->row_count_func= 0;
         tmp_disable_binlog(thd); /* don't binlog the substatements */
 	res= sp->execute_procedure(thd, &lex->value_list);
         reenable_binlog(thd);
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index e751741fa34..e74297461d3 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -78,7 +78,7 @@ public:
 
     if (bodies[event][time_type])
     {
-      bool save_in_sub_stmt= thd->transaction.in_sub_stmt;
+      bool save_in_sub_stmt= thd->in_sub_stmt;
 #ifndef EMBEDDED_LIBRARY
       /* Surpress OK packets in case if we will execute statements */
       my_bool nsok= thd->net.no_send_ok;
@@ -107,11 +107,11 @@ public:
         does NOT go into binlog.
       */
       tmp_disable_binlog(thd);
-      thd->transaction.in_sub_stmt= TRUE;
+      thd->in_sub_stmt= TRUE;
 
       res= bodies[event][time_type]->execute_function(thd, 0, 0, 0);
 
-      thd->transaction.in_sub_stmt= save_in_sub_stmt;
+      thd->in_sub_stmt= save_in_sub_stmt;
       reenable_binlog(thd);
 
 #ifndef EMBEDDED_LIBRARY
-- 
cgit v1.2.1


From efade1900187da51a936af76295f651e4f8663a9 Mon Sep 17 00:00:00 2001
From: unknown <sergefp@mysql.com>
Date: Wed, 3 Aug 2005 03:37:32 +0000
Subject: Prelocking-free SPs, post-review fixes: * Don't activate prelocking
 mode for evaluating procedure arguments when it is not necessary. * Code
 structure simplification and cleanup. * Cleanup in .test files

mysql-test/r/sp-prelocking.result:
  Prelocking-free SPs, post-review fixes:
  Added comment, s/testdb/mysqltest/, fixed a wrong test (error wasnt reported because of known bug in mysqltestrun)
mysql-test/r/sp-security.result:
  Don't drop the table we're not using.
mysql-test/r/sp.result:
  Prelocking-free SPs, post-review fixes:
  remove redundant "drop table if exists t3" statements
mysql-test/t/sp-prelocking.test:
  Prelocking-free SPs, post-review fixes:
  Added comment, s/testdb/mysqltest/, fixed a wrong test (error wasnt reported because of known bug in mysqltestrun)
mysql-test/t/sp-security.test:
  Don't drop the table we're not using.
mysql-test/t/sp.test:
  Prelocking-free SPs, post-review fixes:
  remove redundant "drop table if exists t3" statements
sql/sp.cc:
  New, better defined, sp_get_prelocking_info() function to get info about
    statement prelocking options
sql/sp.h:
  Prelocking-free SPs, post-review fixes: New, better defined, sp_get_prelocking_info()
  function to get info about statement prelocking options
sql/sp_cache.h:
  Prelocking-free SPs, post-review fixes: Amended the comments
sql/sp_head.cc:
  Prelocking-free SPs, post-review fixes: Amend the comments, simplify the code that
  attaches removes statement's prelocking tables.
sql/sql_base.cc:
  Prelocking-free SPs, post-review fixes:
  * Use a better defined sp_get_prelocking_info() function to get info about
    statement prelocking options
  * Don't activate prelocked mode for evaluation of SP arguments that use tables
    but don't need prelocking.
sql/sql_class.cc:
  Prelocking-free SPs, post-review fixes: Initialize THD members in the order they are declared.
---
 mysql-test/r/sp-prelocking.result | 26 +++++++---------
 mysql-test/r/sp-security.result   |  2 +-
 mysql-test/r/sp.result            | 22 +-------------
 mysql-test/t/sp-prelocking.test   | 24 +++++++++------
 mysql-test/t/sp-security.test     |  2 +-
 mysql-test/t/sp.test              | 62 +--------------------------------------
 sql/sp.cc                         | 43 +++++++++++++--------------
 sql/sp.h                          |  4 +--
 sql/sp_cache.h                    |  3 +-
 sql/sp_head.cc                    | 58 +++++++++++++-----------------------
 sql/sql_base.cc                   | 31 ++++++++++----------
 sql/sql_class.cc                  |  4 +--
 12 files changed, 92 insertions(+), 189 deletions(-)

diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result
index da5c95cc2dd..32d6d4e6264 100644
--- a/mysql-test/r/sp-prelocking.result
+++ b/mysql-test/r/sp-prelocking.result
@@ -1,4 +1,4 @@
-drop database if exists testdb;
+drop database if exists mysqltest;
 drop table if exists t1, t2, t3, t4;
 drop procedure if exists sp1;
 drop procedure if exists sp2;
@@ -7,8 +7,8 @@ drop procedure if exists sp4;
 drop function if exists f1;
 drop function if exists f2;
 drop function if exists f3;
-create database testdb;
-use testdb//
+create database mysqltest;
+use mysqltest//
 create procedure sp1 () 
 begin
 drop table if exists t1;
@@ -17,7 +17,7 @@ end;
 //
 select database();
 database()
-testdb
+mysqltest
 call sp1();
 my-col
 1
@@ -25,12 +25,12 @@ Warnings:
 Note	1051	Unknown table 't1'
 select database();
 database()
-testdb
+mysqltest
 use test;
 select database();
 database()
 test
-call testdb.sp1();
+call mysqltest.sp1();
 my-col
 1
 Warnings:
@@ -38,8 +38,8 @@ Note	1051	Unknown table 't1'
 select database();
 database()
 test
-drop procedure testdb.sp1;
-drop database testdb;
+drop procedure mysqltest.sp1;
+drop database mysqltest;
 create procedure sp1() 
 begin 
 create table t1 (a int); 
@@ -95,13 +95,15 @@ create temporary table t1 (a int);
 insert into t1 values(1);
 call sp1();
 select 't1', a from t1;
-select 't2', b from t2;
+select 't2', a from t2;
 drop table t1;
 drop table t2;
 end//
 call sp2();
 t1	a
 t1	1
+t2	a
+t2	1
 drop procedure sp1;
 drop procedure sp2;
 create table t1 (a int);
@@ -138,21 +140,15 @@ end //
 call sp4();
 a
 1
-1
-1
 2
 a
 1
-1
 2
 a
 1
-1
 2
 a
 5
-drop temporary table t1;
-drop temporary table t2;
 drop procedure sp1;
 drop procedure sp2;
 drop procedure sp3;
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index 75ae19eed91..184978e4a0d 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -1,7 +1,7 @@
 use test;
 grant usage on *.* to user1@localhost;
 flush privileges;
-drop table if exists t1,t2;
+drop table if exists t1;
 drop database if exists db1_secret;
 create database db1_secret;
 create procedure db1_secret.dummy() begin end;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 1d5cd689676..b4b0424c2a7 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -84,7 +84,6 @@ foo	1
 kaka	3
 delete from t1|
 drop procedure setcontext|
-drop table if exists t3|
 create table t3 ( d date, i int, f double, s varchar(32) )|
 drop procedure if exists nullset|
 create procedure nullset()
@@ -520,7 +519,6 @@ select data into x from test.t1 limit 1;
 insert into test.t3 values ("into4", x);
 end|
 delete from t1|
-drop table if exists t3|
 create table t3 ( s char(16), d int)|
 call into_test4()|
 Warnings:
@@ -564,13 +562,12 @@ insert into test.t1 values (x, y);
 create temporary table test.t3 select * from test.t1;
 insert into test.t3 values (concat(x, "2"), y+2);
 end|
-drop table if exists t3|
 call create_select("cs", 90)|
 select * from t1, t3|
 id	data	id	data
 cs	90	cs	90
 cs	90	cs2	92
-drop table if exists t3|
+drop table t3|
 delete from t1|
 drop procedure create_select|
 drop function if exists e|
@@ -701,7 +698,6 @@ id	data
 hndlr3	13
 delete from t1|
 drop procedure hndlr3|
-drop table if exists t3|
 create table t3 ( id   char(16), data int )|
 drop procedure if exists hndlr4|
 create procedure hndlr4()
@@ -744,7 +740,6 @@ foo	40
 bar	15
 zap	663
 drop procedure cur1|
-drop table if exists t3|
 create table t3 ( s char(16), i int )|
 drop procedure if exists cur2|
 create procedure cur2()
@@ -1308,7 +1303,6 @@ select t1max()|
 t1max()
 5
 drop function t1max|
-drop table if exists t3|
 create table t3 (
 v char(16) not null primary key,
 c int unsigned not null
@@ -1429,7 +1423,6 @@ select @1, @2|
 2	NULL
 drop table t70|
 drop procedure bug1656|
-drop table if exists t3|
 create table t3(a int)|
 drop procedure if exists bug1862|
 create procedure bug1862()
@@ -1554,7 +1547,6 @@ select @x|
 42
 drop procedure bug2776_1|
 drop procedure bug2776_2|
-drop table if exists t3|
 create table t3 (s1 smallint)|
 insert into t3 values (123456789012)|
 Warnings:
@@ -1615,7 +1607,6 @@ f1	rc	t3
 drop procedure bug1863|
 drop temporary table temp_t1;
 drop table t3, t4|
-drop table if exists t3, t4|
 create table t3 ( 
 OrderID  int not null,
 MarketID int,
@@ -1693,7 +1684,6 @@ select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
 @i	time
 2	01-01-1970 03:16:40
 drop procedure bug3426|
-drop table if exists t3, t4|
 create table t3 (
 a int primary key, 
 ach char(1)
@@ -1723,7 +1713,6 @@ a	ach	b	bch
 1	a	1	b
 drop procedure bug3448|
 drop table t3, t4|
-drop table if exists t3|
 create table t3 (
 id int unsigned auto_increment not null primary key,
 title VARCHAR(200),
@@ -1872,7 +1861,6 @@ select 1+2|
 1+2
 3
 drop procedure bug3843|
-drop table if exists t3|
 create table t3 ( s1 char(10) )|
 insert into t3 values ('a'), ('b')|
 drop procedure if exists bug3368|
@@ -1888,7 +1876,6 @@ group_concat(v)
 yz,yz
 drop procedure bug3368|
 drop table t3|
-drop table if exists t3|
 create table t3 (f1 int, f2 int)|
 insert into t3 values (1,1)|
 drop procedure if exists bug4579_1|
@@ -1913,7 +1900,6 @@ Warning	1329	No data to FETCH
 drop procedure bug4579_1|
 drop procedure bug4579_2|
 drop table t3|
-drop table if exists t3|
 drop procedure if exists bug2773|
 create function bug2773() returns int return null|
 create table t3 as select bug2773()|
@@ -1935,7 +1921,6 @@ select bug3788()|
 bug3788()
 5
 drop function bug3788|
-drop table if exists t3|
 create table t3 (f1 int, f2 int, f3 int)|
 insert into t3 values (1,1,1)|
 drop procedure if exists bug4726|
@@ -2096,7 +2081,6 @@ call bug4902_2()|
 Id	User	Host	db	Command	Time	State	Info
 #	root	localhost	test	Query	#	NULL	show processlist
 drop procedure bug4902_2|
-drop table if exists t3|
 drop procedure if exists bug4904|
 create procedure bug4904()
 begin
@@ -2285,7 +2269,6 @@ flush status|
 flush query cache|
 delete from t1|
 drop procedure bug3583|
-drop table if exists t3|
 drop procedure if exists bug4905|
 create table t3 (s1 int,primary key (s1))|
 drop procedure if exists bug4905|
@@ -2343,7 +2326,6 @@ call bug8540()|
 y	z
 1	1
 drop procedure bug8540|
-drop table if exists t3|
 create table t3 (s1 int)|
 drop procedure if exists bug6642|
 create procedure bug6642()
@@ -2426,7 +2408,6 @@ call bug7992_2()|
 drop procedure bug7992_1|
 drop procedure bug7992_2|
 drop table t3|
-drop table if exists t3|
 create table t3 (  userid bigint(20) not null default 0 )|
 drop procedure if exists bug8116|
 create procedure bug8116(in _userid int)
@@ -2587,7 +2568,6 @@ delete from t1|
 drop procedure if exists bug6900|
 drop procedure if exists bug9074|
 drop procedure if exists bug6900_9074|
-drop table if exists t3|
 create table t3 (w char unique, x char)|
 insert into t3 values ('a', 'b')|
 create procedure bug6900()
diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test
index 9dbf4f4af7e..e5f35b19fa7 100644
--- a/mysql-test/t/sp-prelocking.test
+++ b/mysql-test/t/sp-prelocking.test
@@ -1,5 +1,13 @@
+#
+# 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 testdb;
+drop database if exists mysqltest;
 drop table if exists t1, t2, t3, t4;
 drop procedure if exists sp1;
 drop procedure if exists sp2;
@@ -12,9 +20,9 @@ drop function if exists f3;
 
 # BUG#8072 
 
-create database testdb;
+create database mysqltest;
 delimiter //;
-use testdb//
+use mysqltest//
 create procedure sp1 () 
 begin
   drop table if exists t1;
@@ -29,11 +37,11 @@ select database();
 
 use test;
 select database();
-call testdb.sp1();
+call mysqltest.sp1();
 select database();
 
-drop procedure testdb.sp1;
-drop database testdb;
+drop procedure mysqltest.sp1;
+drop database mysqltest;
 
 # BUG#8766
 
@@ -96,7 +104,7 @@ begin
   insert into t1 values(1);
   call sp1();
   select 't1', a from t1;
-  select 't2', b from t2;
+  select 't2', a from t2;
   drop table t1;
   drop table t2;
 end//
@@ -151,8 +159,6 @@ end //
 delimiter ;//
 call sp4();
 
-drop temporary table t1;
-drop temporary table t2;
 drop procedure sp1;
 drop procedure sp2;
 drop procedure sp3;
diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test
index 69529fd1ed0..15fcba5ebe9 100644
--- a/mysql-test/t/sp-security.test
+++ b/mysql-test/t/sp-security.test
@@ -15,7 +15,7 @@ grant usage on *.* to user1@localhost;
 flush privileges;
 
 --disable_warnings
-drop table if exists t1,t2;
+drop table if exists t1;
 drop database if exists db1_secret;
 --enable_warnings
 # Create our secret database
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index d70ce702daf..a41e54deb1a 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -147,9 +147,6 @@ drop procedure setcontext|
 
 
 # Set things to null
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 ( d date, i int, f double, s varchar(32) )|
 
 --disable_warnings
@@ -683,9 +680,6 @@ begin
 end|
 
 delete from t1|
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 ( s char(16), d int)|
 call into_test4()|
 select * from t3|
@@ -741,14 +735,9 @@ begin
   insert into test.t3 values (concat(x, "2"), y+2);
 end|
 
---disable_warnings
-drop table if exists t3|
---enable_warnings
 call create_select("cs", 90)|
 select * from t1, t3|
---disable_warnings
-drop table if exists t3|
---enable_warnings
+drop table t3|
 delete from t1|
 drop procedure create_select|
 
@@ -922,9 +911,6 @@ drop procedure hndlr3|
 # Variables might be uninitialized when using handlers
 # (Otherwise the compiler can detect if a variable is not set, but
 #  not in this case.)
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 ( id   char(16), data int )|
 
 --disable_warnings
@@ -977,9 +963,6 @@ call cur1()|
 select * from t1|
 drop procedure cur1|
 
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 ( s char(16), i int )|
 
 --disable_warnings
@@ -1611,9 +1594,6 @@ insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)|
 select t1max()|
 drop function t1max|
 
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 (
   v char(16) not null primary key,
   c int unsigned not null
@@ -1747,9 +1727,6 @@ drop procedure bug1656|
 #
 # BUG#1862
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3(a int)|
 
 --disable_warnings
@@ -2006,9 +1983,6 @@ drop procedure bug2776_2|
 #
 # BUG#2780
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 (s1 smallint)|
 
 insert into t3 values (123456789012)|
@@ -2082,9 +2056,6 @@ drop table t3, t4|
 #
 # BUG#2656
 #
---disable_warnings
-drop table if exists t3, t4|
---enable_warnings
 
 create table t3 ( 
   OrderID  int not null,
@@ -2172,8 +2143,6 @@ drop procedure bug3426|
 # BUG#3448
 #
 --disable_warnings
-drop table if exists t3, t4|
-
 create table t3 (
   a int primary key, 
   ach char(1)
@@ -2205,9 +2174,6 @@ drop table t3, t4|
 #
 # BUG#3734
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 (
   id int unsigned auto_increment not null primary key,
   title VARCHAR(200),
@@ -2383,9 +2349,6 @@ drop procedure bug3843|
 #
 # BUG#3368
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 ( s1 char(10) )|
 insert into t3 values ('a'), ('b')|
 
@@ -2405,9 +2368,6 @@ drop table t3|
 #
 # BUG#4579
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 (f1 int, f2 int)|
 insert into t3 values (1,1)|
 
@@ -2442,7 +2402,6 @@ drop table t3|
 # BUG#2773: Function's data type ignored in stored procedures
 #
 --disable_warnings
-drop table if exists t3|
 drop procedure if exists bug2773|
 --enable_warnings
 
@@ -2471,10 +2430,6 @@ drop function bug3788|
 #
 # BUG#4726
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
- 
 create table t3 (f1 int, f2 int, f3 int)|
 insert into t3 values (1,1,1)|
 
@@ -2505,9 +2460,6 @@ drop table t3|
 # BUG#4318
 #
 #QQ Don't know if HANDLER commands can work with SPs, or at all...
-#--disable_warnings
-#drop table if exists t3|
-#--enable_warnings
 # 
 #create table t3 (s1 int)|
 #insert into t3 values (3), (4)|
@@ -2588,10 +2540,6 @@ drop procedure bug4902_2|
 #
 # BUG#4904
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
-
 --disable_warnings
 drop procedure if exists bug4904|
 --enable_warnings
@@ -2845,7 +2793,6 @@ drop procedure bug3583|
 # BUG#4905: Stored procedure doesn't clear for "Rows affected"
 #
 --disable_warnings
-drop table if exists t3|
 drop procedure if exists bug4905|
 --enable_warnings
 
@@ -2945,9 +2892,6 @@ drop procedure bug8540|
 #
 # BUG#6642: Stored procedure crash if expression with set function
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 (s1 int)|
 
 --disable_warnings
@@ -3035,9 +2979,6 @@ drop table t3|
 # BUG#8116: calling simple stored procedure twice in a row results
 #           in server crash
 #
---disable_warnings
-drop table if exists t3|
---enable_warnings
 create table t3 (  userid bigint(20) not null default 0 )|
 
 --disable_warnings
@@ -3280,7 +3221,6 @@ delete from t1|
 drop procedure if exists bug6900|
 drop procedure if exists bug9074|
 drop procedure if exists bug6900_9074|
-drop table if exists t3|
 --enable_warnings
 
 create table t3 (w char unique, x char)|
diff --git a/sql/sp.cc b/sql/sp.cc
index 3d513b16d07..0cc1c988217 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -1176,40 +1176,39 @@ extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first)
 
 
 /*
-  Check if routines in routines_list require sp_cache_routines_and_add_tables
-  call.
+  Check if
+   - current statement (the one in thd->lex) needs table prelocking
+   - first routine in thd->lex->sroutines_list needs to execute its body in
+     prelocked mode.
 
   SYNOPSIS
-    sp_need_cache_routines()
-      thd
-      routines 
-      need_skip_first  OUT TRUE - don't do prelocking for the 1st element in 
-                                  routines list.
-                           FALSE- otherwise
+    sp_get_prelocking_info()
+      thd                  Current thread, thd->lex is the statement to be
+                           checked.
+      need_prelocking      OUT TRUE  - prelocked mode should be activated
+                                       before executing the statement
+                               FALSE - Don't activate prelocking 
+      first_no_prelocking  OUT TRUE  - Tables used by first routine in
+                                       thd->lex->sroutines_list should be
+                                       prelocked.
+                               FALSE - Otherwise.
   NOTES 
     This function assumes that for any "CALL proc(...)" statement routines_list 
     will have 'proc' as first element (it may have several, consider e.g.
     "proc(sp_func(...)))". This property is currently guaranted by the parser.
-
-  RETURN
-    TRUE  Need to sp_cache_routines_and_add_tables call for this statement.
-    FALSE Otherwise.
 */
 
-bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, bool *need_skip_first)
+void sp_get_prelocking_info(THD *thd, bool *need_prelocking, 
+                            bool *first_no_prelocking)
 {
   Sroutine_hash_entry *routine;
-  routine= (Sroutine_hash_entry*)routines_list->first;
-
-  *need_skip_first= FALSE;
-  if (!routine)
-    return FALSE;
+  routine= (Sroutine_hash_entry*)thd->lex->sroutines_list.first;
 
-  if (routine->key.str[0] != TYPE_ENUM_PROCEDURE)
-    return TRUE;
+  DBUG_ASSERT(routine);
+  bool first_is_procedure= (routine->key.str[0] == TYPE_ENUM_PROCEDURE);
 
-  *need_skip_first= TRUE;
-  return TRUE;
+  *first_no_prelocking= first_is_procedure;
+  *need_prelocking= !first_is_procedure || test(routine->next);
 }
 
 
diff --git a/sql/sp.h b/sql/sp.h
index 716f3d90f55..3c837f8b586 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -79,8 +79,8 @@ sp_show_status_function(THD *thd, const char *wild);
   Procedures for pre-caching of stored routines and building table list
   for prelocking.
 */
-bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, 
-                            bool *need_skip_first);
+void sp_get_prelocking_info(THD *thd, bool *need_prelocking, 
+                            bool *first_no_prelocking);
 void sp_add_used_routine(LEX *lex, Query_arena *arena,
                          sp_name *rt, char rt_type);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
diff --git a/sql/sp_cache.h b/sql/sp_cache.h
index 5873c763289..1ea71160a3a 100644
--- a/sql/sp_cache.h
+++ b/sql/sp_cache.h
@@ -25,7 +25,8 @@
 /*
   Stored procedures/functions cache. This is used as follows:
    * Each thread has its own cache.
-   * When SP is used it is always in some thread's cache.
+   * Each sp_head object is put into its thread cache after creation and is
+     removed from there on its deletion.
 */
 
 class sp_head;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 8d56e2a0b38..7002bc7b020 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -921,7 +921,8 @@ sp_head::execute_procedure(THD *thd, List<Item> *args)
 
     /* 
       Okay, got values for all arguments. Close tables that might be used by 
-      arguments evaluation.
+      arguments evaluation. If arguments evaluation required prelocking mode, 
+      we'll leave it here.
     */
     if (!thd->in_sub_stmt)
       close_thread_tables(thd, 0, 0, 0);
@@ -1492,8 +1493,6 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
            instruction if it is not really used.
   */
 
-  bool collect_prelocking_tail= FALSE;
-
   if (thd->prelocked_mode == NON_PRELOCKED)
   {
     /*
@@ -1511,14 +1510,6 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
       *lex_query_tables_own_last= prelocking_tables;
       m_lex->mark_as_requiring_prelocking(lex_query_tables_own_last);
     }
-    else
-    {
-      /* 
-        Let open_tables_calculate list of tables that this statement needs
-        to have prelocked.
-      */
-      collect_prelocking_tail= TRUE;
-    }
   }
     
   reinit_stmt_before_use(thd, m_lex);
@@ -1539,34 +1530,25 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
   thd->proc_info="closing tables";
   close_thread_tables(thd);
 
-  if (thd->prelocked_mode == NON_PRELOCKED)
+  if (m_lex->query_tables_own_last)
   {
-    if (!lex_query_tables_own_last)
-      lex_query_tables_own_last= thd->lex->query_tables_own_last;
-      
-    if (lex_query_tables_own_last)
-    {
-      if (collect_prelocking_tail)
-      {
-        /*
-          This is the first time this statement has entered/left prelocked
-          mode on its own. open_tables() has calculated the set of tables this
-          statement needs to have prelocked and added them to the end of
-          m_lex->query_tables(->next_global)*.
-          Save this "tail" for subsequent calls (and restore original list 
-          below)
-        */
-        lex_query_tables_own_last= m_lex->query_tables_own_last;
-        prelocking_tables= *lex_query_tables_own_last;
-      }
-      /*
-        The table list now has list of tables that need to be prelocked
-        when this statement executes, chop it off, and mark this statement 
-        as not requiring prelocking.
-      */
-      *lex_query_tables_own_last= NULL;
-      m_lex->mark_as_requiring_prelocking(NULL);
-    }
+    /*
+      We've entered and left prelocking mode when executing statement
+      stored in m_lex. 
+      m_lex->query_tables(->next_global)* list now has a 'tail' - a list
+      of tables that are added for prelocking. (If this is the first
+      execution, the 'tail' was added by open_tables(), otherwise we've
+      attached it above in this function).
+      Now we'll save the 'tail', and detach it.
+    */
+    DBUG_ASSERT(!lex_query_tables_own_last ||
+                lex_query_tables_own_last == m_lex->query_tables_own_last &&
+                prelocking_tables == *(m_lex->query_tables_own_last));
+
+    lex_query_tables_own_last= m_lex->query_tables_own_last;
+    prelocking_tables= *lex_query_tables_own_last;
+    *lex_query_tables_own_last= NULL;
+    m_lex->mark_as_requiring_prelocking(NULL);
   }
   thd->rollback_item_tree_changes();
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 367bd2c5ade..90e31edd9bb 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1865,23 +1865,21 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
           document new prelocked behavior.
   */
   
-  if (!thd->prelocked_mode && !thd->lex->requires_prelocking())
+  if (!thd->prelocked_mode && !thd->lex->requires_prelocking() && 
+      thd->lex->sroutines_list.elements)
   {
-    bool first_no_prelocking;
-    if (sp_need_cache_routines(thd, &thd->lex->sroutines_list,
-                               &first_no_prelocking))
-    {
-      TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
+    bool first_no_prelocking, need_prelocking;
+    TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
 
-      DBUG_ASSERT(thd->lex->query_tables == *start);
+    DBUG_ASSERT(thd->lex->query_tables == *start);
+    sp_get_prelocking_info(thd, &need_prelocking, &first_no_prelocking);
 
-      if (sp_cache_routines_and_add_tables(thd, thd->lex,
-                                           first_no_prelocking) ||
-          *start)
-      {
-        query_tables_last_own= save_query_tables_last;
-        *start= thd->lex->query_tables;
-      }
+    if ((sp_cache_routines_and_add_tables(thd, thd->lex,
+                                         first_no_prelocking) ||
+        *start) && need_prelocking)
+    {
+      query_tables_last_own= save_query_tables_last;
+      *start= thd->lex->query_tables;
     }
   }
 
@@ -1917,8 +1915,9 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
           2) Tables used by all stored routines that this statement invokes on
              execution.
           We need to know where the bound between these two parts is. If we've
-          just opened the last table in part #1, and it added tables after
-          itself, adjust the boundary pointer accordingly.
+          just opened a view, which was the last table in part #1, and it
+          has added its base tables after itself, adjust the boundary pointer
+          accordingly.
         */
         if (query_tables_last_own &&
             query_tables_last_own == &(tables->next_global) &&
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 81cdc6562e9..9b0decb3950 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -174,11 +174,11 @@ THD::THD()
   :Statement(CONVENTIONAL_EXECUTION, 0, ALLOC_ROOT_MIN_BLOCK_SIZE, 0),
    Open_tables_state(),
    lock_id(&main_lock_id),
-   user_time(0), global_read_lock(0), is_fatal_error(0),
+   user_time(0), in_sub_stmt(FALSE), global_read_lock(0), is_fatal_error(0),
    rand_used(0), time_zone_used(0),
    last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0),
    in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE),
-   spcont(NULL), in_sub_stmt(FALSE)
+   spcont(NULL)
 {
   current_arena= this;
   host= user= priv_user= db= ip= 0;
-- 
cgit v1.2.1