WL#2130: Table locking for stored FUNCTIONs

Collect all tables and SPs refered by a statement, and open all tables
with an implicit LOCK TABLES. Do find things refered by triggers and views,
we open them first (and then repeat this until nothing new is found), before
doing the actual lock tables.


mysql-test/r/information_schema.result:
  Updated result for WL#2130.
mysql-test/r/lock.result:
  Updated result for WL#2130.
mysql-test/r/sp-error.result:
  Updated result for WL#2130.
mysql-test/r/sp.result:
  Updated result for WL#2130.
mysql-test/r/view.result:
  Updated result for WL#2130.
mysql-test/t/information_schema.test:
  Disabled one test case due to a bug involving LOCK TABLES,
  which shows up with WL#2130.
mysql-test/t/lock.test:
  New error message with WL#2130. This change is under debate and might change
  in the future, but will do for now.
mysql-test/t/sp-error.test:
  Updated for WL#2130. Some tests are voided when table access does work from
  functions.
mysql-test/t/sp.test:
  Updated for WL#2130.
mysql-test/t/view.test:
  Updated for WL#2130.
sql/item_func.cc:
  We now have to set net.no_send_ok for functions too, with WL#2130.
sql/share/errmsg.txt:
  Reused an error code since the old use was voided by WL#2130, but a new
  one was needed instead (similar, but more specific restriction).
sql/sp.cc:
  Fixed error handling and collection of used tables for WL#2130.
sql/sp.h:
  Fixed error handling and collection of used tables for WL#2130.
sql/sp_head.cc:
  Added support functions for collecting and merging hash tables and lists
  of used tables from SPs and substatements, for WL#2130.
sql/sp_head.h:
  Added support functions for collecting and merging hash tables and lists
  of used tables from SPs and substatements, for WL#2130.
sql/sql_base.cc:
  Changed the way table->query_id is tested and set during with locked tables
  in effect. This makes some SP test cases work with WL#2130, but has a side
  effect on some error cases with explicit LOCK TABLES. It's still debated if
  this is the correct way, so it might change.
sql/sql_class.h:
  Added flags for circumventing some interference between WL#2130 and mysql_make_view().
sql/sql_derived.cc:
  Added some missing initializations. (Potential bugs.)
sql/sql_lex.cc:
  Clear the new hash tables for WL#2130.
sql/sql_lex.h:
  Added hash tables for procedures and tables too (as for functions), for WL#2130.
sql/sql_parse.cc:
  WL#2130: Make table accesses from stored functions work by adding an implicit
  LOCK TABLES around (most) executed statements. To do this, we have to go through
  a loop where we collect all SPs and tables in mysql_execute_statement.
sql/sql_prepare.cc:
  Cache both functions and procedures for WL#2130.
sql/sql_show.cc:
  Added some missing initializations. (Potential bugs.)
sql/sql_view.cc:
  Shortcut mysql_make_view() if thd->shortcut_make_view is true during
  the pre-open phase for collecting tables in WL#2130. Otherwise, the
  similar mechanism here causes interference.
sql/sql_yacc.yy:
  For WL#2130, added caching of procedures and disallowed LOCK/UNLOCK TABLES in SPs.

16 files changed, 511 insertions, 146 deletions

diff --git a/sql/item_func.cc b/sql/item_func.cc
index e1d81b2a37d..148f41b2800 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -3568,13 +3568,18 @@ Item_func_sp::execute(Item **itp)
 #endif
 
   if (! m_sp)
-    m_sp= sp_find_function(thd, m_name);
+    m_sp= sp_find_function(thd, m_name, TRUE); // cache only
   if (! m_sp)
   {
     my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION", m_name->m_qname.str);
     DBUG_RETURN(-1);
   }
 
+#ifndef EMBEDDED_LIBRARY
+  my_bool nsok= thd->net.no_send_ok;
+  thd->net.no_send_ok= TRUE;
+#endif
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   if (check_procedure_access(thd, EXECUTE_ACL, 
 			     m_sp->m_db.str, m_sp->m_name.str, 0))
