Better approach for prelocking of tables for stored routines execution

and some SP-related cleanups.

- We don't have separate stage for calculation of list of tables
  to be prelocked and doing implicit LOCK/UNLOCK any more.
  Instead we calculate this list at open_tables() and do implicit
  LOCK in lock_tables() (and UNLOCK in close_thread_tables()).
  Also now we support cases when same table (with same alias) is
  used several times in the same query in SP.

- Cleaned up execution of SP. Moved all common code which handles
  LEX and does preparations before statement execution or complex
  expression evaluation to auxilary sp_lex_keeper class. Now 
  all statements in SP (and corresponding instructions) that
  evaluate expression which can contain subquery have their
  own LEX.


mysql-test/r/lock.result:
  Replaced wrong error code with the correct one after fixing bug in
  SP-locking.
mysql-test/r/mysqldump.result:
  Added dropping of view which is used in test to its beginning.
mysql-test/r/sp.result:
  Added tests for improved SP-locking.
  Temporarily disabled tests for SHOW PROCEDURE STATUS and alike
  (Until Monty will allow to open mysql.proc under LOCK TABLES without
  mentioning it in lock list).
  Replaced wrong results of test for bug #5240 with correct results after
  fixing bug in handling of cursors.
mysql-test/t/lock.test:
  Replaced wrong error code with the correct one after fixing bug in
  SP-locking.
mysql-test/t/mysqldump.test:
  Added dropping of view which is used in test to its beginning.
mysql-test/t/sp.test:
  Added tests for improved SP-locking.
  Temporarily disabled tests for SHOW PROCEDURE STATUS and alike
  (Until Monty will allow to open mysql.proc under LOCK TABLES without
  mentioning it in lock list).
  Removed test for bug #1654 since we already test exactly this function
  in one of SP-locking tests.
  Removed comment about cursor's wrong behavior in test for bug #5240
  after fixing bug which was its cause.
sql/item_func.cc:
  Removed comment which is no longer true.
sql/mysql_priv.h:
  Changed open_tables() signature.
  Now its 2nd parameter is in/out since it can add elements to table list.
sql/sp.cc:
  sp_find_procedure():
   Added one more parameter which enforces cache only lookup.
  
  sp_merge_hash():
   Now uses its return value to indicate that first of two hashes changed
   as result of merge.
  
  sp_cache_routines():
   This function caches all stored routines used in query now.
sql/sp.h:
  - sp_find_procedure() now has one more parameter which enforces cache only
    lookup.
  - sp_merge_hash() now uses its return value to indicate that first of two
    hashes changed as result of merge.
  - sp_cache_routines() caches all stored routines now. So it does not need
    third argument any more.
sql/sp_head.cc:
  sp_head::sp_head():
   Added initialization of new m_spfuns and m_spprocs members.
  
  sp_head::execute():
   Let us save/restore part of thread context which can be damaged by
   execution of instructions.
  sp_head::execute_function()/execute_procedure():
   Now it is responsibility of caller to close tables used in
   subqueries which are passed as routine parameters.
  
  sp_head::restore_lex():
   Let us accumulate information about routines used by this one
   in new m_spfuns, m_spprocs hashes.
  
  sp_lex_keeper::reset_lex_and_exec_core()
   Main method of new auxilary sp_lex_keeper class to which instructions 
   delegate responsibility for handling LEX and preparations before
   executing statement or calculating complex expression.
  
  Since all instructions which calculate complex expression or execute
  command now use sp_lex_keeper they have to implement
  sp_instr::exec_core() method. Most of instruction specific logic
  has moved from sp_instr::execute() to this new method.
  
  Removed sp_instr_set_user_var class which is no longer used, because
  nowdays we allow execution of statements in stored functions and
  triggers.
  
  sp_merge_table_list() became sp_head::merge_table_list() method. It
  also treats sp_head::m_sptabs as multi-set of tables now.
  
  sp_hash_to_table_list() became sp_head::add_used_tables_to_table_list().
  It takes into account that sp_head::m_sptabs is multi-set and allocates
  object into persistent arena of PS.
  
  Removed sp_merge_table_hash(), sp_open_and_lock_tables(),
  sp_unlock_tables(), sp_merge_routine_tables() methods since they are not
  used by new prelocking mechanism.
  
  Added sp_add_sp_tables_to_table_list() which serves for adding tables needed
  by routines used in query to the query table list for prelocking.
sql/sp_head.h:
  class sp_head:
  - Added m_spfuns, m_spprocs members for storing names of routines used
    by this routine.
  - Added add_used_tables_to_table_list() method which allows to add
    tables needed by this routine to query's table list.
  - Converted sp_merge_table_list() to sp_head::merge_table_list() method.
  - Changed semantics of THD::m_sptabs. Now it is multi-set which contains
    only tables which are used by this routine and not routines that are
    called from this one.
  
  Removed sp_merge_routine_tables(), sp_merge_table_hash(),
  sp_open_and_lock_tables(), sp_unlock_tables() calls since they are not
  used for our prelocking list calculation.
  
  Added auxilary sp_lex_keeper class to which instructions delegate
  responsibility for handling LEX and preparations before executing
  statement or calculating complex expression. This class uses
  new sp_instr::exec_core() method which is responsible for executing
  instruction's core function after all preparations were made.
  
  All instructions which hold and calculate complex expression now have
  their own LEX (by aggregating sp_lex_keeper instance). sp_instr_stmt
  now uses sp_lex_keeper too.
  
  Removed sp_instr_set_user_var class which is no longer used, because
  nowdays we allow execution of statements in stored functions and
  triggers.
sql/sp_rcontext.cc:
  Now sp_cursor holds pointer to sp_lex_keeper instead of LEX.
sql/sp_rcontext.h:
  Now sp_cursor holds pointer to sp_lex_keeper instead of LEX.
sql/sql_acl.cc:
  acl_init(), grant_init():
    Now we use simple_open_n_lock_tables() instead of explicit
    calls to open_tables() and mysql_lock_tables().
sql/sql_base.cc:
  Implemented support for execution of statements in "prelocked" mode.
  
  When we have statement which uses stored routines explicitly or
  implicitly (via views or triggers) we have to open and lock all tables
  for these routines at the same time as tables for the main statement.
  In fact we have to do implicit LOCK TABLES at the begining of such
  statement and implict UNLOCK TABLES at its end. We call such mode
  "prelocked".
  
  When open_tables() is called for the statement tables which are needed
  for execution of routines used by it are added to its tables list
  (this process also caches all routines used). Implicit use of routines
  is discovered when we open view or table with trigger and apropriate
  tables are added to the table list at this moment. Statement which has
  such extra tables in its list (well actually any that uses functions)
  is marked as requiring prelocked mode for its execution.
  
  When lock_tables() sees such statement it will issue implicit LOCK TABLES
  for this extended table list instead of doing usual locking, it will also
  set THD::prelocked_mode to indicate that we are in prelocked mode.
  
  When open_tables()/lock_tables() are called for statement of stored
  routine (substatement), they notice that we are running in prelocked mode
  and use one of prelocked tables from those that are not used by upper
  levels of execution.
  
  close_thread_tables() for substatement won't really close tables used
  but will mark them as free for reuse instead.
  
  Finally when close_thread_tables() is called for the main statement it
  really unlocks and closes all tables used.
  
  Everything will work even if one uses such statement under real LOCK
  TABLES (we are simply not doing implicit LOCK/UNLOCK in this case).
sql/sql_class.cc:
  Added initialization of THD::prelocked_mode member.
sql/sql_class.h:
  - Added prelocked_mode_type enum and THD::prelocked_mode member
    which are used for indication whenever "prelocked mode" is on 
    (i.e. that statement uses stored routines and is executed under
     implicit LOCK TABLES).
  - Removed THD::shortcut_make_view which is no longer needed.
    We use TABLE_LIST::prelocking_placeholder for the same purprose
    now.
sql/sql_handler.cc:
  Changed open_tables() invocation.
  Now its 2nd parameter is in/out since it can add elements to table list.
sql/sql_lex.cc:
  lex_start():
    Added initialization of LEX::query_tables_own_last.
    Unused LEX::sptabs member was removed.
  st_lex::unlink_first_table()/link_first_table_back():
    We should update LEX::query_tables_last properly if table list
    contains(ed) only one element.
sql/sql_lex.h:
  LEX:
  - Removed sptabs member since it is no longer used.
  - Added query_tables_own_last member, which if non-0 indicates that
    statement requires prelocking (implicit LOCK TABLES) for its execution
    and points to last own element in query table list. If it is zero
    then this query does not need prelocking.
  - Added requires_prelocking(), mark_as_requiring_prelocking(),
    first_not_own_table() inline methods to incapsulate and simplify
    usage of this new member.
sql/sql_parse.cc:
  dispatch_command():
    To properly leave prelocked mode when needed we should call
    close_thread_tables() even if there are no open tables.
  mysql_execute_command():
  - Removed part of function which were responsible for doing implicit
    LOCK TABLES before statement execution if statement used stored 
    routines (and doing UNLOCK TABLES at the end).
    Now we do all this in open_tables()/lock_tables()/close_thread_tables()
    instead.
  - It is also sensible to reset errors before execution of statement
    which uses routines.
  - SQLCOM_DO, SQLCOM_SET_OPTION, SQLCOM_CALL
    We should always try to open tables because even if statement has empty
    table list, it can call routines using tables, which should be preopened
    before statement execution.
  - SQLCOM_CALL
    We should not look up routine called in mysql.proc, since it should be
    already cached by this moment by open_tables() call.
  - SQLCOM_LOCK_TABLES
    it is better to use simple_open_n_lock_tables() since we want to avoid
    materialization of derived tables for this command.
sql/sql_prepare.cc:
  mysql_test_update():
    Changed open_tables() invocations. Now its 2nd parameter is in/out
    since it can add elements to table list.
  check_prepared_statement():
    Since now we cache all routines used by statement in open_tables() we 
    don't need to do it explicitly.
  mysql_stmt_prepare():
    Now we should call close_thread_tables() when THD::lex points to the
    LEX of statement which opened tables.
  reset_stmt_for_execute():
    Commented why we are resetting all tables in table list.
sql/sql_trigger.h:
  Table_triggers_list::process_triggers():
    We should surpress sending of ok packet when we are calling trigger's
    routine, since now we allow statements in them.
sql/sql_update.cc:
  Changed open_tables() invocations.
  Now its 2nd parameter is in/out since it can add elements to table list.
sql/sql_view.cc:
  mysql_make_view():
  - Removed handling of routines used in view. Instead we add tables which
    are needed for their execution to statement's table list in 
    open_tables().
  - Now we use TABLE_LIST::prelocking_placeholder instead of 
    THD::shortcut_make_view for indicating that view is opened
    only to discover which tables and routines it uses (this happens
    when we build extended table list for prelocking). Also now we try
    to avoid to modify main LEX in this case (except of its table list).
  - Corrected small error we added tables to the table list of the main
    LEX without updating its query_tables_last member properly.
sql/sql_yacc.yy:
  Now each expression which is used in SP statements and can contain
  subquery has its own LEX. This LEX is stored in corresponding sp_instr
  object and used along with Item tree for expression calculation.
  
  We don't need sp_instr_set_user_var() anymore since now we allow
  execution of statements in stored functions and triggers.
sql/table.h:
  Added TABLE_LIST::prelocking_placeholder member for distinguishing
  elements of table list which does not belong to the statement itself
  and added there only for prelocking (as they are to be used by routines
  called by this statement).
sql/tztime.cc:
  my_tz_init():
    Now we use more simplier simple_open_n_lock_tables() call instead of 
    open_tables()/lock_tables() pair.

29 files changed, 1798 insertions, 871 deletions

diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result
index db2842061b4..16c92fa201f 100644
--- a/mysql-test/r/lock.result
+++ b/mysql-test/r/lock.result
@@ -42,7 +42,7 @@ check table t2;
 Table	Op	Msg_type	Msg_text
 test.t2	check	error	Table 't2' was not locked with LOCK TABLES
 insert into t1 select index1,nr from t1;
-ERROR 42000: INSERT command denied to user 'root'@'localhost' for column 'index1' in table 't1'
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
 unlock tables;
 lock tables t1 write, t1 as t1_alias read;
 insert into t1 select index1,nr from t1 as t1_alias;
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 9f08fae4964..cb02d596a8d 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -1,5 +1,6 @@
 DROP TABLE IF EXISTS t1, `"t"1`, t1aa,t2aa;
 drop database if exists mysqldump_test_db;
+drop view if exists v1;
 CREATE TABLE t1(a int);
 INSERT INTO t1 VALUES (1), (2);
 <?xml version="1.0"?>
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 0af6b821ce0..af58b551a17 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -237,6 +237,13 @@ insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
 drop procedure if exists sub1|
 create procedure sub1(id char(16), x int)
 insert into test.t1 values (id, x)|
+drop procedure if exists sub2|
+create procedure sub2(id char(16))
+begin
+declare x int;
+set x = (select sum(t.i) from test.t2 t);
+insert into test.t1 values (id, x);
+end|
 drop procedure if exists sub3|
 create function sub3(i int) returns int
 return i+1|
@@ -244,16 +251,19 @@ call sub1("sub1a", (select 7))|
 call sub1("sub1b", (select max(i) from t2))|
 call sub1("sub1c", (select i,d from t2 limit 1))|
 call sub1("sub1d", (select 1 from (select 1) a))|
+call sub2("sub2");
 select * from t1|
 id	data
 sub1a	7
 sub1b	3
 sub1c	1
 sub1d	1
+sub2	6
 select sub3((select max(i) from t2))|
 sub3((select max(i) from t2))
 4
 drop procedure sub1|
+drop procedure sub2|
 drop function sub3|
 delete from t2|
 drop procedure if exists a0|
@@ -269,6 +279,7 @@ sub1a	7
 sub1b	3
 sub1c	1
 sub1d	1
+sub2	6
 a0	2
 a0	1
 a0	0
@@ -1046,6 +1057,200 @@ select row_count()|
 row_count()
 -1
 drop procedure rc|
+drop function if exists f0|
+drop function if exists f1|
+drop function if exists f2|
+drop function if exists f3|
+drop function if exists f4|
+drop function if exists f5|
+drop function if exists f6|
+drop function if exists f7|
+drop function if exists f8|
+drop view if exists v0|
+drop view if exists v1|
+drop view if exists v2|
+delete from t1|
+delete from t2|
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+create function f1() returns int
+return (select sum(data) from t1)|
+select f1()|
+f1()
+3
+select id, f1() from t1|
+id	f1()
+a	3
+b	3
+create function f2() returns int
+return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
+select f2()|
+f2()
+1
+select id, f2() from t1|
+id	f2()
+a	1
+b	1
+create function f3() returns int
+begin
+declare n int;
+declare m int;
+set n:= (select min(data) from t1);
+set m:= (select max(data) from t1);
+return n < m;
+end|
+select f3()|
+f3()
+1
+select id, f3() from t1|
+id	f3()
+a	1
+b	1
+select f1(), f3()|
+f1()	f3()
+3	1
+select id, f1(), f3() from t1|
+id	f1()	f3()
+a	3	1
+b	3	1
+create function f4() returns double 
+return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
+select f4()|
+f4()
+2
+select s, f4() from t2|
+s	f4()
+a	2
+b	2
+c	2
+create function f5(i int) returns int
+begin
+if i <= 0 then
+return 0;
+elseif i = 1  then
+return (select count(*) from t1 where data = i);
+else
+return (select count(*) + f5( i - 1) from t1 where data = i);
+end if;
+end|
+select f5(1)|
+f5(1)
+1
+select f5(2)|
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+create function f6() returns int
+begin
+declare n int;
+set n:= f1();
+return (select count(*) from t1 where data <= f7() and data <= n);
+end|
+create function f7() returns int
+return (select sum(data) from t1 where data <= f1())|
+select f6()|
+f6()
+2
+select id, f6() from t1|
+id	f6()
+a	2
+b	2
+create view v1 (a) as select f1()|
+select * from v1|
+a
+3
+select id, a from t1, v1|
+id	a
+a	3
+b	3
+select * from v1, v1 as v|
+a	a
+3	3
+create view v2 (a) as select a*10 from v1|
+select * from v2|
+a
+30
+select id, a from t1, v2|
+id	a
+a	30
+b	30
+select * from v1, v2|
+a	a
+3	30
+create function f8 () returns int
+return (select count(*) from v2)|
+select *, f8() from v1|
+a	f8()
+3	1
+drop function f1|
+select * from v1|
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s)
+create function f1() returns int
+return (select sum(data) from t1) + (select sum(data) from v1)|
+drop function f1|
+create function f1() returns int
+return (select sum(data) from t1)|
+create function f0() returns int
+return (select * from (select 100) as r)|
+select f0()|
+f0()
+100
+select *, f0() from (select 1) as t|
+1	f0()
+1	100
+create view v0 as select f0()|
+select * from v0|
+f0()
+100
+select *, f0() from v0|
+f0()	f0()
+100	100
+lock tables t1 read, t1 as t11 read, mysql.proc read|
+select f3()|
+f3()
+1
+select id, f3() from t1 as t11|
+id	f3()
+a	1
+b	1
+select f0()|
+f0()
+100
+select * from v0|
+f0()
+100
+select *, f0() from v0, (select 123) as d1|
+f0()	123	f0()
+100	123	100
+select id, f3() from t1|
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+select f4()|
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables|
+lock tables v2 read, mysql.proc read|
+select * from v2|
+a
+30
+select * from v1|
+a
+3
+select * from v1, v2|
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+select f4()|
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables|
+drop function f0|
+drop function f1|
+drop function f2|
+drop function f3|
+drop function f4|
+drop function f5|
+drop function f6|
+drop function f7|
+drop function f8|
+drop view v0|
+drop view v1|
+drop view v2|
+delete from t1 |
+delete from t2 |
 drop procedure if exists bug822|
 create procedure bug822(a_id char(16), a_data int)
 begin
@@ -1178,56 +1383,6 @@ select @x2|
 @x2
 2
 drop procedure bug2260|
