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.

6 files changed, 473 insertions, 122 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|