@@ -3600,6 +3605,9 @@ Item_func_sp::execute(Item **itp)
   sp_restore_security_context(thd, m_sp, &save_ctx);
 #endif
 
+#ifndef EMBEDDED_LIBRARY
+  thd->net.no_send_ok= nsok;
+#endif
   DBUG_RETURN(res);
 }
 
@@ -3610,7 +3618,7 @@ Item_func_sp::field_type() const
   DBUG_ENTER("Item_func_sp::field_type");
 
   if (! m_sp)
-    m_sp= sp_find_function(current_thd, m_name);
+    m_sp= sp_find_function(current_thd, m_name, TRUE); // cache only
   if (m_sp)
   {
     DBUG_PRINT("info", ("m_returns = %d", m_sp->m_returns));
@@ -3628,7 +3636,7 @@ Item_func_sp::result_type() const
   DBUG_PRINT("info", ("m_sp = %p", m_sp));
 
   if (! m_sp)
-    m_sp= sp_find_function(current_thd, m_name);
+    m_sp= sp_find_function(current_thd, m_name, TRUE); // cache only
   if (m_sp)
   {
     DBUG_RETURN(m_sp->result());
@@ -3643,7 +3651,7 @@ Item_func_sp::fix_length_and_dec()
   DBUG_ENTER("Item_func_sp::fix_length_and_dec");
 
   if (! m_sp)
-    m_sp= sp_find_function(current_thd, m_name);
+    m_sp= sp_find_function(current_thd, m_name, TRUE); // cache only
   if (! m_sp)
   {
     my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION", m_name->m_qname.str);
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index 7826be85679..030949e15d1 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5094,7 +5094,7 @@ ER_SP_BADSELECT 0A000
 ER_SP_BADRETURN 42000 
 	eng "RETURN is only allowed in a FUNCTION"
 ER_SP_BADSTATEMENT 0A000 
-	eng "Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION"
+	eng "LOCK and UNLOCK tables are not allowed in stored procedures"
 ER_UPDATE_LOG_DEPRECATED_IGNORED 42000 
 	eng "The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored"
 ER_UPDATE_LOG_DEPRECATED_TRANSLATED 42000 
diff --git a/sql/sp.cc b/sql/sp.cc
index 84b126e5ecd..46b08c3e847 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -854,13 +854,14 @@ sp_show_status_procedure(THD *thd, const char *wild)
 ******************************************************************************/
 
 sp_head *
-sp_find_function(THD *thd, sp_name *name)
+sp_find_function(THD *thd, sp_name *name, bool cache_only)
 {
   sp_head *sp;
   DBUG_ENTER("sp_find_function");
   DBUG_PRINT("enter", ("name: %*s", name->m_name.length, name->m_name.str));
 
-  if (!(sp= sp_cache_lookup(&thd->sp_func_cache, name)))
+  if (!(sp= sp_cache_lookup(&thd->sp_func_cache, name)) &&
+      !cache_only)
   {
     if (db_find_routine(thd, TYPE_ENUM_FUNCTION, name, &sp) != SP_OK)
       sp= NULL;
@@ -963,7 +964,7 @@ sp_function_exists(THD *thd, sp_name *name)
 
 
 byte *
-sp_lex_spfuns_key(const byte *ptr, uint *plen, my_bool first)
+sp_lex_sp_key(const byte *ptr, uint *plen, my_bool first)
 {
   LEX_STRING *lsp= (LEX_STRING *)ptr;
   *plen= lsp->length;
@@ -972,37 +973,36 @@ sp_lex_spfuns_key(const byte *ptr, uint *plen, my_bool first)
 
 
 void
-sp_add_fun_to_lex(LEX *lex, sp_name *fun)
+sp_add_to_hash(HASH *h, sp_name *fun)
 {
-  if (! hash_search(&lex->spfuns,
-		    (byte *)fun->m_qname.str, fun->m_qname.length))
+  if (! hash_search(h, (byte *)fun->m_qname.str, fun->m_qname.length))
   {
     LEX_STRING *ls= (LEX_STRING *)sql_alloc(sizeof(LEX_STRING));
     ls->str= sql_strmake(fun->m_qname.str, fun->m_qname.length);
     ls->length= fun->m_qname.length;
 
-    my_hash_insert(&lex->spfuns, (byte *)ls);
+    my_hash_insert(h, (byte *)ls);
   }
 }
 
 
 void
-sp_merge_funs(LEX *dst, LEX *src)
+sp_merge_hash(HASH *dst, HASH *src)
 {
-  for (uint i=0 ; i < src->spfuns.records ; i++)
+  for (uint i=0 ; i < src->records ; i++)
   {
-    LEX_STRING *ls= (LEX_STRING *)hash_element(&src->spfuns, i);
+    LEX_STRING *ls= (LEX_STRING *)hash_element(src, i);
 
-    if (! hash_search(&dst->spfuns, (byte *)ls->str, ls->length))
-      my_hash_insert(&dst->spfuns, (byte *)ls);
+    if (! hash_search(dst, (byte *)ls->str, ls->length))
+      my_hash_insert(dst, (byte *)ls);
   }
 }
 
 
 int
-sp_cache_functions(THD *thd, LEX *lex)
+sp_cache_routines(THD *thd, LEX *lex, int type)
 {
-  HASH *h= &lex->spfuns;
+  HASH *h= (type == TYPE_ENUM_FUNCTION ? &lex->spfuns : &lex->spprocs);
   int ret= 0;
 
   for (uint i=0 ; i < h->records ; i++)
@@ -1011,7 +1011,9 @@ sp_cache_functions(THD *thd, LEX *lex)
     sp_name name(*ls);
 
     name.m_qname= *ls;
-    if (! sp_cache_lookup(&thd->sp_func_cache, &name))
+    if (! sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
+			   &thd->sp_func_cache : &thd->sp_proc_cache),
+			  &name))
     {
       sp_head *sp;
       LEX *oldlex= thd->lex;
@@ -1027,11 +1029,23 @@ sp_cache_functions(THD *thd, LEX *lex)
       name.m_name.str+= 1;
       name.m_name.length= name.m_qname.length - name.m_db.length - 1;
 
-      if (db_find_routine(thd, TYPE_ENUM_FUNCTION, &name, &sp)
-	  == SP_OK)
+      if (db_find_routine(thd, type, &name, &sp) == SP_OK)
       {
-	sp_cache_insert(&thd->sp_func_cache, sp);
-	ret= sp_cache_functions(thd, newlex);
+	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)
@@ -1041,9 +1055,6 @@ sp_cache_functions(THD *thd, LEX *lex)
       {
 	delete newlex;
 	thd->lex= oldlex;
-	my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION", ls->str);
-	ret= 1;
-	break;
       }
     }
   }
diff --git a/sql/sp.h b/sql/sp.h
index 152c59d0d02..6290324bb86 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -56,7 +56,7 @@ int
 sp_show_status_procedure(THD *thd, const char *wild);
 
 sp_head *
-sp_find_function(THD *thd, sp_name *name);
+sp_find_function(THD *thd, sp_name *name, bool cache_only = 0);
 
 int
 sp_create_function(THD *thd, sp_head *sp);
@@ -77,14 +77,15 @@ bool
 sp_function_exists(THD *thd, sp_name *name);
 
 
-// This is needed since we have to read the functions before we
-// do anything else.
+/*
+ *  For precaching of functions and procedures
+ */
 void
-sp_add_fun_to_lex(LEX *lex, sp_name *fun);
+sp_add_to_hash(HASH *h, sp_name *fun);
 void
-sp_merge_funs(LEX *dst, LEX *src);
+sp_merge_hash(HASH *dst, HASH *src);
 int
-sp_cache_functions(THD *thd, LEX *lex);
+sp_cache_routines(THD *thd, LEX *lex, int type);
 
 
 //
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index d52474998a8..260e5aacd67 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -23,6 +23,7 @@
 #include "sp.h"
 #include "sp_pcontext.h"
 #include "sp_rcontext.h"
+#include "sp_cache.h"
 
 Item_result
 sp_map_result_type(enum enum_field_types type)
@@ -259,11 +260,14 @@ sp_head::sp_head()
   :Item_arena((bool)FALSE), m_returns_cs(NULL), m_has_return(FALSE),
    m_simple_case(FALSE), m_multi_results(FALSE), m_in_handler(FALSE)
 {
+  extern byte *
+    sp_table_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);
   DBUG_VOID_RETURN;
 }
 
@@ -439,6 +443,8 @@ 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);
   DBUG_VOID_RETURN;
 }
 
@@ -799,48 +805,10 @@ sp_head::restore_lex(THD *thd)
   oldlex->trg_table_fields.push_back(&sublex->trg_table_fields);
 
   // Collect some data from the sub statement lex.
-  sp_merge_funs(oldlex, sublex);
-#ifdef NOT_USED_NOW
-  // QQ We're not using this at the moment.
-  if (sublex.sql_command == SQLCOM_CALL)
-  {
-    // It would be slightly faster to keep the list sorted, but we need
-    // an "insert before" method to do that.
-    char *proc= sublex.udf.name.str;
-
-    List_iterator_fast<char *> li(m_calls);
-    char **it;
-
-    while ((it= li++))
-      if (my_strcasecmp(system_charset_info, proc, *it) == 0)
-	break;
-    if (! it)
-      m_calls.push_back(&proc);
-
-  }
+  sp_merge_hash(&oldlex->spfuns, &sublex->spfuns);
+  sp_merge_hash(&oldlex->spprocs, &sublex->spprocs);
   // Merge used tables
-  // QQ ...or just open tables in thd->open_tables?
-  //    This is not entirerly clear at the moment, but for now, we collect
-  //    tables here.
-  for (sl= sublex.all_selects_list ;
-       sl ;
-       sl= sl->next_select())
-  {
-    for (TABLE_LIST *tables= sl->get_table_list() ;
-	 tables ;
-	 tables= tables->next)
-    {
-      List_iterator_fast<char *> li(m_tables);
-      char **tb;
-
-      while ((tb= li++))
-	if (my_strcasecmp(system_charset_info, tables->table_name, *tb) == 0)
-	  break;
-      if (! tb)
-	m_tables.push_back(&tables->table_name);
-    }
-  }
-#endif
+  sp_merge_table_list(thd, &m_sptabs, sublex->query_tables, sublex);
   if (! sublex->sp_lex_in_use)
     delete sublex;
   thd->lex= oldlex;
@@ -1864,3 +1832,242 @@ sp_restore_security_context(THD *thd, sp_head *sp, st_sp_security_context *ctxp)
 }
 
 #endif /* NO_EMBEDDED_ACCESS_CHECKS */