-drop procedure if exists bug2267_1|
-create procedure bug2267_1()
-begin
-show procedure status;
-end|
-drop procedure if exists bug2267_2|
-create procedure bug2267_2()
-begin
-show function status;
-end|
-drop procedure if exists bug2267_3|
-create procedure bug2267_3()
-begin
-show create procedure bug2267_1;
-end|
-drop procedure if exists bug2267_4|
-create procedure bug2267_4()
-begin
-show create function fac;
-end|
-call bug2267_1()|
-Db	Name	Type	Definer	Modified	Created	Security_type	Comment
-test	bug2267_1	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-test	bug2267_2	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-test	bug2267_3	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-test	bug2267_4	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-call bug2267_2()|
-Db	Name	Type	Definer	Modified	Created	Security_type	Comment
-test	fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-call bug2267_3()|
-Procedure	sql_mode	Create Procedure
-bug2267_1		CREATE PROCEDURE `test`.`bug2267_1`()
-begin
-show procedure status;
-end
-call bug2267_4()|
-Function	sql_mode	Create Function
-fac		CREATE FUNCTION `test`.`fac`(n int unsigned) RETURNS bigint unsigned
-begin
-declare f bigint unsigned default 1;
-while n > 1 do
-set f = f * n;
-set n = n - 1;
-end while;
-return f;
-end
-drop procedure bug2267_1|
-drop procedure bug2267_2|
-drop procedure bug2267_3|
-drop procedure bug2267_4|
 drop procedure if exists bug2227|
 create procedure bug2227(x int)
 begin
@@ -1332,7 +1487,7 @@ declare t2 int;
 declare t3 int;
 declare rc int default 0;
 declare continue handler for 1065 set rc = 1;
-drop table if exists temp_t1;
+drop temporary table if exists temp_t1;
 create temporary table temp_t1 (
 f1 int auto_increment, f2 varchar(20), primary key (f1)
 );
@@ -1352,6 +1507,7 @@ f1	rc	t3
 2	0	NULL
 2	0	NULL
 drop procedure bug1863|
+drop temporary table temp_t1;
 drop table t3, t4|
 drop table if exists t3, t4|
 create table t3 ( 
@@ -2366,7 +2522,7 @@ delete from t1|
 insert into t1 values ("answer", 42)|
 select id, bug5240() from t1|
 id	bug5240()
-42	42
+answer	42
 drop function bug5240|
 drop function if exists bug5278|
 create function bug5278 () returns char
diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test
index 80da2cad192..faa1fa3ac25 100644
--- a/mysql-test/t/lock.test
+++ b/mysql-test/t/lock.test
@@ -53,7 +53,7 @@ check table t1;
 # Check error message
 lock tables t1 write;
 check table t2;
---error 1143
+--error 1100
 insert into t1 select index1,nr from t1;
 unlock tables;
 lock tables t1 write, t1 as t1_alias read;
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 3f19c7f0c52..429812eb74b 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -1,6 +1,7 @@
 --disable_warnings
 DROP TABLE IF EXISTS t1, `"t"1`, t1aa,t2aa;
 drop database if exists mysqldump_test_db;
+drop view if exists v1;
 --enable_warnings
 
 # XML output
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index d474fb1c84e..4587d4b6990 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -339,16 +339,15 @@ drop procedure if exists sub1|
 create procedure sub1(id char(16), x int)
   insert into test.t1 values (id, x)|
 
-# QQ This doesn't work yet
-#--disable_warnings
-#drop procedure if exists sub2|
-#--enable_warnings
-#create procedure sub2(id char(16))
-#begin
-#  declare x int;
-#  set x = (select sum(t.x) from test.t2 t);
-#  insert into test.t1 values (id, x);
-#end|
+--disable_warnings
+drop procedure if exists sub2|
+--enable_warnings
+create procedure sub2(id char(16))
+begin
+  declare x int;
+  set x = (select sum(t.i) from test.t2 t);
+  insert into test.t1 values (id, x);
+end|
 
 --disable_warnings
 drop procedure if exists sub3|
@@ -360,11 +359,11 @@ call sub1("sub1a", (select 7))|
 call sub1("sub1b", (select max(i) from t2))|
 call sub1("sub1c", (select i,d from t2 limit 1))|
 call sub1("sub1d", (select 1 from (select 1) a))|
-#call sub2("sub2");
+call sub2("sub2");
 select * from t1|
 select sub3((select max(i) from t2))|
 drop procedure sub1|
-#drop procedure sub2|
+drop procedure sub2|
 drop function sub3|
 delete from t2|
 
@@ -1279,6 +1278,202 @@ drop procedure rc|
 
 
 #
+# Let us test how well new locking scheme works.
+#
+
+# Let us prepare playground
+--disable_warnings
+drop function if exists f0|
+drop function if exists f1|
+drop function if exists f2|
+drop function if exists f3|
+drop function if exists f4|
+drop function if exists f5|
+drop function if exists f6|
+drop function if exists f7|
+drop function if exists f8|
+drop view if exists v0|
+drop view if exists v1|
+drop view if exists v2|
+--enable_warnings
+delete from t1|
+delete from t2|
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+
+# Test the simplest function using tables
+create function f1() returns int
+  return (select sum(data) from t1)|
+select f1()|
+# This should work too (and give 2 rows as result)
+select id, f1() from t1|
+
+# Function which uses two instances of table simultaneously
+create function f2() returns int
+  return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
+select f2()|
+select id, f2() from t1|
+
+# Function which uses the same table twice in different queries
+create function f3() returns int
+begin
+  declare n int;
+  declare m int;
+  set n:= (select min(data) from t1);
+  set m:= (select max(data) from t1);
+  return n < m;
+end|
+select f3()|
+select id, f3() from t1|
+
+# Calling two functions using same table
+select f1(), f3()|
+select id, f1(), f3() from t1|
+
+# Function which uses two different tables
+create function f4() returns double 
+  return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
+select f4()|
+select s, f4() from t2|
+
+# Recursive functions which due to this recursion require simultaneous
+# access to several instance of the same table won't work
+create function f5(i int) returns int
+begin
+  if i <= 0 then
+    return 0;
+  elseif i = 1  then
+    return (select count(*) from t1 where data = i);
+  else
+    return (select count(*) + f5( i - 1) from t1 where data = i);
+  end if;
+end|
+select f5(1)|
+# This should generate an error about insuficient number of tables locked
+--error 1100
+select f5(2)|
+# But now it simply miserably fails because we are trying to use the same
+# lex on the next iteration :/ It should generate some error too...
+# select f5(3)|
+
+# OTOH this should work 
+create function f6() returns int
+begin
+  declare n int;
+  set n:= f1();
+  return (select count(*) from t1 where data <= f7() and data <= n);
+end|
+create function f7() returns int
+  return (select sum(data) from t1 where data <= f1())|
+select f6()|
+select id, f6() from t1|
+
+# TODO Test temporary table handling
+
+#
+# Let us test how new locking work with views
+#
+# The most trivial view
+create view v1 (a) as select f1()|
+select * from v1|
+select id, a from t1, v1|
+select * from v1, v1 as v|
+# A bit more complex construction
+create view v2 (a) as select a*10 from v1|
+select * from v2|
+select id, a from t1, v2|
+select * from v1, v2|
+
+# Nice example where the same view is used on
+# on different expression levels
+create function f8 () returns int
+  return (select count(*) from v2)|
+
+select *, f8() from v1|
+
+# Let us test what will happen if function is missing
+drop function f1|
+--error 1356
+select * from v1|
+
+# And what will happen if we have recursion which involves
+# views and functions ?
+create function f1() returns int
+  return (select sum(data) from t1) + (select sum(data) from v1)|
+# FIXME All these just exceed file limit for me :)
+#select f1()|
+#select * from v1|
+#select * from v2|
+# Back to the normal cases
+drop function f1|
+create function f1() returns int
+  return (select sum(data) from t1)|
+
+# Let us also test some weird cases where no real tables is used
+create function f0() returns int
+  return (select * from (select 100) as r)|
+select f0()|
+select *, f0() from (select 1) as t|
+create view v0 as select f0()|
+select * from v0|
+select *, f0() from v0|
+
+#
+# Let us test how well prelocking works with explicit LOCK TABLES.
+#
+# Nowdays we have to lock mysql.proc to be able to read SP definitions.
+# But Monty was going to fix this.
+lock tables t1 read, t1 as t11 read, mysql.proc read|
+# These should work well
+select f3()|
+select id, f3() from t1 as t11|
+# Degenerate cases work too :)
+select f0()|
+select * from v0|
+select *, f0() from v0, (select 123) as d1|
+# But these should not !
+--error 1100
+select id, f3() from t1|
+--error 1100
+select f4()|
+unlock tables|
+
+# Let us test how LOCK TABLES which implicitly depends on functions
+# works
+lock tables v2 read, mysql.proc read|
+select * from v2|
+select * from v1|
+# These should not work as we have too little instances of tables locked
+--error 1100
+select * from v1, v2|
+--error 1100
+select f4()|
+unlock tables|
+
+
+# TODO We also should test integration with triggers
+
+
+# Cleanup
+drop function f0|
+drop function f1|
+drop function f2|
+drop function f3|
+drop function f4|
+drop function f5|
+drop function f6|
+drop function f7|
+drop function f8|
+drop view v0|
+drop view v1|
+drop view v2|
+delete from t1 |
+delete from t2 |
+
+# End of non-bug tests
+
+
+#
 # Test cases for old bugs
 #
 
@@ -1453,49 +1648,56 @@ drop procedure bug2260|
 #
 # BUG#2267
 #
---disable_warnings
-drop procedure if exists bug2267_1|
---enable_warnings
-create procedure bug2267_1()
-begin
-  show procedure status;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_2|
---enable_warnings
-create procedure bug2267_2()
-begin
-  show function status;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_3|
---enable_warnings
-create procedure bug2267_3()
-begin
-  show create procedure bug2267_1;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_4|
---enable_warnings
-create procedure bug2267_4()
-begin
-  show create function fac;
-end|
-
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-call bug2267_1()|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-call bug2267_2()|
-call bug2267_3()|
-call bug2267_4()|
-
-drop procedure bug2267_1|
-drop procedure bug2267_2|
-drop procedure bug2267_3|
-drop procedure bug2267_4|
+# NOTE: This test case will be fixed as soon as Monty
+#       will allow to open mysql.proc table under LOCK TABLES
+#       without mentioning in lock list.
+#       
+# FIXME: Other solution would be to use preopened proc table
+#        instead of opening it anew.
+# 
+#--disable_warnings
+#drop procedure if exists bug2267_1|
+#--enable_warnings
+#create procedure bug2267_1()
+#begin
+#  show procedure status;
+#end|
+#
+#--disable_warnings
+#drop procedure if exists bug2267_2|
+#--enable_warnings
+#create procedure bug2267_2()
+#begin
+#  show function status;
+#end|
+#
+#--disable_warnings
+#drop procedure if exists bug2267_3|
+#--enable_warnings
+#create procedure bug2267_3()
+#begin
+#  show create procedure bug2267_1;
+#end|
+#
+#--disable_warnings
+#drop procedure if exists bug2267_4|
+#--enable_warnings
+#create procedure bug2267_4()
+#begin
+#  show create function fac;
+#end|
+#
+#--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+#call bug2267_1()|
+#--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+#call bug2267_2()|
+#call bug2267_3()|
+#call bug2267_4()|
+#
+#drop procedure bug2267_1|
+#drop procedure bug2267_2|
+#drop procedure bug2267_3|
+#drop procedure bug2267_4|
 
 #
 # BUG#2227
@@ -1529,7 +1731,7 @@ drop procedure bug2227|
 #--enable_warnings
 #create procedure bug2614()
 #begin
-#  drop table if exists t3;
+#  drop temporary table if exists t3;
 #  create temporary table t3 (id int default '0' not null);
 #  insert into t3 select 12;
 #  insert into t3 select * from t3;
@@ -1539,7 +1741,7 @@ drop procedure bug2227|
 #call bug2614()|
 #--enable_warnings
 #call bug2614()|
-#drop table t3|
+#drop temporary table t3|
 #drop procedure bug2614|
 
 #
@@ -1680,7 +1882,7 @@ begin
   declare rc int default 0;
   declare continue handler for 1065 set rc = 1;
 
-  drop table if exists temp_t1;
+  drop temporary table if exists temp_t1;
   create temporary table temp_t1 (
     f1 int auto_increment, f2 varchar(20), primary key (f1)
   );
@@ -1702,6 +1904,7 @@ call bug1863(10)|
 select * from t4|
 
 drop procedure bug1863|
+drop temporary table temp_t1;
 drop table t3, t4|
 
 #
@@ -2800,15 +3003,6 @@ drop table t3|
 drop function getcount|
 
 #
-# Former BUG#1654
-# QQ Currently crashes
-#
-#create function bug1654() returns int
-#  return (select sum(t1.data) from test.t1 t)|
-#
-#select bug1654()|
-
-#
 # BUG#5240: Stored procedure crash if function has cursor declaration
 #
 --disable_warnings
@@ -2827,7 +3021,6 @@ end|
 
 delete from t1|
 insert into t1 values ("answer", 42)|
-# QQ BUG: This returns the wrong result, id=42 instead of "answer".
 select id, bug5240() from t1|
 drop function bug5240|
 
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 1e61474f412..61e447b17aa 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4370,11 +4370,6 @@ Item_func_sp::execute(Item **itp)
   }
 #endif
 
-  /*
-    We don't need to suppress sending of OK packet here (by setting
-    thd->net.no_send_ok to true), because we are not allowing statements
-    in functions now.
-  */
   res= m_sp->execute_function(thd, args, arg_count, itp);
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index fdcf061ab7a..c1808145220 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -845,7 +845,7 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves,
 int setup_ftfuncs(SELECT_LEX* select);
 int init_ftfuncs(THD *thd, SELECT_LEX* select, bool no_order);
 void wait_for_refresh(THD *thd);
-int open_tables(THD *thd, TABLE_LIST *tables, uint *counter);
+int open_tables(THD *thd, TABLE_LIST **tables, uint *counter);
 int simple_open_n_lock_tables(THD *thd,TABLE_LIST *tables);
 bool open_and_lock_tables(THD *thd,TABLE_LIST *tables);
 bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables);
diff --git a/sql/sp.cc b/sql/sp.cc
index 46b08c3e847..4ddd5b341d0 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -718,8 +718,29 @@ sp_drop_db_routines(THD *thd, char *db)
   PROCEDURE
 ******************************************************************************/
 
+/*
+  Obtain object representing stored procedure by its name from
+  stored procedures cache and looking into mysql.proc if needed.
+
+  SYNOPSIS
+    sp_find_procedure()
+      thd        - thread context
+      name       - name of procedure
+      cache_only - if true perform cache-only lookup
+                   (Don't look in mysql.proc).
+
+  TODO
+    We should consider merging of sp_find_procedure() and
+    sp_find_function() into one sp_find_routine() function
+    (the same applies to other similarly paired functions).
+
+  RETURN VALUE
+    Non-0 pointer to sp_head object for the procedure, or
+    0 - in case of error.
+*/
+
 sp_head *
-sp_find_procedure(THD *thd, sp_name *name)
+sp_find_procedure(THD *thd, sp_name *name, bool cache_only)
 {
   sp_head *sp;
   DBUG_ENTER("sp_find_procedure");
@@ -727,7 +748,7 @@ sp_find_procedure(THD *thd, sp_name *name)
 		       name->m_db.length, name->m_db.str,
 		       name->m_name.length, name->m_name.str));
 
-  if (!(sp= sp_cache_lookup(&thd->sp_proc_cache, name)))
+  if (!(sp= sp_cache_lookup(&thd->sp_proc_cache, name)) && !cache_only)
   {
     if (db_find_routine(thd, TYPE_ENUM_PROCEDURE, name, &sp) == SP_OK)
       sp_cache_insert(&thd->sp_proc_cache, sp);
@@ -853,6 +874,25 @@ sp_show_status_procedure(THD *thd, const char *wild)
   FUNCTION
 ******************************************************************************/
 
+/*
+  Obtain object representing stored function by its name from
+  stored functions cache and looking into mysql.proc if needed.
+
+  SYNOPSIS
+    sp_find_function()
+      thd        - thread context
+      name       - name of function
+      cache_only - if true perform cache-only lookup
+                   (Don't look in mysql.proc).
+
+  NOTE
+    See TODO section for sp_find_procedure().
+
+  RETURN VALUE
+    Non-0 pointer to sp_head object for the function, or
+    0 - in case of error.
+*/
+
 sp_head *
 sp_find_function(THD *thd, sp_name *name, bool cache_only)
 {
@@ -986,79 +1026,120 @@ sp_add_to_hash(HASH *h, sp_name *fun)
 }
 
 
-void
+/*
+  Merge contents of two hashes containing LEX_STRING's
+
+  SYNOPSIS
+    sp_merge_hash()
+      dst - hash to which elements should be added
+      src - hash from which elements merged
+
+  RETURN VALUE
+    TRUE  - if we have added some new elements to destination hash.
+    FALSE - there were no new elements in src.
+*/
+
+bool
 sp_merge_hash(HASH *dst, HASH *src)
 {
+  bool res= FALSE;
   for (uint i=0 ; i < src->records ; i++)
   {
     LEX_STRING *ls= (LEX_STRING *)hash_element(src, i);
 
     if (! hash_search(dst, (byte *)ls->str, ls->length))
+    {
       my_hash_insert(dst, (byte *)ls);
+      res= TRUE;
+    }
   }
+  return res;
 }
 
 
-int
-sp_cache_routines(THD *thd, LEX *lex, int type)
+/*
+  Cache all routines implicitly or explicitly used by query
+  (or whatever object is represented by LEX).
+
+  SYNOPSIS
+    sp_cache_routines()
+      thd - thread context
+      lex - LEX representing query
+
+  NOTE
+    If some function is missing this won't be reported here.
+    Instead this fact will be discovered during query execution.
+
+  TODO
+    Currently if after passing through routine hashes we discover
+    that we have added something to them, we do one more pass to
+    process all routines which were missed on previous pass because
+    of these additions. We can avoid this if along with hashes
+    we use lists holding routine names and iterate other these
+    lists instead of hashes (since addition to the end of list
+    does not reorder elements in it).
+*/
+
+void
+sp_cache_routines(THD *thd, LEX *lex)
 {
-  HASH *h= (type == TYPE_ENUM_FUNCTION ? &lex->spfuns : &lex->spprocs);
-  int ret= 0;
+  bool routines_added= TRUE;
 
-  for (uint i=0 ; i < h->records ; i++)
+  DBUG_ENTER("sp_cache_routines");
+
+  while (routines_added)
   {
-    LEX_STRING *ls= (LEX_STRING *)hash_element(h, i);
-    sp_name name(*ls);
+    routines_added= FALSE;
 
-    name.m_qname= *ls;
-    if (! sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
-			   &thd->sp_func_cache : &thd->sp_proc_cache),
-			  &name))
+    for (int type= TYPE_ENUM_FUNCTION; type < TYPE_ENUM_TRIGGER; type++)
     {
-      sp_head *sp;
-      LEX *oldlex= thd->lex;
-      LEX *newlex= new st_lex;
-
-      thd->lex= newlex;
-      newlex->proc_table= oldlex->proc_table; // hint if mysql.oper is opened
-      newlex->current_select= NULL;
-      name.m_name.str= strchr(name.m_qname.str, '.');
-      name.m_db.length= name.m_name.str - name.m_qname.str;
-      name.m_db.str= strmake_root(thd->mem_root,
-				  name.m_qname.str, name.m_db.length);
-      name.m_name.str+= 1;
-      name.m_name.length= name.m_qname.length - name.m_db.length - 1;
-
-      if (db_find_routine(thd, type, &name, &sp) == SP_OK)
-      {
-	if (type == TYPE_ENUM_FUNCTION)
-	  sp_cache_insert(&thd->sp_func_cache, sp);
-	else
-	  sp_cache_insert(&thd->sp_proc_cache, sp);
-	ret= sp_cache_routines(thd, newlex, TYPE_ENUM_FUNCTION);
-	if (!ret)
-	{
-	  sp_merge_hash(&lex->spfuns, &newlex->spfuns);
-	  ret= sp_cache_routines(thd, newlex, TYPE_ENUM_PROCEDURE);
-	}
-	if (!ret)
-	{
-	  sp_merge_hash(&lex->spprocs, &newlex->spprocs);
-	  sp_merge_table_hash(&lex->sptabs, &sp->m_sptabs);
-	}
-	delete newlex;
-	thd->lex= oldlex;
-	if (ret)
-	  break;
-      }
-      else
+      HASH *h= (type == TYPE_ENUM_FUNCTION ? &lex->spfuns : &lex->spprocs);
+
+      for (uint i=0 ; i < h->records ; i++)
       {
-	delete newlex;
-	thd->lex= oldlex;
+        LEX_STRING *ls= (LEX_STRING *)hash_element(h, i);
+        sp_name name(*ls);
+        sp_head *sp;
+
+        name.m_qname= *ls;
+        if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
+                                   &thd->sp_func_cache : &thd->sp_proc_cache),
+                                  &name)))
+        {
+          LEX *oldlex= thd->lex;
+          LEX *newlex= new st_lex;
+
+          thd->lex= newlex;
+          /* Pass hint pointer to mysql.proc table */
+          newlex->proc_table= oldlex->proc_table;
+          newlex->current_select= NULL;
+          name.m_name.str= strchr(name.m_qname.str, '.');
+          name.m_db.length= name.m_name.str - name.m_qname.str;
+          name.m_db.str= strmake_root(thd->mem_root, name.m_qname.str,
+                                      name.m_db.length);
+          name.m_name.str+= 1;
+          name.m_name.length= name.m_qname.length - name.m_db.length - 1;
+
+          if (db_find_routine(thd, type, &name, &sp) == SP_OK)
+          {
+            if (type == TYPE_ENUM_FUNCTION)
+              sp_cache_insert(&thd->sp_func_cache, sp);
+            else
+              sp_cache_insert(&thd->sp_proc_cache, sp);
+          }
+          delete newlex;
+          thd->lex= oldlex;
+        }
+
+        if (sp)
+        {
+          routines_added|= sp_merge_hash(&lex->spfuns, &sp->m_spfuns);
+          routines_added|= sp_merge_hash(&lex->spprocs, &sp->m_spprocs);
+        }
       }
     }
   }
-  return ret;
+  DBUG_VOID_RETURN;
 }
 
 /*
diff --git a/sql/sp.h b/sql/sp.h
index 6290324bb86..00dd8416c1d 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -34,7 +34,7 @@ int
 sp_drop_db_routines(THD *thd, char *db);
 
 sp_head *
-sp_find_procedure(THD *thd, sp_name *name);
+sp_find_procedure(THD *thd, sp_name *name, bool cache_only = 0);
 
 int
 sp_exists_routine(THD *thd, TABLE_LIST *procs, bool any, bool no_error);
@@ -82,10 +82,10 @@ sp_function_exists(THD *thd, sp_name *name);
  */
 void
 sp_add_to_hash(HASH *h, sp_name *fun);
-void
+bool
 sp_merge_hash(HASH *dst, HASH *src);
-int
-sp_cache_routines(THD *thd, LEX *lex, int type);
+void
+sp_cache_routines(THD *thd, LEX *lex);
 
 
 //
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 075aef9d286..c490ff558b6 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -334,12 +334,16 @@ sp_head::sp_head()
 {
   extern byte *
     sp_table_key(const byte *ptr, uint *plen, my_bool first);
+  extern byte 
+    *sp_lex_sp_key(const byte *ptr, uint *plen, my_bool first);
   DBUG_ENTER("sp_head::sp_head");
 
   state= INITIALIZED;
   m_backpatch.empty();
   m_lex.empty();
   hash_init(&m_sptabs, system_charset_info, 0, 0, 0, sp_table_key, 0, 0);
+  hash_init(&m_spfuns, system_charset_info, 0, 0, 0, sp_lex_sp_key, 0, 0);
+  hash_init(&m_spprocs, system_charset_info, 0, 0, 0, sp_lex_sp_key, 0, 0);
   DBUG_VOID_RETURN;
 }
 
@@ -515,8 +519,9 @@ sp_head::destroy()
     if (lex != &m_thd->main_lex) // We got interrupted and have lex'es left
       delete lex;
   }
-  if (m_sptabs.array.buffer)
-    hash_free(&m_sptabs);
+  hash_free(&m_sptabs);
+  hash_free(&m_spfuns);
+  hash_free(&m_spprocs);
   DBUG_VOID_RETURN;
 }
 
@@ -530,6 +535,11 @@ sp_head::execute(THD *thd)
   int ret= 0;
   uint ip= 0;
   Item_arena *old_arena;
+  ulong old_query_id;
+  TABLE *old_derived_tables;
+  LEX *old_lex;
+  Item_change_list old_change_list;
+  String old_packet;
 
 
 #ifndef EMBEDDED_LIBRARY
@@ -550,6 +560,34 @@ sp_head::execute(THD *thd)
   old_arena= thd->current_arena;
   thd->current_arena= this;
 
+  /*
+    We have to save/restore this info when we are changing call level to
+    be able properly do close_thread_tables() in instructions.
+  */
+  old_query_id= thd->query_id;
+  old_derived_tables= thd->derived_tables;
+  thd->derived_tables= 0;
+  /*
+    It is also more efficient to save/restore current thd->lex once when
+    do it in each instruction
+  */
+  old_lex= thd->lex;
+  /*
+    We should also save Item tree change list to avoid rollback something
+    too early in the calling query.
+  */
+  old_change_list= thd->change_list;
+  thd->change_list.empty();
+  /*
+    Cursors will use thd->packet, so they may corrupt data which was prepared
+    for sending by upper level. OTOH cursors in the same routine can share this
+    buffer safely so let use use routine-local packet instead of having own
+    packet buffer for each cursor.
+
+    It is probably safe to use same thd->convert_buff everywhere.
+  */
+  old_packet.swap(thd->packet);
+
   do
   {
     sp_instr *i;
@@ -588,6 +626,17 @@ sp_head::execute(THD *thd)
     }
   } while (ret == 0 && !thd->killed);
 
+  /* Restore all saved */
+  old_packet.swap(thd->packet);
+  DBUG_ASSERT(thd->change_list.is_empty());
+  thd->change_list= old_change_list;
+  /* To avoid wiping out thd->change_list on old_change_list destruction */
+  old_change_list.empty();
+  thd->lex= old_lex;
+  thd->query_id= old_query_id;
+  DBUG_ASSERT(!thd->derived_tables);
+  thd->derived_tables= old_derived_tables;
+
   cleanup_items(thd->current_arena->free_list);
   thd->current_arena= old_arena;
 
@@ -645,14 +694,6 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp)
       DBUG_RETURN(-1);
     }
   }
-#ifdef NOT_WORKING
-  /*
-    Close tables opened for subselect in argument list
-    This can't be done as this will close all other tables used
-    by the query.
-  */
-  close_thread_tables(thd);
-#endif
   // 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).
@@ -758,10 +799,6 @@ sp_head::execute_procedure(THD *thd, List<Item> *args)
 	  nctx->set_oindex(i, static_cast<Item_splocal *>(it)->get_offset());
       }
     }
-    // Clean up the joins before closing the tables.
-    thd->lex->unit.cleanup();
-    // Close tables opened for subselect in argument list
-    close_thread_tables(thd);
 
     // The rest of the frame are local variables which are all IN.
     // Default all variables to null (those with default clauses will
@@ -880,11 +917,17 @@ sp_head::restore_lex(THD *thd)
   oldlex->next_state= sublex->next_state;
   oldlex->trg_table_fields.push_back(&sublex->trg_table_fields);
 
-  // Collect some data from the sub statement lex.
-  sp_merge_hash(&oldlex->spfuns, &sublex->spfuns);
-  sp_merge_hash(&oldlex->spprocs, &sublex->spprocs);
-  // Merge used tables
-  sp_merge_table_list(thd, &m_sptabs, sublex->query_tables, sublex);
+  /*
+    Add routines which are used by statement to respective sets for
+    this routine
+  */
+  sp_merge_hash(&m_spfuns, &sublex->spfuns);
+  sp_merge_hash(&m_spprocs, &sublex->spprocs);
+  /*
+    Merge tables used by this statement (but not by its functions or
+    procedures) to multiset of tables used by this routine.
+  */
+  merge_table_list(thd, sublex->query_tables, sublex);
   if (! sublex->sp_lex_in_use)
     delete sublex;
   thd->lex= oldlex;
@@ -1186,22 +1229,121 @@ sp_head::opt_mark(uint ip)
 
 // ------------------------------------------------------------------
 
+
+/*
+  Prepare LEX and thread for execution of instruction, if requested open
+  and lock LEX's tables, execute instruction's core function, perform
+  cleanup afterwards.
+
+  SYNOPSIS
+    reset_lex_and_exec_core()
+      thd         - thread context
+      nextp       - out - next instruction
+      open_tables - if TRUE then check read access to tables in LEX's table
+                    list and open and lock them (used in instructions which
+                    need to calculate some expression and don't execute
+                    complete statement).
+      sp_instr    - instruction for which we prepare context, and which core
+                    function execute by calling its exec_core() method.
+
+  NOTE
+    We are not saving/restoring some parts of THD which may need this because
+    we do this once for whole routine execution in sp_head::execute().
+
+  RETURN VALUE
+    0/non-0 - Success/Failure
+*/
+
+int
+sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
+                                       bool open_tables, sp_instr* instr)
+{
+  int res= 0;
+
+  DBUG_ASSERT(!thd->derived_tables);
+  DBUG_ASSERT(thd->change_list.is_empty());
+  /*
+    Use our own lex.
+    We should not save old value since it is saved/restored in
+    sp_head::execute() when we are entering/leaving routine.
+  */
+  thd->lex= m_lex;
+
+  VOID(pthread_mutex_lock(&LOCK_thread_count));
+  thd->query_id= query_id++;
+  VOID(pthread_mutex_unlock(&LOCK_thread_count));
+
+  /*
+    FIXME. Resetting statement (and using it) is not reentrant, thus recursive
+           functions which try to use the same LEX twice will crash server.
+           We should prevent such situations by tracking if LEX is already
+           in use and throwing error about unallowed recursion if needed.
+           OTOH it is nice to allow recursion in cases when LEX is not really
+           used (e.g. in mathematical functions), so such tracking should be
+           implemented at the same time as ability not to store LEX for
+           instruction if it is not really used.
+  */
+  reset_stmt_for_execute(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.
+  */
+  if (open_tables &&
+      (check_table_access(thd, SELECT_ACL, m_lex->query_tables, 0) ||
+       open_and_lock_tables(thd, m_lex->query_tables)))
+      res= -1;
+
+  if (!res)
+    res= instr->exec_core(thd, nextp);
+
+  m_lex->unit.cleanup();
+
+  thd->proc_info="closing tables";
+  close_thread_tables(thd);
+
+  thd->rollback_item_tree_changes();
+
+  /*
+    Unlike for PS we should not call Item's destructors for newly created
+    items after execution of each instruction in stored routine. This is
+    because SP often create Item (like Item_int, Item_string etc...) when
+    they want to store some value in local variable, pass return value and
+    etc... So their life time should be longer than one instruction.
+
+    Probably we can call destructors for most of them then we are leaving
+    routine. But this won't help much as they are allocated in main query
+    MEM_ROOT anyway. So they all go to global thd->free_list.
+
+    May be we can use some other MEM_ROOT for this purprose ???
+
+    What else should we do for cleanup ?
+    cleanup_items() is called in sp_head::execute()
+  */
+  return res;
+}
+
+
 //
-// sp_instr_stmt
+// sp_instr
 //
-sp_instr_stmt::~sp_instr_stmt()
+int sp_instr::exec_core(THD *thd, uint *nextp)
 {
-  if (m_lex)
-    delete m_lex;
+  DBUG_ASSERT(0);
+  return 0;
 }
 
+
+//
+// sp_instr_stmt
+//
 int
 sp_instr_stmt::execute(THD *thd, uint *nextp)
 {
   char *query;
   uint32 query_length;
   DBUG_ENTER("sp_instr_stmt::execute");
-  DBUG_PRINT("info", ("command: %d", m_lex->sql_command));
+  DBUG_PRINT("info", ("command: %d", m_lex_keeper.sql_command()));
   int res;
 
   query= thd->query;
@@ -1211,13 +1353,14 @@ sp_instr_stmt::execute(THD *thd, uint *nextp)
     if (query_cache_send_result_to_client(thd,
 					  thd->query, thd->query_length) <= 0)
     {
-      res= exec_stmt(thd, m_lex);
+      res= m_lex_keeper.reset_lex_and_exec_core(thd, nextp, FALSE, this);
       query_cache_end_of_result(thd);
     }
+    else
+      *nextp= m_ip+1;
     thd->query= query;
     thd->query_length= query_length;
   }
-  *nextp = m_ip+1;
   DBUG_RETURN(res);
 }
 
@@ -1226,39 +1369,15 @@ sp_instr_stmt::print(String *str)
 {
   str->reserve(12);
   str->append("stmt ");
-  str->qs_append((uint)m_lex->sql_command);
+  str->qs_append((uint)m_lex_keeper.sql_command());
 }
 
 
 int
-sp_instr_stmt::exec_stmt(THD *thd, LEX *lex)
+sp_instr_stmt::exec_core(THD *thd, uint *nextp)
 {
-  LEX *olex;			// The other lex
-  int res;
-
-  olex= thd->lex;		// Save the other lex
-  thd->lex= lex;		// Use my own lex
-  thd->lex->thd = thd;		// QQ Not reentrant!
-  thd->lex->unit.thd= thd;	// QQ Not reentrant
-  thd->free_list= NULL;
-
-  VOID(pthread_mutex_lock(&LOCK_thread_count));
-  thd->query_id= query_id++;
-  VOID(pthread_mutex_unlock(&LOCK_thread_count));
-
-  reset_stmt_for_execute(thd, lex);
-
-  res= mysql_execute_command(thd);
-
-  lex->unit.cleanup();
-  if (thd->lock || thd->open_tables || thd->derived_tables)
-  {
-    thd->proc_info="closing tables";
-    close_thread_tables(thd);			/* Free tables */
-  }
-
-  thd->lex= olex;		// Restore the other lex
-
+  int res= mysql_execute_command(thd);
+  *nextp= m_ip+1;
   return res;
 }
 
@@ -1270,14 +1389,16 @@ sp_instr_set::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_set::execute");
   DBUG_PRINT("info", ("offset: %u", m_offset));
+
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+int
+sp_instr_set::exec_core(THD *thd, uint *nextp)
+{
   Item *it;
   int res;
 
-  if (tables &&
-      ((res= check_table_access(thd, SELECT_ACL, tables, 0)) ||
-       (res= open_and_lock_tables(thd, tables))))
-    DBUG_RETURN(res);
-
   it= sp_eval_func_item(thd, m_value, m_type);
   if (! it)
     res= -1;
@@ -1287,9 +1408,8 @@ sp_instr_set::execute(THD *thd, uint *nextp)
     thd->spcont->set_item(m_offset, it);
   }
   *nextp = m_ip+1;
-  if (tables && (thd->lock || thd->open_tables || thd->derived_tables))
-    close_thread_tables(thd);
-  DBUG_RETURN(res);
+
+  return res;
 }
 
 void
@@ -1302,32 +1422,6 @@ sp_instr_set::print(String *str)
   m_value->print(str);
 }
 
-//
-// sp_instr_set_user_var
-//
-int
-sp_instr_set_user_var::execute(THD *thd, uint *nextp)
-{
-  int res= 0;
-
-  DBUG_ENTER("sp_instr_set_user_var::execute");
-  /*
-    It is ok to pass 0 as 3rd argument to fix_fields() since
-    Item_func_set_user_var::fix_fields() won't use it.
-    QQ: Still unsure what should we return in case of error 1 or -1 ?
-  */
-  if (!m_set_var_item.fixed && m_set_var_item.fix_fields(thd, 0, 0) ||
-      m_set_var_item.check() || m_set_var_item.update())
-    res= -1;
-  *nextp= m_ip + 1;
-  DBUG_RETURN(res);
-}
-
-void
-sp_instr_set_user_var::print(String *str)
-{
-  m_set_var_item.print_as_stmt(str);
-}
 
 //
 // sp_instr_set_trigger_field
@@ -1420,19 +1514,21 @@ sp_instr_jump::opt_move(uint dst, List<sp_instr> *bp)
 //
 // sp_instr_jump_if
 //
+
 int
 sp_instr_jump_if::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_jump_if::execute");
   DBUG_PRINT("info", ("destination: %u", m_dest));
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+int
+sp_instr_jump_if::exec_core(THD *thd, uint *nextp)
+{
   Item *it;
   int res;
 
-  if (tables &&
-      ((res= check_table_access(thd, SELECT_ACL, tables, 0)) ||
-       (res= open_and_lock_tables(thd, tables))))
-    DBUG_RETURN(res);
-
   it= sp_eval_func_item(thd, m_expr, MYSQL_TYPE_TINY);
   if (!it)
     res= -1;
@@ -1444,9 +1540,8 @@ sp_instr_jump_if::execute(THD *thd, uint *nextp)
     else
       *nextp = m_ip+1;
   }
-  if (tables && (thd->lock || thd->open_tables || thd->derived_tables))
-    close_thread_tables(thd);
-  DBUG_RETURN(res);
+
+  return res;
 }
 
 void
@@ -1482,14 +1577,16 @@ sp_instr_jump_if_not::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_jump_if_not::execute");
   DBUG_PRINT("info", ("destination: %u", m_dest));
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+
+int
+sp_instr_jump_if_not::exec_core(THD *thd, uint *nextp)
+{
   Item *it;
   int res;
 
-  if (tables &&
-      ((res= check_table_access(thd, SELECT_ACL, tables, 0)) ||
-       (res= open_and_lock_tables(thd, tables))))
-    DBUG_RETURN(res);
-
   it= sp_eval_func_item(thd, m_expr, MYSQL_TYPE_TINY);
   if (! it)
     res= -1;
@@ -1501,9 +1598,8 @@ sp_instr_jump_if_not::execute(THD *thd, uint *nextp)
     else
       *nextp = m_ip+1;
   }