+
+/*
+ *  Table merge hash table
+ *
+ */
+typedef struct st_sp_table
+{
+  LEX_STRING qname;
+  bool temp;
+  TABLE_LIST *table;
+} SP_TABLE;
+
+byte *
+sp_table_key(const byte *ptr, uint *plen, my_bool first)
+{
+  SP_TABLE *tab= (SP_TABLE *)ptr;
+  *plen= tab->qname.length;
+  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.
+ */
+bool
+sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
+		    LEX *lex_for_tmp_check)
+{
+  for (; table ; table= table->next_global)
+    if (!table->derived &&
+	(!table->select_lex ||
+	 !(table->select_lex->options & OPTION_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);
+      tname[tlen++]= '.';
+      memcpy(tname+tlen, table->table_name, table->table_name_length);
+      tlen+= table->table_name_length;
+      tname[tlen++]= '.';
+      alen= strlen(table->alias);
+      memcpy(tname+tlen, table->alias, alen);
+      tlen+= alen;
+      tname[tlen]= '\0';
+
+      if ((tab= (SP_TABLE *)hash_search(h, (byte *)tname, tlen)))
+      {
+	if (tab->table->lock_type < table->lock_type)
+	  tab->table= table;	// Use the table with the highest lock type
+      }
+      else
+      {
+	if (!(tab= (SP_TABLE *)thd->calloc(sizeof(SP_TABLE))))
+	  return FALSE;
+	tab->qname.length= tlen;
+	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 &&
+	    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);
+      }
+    }
+  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);
+
+    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);
+
+    name.m_qname= *ls;
+    if ((sp= sp_cache_lookup(&thd->sp_proc_cache, &name)))
+      sp_merge_table_hash(&lex->sptabs, &sp->m_sptabs);
+  }
+}
+
+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);
+
+    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
+    }
+  }
+}
+
+TABLE_LIST *
+sp_hash_to_table_list(THD *thd, HASH *h)
+{
+  uint i;
+  TABLE_LIST *tables= NULL;
+  DBUG_ENTER("sp_hash_to_table_list");
+
+  for (i=0 ; i < h->records ; i++)
+  {
+    SP_TABLE *stab= (SP_TABLE *)hash_element(h, 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;
+
+  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);
+}
+
+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);
+  }
+  if (thd->global_read_lock)
+    unlock_global_read_lock(thd);
+}
+
+/*
+ * Simple function for adding an explicetly named (systems) table to
+ * the global table list, e.g. "mysql", "proc".
+ *
+ */
+TABLE_LIST *
+sp_add_to_query_tables(THD *thd, LEX *lex,
+		       const char *db, const char *name,
+		       thr_lock_type locktype)
+{
+  TABLE_LIST *table;
+
+  if (!(table= (TABLE_LIST *)thd->calloc(sizeof(TABLE_LIST))))
+  {
+    my_error(ER_OUTOFMEMORY, MYF(0), sizeof(TABLE_LIST));
+    return NULL;
+  }
+  table->db_length= strlen(db);
+  table->db= thd->strmake(db, table->db_length);
+  table->table_name_length= strlen(name);
+  table->table_name= thd->strmake(name, table->table_name_length);
+  table->alias= thd->strdup(name);
+  table->lock_type= locktype;
+  table->select_lex= lex->current_select; // QQ?
+  table->cacheable_table= 1;
+  
+  lex->add_to_query_tables(table);
+  return table;
+}
diff --git a/sql/sp_head.h b/sql/sp_head.h
index c4d2068661c..5df9c753048 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -92,11 +92,6 @@ public:
   uint m_old_cmq;		// Old CLIENT_MULTI_QUERIES value
   st_sp_chistics *m_chistics;
   ulong m_sql_mode;		// For SHOW CREATE