-  if (tables && (thd->lock || thd->open_tables || thd->derived_tables))
-    close_thread_tables(thd);
-  DBUG_RETURN(res);
+
+  return res;
 }
 
 void
@@ -1534,18 +1630,21 @@ sp_instr_jump_if_not::opt_mark(sp_head *sp)
 //
 // sp_instr_freturn
 //
+
 int
 sp_instr_freturn::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_freturn::execute");
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+
+int
+sp_instr_freturn::exec_core(THD *thd, uint *nextp)
+{
   Item *it;
   int res;
 
-  if (tables &&
-      ((res= check_table_access(thd, SELECT_ACL, tables, 0)) ||
-       (res= open_and_lock_tables(thd, tables))))
-    DBUG_RETURN(res);
-
   it= sp_eval_func_item(thd, m_value, m_type);
   if (! it)
     res= -1;
@@ -1555,7 +1654,8 @@ sp_instr_freturn::execute(THD *thd, uint *nextp)
     thd->spcont->set_result(it);
   }
   *nextp= UINT_MAX;
-  DBUG_RETURN(res);
+
+  return res;
 }
 
 void
@@ -1689,17 +1789,11 @@ int
 sp_instr_cpush::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_cpush::execute");
-  thd->spcont->push_cursor(m_lex);
+  thd->spcont->push_cursor(&m_lex_keeper);
   *nextp= m_ip+1;
   DBUG_RETURN(0);
 }
 
-sp_instr_cpush::~sp_instr_cpush()
-{
-  if (m_lex)
-    delete m_lex;
-}
-
 void
 sp_instr_cpush::print(String *str)
 {
@@ -1746,19 +1840,30 @@ sp_instr_copen::execute(THD *thd, uint *nextp)
     res= -1;
   else
   {
-    LEX *lex= c->pre_open(thd);
+    sp_lex_keeper *lex_keeper= c->pre_open(thd);
 
-    if (! lex)
+    if (!lex_keeper)
+    {
       res= -1;
+      *nextp= m_ip+1;
+    }
     else
-      res= exec_stmt(thd, lex);
-    c->post_open(thd, (lex ? TRUE : FALSE));
+      res= lex_keeper->reset_lex_and_exec_core(thd, nextp, FALSE, this);
+
+    c->post_open(thd, (lex_keeper ? TRUE : FALSE));
   }
 
-  *nextp= m_ip+1;
   DBUG_RETURN(res);
 }
 
+int
+sp_instr_copen::exec_core(THD *thd, uint *nextp)
+{
+  int res= mysql_execute_command(thd);
+  *nextp= m_ip+1;
+  return res;
+}
+
 void
 sp_instr_copen::print(String *str)
 {
@@ -1910,14 +2015,17 @@ sp_restore_security_context(THD *thd, sp_head *sp, st_sp_security_context *ctxp)
 #endif /* NO_EMBEDDED_ACCESS_CHECKS */
 
 /*
- *  Table merge hash table
- *
- */
+  Structure that represent all instances of one table
+  in optimized multi-set of tables used by routine.
+*/
+
 typedef struct st_sp_table
 {
   LEX_STRING qname;
   bool temp;
   TABLE_LIST *table;
+  uint lock_count;
+  uint query_lock_count;
 } SP_TABLE;
 
 byte *
@@ -1928,23 +2036,47 @@ sp_table_key(const byte *ptr, uint *plen, my_bool first)
   return (byte *)tab->qname.str;
 }
 
+
 /*
- *  Merge the table list into the hash table.
- *  If the optional lex is provided, it's used to check and set
- *  the flag for creation of a temporary table.
- */
+  Merge the list of tables used by some query into the multi-set of
+  tables used by routine.
+
+  SYNOPSIS
+    merge_table_list()
+      thd               - thread context
+      table             - table list
+      lex_for_tmp_check - LEX of the query for which we are merging
+                          table list.
+
+  NOTE
+    This method will use LEX provided to check whenever we are creating
+    temporary table and mark it as such in target multi-set.
+
+  RETURN VALUE
+    TRUE  - Success
+    FALSE - Error
+*/
+
 bool
-sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
-		    LEX *lex_for_tmp_check)
+sp_head::merge_table_list(THD *thd, TABLE_LIST *table, LEX *lex_for_tmp_check)
 {
+  SP_TABLE *tab;
+
+  if (lex_for_tmp_check->sql_command == SQLCOM_DROP_TABLE &&
+      lex_for_tmp_check->drop_temporary)
+    return TRUE;
+
+  for (uint i= 0 ; i < m_sptabs.records ; i++)
+  {
+    tab= (SP_TABLE *)hash_element(&m_sptabs, i);
+    tab->query_lock_count= 0;
+  }
+
   for (; table ; table= table->next_global)
-    if (!table->derived &&
-	(!table->select_lex ||
-	 !(table->select_lex->options & OPTION_SCHEMA_TABLE)))
+    if (!table->derived && !table->schema_table)
     {
       char tname[64+1+64+1+64+1];	// db.table.alias\0
       uint tlen, alen;
-      SP_TABLE *tab;
 
       tlen= table->db_length;
       memcpy(tname, table->db, tlen);
@@ -1957,10 +2089,17 @@ sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
       tlen+= alen;
       tname[tlen]= '\0';
 
-      if ((tab= (SP_TABLE *)hash_search(h, (byte *)tname, tlen)))
+      /*
+        It is safe to store pointer to table list elements in hash,
+        since they are supposed to have the same lifetime.
+      */
+      if ((tab= (SP_TABLE *)hash_search(&m_sptabs, (byte *)tname, tlen)))
       {
 	if (tab->table->lock_type < table->lock_type)
 	  tab->table= table;	// Use the table with the highest lock type
+        tab->query_lock_count++;
+        if (tab->query_lock_count > tab->lock_count)
+          tab->lock_count++;
       }
       else
       {
@@ -1970,152 +2109,102 @@ sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
 	tab->qname.str= (char *)thd->strmake(tname, tab->qname.length);
 	if (!tab->qname.str)
 	  return FALSE;
-	if (lex_for_tmp_check &&
-	    lex_for_tmp_check->sql_command == SQLCOM_CREATE_TABLE &&
+	if (lex_for_tmp_check->sql_command == SQLCOM_CREATE_TABLE &&
 	    lex_for_tmp_check->query_tables == table &&
 	    lex_for_tmp_check->create_info.options & HA_LEX_CREATE_TMP_TABLE)
 	  tab->temp= TRUE;
 	tab->table= table;
-	my_hash_insert(h, (byte *)tab);
+        tab->lock_count= tab->query_lock_count= 1;
+	my_hash_insert(&m_sptabs, (byte *)tab);
       }
     }
   return TRUE;
 }
 
-void
-sp_merge_routine_tables(THD *thd, LEX *lex)
-{
-  uint i;
 
-  for (i= 0 ; i < lex->spfuns.records ; i++)
-  {
-    sp_head *sp;
-    LEX_STRING *ls= (LEX_STRING *)hash_element(&lex->spfuns, i);
-    sp_name name(*ls);
+/*
+  Add tables used by routine to the table list.
 
-    name.m_qname= *ls;
-    if ((sp= sp_cache_lookup(&thd->sp_func_cache, &name)))
-      sp_merge_table_hash(&lex->sptabs, &sp->m_sptabs);
-  }
-  for (i= 0 ; i < lex->spprocs.records ; i++)
-  {
-    sp_head *sp;
-    LEX_STRING *ls= (LEX_STRING *)hash_element(&lex->spprocs, i);
-    sp_name name(*ls);
+  SYNOPSIS
+    add_used_tables_to_table_list()
+      thd                   - thread context
+      query_tables_last_ptr - (in/out) pointer the next_global member of last
+                              element of the list where tables will be added
+                              (or to its root).
 
-    name.m_qname= *ls;
-    if ((sp= sp_cache_lookup(&thd->sp_proc_cache, &name)))
-      sp_merge_table_hash(&lex->sptabs, &sp->m_sptabs);
-  }
-}
+  DESCRIPTION
+    Converts multi-set of tables used by this routine to table list and adds
+    this list to the end of table list specified by 'query_tables_last_ptr'.
 
-void
-sp_merge_table_hash(HASH *hdst, HASH *hsrc)
-{
-  for (uint i=0 ; i < hsrc->records ; i++)
-  {
-    SP_TABLE *tabdst;
-    SP_TABLE *tabsrc= (SP_TABLE *)hash_element(hsrc, i);
+    Elements of list will be allocated in PS memroot, so this list will be
+    persistent between PS executions.
 
-    if (! (tabdst= (SP_TABLE *)hash_search(hdst,
-					   tabsrc->qname.str,
-					   tabsrc->qname.length)))
-    {
-      my_hash_insert(hdst, (byte *)tabsrc);
-    }
-    else
-    {
-      if (tabdst->table->lock_type < tabsrc->table->lock_type)
-	tabdst->table= tabsrc->table; // Use the highest lock type
-    }
-  }
-}
+  RETURN VALUE
+    TRUE - if some elements were added, FALSE - otherwise.
+*/
 
-TABLE_LIST *
-sp_hash_to_table_list(THD *thd, HASH *h)
+bool
+sp_head::add_used_tables_to_table_list(THD *thd,
+                                       TABLE_LIST ***query_tables_last_ptr)
 {
   uint i;
-  TABLE_LIST *tables= NULL;
-  DBUG_ENTER("sp_hash_to_table_list");
+  Item_arena *arena, backup;
+  bool result= FALSE;
+  DBUG_ENTER("sp_head::add_used_tables_to_table_list");
+
+  /*
+    Use persistent arena for table list allocation to be PS friendly.
+  */
+  arena= thd->change_arena_if_needed(&backup);
 
-  for (i=0 ; i < h->records ; i++)
+  for (i=0 ; i < m_sptabs.records ; i++)
   {
-    SP_TABLE *stab= (SP_TABLE *)hash_element(h, i);
+    char *tab_buff;
+    TABLE_LIST *table, *otable;
+    SP_TABLE *stab= (SP_TABLE *)hash_element(&m_sptabs, i);
     if (stab->temp)
       continue;
-    TABLE_LIST *table, *otable= stab->table;
-
-    if (! (table= (TABLE_LIST *)thd->calloc(sizeof(TABLE_LIST))))
-      return NULL;
-    table->db= otable->db;
-    table->db_length= otable->db_length;
-    table->alias= otable->alias;
-    table->table_name= otable->table_name;
-    table->table_name_length= otable->table_name_length;
-    table->lock_type= otable->lock_type;
-    table->updating= otable->updating;
-    table->force_index= otable->force_index;
-    table->ignore_leaves= otable->ignore_leaves;
-    table->derived= otable->derived;
-    table->schema_table= otable->schema_table;
-    table->select_lex= otable->select_lex;
-    table->cacheable_table= otable->cacheable_table;
-    table->use_index= otable->use_index;
-    table->ignore_index= otable->ignore_index;
-    table->option= otable->option;
-
-    table->next_global= tables;
-    tables= table;
-  }
-  DBUG_RETURN(tables);
-}
 
-bool
-sp_open_and_lock_tables(THD *thd, TABLE_LIST *tables)
-{
-  DBUG_ENTER("sp_open_and_lock_tables");
-  bool ret;
+    otable= stab->table;
 
-  thd->in_lock_tables= 1;
-  thd->options|= OPTION_TABLE_LOCK;
-  if (simple_open_n_lock_tables(thd, tables))
-  {
-    thd->options&= ~(ulong)(OPTION_TABLE_LOCK);
-    ret= FALSE;
-  }
-  else
-  {
-#if 0
-    // QQ What about this?
-#ifdef HAVE_QUERY_CACHE
-    if (thd->variables.query_cache_wlock_invalidate)
-      query_cache.invalidate_locked_for_write(first_table); // QQ first_table?
-#endif /* HAVE_QUERY_CACHE */
-#endif
-    thd->locked_tables= thd->lock;
-    thd->lock= 0;
-    ret= TRUE;
-  }
-  thd->in_lock_tables= 0;
-  DBUG_RETURN(ret);
-}
+    if (!(tab_buff= (char *)thd->calloc(ALIGN_SIZE(sizeof(TABLE_LIST)) *
+                                        stab->lock_count)))
+      DBUG_RETURN(FALSE);
 
-void
-sp_unlock_tables(THD *thd)
-{
-  thd->lock= thd->locked_tables;
-  thd->locked_tables= 0;
-  close_thread_tables(thd);			// Free tables
-  if (thd->options & OPTION_TABLE_LOCK)
-  {
-#if 0
-    // QQ What about this?
-    end_active_trans(thd);
-#endif
-    thd->options&= ~(ulong)(OPTION_TABLE_LOCK);
+    for (uint j= 0; j < stab->lock_count; j++)
+    {
+      table= (TABLE_LIST *)tab_buff;
+
+      /*
+        It's enough to just copy the pointers as the data will not change
+        during the lifetime of the SP. If the SP is used by PS, we assume
+        that the PS will be invalidated if the functions is deleted or
+        changed.
+      */
+      table->db= otable->db;
+      table->db_length= otable->db_length;
+      table->alias= otable->alias;
+      table->table_name= otable->table_name;
+      table->table_name_length= otable->table_name_length;
+      table->lock_type= otable->lock_type;
+      table->cacheable_table= 1;
+      table->prelocking_placeholder= 1;
+
+      /* Everyting else should be zeroed */
+
+      **query_tables_last_ptr= table;
+      table->prev_global= *query_tables_last_ptr;
+      *query_tables_last_ptr= &table->next_global;
+
+      tab_buff+= ALIGN_SIZE(sizeof(TABLE_LIST));
+      result= TRUE;
+    }
   }
-  if (thd->global_read_lock)
-    unlock_global_read_lock(thd);
+
+  if (arena)
+    thd->restore_backup_item_arena(arena, &backup);
+
+  DBUG_RETURN(result);
 }
 
 /*
@@ -2147,3 +2236,73 @@ sp_add_to_query_tables(THD *thd, LEX *lex,
   lex->add_to_query_tables(table);
   return table;
 }
+
+
+/*
+  Auxilary function for adding tables used by routines used in query
+  to table lists.
+
+  SYNOPSIS
+    sp_add_sp_tables_to_table_list_aux()
+      thd       - thread context
+      lex       - LEX to which table list tables will be added
+      func_hash - routines for which tables should be added
+      func_cache- SP cache in which this routines should be looked up
+
+  NOTE
+    See sp_add_sp_tables_to_table_list() for more info.
+
+  RETURN VALUE
+    TRUE  - some tables were added
+    FALSE - no tables were added.
+*/
+
+static bool
+sp_add_sp_tables_to_table_list_aux(THD *thd, LEX *lex, HASH *func_hash,
+                                   sp_cache **func_cache)
+{
+  uint i;
+  bool result= FALSE;
+
+  for (i= 0 ; i < func_hash->records ; i++)
+  {
+    sp_head *sp;
+    LEX_STRING *ls= (LEX_STRING *)hash_element(func_hash, i);
+    sp_name name(*ls);
+
+    name.m_qname= *ls;
+    if ((sp= sp_cache_lookup(func_cache, &name)))
+      result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+  }
+
+  return result;
+}
+
+
+/*
+  Add tables used by routines used in query to table list.
+
+  SYNOPSIS
+    sp_add_sp_tables_to_table_list()
+      thd      - thread context
+      lex      - LEX to which table list tables will be added
+      func_lex - LEX for which functions we get tables
+                 (useful for adding tables used by view routines)
+
+  NOTE
+    Elements of list will be allocated in PS memroot, so this
+    list will be persistent between PS execetutions.
+
+  RETURN VALUE
+    TRUE  - some tables were added
+    FALSE - no tables were added.
+*/
+
+bool
+sp_add_sp_tables_to_table_list(THD *thd, LEX *lex, LEX *func_lex)
+{
+  return (sp_add_sp_tables_to_table_list_aux(thd, lex, &func_lex->spfuns,
+                                             &thd->sp_func_cache) |
+          sp_add_sp_tables_to_table_list_aux(thd, lex, &func_lex->spprocs,
+                                             &thd->sp_proc_cache));
+}
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 5df9c753048..60979a438cb 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -103,7 +103,14 @@ public:
   LEX_STRING m_definer_host;
   longlong m_created;
   longlong m_modified;
-  HASH m_sptabs;		/* Merged table lists */
+  /*
+    Sets containing names of SP and SF used by this routine.
+
+    TODO Probably we should combine these two hashes in one. It will
+    decrease memory overhead ans simplify algorithms using them. The
+    same applies to similar hashes in LEX.
+  */
+  HASH m_spfuns, m_spprocs;
   // Pointers set during parsing
   uchar *m_param_begin, *m_param_end, *m_returns_begin, *m_returns_end,
     *m_body_begin;
@@ -225,6 +232,10 @@ public:
     return ip;
   }
 
+  /* Add tables used by routine to the table list. */
+  bool add_used_tables_to_table_list(THD *thd,
+                                     TABLE_LIST ***query_tables_last_ptr);
+
 private:
 
   MEM_ROOT *m_thd_root;		// Temp. store for thd's mem_root
@@ -240,10 +251,20 @@ private:
     sp_instr *instr;
   } bp_t;
   List<bp_t> m_backpatch;	// Instructions needing backpatching
+  /*
+    Multi-set representing optimized list of tables to be locked by this
+    routine. Does not include tables which are used by invoked routines.
+  */
+  HASH m_sptabs;
 
   int
   execute(THD *thd);
 
+  /*
+    Merge the list of tables used by query into the multi-set of tables used
+    by routine.
+  */
+  bool merge_table_list(THD *thd, TABLE_LIST *table, LEX *lex_for_tmp_check);
 }; // class sp_head : public Sql_alloc
 
 
@@ -277,6 +298,17 @@ public:
   // Returns 0 on success, non-zero if some error occured.
   virtual int execute(THD *thd, uint *nextp) = 0;
 
+  /*
+    Execute core function of instruction after all preparations (e.g.
+    setting of proper LEX, saving part of the thread context have been
+    done).
+
+    Should be implemented for instructions using expressions or whole
+    statements (thus having to have own LEX). Used in concert with
+    sp_lex_keeper class and its descendants.
+  */
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str) = 0;
 
   virtual void backpatch(uint dest, sp_pcontext *dst_ctx)
@@ -301,6 +333,60 @@ public:
 }; // class sp_instr : public Sql_alloc
 
 
+/*
+  Auxilary class to which instructions delegate responsibility
+  for handling LEX and preparations before executing statement
+  or calculating complex expression.
+
+  Exist mainly to avoid having double hierarchy between instruction
+  classes.
+
+  TODO: Add ability to not store LEX and do any preparations if
+        expression used is simple.
+*/
+
+class sp_lex_keeper
+{
+  /* Prevent use of these */
+  sp_lex_keeper(const sp_lex_keeper &);
+  void operator=(sp_lex_keeper &);
+public:
+
+  sp_lex_keeper(LEX *lex, bool lex_resp)
+    : m_lex(lex), m_lex_resp(lex_resp)
+  {
+    lex->sp_lex_in_use= TRUE;
+  }
+  virtual ~sp_lex_keeper()
+  {
+    if (m_lex_resp)
+      delete m_lex;
+  }
+
+  /*
+    Prepare execution of instruction using LEX, if requested check whenever
+    we have read access to tables used and open/lock them, call instruction's
+    exec_core() method, perform cleanup afterwards.
+  */
+  int reset_lex_and_exec_core(THD *thd, uint *nextp, bool open_tables,
+                              sp_instr* instr);
+
+  inline uint sql_command() const
+  {
+    return (uint)m_lex->sql_command;
+  }
+
+private:
+
+  LEX *m_lex;
+  /*
+    Indicates whenever this sp_lex_keeper instance responsible
+    for LEX deletion.
+  */
+  bool m_lex_resp;
+};
+
+
 //
 // Call out to some prepared SQL statement.
 //
@@ -313,38 +399,25 @@ public:
 
   LEX_STRING m_query;		// For thd->query
 
-  sp_instr_stmt(uint ip, sp_pcontext *ctx)
-    : sp_instr(ip, ctx), m_lex(NULL)
+  sp_instr_stmt(uint ip, sp_pcontext *ctx, LEX *lex)
+    : sp_instr(ip, ctx), m_lex_keeper(lex, TRUE)
   {
     m_query.str= 0;
     m_query.length= 0;
   }
 
-  virtual ~sp_instr_stmt();
+  virtual ~sp_instr_stmt()
+  {};
 
   virtual int execute(THD *thd, uint *nextp);
 
-  virtual void print(String *str);
-
-  inline void
-  set_lex(LEX *lex)
-  {
-    m_lex= lex;
-  }
-
-  inline LEX *
-  get_lex()
-  {
-    return m_lex;
-  }
+  virtual int exec_core(THD *thd, uint *nextp);
 
-protected:
-
-  int exec_stmt(THD *thd, LEX *lex); // Execute a statement
+  virtual void print(String *str);
 
 private:
 
-  LEX *m_lex;			// My own lex
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_stmt : public sp_instr
 
@@ -356,12 +429,11 @@ class sp_instr_set : public sp_instr
 
 public:
 
-  TABLE_LIST *tables;
-
   sp_instr_set(uint ip, sp_pcontext *ctx,
-	       uint offset, Item *val, enum enum_field_types type)
-    : sp_instr(ip, ctx),
-      tables(NULL), m_offset(offset), m_value(val), m_type(type)
+	       uint offset, Item *val, enum enum_field_types type,
+               LEX *lex, bool lex_resp)
+    : sp_instr(ip, ctx), m_offset(offset), m_value(val), m_type(type),
+      m_lex_keeper(lex, lex_resp)
   {}
 
   virtual ~sp_instr_set()
@@ -369,6 +441,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
 private:
@@ -376,42 +450,12 @@ private:
   uint m_offset;		// Frame offset
   Item *m_value;
   enum enum_field_types m_type;	// The declared type
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_set : public sp_instr
 
 
 /*
-  Set user variable instruction.
-  Used in functions and triggers to set user variables because we don't
-  want use sp_instr_stmt + "SET @a:=..." statement in this case since
-  latter will close all tables and thus will ruin execution of statement
-  calling/invoking this function/trigger.
-*/
-class sp_instr_set_user_var : public sp_instr
-{
-  sp_instr_set_user_var(const sp_instr_set_user_var &);
-  void operator=(sp_instr_set_user_var &);
-
-public:
-
-  sp_instr_set_user_var(uint ip, sp_pcontext *ctx, LEX_STRING var, Item *val)
-    : sp_instr(ip, ctx), m_set_var_item(var, val)
-  {}
-
-  virtual ~sp_instr_set_user_var()
-  {}
-
-  virtual int execute(THD *thd, uint *nextp);
-
-  virtual void print(String *str);
-
-private:
-
-  Item_func_set_user_var m_set_var_item;
-}; // class sp_instr_set_user_var : public sp_instr
-
-
-/*
   Set NEW/OLD row field value instruction. Used in triggers.
 */
 class sp_instr_set_trigger_field : public sp_instr
@@ -492,14 +536,12 @@ class sp_instr_jump_if : public sp_instr_jump
 
 public:
 
-  TABLE_LIST *tables;
-
-  sp_instr_jump_if(uint ip, sp_pcontext *ctx, Item *i)
-    : sp_instr_jump(ip, ctx), tables(NULL), m_expr(i)
+  sp_instr_jump_if(uint ip, sp_pcontext *ctx, Item *i, LEX *lex)
+    : sp_instr_jump(ip, ctx), m_expr(i), m_lex_keeper(lex, TRUE)
   {}
 
-  sp_instr_jump_if(uint ip, sp_pcontext *ctx, Item *i, uint dest)
-    : sp_instr_jump(ip, ctx, dest), tables(NULL), m_expr(i)
+  sp_instr_jump_if(uint ip, sp_pcontext *ctx, Item *i, uint dest, LEX *lex)
+    : sp_instr_jump(ip, ctx, dest), m_expr(i), m_lex_keeper(lex, TRUE)
   {}
 
   virtual ~sp_instr_jump_if()
@@ -507,6 +549,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
   virtual uint opt_mark(sp_head *sp);
@@ -519,6 +563,7 @@ public:
 private:
 
   Item *m_expr;			// The condition
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_jump_if : public sp_instr_jump
 
@@ -530,14 +575,12 @@ class sp_instr_jump_if_not : public sp_instr_jump
 
 public:
 
-  TABLE_LIST *tables;
-
-  sp_instr_jump_if_not(uint ip, sp_pcontext *ctx, Item *i)
-    : sp_instr_jump(ip, ctx), tables(NULL), m_expr(i)
+  sp_instr_jump_if_not(uint ip, sp_pcontext *ctx, Item *i, LEX *lex)
+    : sp_instr_jump(ip, ctx), m_expr(i), m_lex_keeper(lex, TRUE)
   {}
 
-  sp_instr_jump_if_not(uint ip, sp_pcontext *ctx, Item *i, uint dest)
-    : sp_instr_jump(ip, ctx, dest), tables(NULL), m_expr(i)
+  sp_instr_jump_if_not(uint ip, sp_pcontext *ctx, Item *i, uint dest, LEX *lex)
+    : sp_instr_jump(ip, ctx, dest), m_expr(i), m_lex_keeper(lex, TRUE)
   {}
 
   virtual ~sp_instr_jump_if_not()
@@ -545,6 +588,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
   virtual uint opt_mark(sp_head *sp);
@@ -557,6 +602,7 @@ public:
 private:
 
   Item *m_expr;			// The condition
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_jump_if_not : public sp_instr_jump
 
@@ -568,11 +614,9 @@ class sp_instr_freturn : public sp_instr
 
 public:
 
-  TABLE_LIST *tables;
-
   sp_instr_freturn(uint ip, sp_pcontext *ctx,
-		   Item *val, enum enum_field_types type)
-    : sp_instr(ip, ctx), tables(NULL), m_value(val), m_type(type)
+		   Item *val, enum enum_field_types type, LEX *lex)
+    : sp_instr(ip, ctx), m_value(val), m_type(type), m_lex_keeper(lex, TRUE)
   {}
 
   virtual ~sp_instr_freturn()
@@ -580,6 +624,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
   virtual uint opt_mark(sp_head *sp)
@@ -592,6 +638,7 @@ protected:
 
   Item *m_value;
   enum enum_field_types m_type;
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_freturn : public sp_instr
 
@@ -710,10 +757,11 @@ class sp_instr_cpush : public sp_instr
 public:
 
   sp_instr_cpush(uint ip, sp_pcontext *ctx, LEX *lex)
-    : sp_instr(ip, ctx), m_lex(lex)
+    : sp_instr(ip, ctx), m_lex_keeper(lex, TRUE)
   {}
 
-  virtual ~sp_instr_cpush();
+  virtual ~sp_instr_cpush()
+  {}
 
   virtual int execute(THD *thd, uint *nextp);
 
@@ -721,7 +769,7 @@ public:
 
 private:
 
-  LEX *m_lex;
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_cpush : public sp_instr
 
@@ -760,7 +808,7 @@ private:
 }; // class sp_instr_cpop : public sp_instr
 
 
-class sp_instr_copen : public sp_instr_stmt
+class sp_instr_copen : public sp_instr
 {
   sp_instr_copen(const sp_instr_copen &); /* Prevent use of these */
   void operator=(sp_instr_copen &);
@@ -768,7 +816,7 @@ class sp_instr_copen : public sp_instr_stmt
 public:
 
   sp_instr_copen(uint ip, sp_pcontext *ctx, uint c)
-    : sp_instr_stmt(ip, ctx), m_cursor(c)
+    : sp_instr(ip, ctx), m_cursor(c)
   {}
 
   virtual ~sp_instr_copen()
@@ -776,6 +824,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
 private:
@@ -893,22 +943,11 @@ void
 sp_restore_security_context(THD *thd, sp_head *sp,st_sp_security_context *ctxp);
 #endif /* NO_EMBEDDED_ACCESS_CHECKS */
 
-bool
-sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
-		    LEX *lex_for_tmp_check = 0);
-void
-sp_merge_routine_tables(THD *thd, LEX *lex);
-void
-sp_merge_table_hash(HASH *hdst, HASH *hsrc);
-TABLE_LIST *
-sp_hash_to_table_list(THD *thd, HASH *h);
-bool
-sp_open_and_lock_tables(THD *thd, TABLE_LIST *tables);
-void
-sp_unlock_tables(THD *thd);
 TABLE_LIST *
 sp_add_to_query_tables(THD *thd, LEX *lex,
 		       const char *db, const char *name,
 		       thr_lock_type locktype);
+bool
+sp_add_sp_tables_to_table_list(THD *thd, LEX *lex, LEX *func_lex);
 
 #endif /* _SP_HEAD_H_ */
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index 0c6c8c5aa70..a2e2a38dfff 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -125,9 +125,9 @@ sp_rcontext::restore_variables(uint fp)
 }
 
 void
-sp_rcontext::push_cursor(LEX *lex)
+sp_rcontext::push_cursor(sp_lex_keeper *lex_keeper)
 {
-  m_cstack[m_ccount++]= new sp_cursor(lex);
+  m_cstack[m_ccount++]= new sp_cursor(lex_keeper);
 }
 
 void
@@ -148,7 +148,7 @@ sp_rcontext::pop_cursors(uint count)
 
 // We have split this in two to make it easy for sp_instr_copen
 // to reuse the sp_instr::exec_stmt() code.
-LEX *
+sp_lex_keeper*
 sp_cursor::pre_open(THD *thd)
 {
   if (m_isopen)
@@ -168,7 +168,7 @@ sp_cursor::pre_open(THD *thd)
 
   m_nseof= thd->net.no_send_eof;
   thd->net.no_send_eof= TRUE;
-  return m_lex;
+  return m_lex_keeper;
 }
 
 void
diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h
index 8e818ab76d1..37d718048a0 100644
--- a/sql/sp_rcontext.h
+++ b/sql/sp_rcontext.h
@@ -25,6 +25,7 @@
 struct sp_cond_type;
 class sp_cursor;
 struct sp_pvar;
+class sp_lex_keeper;
 
 #define SP_HANDLER_NONE      0
 #define SP_HANDLER_EXIT      1
@@ -164,7 +165,7 @@ class sp_rcontext : public Sql_alloc
   restore_variables(uint fp);
 
   void
-  push_cursor(LEX *lex);
+  push_cursor(sp_lex_keeper *lex_keeper);
 
   void
   pop_cursors(uint count);
@@ -207,8 +208,8 @@ class sp_cursor : public Sql_alloc
 {
 public:
 
-  sp_cursor(LEX *lex)
-    : m_lex(lex), m_prot(NULL), m_isopen(0), m_current_row(NULL)
+  sp_cursor(sp_lex_keeper *lex_keeper)
+    : m_lex_keeper(lex_keeper), m_prot(NULL), m_isopen(0), m_current_row(NULL)
   {
     /* Empty */
   }
@@ -220,7 +221,7 @@ public:
 
   // We have split this in two to make it easy for sp_instr_copen
   // to reuse the sp_instr::exec_stmt() code.
-  LEX *
+  sp_lex_keeper *
   pre_open(THD *thd);
   void
   post_open(THD *thd, my_bool was_opened);
@@ -240,7 +241,7 @@ public:
 private:
 
   MEM_ROOT m_mem_root;		// My own mem_root
-  LEX *m_lex;
+  sp_lex_keeper *m_lex_keeper;
   Protocol_cursor *m_prot;
   my_bool m_isopen;
   my_bool m_nseof;		// Original no_send_eof
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 71d042eda02..ab7bc8e9145 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -138,7 +138,6 @@ my_bool acl_init(THD *org_thd, bool dont_read_acl_tables)
   TABLE_LIST tables[3];
   TABLE *table;
   READ_RECORD read_record_info;
-  MYSQL_LOCK *lock;
   my_bool return_val=1;
   bool check_no_resolve= specialflag & SPECIAL_NO_RESOLVE;
   DBUG_ENTER("acl_init");
@@ -174,20 +173,9 @@ my_bool acl_init(THD *org_thd, bool dont_read_acl_tables)
   tables[0].lock_type=tables[1].lock_type=tables[2].lock_type=TL_READ;
   tables[0].db=tables[1].db=tables[2].db=thd->db;
 
-  uint counter;
-  if (open_tables(thd, tables, &counter))
-  {
-    sql_print_error("Fatal error: Can't open privilege tables: %s",
-		    thd->net.last_error);
-    goto end;
-  }
-  TABLE *ptr[3];				// Lock tables for quick update
-  ptr[0]= tables[0].table;
-  ptr[1]= tables[1].table;
-  ptr[2]= tables[2].table;
-  if (!(lock=mysql_lock_tables(thd,ptr,3)))
+  if (simple_open_n_lock_tables(thd, tables))
   {
-    sql_print_error("Fatal error: Can't lock privilege tables: %s",
+    sql_print_error("Fatal error: Can't open and lock privilege tables: %s",
 		    thd->net.last_error);
     goto end;
   }
@@ -425,7 +413,6 @@ my_bool acl_init(THD *org_thd, bool dont_read_acl_tables)
   freeze_size(&acl_dbs);
   init_check_host();
 
-  mysql_unlock_tables(thd, lock);
   initialized=1;
   thd->version--;				// Force close to free memory
   return_val=0;
@@ -3091,7 +3078,6 @@ my_bool grant_init(THD *org_thd)
 {
   THD  *thd;
   TABLE_LIST tables[3];
-  MYSQL_LOCK *lock;
   MEM_ROOT *memex_ptr;
   my_bool return_val= 1;
   TABLE *t_table, *c_table, *p_table;
@@ -3125,15 +3111,7 @@ my_bool grant_init(THD *org_thd)
   tables[0].lock_type=tables[1].lock_type=tables[2].lock_type=TL_READ;
   tables[0].db=tables[1].db=tables[2].db=thd->db;
 
-  uint counter;
-  if (open_tables(thd, tables, &counter))
-    goto end;
-
-  TABLE *ptr[3];				// Lock tables for quick update
-  ptr[0]= tables[0].table;
-  ptr[1]= tables[1].table;
-  ptr[2]= tables[2].table;
-  if (!(lock=mysql_lock_tables(thd,ptr,3)))
+  if (simple_open_n_lock_tables(thd, tables))
     goto end;
 
   t_table = tables[0].table; c_table = tables[1].table;
@@ -3223,7 +3201,6 @@ my_bool grant_init(THD *org_thd)
 end_unlock:
   t_table->file->ha_index_end();
   p_table->file->ha_index_end();
-  mysql_unlock_tables(thd, lock);
   thd->version--;				// Force close to free memory
 
 end:
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index eef86921012..a49d98701cd 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -20,6 +20,7 @@
 #include "mysql_priv.h"
 #include "sql_select.h"
 #include "sp_head.h"
+#include "sp.h"
 #include "sql_trigger.h"
 #include <m_ctype.h>
 #include <my_dir.h>
@@ -359,7 +360,30 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
 
 
 /*
-  Close all tables used by thread
+  Mark all tables in the list which were used by current substatement
+  as free for reuse.
+
+  SYNOPSIS
+    mark_used_tables_as_free_for_reuse()
+      thd   - thread context
+      table - head of the list of tables
+
+  DESCRIPTION
+    Marks all tables in the list which were used by current substatement
+    (they are marked by its query_id) as free for reuse.
+*/
+
+static void mark_used_tables_as_free_for_reuse(THD *thd, TABLE *table)
+{
+  for (; table ; table= table->next)
+    if (table->query_id == thd->query_id)
+      table->query_id= 0;
+}
+
+
+/*
+  Close all tables used by the current substatement, or all tables
+  used by this thread if we are on the upper level.
 
   SYNOPSIS
     close_thread_tables()
@@ -372,14 +396,31 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
   IMPLEMENTATION
     Unlocks tables and frees derived tables.
     Put all normal tables used by thread in free list.
+
+    When in prelocked mode it will only close/mark as free for reuse
+    tables opened by this substatement, it will also check if we are
+    closing tables after execution of complete query (i.e. we are on
+    upper level) and will leave prelocked mode if needed.
 */
 
 void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived, 
                          TABLE *stopper)
 {
   bool found_old_table;
+  prelocked_mode_type prelocked_mode= thd->prelocked_mode;
   DBUG_ENTER("close_thread_tables");
 
+  /*
+    We are assuming here that thd->derived_tables contains ONLY derived
+    tables for this substatement. i.e. instead of approach which uses
+    query_id matching for determining which of the derived tables belong
+    to this substatement we rely on the ability of substatements to
+    save/restore thd->derived_tables during their execution.
+
+    TODO: Probably even better approach is to simply associate list of
+          derived tables with (sub-)statement instead of thread and destroy
+          them at the end of its execution.
+  */
   if (thd->derived_tables && !skip_derived)
   {
     TABLE *table, *next;
@@ -394,10 +435,50 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived,
     }
     thd->derived_tables= 0;
   }
-  if (thd->locked_tables)
+
+  if (prelocked_mode)
+  {
+    /*
+      Mark all temporary tables used by this substatement as free for reuse.
+    */
+    mark_used_tables_as_free_for_reuse(thd, thd->temporary_tables);
+  }
+
+  if (thd->locked_tables || prelocked_mode)
   {
-    ha_commit_stmt(thd);			// If select statement
-    DBUG_VOID_RETURN;				// LOCK TABLES in use
+    /*
+      TODO: It is not 100% clear whenever we should do ha_commit_stmt() for
+            sub-statements. This issue needs additional investigation.
+    */
+    ha_commit_stmt(thd);
+
+    /* We are under simple LOCK TABLES so should not do anything else. */
+    if (!prelocked_mode)
+      DBUG_VOID_RETURN;
+
+    if (!thd->lex->requires_prelocking())
+    {
+      /*
+        If we are executing one of substatements we have to mark
+        all tables which it used as free for reuse.
+      */
+      mark_used_tables_as_free_for_reuse(thd, thd->open_tables);
+      DBUG_VOID_RETURN;
+    }
+
+    DBUG_ASSERT(prelocked_mode);
+    /*
+      We are in prelocked mode, so we have to leave it now with doing
+      implicit UNLOCK TABLES if need.
+    */
+    thd->prelocked_mode= NON_PRELOCKED;
+
+    if (prelocked_mode == PRELOCKED_UNDER_LOCK_TABLES)
+      DBUG_VOID_RETURN;
+
+    thd->lock= thd->locked_tables;
+    thd->locked_tables= 0;
+    /* Fallthrough */
   }
 
   if (thd->lock)
@@ -429,6 +510,17 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived,
   if (!lock_in_use)
     VOID(pthread_mutex_unlock(&LOCK_open));
   /*  VOID(pthread_sigmask(SIG_SETMASK,&thd->signals,NULL)); */
+
+  if (prelocked_mode == PRELOCKED)
+  {
+    /*
+      If we are here then we are leaving normal prelocked mode, so it is
+      good idea to turn off OPTION_TABLE_LOCK flag.
+    */
+    DBUG_ASSERT(thd->lex->requires_prelocking());
+    thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+  }
+
   DBUG_VOID_RETURN;
 }
 
@@ -898,7 +990,8 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
 	  !memcmp(table->s->table_cache_key, key,
 		  key_length + TMP_TABLE_KEY_EXTRA))
       {
-	if (table->query_id == thd->query_id)
+	if (table->query_id == thd->query_id ||
+            thd->prelocked_mode && table->query_id)
 	{
 	  my_error(ER_CANT_REOPEN_TABLE, MYF(0), table->alias);
 	  DBUG_RETURN(0);
@@ -912,16 +1005,17 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     }
   }
 