-#if NOT_USED_NOW
-  // QQ We're not using this at the moment.
-  List<char *> m_calls;		// Called procedures.
-  List<char *> m_tables;	// Used tables.
-#endif
   LEX_STRING m_qname;		// db.name
   LEX_STRING m_db;
   LEX_STRING m_name;
@@ -108,6 +103,7 @@ public:
   LEX_STRING m_definer_host;
   longlong m_created;
   longlong m_modified;
+  HASH m_sptabs;		/* Merged table lists */
   // Pointers set during parsing
   uchar *m_param_begin, *m_param_end, *m_returns_begin, *m_returns_end,
     *m_body_begin;
@@ -897,4 +893,22 @@ 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);
+
 #endif /* _SP_HEAD_H_ */
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0a57c0f6bc9..b123dfe8176 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -919,10 +919,10 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     {
       if (table->s->key_length == key_length &&
 	  !memcmp(table->s->table_cache_key,key,key_length) &&
-	  !my_strcasecmp(system_charset_info, table->alias, alias) &&
-	  table->query_id != thd->query_id)
+	  !my_strcasecmp(system_charset_info, table->alias, alias))
       {
-	table->query_id=thd->query_id;
+	if (table->query_id != thd->query_id)
+	  table->query_id=thd->query_id;
         DBUG_PRINT("info",("Using locked table"));
 	goto reset;
       }
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 29185dfbf7b..2898e58baf2 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1031,6 +1031,8 @@ 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
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index bed65f90c00..c01728e68d5 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -165,16 +165,21 @@ exit:
     else
     {
       if (!thd->fill_derived_tables())
+      {
 	delete derived_result;
+	derived_result= NULL;
+      }
       orig_table_list->derived_result= derived_result;
       orig_table_list->table= table;
       orig_table_list->table_name= (char*) table->s->table_name;
+      orig_table_list->table_name_length= strlen((char*)table->s->table_name);
       table->derived_select_number= first_select->select_number;
       table->s->tmp_table= TMP_TABLE;
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
       table->grant.privilege= SELECT_ACL;
 #endif
       orig_table_list->db= (char *)"";
+      orig_table_list->db_length= 0;
       // Force read of table stats in the optimizer
       table->file->info(HA_STATUS_VARIABLE);
       /* Add new temporary table to list of open derived tables */
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b8c77a822c4..5554cf82034 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -176,6 +176,10 @@ void lex_start(THD *thd, uchar *buf,uint length)
 
   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;
 }
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 266cb3cc030..5d232d60e79 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -748,6 +748,8 @@ typedef struct st_lex
   bool all_privileges;
   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,14 +770,21 @@ typedef struct st_lex
 
   st_lex() :result(0), sql_command(SQLCOM_END)
   {
-    extern byte *sp_lex_spfuns_key(const byte *ptr, uint *plen, my_bool first);
-    hash_init(&spfuns, system_charset_info, 0, 0, 0, sp_lex_spfuns_key, 0, 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);
   }
 
   inline void uncacheable(uint8 cause)
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 5981a8b2d4d..37c988fa5af 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2231,6 +2231,10 @@ 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 */
+  my_bool old_innodb_table_locks= thd->variables.innodb_table_locks;
   DBUG_ENTER("mysql_execute_command");
 
   /*
@@ -2252,11 +2256,89 @@ 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_CREATE_SPFUNCTION &&
+      lex->sql_command != SQLCOM_LOCK_TABLES &&
+      lex->sql_command != SQLCOM_UNLOCK_TABLES)
   {
-    if (sp_cache_functions(thd, lex))
-      DBUG_RETURN(-1);
+    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)))
+	  {
+	    thd->variables.innodb_table_locks= FALSE;
+	    sp_open_and_lock_tables(thd, locked_tables);
+	  }
+	}
+	if (thdmemroot)
+	  thd->mem_root= thdmemroot;
+      }
+      break;
+    } // while (1)
   }
 
   /*
@@ -4261,6 +4343,12 @@ cleanup:
       thd->lock= 0;
   }
 
+  if (locked_tables)
+  {
+    thd->variables.innodb_table_locks= old_innodb_table_locks;
+    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 a71b8148f8e..fddf95327d6 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1541,9 +1541,11 @@ static int check_prepared_statement(Prepared_statement *stmt,
   if (lex->sql_command != SQLCOM_CREATE_PROCEDURE &&
       lex->sql_command != SQLCOM_CREATE_SPFUNCTION)
   {
-    /* the error is print inside */
-    if (sp_cache_functions(thd, lex))
-      DBUG_RETURN(1);
+    /* 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) {
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 5abfe44f51b..f2485edb695 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -311,7 +311,9 @@ mysql_find_files(THD *thd,List<char> *files, const char *db,const char *path,
     if (db && !(col_access & TABLE_ACLS))
     {
       table_list.db= (char*) db;
+      table_list.db_length= strlen(db);
       table_list.table_name= file->name;
+      table_list.table_name_length= strlen(file->name);
       table_list.grant.privilege=col_access;
       if (check_grant(thd, TABLE_ACLS, &table_list, 1, UINT_MAX, 1))
         continue;
@@ -2519,7 +2521,9 @@ int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
 
   bzero((char*) &proc_tables,sizeof(proc_tables));
   proc_tables.db= (char*) "mysql";
+  proc_tables.db_length= 5;
   proc_tables.table_name= proc_tables.alias= (char*) "proc";
+  proc_tables.table_name_length= 4;
   proc_tables.lock_type= TL_READ;
   if (!(proc_table= open_ltable(thd, &proc_tables, TL_READ)))
   {
@@ -3197,6 +3201,7 @@ int mysql_schema_table(THD *thd, LEX *lex, TABLE_LIST *table_list)
                                           table_list->schema_table_name,
                                           table_list->alias);
   table_list->table_name= (char*) table->s->table_name;
+  table_list->table_name_length= strlen(table->s->table_name);
   table_list->table= table;
   table->next= thd->derived_tables;
   thd->derived_tables= table;
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index e2c4b1289fd..456f513dab0 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -556,6 +556,7 @@ my_bool
 mysql_make_view(File_parser *parser, TABLE_LIST *table)
 {
   DBUG_ENTER("mysql_make_view");
+  DBUG_PRINT("info", ("table=%p (%s)", table, table->table_name));
 
   if (table->view)
   {
@@ -612,7 +613,9 @@ 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;
-  view_select->select_number= ++thd->select_number;
+  /* 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;
   {
     ulong options= thd->options;
@@ -657,27 +660,29 @@ 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)
-    {
-      /* move SP to main LEX */
-      sp_merge_funs(old_lex, lex);
-      /* open mysq.proc for functions which are not in cache */
-      if (old_lex->proc_table == 0 &&
-          (old_lex->proc_table=
-           (TABLE_LIST*)thd->calloc(sizeof(TABLE_LIST))) != 0)
-      {
-        TABLE_LIST *table= old_lex->proc_table;
-        table->db= (char*)"mysql";
-        table->db_length= 5;
-        table->table_name= table->alias= (char*)"proc";
-        table->table_name_length= 4;
-        table->cacheable_table= 1;
-        old_lex->add_to_query_tables(table);
-      }
-    }
+      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
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 1402a85229b..d381b035791 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1464,6 +1464,7 @@ call:
 	    lex->sql_command= SQLCOM_CALL;
 	    lex->spname= $2;
 	    lex->value_list.empty();