-  if (thd->locked_tables)
+  if (thd->locked_tables || thd->prelocked_mode)
   {						// Using table locks
     for (table=thd->open_tables; table ; table=table->next)
     {
       if (table->s->key_length == key_length &&
-	  !memcmp(table->s->table_cache_key,key,key_length) &&
-	  !my_strcasecmp(system_charset_info, table->alias, alias))
+          !memcmp(table->s->table_cache_key, key, key_length) &&
+          !my_strcasecmp(system_charset_info, table->alias, alias) &&
+          table->query_id != thd->query_id && /* skip tables already used by this query */
+          !(thd->prelocked_mode && table->query_id))
       {
-	if (table->query_id != thd->query_id)
-	  table->query_id=thd->query_id;
+        table->query_id= thd->query_id;
         DBUG_PRINT("info",("Using locked table"));
 	goto reset;
       }
@@ -1613,21 +1707,34 @@ err:
   SYNOPSIS
     open_tables()
     thd - thread handler
-    start - list of tables
+    start - list of tables in/out
     counter - number of opened tables will be return using this parameter
 
+  NOTE
+    Unless we are already in prelocked mode, this function will also precache
+    all SP/SFs explicitly or implicitly (via views and triggers) used by the
+    query and add tables needed for their execution to table list. If resulting
+    tables list will be non empty it will mark query as requiring precaching.
+    Prelocked mode will be enabled for such query during lock_tables() call.
+
+    If query for which we are opening tables is already marked as requiring
+    prelocking it won't do such precaching and will simply reuse table list
+    which is already built.
+
   RETURN
     0  - OK
     -1 - error
 */
 
-int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
+int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
 {
   TABLE_LIST *tables;
   bool refresh;
   int result=0;
-  DBUG_ENTER("open_tables");
   MEM_ROOT new_frm_mem;
+  /* Also used for indicating that prelocking is need */
+  TABLE_LIST **query_tables_last_own;
+  DBUG_ENTER("open_tables");
   /*
     temporary mem_root for new .frm parsing.
     TODO: variables for size
@@ -1637,8 +1744,51 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
   thd->current_tablenr= 0;
  restart:
   *counter= 0;
+  query_tables_last_own= 0;
   thd->proc_info="Opening tables";
-  for (tables= start; tables ;tables= tables->next_global)
+
+  /*
+    If we are not already executing prelocked statement and don't have
+    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.
+    And current views implementation assumes that view tables are added to
+    global table list only once during PS preparing/first SP execution.
+    Also locking at earlier stage is probably faster altough may decrease
+    concurrency a bit.
+
+    NOTE: We will mark statement as requiring prelocking only if we will
+    have non empty table list. But this does not guarantee that in prelocked
+    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...
+  */
+  if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
+      (thd->lex->spfuns.records || thd->lex->spprocs.records))
+  {
+    TABLE_LIST **save_query_tables_last;
+
+    sp_cache_routines(thd, thd->lex);
+    save_query_tables_last= thd->lex->query_tables_last;
+
+    DBUG_ASSERT(thd->lex->query_tables == *start);
+
+    if (sp_add_sp_tables_to_table_list(thd, thd->lex, thd->lex) ||
+        *start)
+    {
+      query_tables_last_own= save_query_tables_last;
+      *start= thd->lex->query_tables;
+    }
+  }
+
+  for (tables= *start; tables ;tables= tables->next_global)
   {
     /*
       Ignore placeholders for derived tables. After derived tables
@@ -1659,8 +1809,27 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
       if (tables->view)
       {
+        /* VIEW placeholder */
 	(*counter)--;
-	continue; //VIEW placeholder
+        /*
+          Again if needed we have to get cache all routines used by this view
+          and add tables used by them to table list.
+        */
+        if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
+            (tables->view->spfuns.records || tables->view->spprocs.records))
+        {
+          // FIXME We should catch recursion for both views and funcs here
+          sp_cache_routines(thd, tables->view);
+
+          /* We have at least one table in TL here */
+          if (!query_tables_last_own)
+            query_tables_last_own= thd->lex->query_tables_last;
+          sp_add_sp_tables_to_table_list(thd, thd->lex, tables->view);
+        }
+        /* Cleanup hashes because destructo for this LEX is never called */
+        hash_free(&tables->view->spfuns);
+        hash_free(&tables->view->spprocs);
+	continue;
       }
 
       if (refresh)				// Refresh in progress
@@ -1672,7 +1841,12 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
 	thd->version=refresh_version;
 	TABLE **prev_table= &thd->open_tables;
 	bool found=0;
-	for (TABLE_LIST *tmp= start; tmp; tmp= tmp->next_global)
+        /*
+          QQ: What we should do if we have started building of table list
+          for prelocking ??? Probably throw it away ? But before we should
+          mark all temporary tables as free? How about locked ?
+        */
+	for (TABLE_LIST *tmp= *start; tmp; tmp= tmp->next_global)
 	{
 	  /* Close normal (not temporary) changed tables */
 	  if (tmp->table && ! tmp->table->s->tmp_table)
@@ -1701,7 +1875,27 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
       break;
     }
     else
+    {
+      /*
+        If we are not already in prelocked mode and extended table list is not
+        yet built and we have trigger for table being opened then we should
+        cache all routines used by its triggers and add their tables to
+        prelocking list.
+        If we lock table for reading we won't update it so there is no need to
+        process its triggers since they never will be activated.
+
+        FIXME Now we are simply turning on prelocking. Proper integration
+        and testing is to be done later.
+      */
+      if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
+          tables->table->triggers &&
+          tables->lock_type >= TL_WRITE_ALLOW_WRITE)
+      {
+        if (!query_tables_last_own)
+            query_tables_last_own= thd->lex->query_tables_last;
+      }
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
+    }
 
     if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables)
       tables->table->reginfo.lock_type=tables->lock_type;
@@ -1709,6 +1903,10 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
   }
   thd->proc_info=0;
   free_root(&new_frm_mem, MYF(0));              // Free pre-alloced block
+
+  if (query_tables_last_own)
+    thd->lex->mark_as_requiring_prelocking(query_tables_last_own);
+
   DBUG_RETURN(result);
 }
 
@@ -1757,6 +1955,11 @@ static bool check_lock_and_start_stmt(THD *thd, TABLE *table,
     table_list		Table to open is first table in this list
     lock_type		Lock to use for open
 
+  NOTE
+    This function don't do anything like SP/SF/views/triggers analysis done
+    in open_tables(). It is intended for opening of only one concrete table.
+    And used only in special contexts.
+
   RETURN VALUES
     table		Opened table
     0			Error
@@ -1831,7 +2034,7 @@ int simple_open_n_lock_tables(THD *thd, TABLE_LIST *tables)
 {
   DBUG_ENTER("simple_open_n_lock_tables");
   uint counter;
-  if (open_tables(thd, tables, &counter) || lock_tables(thd, tables, counter))
+  if (open_tables(thd, &tables, &counter) || lock_tables(thd, tables, counter))
     DBUG_RETURN(-1);				/* purecov: inspected */
   DBUG_RETURN(0);
 }
@@ -1858,7 +2061,7 @@ bool open_and_lock_tables(THD *thd, TABLE_LIST *tables)
 {
   uint counter;
   DBUG_ENTER("open_and_lock_tables");
-  if (open_tables(thd, tables, &counter) ||
+  if (open_tables(thd, &tables, &counter) ||
       lock_tables(thd, tables, counter) ||
       mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
       (thd->fill_derived_tables() &&
@@ -1891,7 +2094,7 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables)
   uint counter;
   DBUG_ENTER("open_normal_and_derived_tables");
   DBUG_ASSERT(!thd->fill_derived_tables());
-  if (open_tables(thd, tables, &counter) ||
+  if (open_tables(thd, &tables, &counter) ||
       mysql_handle_derived(thd->lex, &mysql_derived_prepare))
     DBUG_RETURN(TRUE); /* purecov: inspected */
   relink_tables_for_multidelete(thd);           // Not really needed, but
@@ -1925,6 +2128,27 @@ static void relink_tables_for_multidelete(THD *thd)
 
 
 /*
+  Mark all real tables in the list as free for reuse.
+
+  SYNOPSIS
+    mark_real_tables_as_free_for_reuse()
+      thd   - thread context
+      table - head of the list of tables
+
+  DESCRIPTION
+    Marks all real tables in the list (i.e. not views, derived
+    or schema tables) as free for reuse.
+*/
+
+static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table)
+{
+  for (; table; table= table->next_global)
+    if (!table->placeholder() && !table->schema_table)
+      table->table->query_id= 0;
+}
+
+
+/*
   Lock all tables in list
 
   SYNOPSIS
@@ -1938,6 +2162,10 @@ static void relink_tables_for_multidelete(THD *thd)
     handling thr_lock gives us.  You most always get all needed locks at
     once.
 
+    If query for which we are calling this function marked as requring
+    prelocking, this function will do implicit LOCK TABLES and change
+    thd::prelocked_mode accordingly.
+
   RETURN VALUES
    0	ok
    -1	Error
@@ -1946,36 +2174,125 @@ static void relink_tables_for_multidelete(THD *thd)
 int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
 {
   TABLE_LIST *table;
+
+  DBUG_ENTER("lock_tables");
+  /*
+    We can't meet statement requiring prelocking if we already
+    in prelocked mode.
+  */
+  DBUG_ASSERT(!thd->prelocked_mode || !thd->lex->requires_prelocking());
+  /*
+    If statement requires prelocking then it has non-empty table list.
+    So it is safe to shortcut.
+  */
+  DBUG_ASSERT(!thd->lex->requires_prelocking() || tables);
+
   if (!tables)
-    return 0;
+    DBUG_RETURN(0);
 
-  if (!thd->locked_tables)
+  /*
+    We need this extra check for thd->prelocked_mode because we want to avoid
+    attempts to lock tables in substatements. Checking for thd->locked_tables
+    is not enough in some situations. For example for SP containing
+    "drop table t3; create temporary t3 ..; insert into t3 ...;"
+    thd->locked_tables may be 0 after drop tables, and without this extra
+    check insert will try to lock temporary table t3, that will lead
+    to memory leak...
+  */
+  if (!thd->locked_tables && !thd->prelocked_mode)
   {
     DBUG_ASSERT(thd->lock == 0);	// You must lock everything at once
     TABLE **start,**ptr;
+
     if (!(ptr=start=(TABLE**) thd->alloc(sizeof(TABLE*)*count)))
-      return -1;
+      DBUG_RETURN(-1);
     for (table= tables; table; table= table->next_global)
     {
       if (!table->placeholder() && !table->schema_table)
 	*(ptr++)= table->table;
     }
+
+    /* We have to emulate LOCK TABLES if we are statement needs prelocking. */
+    if (thd->lex->requires_prelocking())
+    {
+      thd->in_lock_tables=1;
+      thd->options|= OPTION_TABLE_LOCK;
+    }
+
     if (!(thd->lock=mysql_lock_tables(thd,start, (uint) (ptr - start))))
-      return -1;				/* purecov: inspected */
+    {
+      if (thd->lex->requires_prelocking())
+      {
+        thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+        thd->in_lock_tables=0;
+      }
+      DBUG_RETURN(-1);
+    }
+    if (thd->lex->requires_prelocking() &&
+        thd->lex->sql_command != SQLCOM_LOCK_TABLES)
+    {
+      TABLE_LIST *first_not_own= thd->lex->first_not_own_table();
+      /*
+        We just have done implicit LOCK TABLES, and now we have
+        to emulate first open_and_lock_tables() after it.
+
+        Note that "LOCK TABLES" can also be marked as requiring prelocking
+        (e.g. if one locks view which uses functions). We should not emulate
+        such open_and_lock_tables() in this case. We also should not set
+        THD::prelocked_mode or first close_thread_tables() call will do
+        "UNLOCK TABLES".
+      */
+      thd->locked_tables= thd->lock;
+      thd->lock= 0;
+      thd->in_lock_tables=0;
+
+      for (table= tables; table != first_not_own; table= table->next_global)
+      {
+        if (!table->placeholder() && !table->schema_table)
+        {
+          table->table->query_id= thd->query_id;
+          if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
+          {
+            ha_rollback_stmt(thd);
+            mysql_unlock_tables(thd, thd->locked_tables);
+            thd->locked_tables= 0;
+            thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+            DBUG_RETURN(-1);
+          }
+        }
+      }
+      /*
+        Let us mark all tables which don't belong to the statement itself,
+        and was marked as occupied during open_tables() as free for reuse.
+      */
+      mark_real_tables_as_free_for_reuse(first_not_own);
+      thd->prelocked_mode= PRELOCKED;
+    }
   }
   else
   {
-    for (table= tables; table; table= table->next_global)
+    TABLE_LIST *first_not_own= thd->lex->first_not_own_table();
+    for (table= tables; table != first_not_own; table= table->next_global)
     {
-      if (!table->placeholder() && 
+      if (!table->placeholder() && !table->schema_table &&
 	  check_lock_and_start_stmt(thd, table->table, table->lock_type))
       {
 	ha_rollback_stmt(thd);
-	return -1;
+	DBUG_RETURN(-1);
       }
     }
+    /*
+      If we are under explicit LOCK TABLES and our statement requires
+      prelocking, we should mark all "additional" tables as free for use
+      and enter prelocked mode.
+    */
+    if (thd->lex->requires_prelocking())
+    {
+      mark_real_tables_as_free_for_reuse(first_not_own);
+      thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES;
+    }
   }
-  return 0;
+  DBUG_RETURN(0);
 }
 
 
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 32c9e2a50f7..e93ea6ac83c 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -272,6 +272,7 @@ THD::THD()
     ulong tmp=sql_rnd_with_mutex();
     randominit(&rand, tmp + (ulong) &rand, tmp + (ulong) ::query_id);
   }
+  prelocked_mode= NON_PRELOCKED;
 }
 
 
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 32a2390a402..aa1952345e9 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -778,6 +778,15 @@ typedef I_List<Item_change_record> Item_change_list;
 
 
 /*
+  Type of prelocked mode.
+  See comment for THD::prelocked_mode for complete description.
+*/
+
+enum prelocked_mode_type {NON_PRELOCKED= 0, PRELOCKED= 1,
+                          PRELOCKED_UNDER_LOCK_TABLES= 2};
+
+
+/*
   For each client connection we create a separate thread with THD serving as
   a thread/connection descriptor
 */
@@ -877,7 +886,13 @@ public:
     See also lock_tables() for details.
   */
   MYSQL_LOCK	*lock;				/* Current locks */
-  MYSQL_LOCK	*locked_tables;			/* Tables locked with LOCK */
+  /*
+    Tables that were locked with explicit or implicit LOCK TABLES.
+    (Implicit LOCK TABLES happens when we are prelocking tables for
+     execution of statement which uses stored routines. See description
+     THD::prelocked_mode for more info.)
+  */
+  MYSQL_LOCK	*locked_tables;
   HASH		handler_tables_hash;
   /*
     One thread can hold up to one named user-level lock. This variable
@@ -1032,8 +1047,6 @@ public:
   sp_rcontext *spcont;		// SP runtime context
   sp_cache   *sp_proc_cache;
   sp_cache   *sp_func_cache;
-  bool       shortcut_make_view; /* Don't do full mysql_make_view()
-				    during pre-opening of tables. */
 
   /*
     If we do a purge of binary logs, log index info of the threads
@@ -1049,6 +1062,31 @@ public:
     long    long_value;
   } sys_var_tmp;
 
+  /*
+    prelocked_mode_type enum and prelocked_mode member are used for
+    indicating whenever "prelocked mode" is on, and what type of
+    "prelocked mode" is it.
+
+    Prelocked mode is used for execution of queries which explicitly
+    or implicitly (via views or triggers) use functions, thus may need
+    some additional tables (mentioned in query table list) for their
+    execution.
+
+    First open_tables() call for such query will analyse all functions
+    used by it and add all additional tables to table its list. It will
+    also mark this query as requiring prelocking. After that lock_tables()
+    will issue implicit LOCK TABLES for the whole table list and change
+    thd::prelocked_mode to non-0. All queries called in functions invoked
+    by the main query will use prelocked tables. Non-0 prelocked_mode
+    will also surpress mentioned analysys in those queries thus saving
+    cycles. Prelocked mode will be turned off once close_thread_tables()
+    for the main query will be called.
+
+    Note: Since not all "tables" present in table list are really locked
+    thd::relocked_mode does not imply thd::locked_tables.
+  */
+  prelocked_mode_type prelocked_mode;
+
   THD();
   ~THD();
 
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index dd2ac3c013b..bb48b7ada77 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -187,7 +187,7 @@ bool mysql_ha_open(THD *thd, TABLE_LIST *tables, bool reopen)
 
   /* for now HANDLER can be used only for real TABLES */
   tables->required_type= FRMTYPE_TABLE;
-  error= open_tables(thd, tables, &counter);
+  error= open_tables(thd, &tables, &counter);
 
   HANDLER_TABLES_HACK(thd);
   if (error)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 30c657c3b79..da89f7e0698 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -169,13 +169,12 @@ void lex_start(THD *thd, uchar *buf,uint length)
   lex->sphead= NULL;
   lex->spcont= NULL;
   lex->proc_list.first= 0;
+  lex->query_tables_own_last= 0;
 
   if (lex->spfuns.records)
     my_hash_reset(&lex->spfuns);
   if (lex->spprocs.records)
     my_hash_reset(&lex->spprocs);
-  if (lex->sptabs.records)
-    my_hash_reset(&lex->sptabs);
   DBUG_VOID_RETURN;
 }
 
@@ -1843,6 +1842,8 @@ TABLE_LIST *st_lex::unlink_first_table(bool *link_to_local)
     */
     if ((query_tables= query_tables->next_global))
       query_tables->prev_global= &query_tables;
+    else
+      query_tables_last= &query_tables;
     first->next_global= 0;
 
     /*
@@ -1948,6 +1949,8 @@ void st_lex::link_first_table_back(TABLE_LIST *first,
   {
     if ((first->next_global= query_tables))
       query_tables->prev_global= &first->next_global;
+    else
+      query_tables_last= &first->next_global;
     query_tables= first;
 
     if (link_to_local)
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5d232d60e79..d8f5fa7f346 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -749,7 +749,6 @@ typedef struct st_lex
   sp_pcontext *spcont;
   HASH spfuns;		/* Called functions */
   HASH spprocs;		/* Called procedures */
-  HASH sptabs;		/* Merged table lists */
   st_sp_chistics sp_chistics;
   bool only_view;       /* used for SHOW CREATE TABLE/VIEW */
   /*
@@ -768,23 +767,25 @@ typedef struct st_lex
   */
   SQL_LIST trg_table_fields;
 
-  st_lex() :result(0), sql_command(SQLCOM_END)
+  /*
+    If non-0 then indicates that query requires prelocking and points to
+    next_global member of last own element in query table list (i.e. last
+    table which was not added to it as part of preparation to prelocking).
+    0 - indicates that this query does not need prelocking.
+  */
+  TABLE_LIST **query_tables_own_last;
+
+  st_lex() :result(0), sql_command(SQLCOM_END), query_tables_own_last(0)
   {
     extern byte *sp_lex_sp_key(const byte *ptr, uint *plen, my_bool first);
-    extern byte *sp_table_key(const byte *ptr, uint *plen, my_bool first);
     hash_init(&spfuns, system_charset_info, 0, 0, 0, sp_lex_sp_key, 0, 0);
     hash_init(&spprocs, system_charset_info, 0, 0, 0, sp_lex_sp_key, 0, 0);
-    hash_init(&sptabs, system_charset_info, 0, 0, 0, sp_table_key, 0, 0);
   }
   
   ~st_lex()
   {
-    if (spfuns.array.buffer)
-      hash_free(&spfuns);
-    if (spprocs.array.buffer)
-      hash_free(&spprocs);
-    if (sptabs.array.buffer)
-      hash_free(&sptabs);
+    hash_free(&spfuns);
+    hash_free(&spprocs);
   }
 
   inline void uncacheable(uint8 cause)
@@ -821,6 +822,21 @@ typedef struct st_lex
   bool can_not_use_merged();
   bool only_view_structure();
   bool need_correct_ident();
+
+  inline bool requires_prelocking()
+  {
+    return query_tables_own_last;
+  }
+  inline void mark_as_requiring_prelocking(TABLE_LIST **tables_own_last)
+  {
+    query_tables_own_last= tables_own_last;
+  }
+  /* Return pointer to first not-own table in query-tables or 0 */
+  TABLE_LIST* first_not_own_table()
+  {
+    return ( query_tables_own_last ? *query_tables_own_last : 0);
+  }
+
 } LEX;
 
 struct st_lex_local: public st_lex
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 0a0258465fb..a9cc4ac5753 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1682,7 +1682,8 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
 	in embedded server - just store them to be executed later 
       */
 #ifndef EMBEDDED_LIBRARY
-      if (thd->lock || thd->open_tables || thd->derived_tables)
+      if (thd->lock || thd->open_tables || thd->derived_tables ||
+          thd->prelocked_mode)
         close_thread_tables(thd);
 #endif
       ulong length= thd->query_length-(ulong)(packet-thd->query);
@@ -2003,7 +2004,8 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     my_message(ER_UNKNOWN_COM_ERROR, ER(ER_UNKNOWN_COM_ERROR), MYF(0));
     break;
   }
-  if (thd->lock || thd->open_tables || thd->derived_tables)
+  if (thd->lock || thd->open_tables || thd->derived_tables ||
+      thd->prelocked_mode)
   {
     thd->proc_info="closing tables";
     close_thread_tables(thd);			/* Free tables */
@@ -2231,12 +2233,7 @@ mysql_execute_command(THD *thd)
   TABLE_LIST *all_tables;
   /* most outer SELECT_LEX_UNIT of query */
   SELECT_LEX_UNIT *unit= &lex->unit;
-  /* Locked closure of all tables */
-  TABLE_LIST *locked_tables= NULL;
   /* Saved variable value */
-#ifdef HAVE_INNOBASE_DB
-  my_bool old_innodb_table_locks= thd->variables.innodb_table_locks;
-#endif
   DBUG_ENTER("mysql_execute_command");
 
   /*
@@ -2258,100 +2255,14 @@ mysql_execute_command(THD *thd)
   /* should be assigned after making first tables same */
   all_tables= lex->query_tables;
 
-  thd->shortcut_make_view= 0;
-  if (lex->sql_command != SQLCOM_CREATE_PROCEDURE &&
-      lex->sql_command != SQLCOM_CREATE_SPFUNCTION &&
-      lex->sql_command != SQLCOM_LOCK_TABLES &&
-      lex->sql_command != SQLCOM_UNLOCK_TABLES)
-  {
-    while (1)
-    {
-      if (sp_cache_routines(thd, lex, TYPE_ENUM_FUNCTION))
-	DBUG_RETURN(-1);
-      if (sp_cache_routines(thd, lex, TYPE_ENUM_PROCEDURE))
-	DBUG_RETURN(-1);
-      if (!thd->locked_tables &&
-	  lex->sql_command != SQLCOM_CREATE_TABLE &&
-	  lex->sql_command != SQLCOM_CREATE_VIEW)
-      {
-	MEM_ROOT *thdmemroot= NULL;
-
-	sp_merge_routine_tables(thd, lex);
-	// QQ Preopen tables to find views and triggers.
-	// This means we open, close and open again, which sucks, but
-	// right now it's the easiest way to get it to work. A better
-	// solution will hopefully be found soon...
-	if (lex->sptabs.records || lex->query_tables)
-	{
-	  uint procs, funs, tabs;
-
-	  if (thd->mem_root != thd->current_arena->mem_root)
-	  {
-	    thdmemroot= thd->mem_root;
-	    thd->mem_root= thd->current_arena->mem_root;
-	  }
-	  if (!sp_merge_table_list(thd, &lex->sptabs, lex->query_tables))
-	    DBUG_RETURN(-1);
-	  procs= lex->spprocs.records;
-	  funs= lex->spfuns.records;
-	  tabs= lex->sptabs.records;
-
-	  if ((locked_tables= sp_hash_to_table_list(thd, &lex->sptabs)))
-	  {
-	    // We don't want these updated now
-	    uint ctmpdtabs= thd->status_var.created_tmp_disk_tables;
-	    uint ctmptabs= thd->status_var.created_tmp_tables;
-	    uint count;
-
-	    thd->shortcut_make_view= TRUE;
-	    open_tables(thd, locked_tables, &count);
-	    thd->shortcut_make_view= FALSE;
-	    close_thread_tables(thd);
-	    thd->status_var.created_tmp_disk_tables= ctmpdtabs;
-	    thd->status_var.created_tmp_tables= ctmptabs;
-	    thd->clear_error();
-	    mysql_reset_errors(thd);
-	    locked_tables= NULL;
-	  }
-	  // A kludge: Decrease all temp. table's query ids to allow a
-	  // second opening.
-	  for (TABLE *table= thd->temporary_tables; table ; table=table->next)
-	    table->query_id-= 1;
-	  if (procs < lex->spprocs.records ||
-	      funs < lex->spfuns.records ||
-	      tabs < lex->sptabs.records)
-	  {
-	    if (thdmemroot)
-	      thd->mem_root= thdmemroot;
-	    continue;		// Found more SPs or tabs, try again
-	  }
-	}
-	if (lex->sptabs.records &&
-	    (lex->spfuns.records || lex->spprocs.records) &&
-	    sp_merge_table_list(thd, &lex->sptabs, lex->query_tables))
-	{
-	  if ((locked_tables= sp_hash_to_table_list(thd, &lex->sptabs)))
-	  {
-#ifdef HAVE_INNOBASE_DB
-	    thd->variables.innodb_table_locks= FALSE;
-#endif
-	    sp_open_and_lock_tables(thd, locked_tables);
-	  }
-	}
-	if (thdmemroot)
-	  thd->mem_root= thdmemroot;
-      }
-      break;
-    } // while (1)
-  }
-
   /*
     Reset warning count for each query that uses tables
     A better approach would be to reset this for any commands
     that is not a SHOW command or a select that only access local
     variables, but for now this is probably good enough.
   */
-  if (all_tables || &lex->select_lex != lex->all_selects_list)
+  if (all_tables || &lex->select_lex != lex->all_selects_list ||
+      lex->spfuns.records || lex->spprocs.records)
     mysql_reset_errors(thd);
 
 #ifdef HAVE_REPLICATION
@@ -2580,9 +2491,8 @@ mysql_execute_command(THD *thd)
     break;
   }
   case SQLCOM_DO:
-    if (all_tables &&
-	(check_table_access(thd, SELECT_ACL, all_tables, 0) ||
-         open_and_lock_tables(thd, all_tables)))
+    if (check_table_access(thd, SELECT_ACL, all_tables, 0) ||
+        open_and_lock_tables(thd, all_tables))
       goto error;
 
     res= mysql_do(thd, *lex->insert_list);