+	    sp_add_to_hash(&lex->spprocs, $2);
 	  }
           '(' sp_cparam_list ')' {}
 	;
@@ -1869,36 +1870,21 @@ sp_proc_stmt:
 	    if (lex->sql_command != SQLCOM_SET_OPTION ||
 		! lex->var_list.is_empty())
 	    {
-              /*
-                Currently we can't handle queries inside a FUNCTION or
-                TRIGGER, because of the way table locking works. This is 
-                unfortunate, and limits the usefulness of functions and
-                especially triggers a tremendously, but it's nothing we 
-                can do about this at the moment.
-              */
-	      if (sp->m_type != TYPE_ENUM_PROCEDURE)
-	      {
-		my_message(ER_SP_BADSTATEMENT, ER(ER_SP_BADSTATEMENT), MYF(0));
-		YYABORT;
-	      }
+	      sp_instr_stmt *i=new sp_instr_stmt(sp->instructions(),
+						 lex->spcont);
+
+	      /* Extract the query statement from the tokenizer:
+                 The end is either lex->tok_end or tok->ptr. */
+	      if (lex->ptr - lex->tok_end > 1)
+		i->m_query.length= lex->ptr - sp->m_tmp_query;
 	      else
-	      {
-		sp_instr_stmt *i=new sp_instr_stmt(sp->instructions(),
-						   lex->spcont);
-
-		/* Extract the query statement from the tokenizer:
-                   The end is either lex->tok_end or tok->ptr. */
-		if (lex->ptr - lex->tok_end > 1)
-		  i->m_query.length= lex->ptr - sp->m_tmp_query;
-		else
-		  i->m_query.length= lex->tok_end - sp->m_tmp_query;
-		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;
-	      }
+		i->m_query.length= lex->tok_end - sp->m_tmp_query;
+	      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);
           }