@@ -3454,8 +3364,7 @@ unsent_create_error:
   case SQLCOM_SET_OPTION:
   {
     List<set_var_base> *lex_var_list= &lex->var_list;
-    if (all_tables &&
-	(check_table_access(thd, SELECT_ACL, all_tables, 0) ||
+    if ((check_table_access(thd, SELECT_ACL, all_tables, 0) ||
 	 open_and_lock_tables(thd, all_tables)))
       goto error;
     if (lex->one_shot_set && not_all_support_one_shot(lex_var_list))
@@ -3501,7 +3410,7 @@ unsent_create_error:
     thd->in_lock_tables=1;
     thd->options|= OPTION_TABLE_LOCK;
 
-    if (!(res= open_and_lock_tables(thd, all_tables)))
+    if (!(res= simple_open_n_lock_tables(thd, all_tables)))
     {
 #ifdef HAVE_QUERY_CACHE
       if (thd->variables.query_cache_wlock_invalidate)
@@ -4004,7 +3913,20 @@ unsent_create_error:
     {
       sp_head *sp;
 
-      if (!(sp= sp_find_procedure(thd, lex->spname)))
+      /*
+        This will cache all SP and SF and open and lock all tables
+        required for execution.
+      */
+      if (check_table_access(thd, SELECT_ACL, all_tables, 0) ||
+	  open_and_lock_tables(thd, all_tables))
+       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
+      */
+      if (!(sp= sp_find_procedure(thd, lex->spname, TRUE)))
       {
 	my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "PROCEDURE",
                  lex->spname->m_qname.str);
@@ -4019,12 +3941,6 @@ unsent_create_error:
         /* bits that should be cleared in thd->server_status */
 	uint bits_to_be_cleared= 0;
 
-	/* In case the arguments are subselects... */
-	if (all_tables &&
-	    (check_table_access(thd, SELECT_ACL, all_tables, 0) ||
-	     open_and_lock_tables(thd, all_tables)))
-          goto error;
-
 #ifndef EMBEDDED_LIBRARY
 	my_bool nsok= thd->net.no_send_ok;
 	thd->net.no_send_ok= TRUE;
@@ -4348,14 +4264,6 @@ cleanup:
       thd->lock= 0;
   }
 
-  if (locked_tables)
-  {
-#ifdef HAVE_INNOBASE_DB
-    thd->variables.innodb_table_locks= old_innodb_table_locks;
-#endif
-    if (thd->locked_tables)
-      sp_unlock_tables(thd);
-  }
   DBUG_RETURN(res || thd->net.report_error);
 }
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 93197b1a2eb..32cf2dea2b1 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1004,7 +1004,7 @@ static int mysql_test_update(Prepared_statement *stmt,
   if (update_precheck(thd, table_list))
     DBUG_RETURN(1);
 
-  if (!open_tables(thd, table_list, &table_count))
+  if (!open_tables(thd, &table_list, &table_count))
   {
     if (table_list->ancestor && table_list->ancestor->next_local)
     {
@@ -1545,22 +1545,6 @@ static int check_prepared_statement(Prepared_statement *stmt,
   lex->first_lists_tables_same();
   tables= lex->query_tables;
 
-  /*
-    Preopen 'proc' system table and cache all functions used in this
-    statement. We must do that before we open ordinary tables to avoid
-    deadlocks. We can't open and lock any table once query tables were
-    opened.
-  */
-  if (lex->sql_command != SQLCOM_CREATE_PROCEDURE &&
-      lex->sql_command != SQLCOM_CREATE_SPFUNCTION)
-  {
-    /* The error is printed inside */
-    if (sp_cache_routines(thd, lex, TYPE_ENUM_FUNCTION))
-      DBUG_RETURN(-1);
-    if (sp_cache_routines(thd, lex, TYPE_ENUM_PROCEDURE))
-      DBUG_RETURN(-1);
-  }
-
   switch (sql_command) {
   case SQLCOM_REPLACE:
   case SQLCOM_INSERT:
@@ -1794,9 +1778,9 @@ bool mysql_stmt_prepare(THD *thd, char *packet, uint packet_length,
     thd->lex->sphead= NULL;
   }
   lex_end(lex);
+  close_thread_tables(thd);
   thd->restore_backup_statement(stmt, &thd->stmt_backup);
   cleanup_items(stmt->free_list);
-  close_thread_tables(thd);
   thd->rollback_item_tree_changes();
   thd->cleanup_after_query();
   thd->current_arena= thd;
@@ -1873,6 +1857,11 @@ void reset_stmt_for_execute(THD *thd, LEX *lex)
     to indicate the table is altered, and re-do the setup_* 
     and open the tables back.
   */
+  /*
+    NOTE: We should reset whole table list here including all tables added
+    by prelocking algorithm (it is not a problem for substatements since
+    they have their own table list).
+  */
   for (TABLE_LIST *tables= lex->query_tables;
 	 tables;
 	 tables= tables->next_global)
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 82e7c1ce023..7dd6734eb89 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -42,14 +42,21 @@ public:
 
     if (bodies[event][time_type])
     {
-      /*
-        Similar to function invocation we don't need to surpress sending of
-        ok packets here because don't allow execute statements from trigger.
+#ifndef EMBEDDED_LIBRARY
+      /* Surpress OK packets in case if we will execute statements */
+      my_bool nsok= thd->net.no_send_ok;
+      thd->net.no_send_ok= TRUE;
+#endif
 
+      /*
         FIXME: We should juggle with security context here (because trigger
         should be invoked with creator rights).
       */
       res= bodies[event][time_type]->execute_function(thd, 0, 0, 0);
+
+#ifndef EMBEDDED_LIBRARY
+      thd->net.no_send_ok= nsok;
+#endif
     }
 
     return res;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index f9df1be2abd..641c29f9b32 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -136,7 +136,7 @@ int mysql_update(THD *thd,
 
   LINT_INIT(timestamp_query_id);
 
-  if (open_tables(thd, table_list, &table_count))
+  if (open_tables(thd, &table_list, &table_count))
     DBUG_RETURN(1);
 
   if (table_list->ancestor && table_list->ancestor->next_local)
@@ -637,7 +637,7 @@ bool mysql_multi_update_prepare(THD *thd)
   thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
 
   /* open tables and create derived ones, but do not lock and fill them */
-  if ((original_multiupdate && open_tables(thd, table_list, & table_count)) ||
+  if ((original_multiupdate && open_tables(thd, &table_list, & table_count)) ||
       mysql_handle_derived(lex, &mysql_derived_prepare))
     DBUG_RETURN(TRUE);
   /*
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 456f513dab0..0862ff84b46 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -19,6 +19,7 @@
 #include "sql_select.h"
 #include "parse_file.h"
 #include "sp.h"
+#include "sp_head.h"
 
 #define MD5_BUFF_LENGTH 33
 
@@ -613,10 +614,7 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
   table->view= lex= thd->lex= (LEX*) new(thd->mem_root) st_lex_local;
   lex_start(thd, (uchar*)table->query.str, table->query.length);
   view_select= &lex->select_lex;
-  /* Only if we're not in the pre-open phase */
-  if (!thd->shortcut_make_view)
-    view_select->select_number= ++thd->select_number;
-  old_lex->derived_tables|= DERIVED_VIEW;
+  view_select->select_number= ++thd->select_number;
   {
     ulong options= thd->options;
     /* switch off modes which can prevent normal parsing of VIEW
@@ -660,35 +658,13 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
     TABLE_LIST *view_tables_tail= 0;
     TABLE_LIST *tbl;
 
-    /* move SP to main LEX */
-    if (lex->spfuns.records)
-      sp_merge_hash(&old_lex->spfuns, &lex->spfuns);
-
-    /* cleanup LEX */
-    if (lex->spfuns.array.buffer)
-      hash_free(&lex->spfuns);
-    if (lex->spprocs.array.buffer)
-      hash_free(&lex->spprocs);
-    if (lex->sptabs.array.buffer)
-      hash_free(&lex->sptabs);
-
-    /* If we're pre-opening tables to find SPs and tables we need
-       not go any further; doing so will cause an infinite loop. */
-    if (thd->shortcut_make_view)
-    {
-      extern bool
-	sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
-			    LEX *lex_for_tmp_check = 0);
-
-      sp_merge_table_list(thd, &old_lex->sptabs, view_tables);
-      goto ok;
-    }
-
     /*
-      check rights to run commands (EXPLAIN SELECT & SHOW CREATE) which show
-      underlying tables
+      Check rights to run commands (EXPLAIN SELECT & SHOW CREATE) which show
+      underlying tables.
+      Skip this step if we are opening view for prelocking only.
     */
-    if ((old_lex->sql_command == SQLCOM_SELECT && old_lex->describe))
+    if (!table->prelocking_placeholder &&
+        (old_lex->sql_command == SQLCOM_SELECT && old_lex->describe))
     {
       if (check_table_access(thd, SELECT_ACL, view_tables, 1) &&
           check_table_access(thd, SHOW_VIEW_ACL, table, 1))
@@ -697,7 +673,8 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
         goto err;
       }
     }
-    else if (old_lex->sql_command == SQLCOM_SHOW_CREATE)
+    else if (!table->prelocking_placeholder &&
+             old_lex->sql_command == SQLCOM_SHOW_CREATE)
     {
       if (check_table_access(thd, SHOW_VIEW_ACL, table, 0))
         goto err;
@@ -715,13 +692,6 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
       tbl->belong_to_view= top_view;
     }
 
-    /* move SQL_NO_CACHE & Co to whole query */
-    old_lex->safe_to_cache_query= (old_lex->safe_to_cache_query &&
-				   lex->safe_to_cache_query);
-    /* move SQL_CACHE to whole query */
-    if (view_select->options & OPTION_TO_QUERY_CACHE)
-      old_lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
-
     /*
       Put tables of VIEW after VIEW TABLE_LIST
 
@@ -738,13 +708,30 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
       }
       else
       {
-        lex->query_tables_last= &view_tables_tail->next_global;
+        old_lex->query_tables_last= &view_tables_tail->next_global;
       }
       view_tables->prev_global= &table->next_global;
       table->next_global= view_tables;
     }
 
     /*
+      If we are opening this view as part of implicit LOCK TABLES, then
+      this view serves as simple placeholder and we should not continue
+      further processing.
+    */
+    if (table->prelocking_placeholder)
+      goto ok2;
+
+    old_lex->derived_tables|= DERIVED_VIEW;
+
+    /* move SQL_NO_CACHE & Co to whole query */
+    old_lex->safe_to_cache_query= (old_lex->safe_to_cache_query &&
+				   lex->safe_to_cache_query);
+    /* move SQL_CACHE to whole query */
+    if (view_select->options & OPTION_TO_QUERY_CACHE)
+      old_lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
+
+    /*
       check MERGE algorithm ability
       - algorithm is not explicit TEMPORARY TABLE
       - VIEW SELECT allow merging
@@ -848,8 +835,6 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
     goto err;
 
 ok:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
   /* global SELECT list linking */
   end= view_select;	// primary SELECT_LEX is always last
   end->link_next= old_lex->all_selects_list;
@@ -858,6 +843,9 @@ ok:
   lex->all_selects_list->link_prev=
     (st_select_lex_node**)&old_lex->all_selects_list;
 
+ok2:
+  if (arena)
+    thd->restore_backup_item_arena(arena, &backup);
   thd->lex= old_lex;
   DBUG_RETURN(0);
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index c4649c57269..cfba4d7255b 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1548,12 +1548,12 @@ sp_opt_inout:
 
 sp_proc_stmts:
 	  /* Empty */ {}
-	| sp_proc_stmts  { Lex->query_tables= 0; } sp_proc_stmt ';'
+	| sp_proc_stmts  sp_proc_stmt ';'
 	;
 
 sp_proc_stmts1:
 	  sp_proc_stmt ';' {}
-	| sp_proc_stmts1  { Lex->query_tables= 0; } sp_proc_stmt ';'
+	| sp_proc_stmts1  sp_proc_stmt ';'
 	;
 
 sp_decls:
@@ -1587,13 +1587,15 @@ sp_decls:
 	;
 
 sp_decl:
-	  DECLARE_SYM sp_decl_idents type sp_opt_default
+	  DECLARE_SYM sp_decl_idents type 
+          { Lex->sphead->reset_lex(YYTHD); }
+          sp_opt_default
 	  {
 	    LEX *lex= Lex;
 	    sp_pcontext *ctx= lex->spcont;
 	    uint max= ctx->context_pvars();
 	    enum enum_field_types type= (enum enum_field_types)$3;
-	    Item *it= $4;
+	    Item *it= $5;
 
 	    for (uint i = max-$2 ; i < max ; i++)
 	    {
@@ -1605,15 +1607,19 @@ sp_decl:
 	        sp_instr_set *in= new sp_instr_set(lex->sphead->instructions(),
 	                                           ctx,
 						   ctx->pvar_context2index(i),
-						   it, type);
+                                                   it, type, lex,
+                                                   (i == max - 1));
 
-		in->tables= lex->query_tables;
-		lex->query_tables= 0;
+                /*
+                  The last instruction is assigned to be responsible for
+                  freeing LEX.
+                */
 	        lex->sphead->add_instr(in);
 	        ctx->set_isset(i, TRUE);
 		ctx->set_default(i, it);
 	      }
 	    }
+            lex->sphead->restore_lex(YYTHD);
 	    $$.vars= $2;
 	    $$.conds= $$.hndlrs= $$.curs= 0;
 	  }
@@ -1863,15 +1869,17 @@ sp_proc_stmt:
 	      my_message(ER_SP_NO_USE, ER(ER_SP_NO_USE), MYF(0));
 	      YYABORT;
 	    }
-	    /* Don't add an instruction for empty SET statements.
-	    ** (This happens if the SET only contained local variables,
-	    **  which get their set instructions generated separately.)
+	    /*
+              Don't add an instruction for SET statements, since all
+              instructions for them were already added during processing
+              of "set" rule.
 	    */
-	    if (lex->sql_command != SQLCOM_SET_OPTION ||
-		! lex->var_list.is_empty())
+            DBUG_ASSERT(lex->sql_command != SQLCOM_SET_OPTION ||
+                        lex->var_list.is_empty());
+            if (lex->sql_command != SQLCOM_SET_OPTION)
 	    {
 	      sp_instr_stmt *i=new sp_instr_stmt(sp->instructions(),
-						 lex->spcont);
+						 lex->spcont, lex);
 
 	      /* Extract the query statement from the tokenizer:
                  The end is either lex->tok_end or tok->ptr. */
@@ -1882,17 +1890,18 @@ sp_proc_stmt:
 	      i->m_query.str= strmake_root(YYTHD->mem_root,
 					   (char *)sp->m_tmp_query,
 					   i->m_query.length);
-	      i->set_lex(lex);
 	      sp->add_instr(i);
-	      lex->sp_lex_in_use= TRUE;
             }
 	    sp->restore_lex(YYTHD);
           }
-	| RETURN_SYM expr
+          | RETURN_SYM 
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr
 	  {
 	    LEX *lex= Lex;
+	    sp_head *sp= lex->sphead;
 
-	    if (lex->sphead->m_type == TYPE_ENUM_PROCEDURE)
+	    if (sp->m_type == TYPE_ENUM_PROCEDURE)
 	    {
 	      my_message(ER_SP_BADRETURN, ER(ER_SP_BADRETURN), MYF(0));
 	      YYABORT;
@@ -1901,12 +1910,12 @@ sp_proc_stmt:
 	    {
 	      sp_instr_freturn *i;
 
-	      i= new sp_instr_freturn(lex->sphead->instructions(),
-				      lex->spcont,
-		                      $2, lex->sphead->m_returns);
-	      lex->sphead->add_instr(i);
-	      lex->sphead->m_has_return= TRUE;
+	      i= new sp_instr_freturn(sp->instructions(), lex->spcont,
+		                      $3, sp->m_returns, lex);
+	      sp->add_instr(i);
+	      sp->m_has_return= TRUE;
 	    }
+	    sp->restore_lex(YYTHD);
 	  }
 	| IF sp_if END IF {}
 	| CASE_SYM WHEN_SYM
@@ -1914,7 +1923,9 @@ sp_proc_stmt:
 	    Lex->sphead->m_simple_case= FALSE;
 	  }
 	  sp_case END CASE_SYM {}
-	| CASE_SYM expr WHEN_SYM
+        | CASE_SYM
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr WHEN_SYM
 	  {
 	    /* We "fake" this by using an anonymous variable which we
 	       set to the expression. Note that all WHENs are evaluate
@@ -1923,17 +1934,16 @@ sp_proc_stmt:
 	    LEX *lex= Lex;
 	    uint offset= lex->spcont->current_pvars();
 	    sp_instr_set *i = new sp_instr_set(lex->sphead->instructions(),
-					       lex->spcont,
-	                                       offset, $2, MYSQL_TYPE_STRING);
+                                               lex->spcont, offset, $3,
+                                               MYSQL_TYPE_STRING, lex, TRUE);
 	    LEX_STRING dummy;
 
 	    dummy.str= (char *)"";
 	    dummy.length= 0;
 	    lex->spcont->push_pvar(&dummy, MYSQL_TYPE_STRING, sp_param_in);
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
 	    lex->sphead->add_instr(i);
 	    lex->sphead->m_simple_case= TRUE;
+            lex->sphead->restore_lex(YYTHD);
 	  }
 	  sp_case END CASE_SYM
 	  {
@@ -2187,18 +2197,19 @@ sp_fetch_list:
 	;
 
 sp_if:
-	  expr THEN_SYM
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr THEN_SYM
 	  {
 	    LEX *lex= Lex;
 	    sp_head *sp= lex->sphead;
 	    sp_pcontext *ctx= lex->spcont;
 	    uint ip= sp->instructions();
-	    sp_instr_jump_if_not *i = new sp_instr_jump_if_not(ip, ctx, $1);
+	    sp_instr_jump_if_not *i = new sp_instr_jump_if_not(ip, ctx,
+                                                               $2, lex);
 
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
 	    sp->push_backpatch(i, ctx->push_label((char *)"", 0));
             sp->add_instr(i);
+            sp->restore_lex(YYTHD);
 	  }
 	  sp_proc_stmts1
 	  {
@@ -2226,7 +2237,8 @@ sp_elseifs:
 	;
 
 sp_case:
-	  expr THEN_SYM
+	  { Lex->sphead->reset_lex(YYTHD); }
+          expr THEN_SYM
 	  {
             LEX *lex= Lex;
 	    sp_head *sp= lex->sphead;
@@ -2235,7 +2247,7 @@ sp_case:
 	    sp_instr_jump_if_not *i;
 
 	    if (! sp->m_simple_case)
-	      i= new sp_instr_jump_if_not(ip, ctx, $1);
+	      i= new sp_instr_jump_if_not(ip, ctx, $2, lex);
 	    else
 	    { /* Simple case: <caseval> = <whenval> */
 	      LEX_STRING ivar;
@@ -2244,15 +2256,14 @@ sp_case:
 	      ivar.length= 5;
 	      Item *var= (Item*) new Item_splocal(ivar, 
 						  ctx->current_pvars()-1);
-	      Item *expr= new Item_func_eq(var, $1);
+	      Item *expr= new Item_func_eq(var, $2);
 
-	      i= new sp_instr_jump_if_not(ip, ctx, expr);
+	      i= new sp_instr_jump_if_not(ip, ctx, expr, lex);
               lex->variables_used= 1;
 	    }
 	    sp->push_backpatch(i, ctx->push_label((char *)"", 0));
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
             sp->add_instr(i);
+            sp->restore_lex(YYTHD);
 	  }
 	  sp_proc_stmts1
 	  {
@@ -2370,19 +2381,20 @@ sp_unlabeled_control:
 
 	    lex->sphead->add_instr(i);
 	  }
-	| WHILE_SYM expr DO_SYM
+        | WHILE_SYM 
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr DO_SYM
 	  {
 	    LEX *lex= Lex;
 	    sp_head *sp= lex->sphead;
 	    uint ip= sp->instructions();
 	    sp_instr_jump_if_not *i = new sp_instr_jump_if_not(ip, lex->spcont,
-							       $2);
+							       $3, lex);
 
 	    /* Jumping forward */
 	    sp->push_backpatch(i, lex->spcont->last_label());
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
             sp->add_instr(i);
+            sp->restore_lex(YYTHD);
 	  }
 	  sp_proc_stmts1 END WHILE_SYM
 	  {
@@ -2393,17 +2405,18 @@ sp_unlabeled_control:
 
 	    lex->sphead->add_instr(i);
 	  }
-	| REPEAT_SYM sp_proc_stmts1 UNTIL_SYM expr END REPEAT_SYM
+        | REPEAT_SYM sp_proc_stmts1 UNTIL_SYM 
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr END REPEAT_SYM
 	  {
 	    LEX *lex= Lex;
 	    uint ip= lex->sphead->instructions();
 	    sp_label_t *lab= lex->spcont->last_label();  /* Jumping back */
 	    sp_instr_jump_if_not *i = new sp_instr_jump_if_not(ip, lex->spcont,
-							       $4, lab->ip);
-
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
+                                                               $5, lab->ip,
+                                                               lex);
             lex->sphead->add_instr(i);
+            lex->sphead->restore_lex(YYTHD);
 	  }
 	;
 
@@ -7140,8 +7153,75 @@ opt_option:
 	| OPTION {};
 
 option_value_list:
+	option_type_value
+	| option_value_list ',' option_type_value;
+
+option_type_value:
+        {
+          if (Lex->sphead)
+          {
+            /*
+              If we are in SP we want have own LEX for each assignment.
+              This is mostly because it is hard for several sp_instr_set
+              and sp_instr_set_trigger instructions share one LEX. 
+              (Well, it is theoretically possible but adds some extra 
+               overhead on preparation for execution stage and IMO less
+               robust).
+
+              QQ: May be we should simply prohibit group assignments in SP?
+            */
+            LEX *lex;
+            Lex->sphead->reset_lex(YYTHD);
+            lex= Lex;
+            
+            /* Set new LEX as if we at start of set rule. */
+	    lex->sql_command= SQLCOM_SET_OPTION;
+	    mysql_init_select(lex);
+	    lex->option_type=OPT_SESSION;
+	    lex->var_list.empty();
+            lex->one_shot_set= 0;
+	    lex->sphead->m_tmp_query= lex->tok_start;
+          }
+        }
 	option_type option_value
-	| option_value_list ',' option_type option_value;
+        {
+          LEX *lex= Lex;
+          
+          if (lex->sphead)
+          {
+            sp_head *sp= lex->sphead;
+            
+	    if (!lex->var_list.is_empty())
+	    {
+              /*
+                We have assignment to user or system variable or
+                option setting, so we should construct sp_instr_stmt
+                for it.
+              */
+              LEX_STRING qbuff;
+	      sp_instr_stmt *i;
+              
+              if (!(i= new sp_instr_stmt(sp->instructions(), lex->spcont,
+                                         lex)))
+                YYABORT;
+                
+              if (lex->ptr - lex->tok_end > 1)
+                qbuff.length= lex->ptr - sp->m_tmp_query;
+              else
+                qbuff.length= lex->tok_end - sp->m_tmp_query;
+              
+              if (!(qbuff.str= alloc_root(YYTHD->mem_root, qbuff.length + 5)))
+                YYABORT;
+                
+              strmake(strmake(qbuff.str, "SET ", 4), (char *)sp->m_tmp_query,
+                      qbuff.length);
+              qbuff.length+= 4;
+              i->m_query= qbuff;
+              sp->add_instr(i);
+            }
+            lex->sphead->restore_lex(YYTHD);
+          }
+        };
 
 option_type:
 	/* empty */	{}
@@ -7168,31 +7248,7 @@ opt_var_ident_type:
 option_value:
 	  '@' ident_or_text equal expr
 	  {
-            LEX *lex= Lex;
-
-            if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-            {
-              /*
-                We have to use special instruction in functions and triggers
-                because sp_instr_stmt will close all tables and thus ruin
-                execution of statement invoking function or trigger.
-
-                We also do not want to allow expression with subselects in
-                this case.
-              */
-              if (lex->query_tables)
-              {
-                my_message(ER_SP_SUBSELECT_NYI, ER(ER_SP_SUBSELECT_NYI),
-                           MYF(0));
-                YYABORT;
-              }
-              sp_instr_set_user_var *i= 
-                new sp_instr_set_user_var(lex->sphead->instructions(),
-                                          lex->spcont, $2, $4);
-	      lex->sphead->add_instr(i);
-            }
-            else
-              lex->var_list.push_back(new set_var_user(new Item_func_set_user_var($2,$4)));
+            Lex->var_list.push_back(new set_var_user(new Item_func_set_user_var($2,$4)));
 	  }
 	| internal_variable_name equal set_expr_or_default
 	  {
@@ -7253,9 +7309,7 @@ option_value:
 	      else
 	        it= new Item_null();
               i= new sp_instr_set(lex->sphead->instructions(), ctx,
-	                          spv->offset, it, spv->type);
-	      i->tables= lex->query_tables;
-	      lex->query_tables= 0;
+	                          spv->offset, it, spv->type, lex, TRUE);
 	      lex->sphead->add_instr(i);
 	      spv->isset= TRUE;
 	    }
diff --git a/sql/table.h b/sql/table.h
index 5ab1f900195..6ae8af4c2c6 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -420,6 +420,11 @@ typedef struct st_table_list
   /* FRMTYPE_ERROR if any type is acceptable */
   enum frm_type_enum required_type;
   char		timestamp_buffer[20];	/* buffer for timestamp (19+1) */
+  /*
+    This TABLE_LIST object is just placeholder for prelocking, it will be
+    used for implicit LOCK TABLES only and won't be used in real statement.
+  */
+  bool          prelocking_placeholder;
 
   void calc_md5(char *buffer);
   void set_ancestor();
diff --git a/sql/tztime.cc b/sql/tztime.cc
index b2b3576e221..bd9d49f0ab0 100644
--- a/sql/tztime.cc
+++ b/sql/tztime.cc
@@ -1524,7 +1524,6 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap)
   TZ_NAMES_ENTRY *tmp_tzname;
   my_bool return_val= 1;
   int res;
-  uint counter;
   DBUG_ENTER("my_tz_init");
 
   /*
@@ -1593,8 +1592,7 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap)
   last_global_next_ptr= &(tables_buff[0].next_global);
   tz_init_table_list(tables_buff + 1, &last_global_next_ptr);
 
-  if (open_tables(thd, tables_buff, &counter) ||
-      lock_tables(thd, tables_buff, counter))
+  if (simple_open_n_lock_tables(thd, tables_buff))
   {
     sql_print_warning("Can't open and lock time zone table: %s "
                       "trying to live without them", thd->net.last_error);