@@ -1915,11 +1901,6 @@ sp_proc_stmt:
 	    {
 	      sp_instr_freturn *i;
 
-	      if ($2->type() == Item::SUBSELECT_ITEM)
-	      {  /* QQ For now, just disallow subselects as values */
-	        my_message(ER_SP_BADSTATEMENT, ER(ER_SP_BADSTATEMENT), MYF(0));
-	        YYABORT;
-	      }
 	      i= new sp_instr_freturn(lex->sphead->instructions(),
 				      lex->spcont,
 		                      $2, lex->sphead->m_returns);
@@ -4503,7 +4484,7 @@ simple_expr:
 	    sp_name *name= new sp_name($1, $3);
 
 	    name->init_qname(YYTHD);
-	    sp_add_fun_to_lex(Lex, name);
+	    sp_add_to_hash(&Lex->spfuns, name);
 	    if ($5)
 	      $$= new Item_func_sp(name, *$5);
 	    else
@@ -4574,7 +4555,7 @@ simple_expr:
             {
               sp_name *name= sp_name_current_db_new(YYTHD, $1);
 
-              sp_add_fun_to_lex(Lex, name);
+              sp_add_to_hash(&Lex->spfuns, name);
               if ($3)
                 $$= new Item_func_sp(name, *$3);
               else
@@ -6123,6 +6104,8 @@ show_param:
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_SELECT;
             lex->orig_sql_command= SQLCOM_SHOW_STATUS_PROC;
+	    if (!sp_add_to_query_tables(YYTHD, lex, "mysql", "proc", TL_READ))
+	      YYABORT;
             if (prepare_schema_table(YYTHD, lex, 0, SCH_PROCEDURES))
               YYABORT;
 	  }
@@ -6131,6 +6114,8 @@ show_param:
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_SELECT;
             lex->orig_sql_command= SQLCOM_SHOW_STATUS_FUNC;
+	    if (!sp_add_to_query_tables(YYTHD, lex, "mysql", "proc", TL_READ))
+	      YYABORT;
             if (prepare_schema_table(YYTHD, lex, 0, SCH_PROCEDURES))
               YYABORT;
 	  };
@@ -7434,7 +7419,14 @@ set_expr_or_default:
 lock:
 	LOCK_SYM table_or_tables
 	{
-	  Lex->sql_command=SQLCOM_LOCK_TABLES;
+	  LEX *lex= Lex;
+
+	  if (lex->sphead)
+	  {
+	    my_message(ER_SP_BADSTATEMENT, ER(ER_SP_BADSTATEMENT), MYF(0));
+	    YYABORT;
+	  }
+	  lex->sql_command= SQLCOM_LOCK_TABLES;
 	}
 	table_lock_list
 	{}
@@ -7464,7 +7456,19 @@ lock_option:
         ;
 
 unlock:
-	UNLOCK_SYM table_or_tables { Lex->sql_command=SQLCOM_UNLOCK_TABLES; }
+	UNLOCK_SYM
+	{
+	  LEX *lex= Lex;
+
+	  if (lex->sphead)
+	  {
+	    my_message(ER_SP_BADSTATEMENT, ER(ER_SP_BADSTATEMENT), MYF(0));
+	    YYABORT;
+	  }
+	  lex->sql_command= SQLCOM_UNLOCK_TABLES;
+	}
+	table_or_tables
+	{}
         ;