Enable support of access to tables from triggers. Thus fix bug #8406 "Triggers

crash if referencing a table" and several other related bugs.
Fix for bug #11834 "Re-execution of prepared statement with dropped function
crashes server." which was spotted during work on previous bugs.

Also couple of nice cleanups:
- Replaced two separate hashes for stored routines used by statement with one.
- Now instead of doing one pass through all routines used in statement for
  caching them and then doing another pass for adding their tables to table
  list, we do only one pass during which do both things.

12 files changed, 448 insertions, 244 deletions

diff --git a/sql/item_func.cc b/sql/item_func.cc
index 4dc7e55f195..ee8f8de5177 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4707,6 +4707,7 @@ Item_func_sp::cleanup()
     delete result_field;
     result_field= NULL;
   }
+  m_sp= NULL;
   Item_func::cleanup();
 }
 
diff --git a/sql/sp.cc b/sql/sp.cc
index 4f89d6ba6da..06d85ad5c60 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -19,6 +19,7 @@
 #include "sp.h"
 #include "sp_head.h"
 #include "sp_cache.h"
+#include "sql_trigger.h"
 
 static bool
 create_string(THD *thd, String *buf,
@@ -1072,145 +1073,317 @@ sp_function_exists(THD *thd, sp_name *name)
 }
 
 
-byte *
-sp_lex_sp_key(const byte *ptr, uint *plen, my_bool first)
+/*
+  Structure that represents element in the set of stored routines
+  used by statement or routine.
+*/
+struct Sroutine_hash_entry;
+
+struct Sroutine_hash_entry
 {
-  LEX_STRING *lsp= (LEX_STRING *)ptr;
-  *plen= lsp->length;
-  return (byte *)lsp->str;
+  /* Set key consisting of one-byte routine type and quoted routine name. */
+  LEX_STRING key;
+  /*
+    Next element in list linking all routines in set. See also comments
+    for LEX::sroutine/sroutine_list and sp_head::m_sroutines.
+  */
+  Sroutine_hash_entry *next;
+};
+
+
+extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first)
+{
+  Sroutine_hash_entry *rn= (Sroutine_hash_entry *)ptr;
+  *plen= rn->key.length;
+  return (byte *)rn->key.str;
 }
 
 
-void
-sp_add_to_hash(HASH *h, sp_name *fun)
+/*
+  Auxilary function that adds new element to the set of stored routines
+  used by statement.
+
+  SYNOPSIS
+    add_used_routine()
+      lex     - LEX representing statement
+      arena   - arena in which memory for new element will be allocated
+      key     - key for the hash representing set
+
+  NOTES
+    Will also add element to end of 'LEX::sroutines_list' list.
+
+    In case when statement uses stored routines but does not need
+    prelocking (i.e. it does not use any tables) we will access the
+    elements of LEX::sroutines set on prepared statement re-execution.
+    Because of this we have to allocate memory for both hash element
+    and copy of its key in persistent arena.
+
+  TODO
+    When we will got rid of these accesses on re-executions we will be
+    able to allocate memory for hash elements in non-persitent arena
+    and directly use key values from sp_head::m_sroutines sets instead
+    of making their copies.
+
+  RETURN VALUE
+    TRUE  - new element was added.
+    FALSE - element was not added (because it is already present in the set).
+*/
+
+static bool add_used_routine(LEX *lex, Item_arena *arena,
+                             const LEX_STRING *key)
 {
-  if (! hash_search(h, (byte *)fun->m_qname.str, fun->m_qname.length))
+  if (!hash_search(&lex->sroutines, (byte *)key->str, key->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(h, (byte *)ls);
+    Sroutine_hash_entry *rn=
+      (Sroutine_hash_entry *)arena->alloc(sizeof(Sroutine_hash_entry) +
+                                          key->length);
+    if (!rn)              // OOM. Error will be reported using fatal_error().
+      return FALSE;
+    rn->key.length= key->length;
+    rn->key.str= (char *)rn + sizeof(Sroutine_hash_entry);
+    memcpy(rn->key.str, key->str, key->length);
+    my_hash_insert(&lex->sroutines, (byte *)rn);
+    lex->sroutines_list.link_in_list((byte *)rn, (byte **)&rn->next);
+    return TRUE;
   }
+  return FALSE;
 }
 
 
 /*
-  Merge contents of two hashes containing LEX_STRING's
+  Add routine to the set of stored routines used by statement.
 
   SYNOPSIS
-    sp_merge_hash()
+    sp_add_used_routine()
+      lex     - LEX representing statement
+      arena   - arena in which memory for new element of the set
+                will be allocated
+      rt      - routine name
+      rt_type - routine type (one of TYPE_ENUM_PROCEDURE/...)
+
+  NOTES
+    Will also add element to end of 'LEX::sroutines_list' list.
+
+    To be friendly towards prepared statements one should pass
+    persistent arena as second argument.
+*/
+
+void sp_add_used_routine(LEX *lex, Item_arena *arena,
+                         sp_name *rt, char rt_type)
+{
+  rt->set_routine_type(rt_type);
+  (void)add_used_routine(lex, arena, &rt->m_sroutines_key);
+}
+
+
+/*
+  Merge contents of two hashes representing sets of routines used
+  by statements or by other routines.
+
+  SYNOPSIS
+    sp_update_sp_used_routines()
       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.
+  NOTE
+    This procedure won't create new Sroutine_hash_entry objects,
+    instead it will simply add elements from source to destination
+    hash. Thus time of life of elements in destination hash becomes
+    dependant on time of life of elements from source hash. It also
+    won't touch lists linking elements in source and destination
+    hashes.
 */
 
-bool
-sp_merge_hash(HASH *dst, HASH *src)
+void sp_update_sp_used_routines(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;
-    }
+    Sroutine_hash_entry *rt= (Sroutine_hash_entry *)hash_element(src, i);
+    if (!hash_search(dst, (byte *)rt->key.str, rt->key.length))
+      my_hash_insert(dst, (byte *)rt);
   }
-  return res;
 }
 
 
 /*
-  Cache all routines implicitly or explicitly used by query
-  (or whatever object is represented by LEX).
+  Add contents of hash representing set of routines to the set of
+  routines used by statement.
 
   SYNOPSIS
-    sp_cache_routines()
+    sp_update_stmt_used_routines()
       thd - thread context
-      lex - LEX representing query
+      lex - LEX representing statement
+      src - hash representing set from which routines will be added
+
+  NOTE
+    It will also add elements to end of 'LEX::sroutines_list' list.
+*/
+
+static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src)
+{
+  for (uint i=0 ; i < src->records ; i++)
+  {
+    Sroutine_hash_entry *rt= (Sroutine_hash_entry *)hash_element(src, i);
+    (void)add_used_routine(lex, thd->current_arena, &rt->key);
+  }
+}
+
+
+/*
+  Cache sub-set of routines used by statement, add tables used by these
+  routines to statement table list. Do the same for all routines used
+  by these routines.
+
+  SYNOPSIS
+    sp_cache_routines_and_add_tables_aux()
+      thd   - thread context
+      lex   - LEX representing statement
+      start - first routine from the list of routines to be cached
+              (this list defines mentioned sub-set).
 
   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).
+  RETURN VALUE
+    TRUE  - some tables were added
+    FALSE - no tables were added.
 */
 
-void
-sp_cache_routines(THD *thd, LEX *lex)
+static bool
+sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
+                                     Sroutine_hash_entry *start)
 {
-  bool routines_added= TRUE;
+  bool result= FALSE;
 
-  DBUG_ENTER("sp_cache_routines");
+  DBUG_ENTER("sp_cache_routines_and_add_tables_aux");
 
-  while (routines_added)
+  for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
   {
-    routines_added= FALSE;
+    sp_name name(rt->key.str, rt->key.length);
+    int type= rt->key.str[0];
+    sp_head *sp;
 
-    for (int type= TYPE_ENUM_FUNCTION; type < TYPE_ENUM_TRIGGER; type++)
+    if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
+                              &thd->sp_func_cache : &thd->sp_proc_cache),
+                              &name)))
     {
-      HASH *h= (type == TYPE_ENUM_FUNCTION ? &lex->spfuns : &lex->spprocs);
-
-      for (uint i=0 ; i < h->records ; i++)
+      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)
       {
-        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 (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)
+    {
+      sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines);
+      result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+    }
+  }
+  DBUG_RETURN(result);
+}
+
+
+/*
+  Cache all routines from the set of used by statement, add tables used
+  by those routines to statement table list. Do the same for all routines
+  used by those routines.
+
+  SYNOPSIS
+    sp_cache_routines_and_add_tables()
+      thd   - thread context
+      lex   - LEX representing statement
+
+  RETURN VALUE
+    TRUE  - some tables were added
+    FALSE - no tables were added.
+*/
+
+bool
+sp_cache_routines_and_add_tables(THD *thd, LEX *lex)
+{
 
-        if (sp)
+  return sp_cache_routines_and_add_tables_aux(thd, lex,
+           (Sroutine_hash_entry *)lex->sroutines_list.first);
+}
+
+
+/*
+  Add all routines used by view to the set of routines used by statement.
+  Add tables used by those routines to statement table list. Do the same
+  for all routines used by these routines.
+
+  SYNOPSIS
+    sp_cache_routines_and_add_tables_for_view()
+      thd     - thread context
+      lex     - LEX representing statement
+      aux_lex - LEX representing view
+*/
+
+void
+sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex)
+{
+  Sroutine_hash_entry **last_cached_routine_ptr=
+                          (Sroutine_hash_entry **)lex->sroutines_list.next;
+  sp_update_stmt_used_routines(thd, lex, &aux_lex->sroutines);
+  (void)sp_cache_routines_and_add_tables_aux(thd, lex,
+                                             *last_cached_routine_ptr);
+}
+
+
+/*
+  Add triggers for table to the set of routines used by statement.
+  Add tables used by them to statement table list. Do the same for
+  all implicitly used routines.
+
+  SYNOPSIS
+    sp_cache_routines_and_add_tables_for_triggers()
+      thd      - thread context
+      lex      - LEX respresenting statement
+      triggers - triggers of the table
+*/
+
+void
+sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
+                                              Table_triggers_list *triggers)
+{
+  if (add_used_routine(lex, thd->current_arena, &triggers->sroutines_key))
+  {
+    Sroutine_hash_entry **last_cached_routine_ptr=
+                            (Sroutine_hash_entry **)lex->sroutines_list.next;
+    for (int i= 0; i < 3; i++)
+      for (int j= 0; j < 2; j++)
+        if (triggers->bodies[i][j])
         {
-          routines_added|= sp_merge_hash(&lex->spfuns, &sp->m_spfuns);
-          routines_added|= sp_merge_hash(&lex->spprocs, &sp->m_spprocs);
+          (void)triggers->bodies[i][j]->add_used_tables_to_table_list(thd,
+                                          &lex->query_tables_last);
+          sp_update_stmt_used_routines(thd, lex,
+                                       &triggers->bodies[i][j]->m_sroutines);
         }
-      }
-    }
+
+    (void)sp_cache_routines_and_add_tables_aux(thd, lex,
+                                               *last_cached_routine_ptr);
   }
-  DBUG_VOID_RETURN;
 }
 
+
 /*
  * Generates the CREATE... string from the table information.
  * Returns TRUE on success, FALSE on (alloc) failure.
diff --git a/sql/sp.h b/sql/sp.h
index 16d79026132..1854cee00f9 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -79,15 +79,19 @@ sp_function_exists(THD *thd, sp_name *name);
 
 
 /*
- *  For precaching of functions and procedures
- */
-void
-sp_add_to_hash(HASH *h, sp_name *fun);
-bool
-sp_merge_hash(HASH *dst, HASH *src);
-void
-sp_cache_routines(THD *thd, LEX *lex);
-
+  Procedures for pre-caching of stored routines and building table list
+  for prelocking.
+*/
+void sp_add_used_routine(LEX *lex, Item_arena *arena,
+                         sp_name *rt, char rt_type);
+void sp_update_sp_used_routines(HASH *dst, HASH *src);
+bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex);
+void sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
+                                               LEX *aux_lex);
+void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
+                                         Table_triggers_list *triggers);
+
+extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first);
 
 //
 // Utilities...
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 29898437cfb..b2e814cee77 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -242,8 +242,11 @@ sp_eval_func_item(THD *thd, Item **it_addr, enum enum_field_types type,
 void
 sp_name::init_qname(THD *thd)
 {
-  m_qname.length= m_db.length+m_name.length+1;
-  m_qname.str= thd->alloc(m_qname.length+1);
+  m_sroutines_key.length=  m_db.length + m_name.length + 2;
+  if (!(m_sroutines_key.str= thd->alloc(m_sroutines_key.length + 1)))
+    return;
+  m_qname.length= m_sroutines_key.length - 1;
+  m_qname.str= m_sroutines_key.str + 1;
   sprintf(m_qname.str, "%*s.%*s",
 	  m_db.length, (m_db.length ? m_db.str : ""),
 	  m_name.length, m_name.str);
@@ -315,16 +318,13 @@ 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_FOR_SP;
   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);
+  hash_init(&m_sroutines, system_charset_info, 0, 0, 0, sp_sroutine_key, 0, 0);
   DBUG_VOID_RETURN;
 }
 
@@ -527,8 +527,7 @@ sp_head::destroy()
   }
 
   hash_free(&m_sptabs);
-  hash_free(&m_spfuns);
-  hash_free(&m_spprocs);
+  hash_free(&m_sroutines);
   DBUG_VOID_RETURN;
 }
 
@@ -1037,11 +1036,10 @@ sp_head::restore_lex(THD *thd)
   oldlex->trg_table_fields.push_back(&sublex->trg_table_fields);
 
   /*
-    Add routines which are used by statement to respective sets for
-    this routine
+    Add routines which are used by statement to respective set for
+    this routine.
   */
-  sp_merge_hash(&m_spfuns, &sublex->spfuns);
-  sp_merge_hash(&m_spprocs, &sublex->spprocs);
+  sp_update_sp_used_routines(&m_sroutines, &sublex->sroutines);
   /*
     Merge tables used by this statement (but not by its functions or
     procedures) to multiset of tables used by this routine.
@@ -1578,16 +1576,22 @@ sp_instr_set::print(String *str)
 int
 sp_instr_set_trigger_field::execute(THD *thd, uint *nextp)
 {
-  int res= 0;
-
   DBUG_ENTER("sp_instr_set_trigger_field::execute");
-  /* QQ: Still unsure what should we return in case of error 1 or -1 ? */
-  if (!value->fixed && value->fix_fields(thd, 0, &value) ||
-      trigger_field->fix_fields(thd, 0, 0) ||
-      (value->save_in_field(trigger_field->field, 0) < 0))
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+
+int
+sp_instr_set_trigger_field::exec_core(THD *thd, uint *nextp)
+{
+  int res= 0;
+  Item *it= sp_prepare_func_item(thd, &value);
+  if (!it ||
+      !trigger_field->fixed && trigger_field->fix_fields(thd, 0, 0) ||
+      (it->save_in_field(trigger_field->field, 0) < 0))
     res= -1;
-  *nextp= m_ip + 1;
-  DBUG_RETURN(res);
+  *nextp = m_ip+1;
+  return res;
 }
 
 void
@@ -2399,72 +2403,3 @@ sp_add_to_query_tables(THD *thd, LEX *lex,
   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 617d6622037..28048285fb3 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -48,24 +48,50 @@ public:
   LEX_STRING m_db;
   LEX_STRING m_name;
   LEX_STRING m_qname;
+  /*
+    Key representing routine in the set of stored routines used by statement.
+    Consists of 1-byte routine type and m_qname (which usually refences to
+    same buffer). Note that one must complete initialization of the key by
+    calling set_routine_type().
+  */
+  LEX_STRING m_sroutines_key;
 
   sp_name(LEX_STRING name)
     : m_name(name)
   {
-    m_db.str= m_qname.str= 0;
-    m_db.length= m_qname.length= 0;
+    m_db.str= m_qname.str= m_sroutines_key.str= 0;
+    m_db.length= m_qname.length= m_sroutines_key.length= 0;
   }
 
   sp_name(LEX_STRING db, LEX_STRING name)
     : m_db(db), m_name(name)
   {
-    m_qname.str= 0;
-    m_qname.length= 0;
+    m_qname.str= m_sroutines_key.str= 0;
+    m_qname.length= m_sroutines_key.length= 0;
+  }
+
+  /*
+    Creates temporary sp_name object from key, used mainly
+    for SP-cache lookups.
+  */
+  sp_name(char *key, uint key_len)
+  {
+    m_sroutines_key.str= key;
+    m_sroutines_key.length= key_len;
+    m_name.str= m_qname.str= key + 1;
+    m_name.length= m_qname.length= key_len - 1;
+    m_db.str= 0;
+    m_db.length= 0;
   }
 
   // Init. the qualified name from the db and name.
   void init_qname(THD *thd);	// thd for memroot allocation
 
+  void set_routine_type(char type)
+  {
+    m_sroutines_key.str[0]= type;
+  }
+
   ~sp_name()
   {}
 };
@@ -106,13 +132,13 @@ public:
   longlong m_created;
   longlong m_modified;
   /*
-    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.
+    Set containing names of stored routines used by this routine.
+    Note that unlike elements of similar set for statement elements of this
+    set are not linked in one list. Because of this we are able save memory
+    by using for this set same objects that are used in 'sroutines' sets
+    for statements of which this stored routine consists.
   */
-  HASH m_spfuns, m_spprocs;
+  HASH m_sroutines;
   // Pointers set during parsing
   uchar *m_param_begin, *m_param_end, *m_body_begin;
 
@@ -471,10 +497,11 @@ class sp_instr_set_trigger_field : public sp_instr
 public:
 
   sp_instr_set_trigger_field(uint ip, sp_pcontext *ctx,
-                             Item_trigger_field *trg_fld, Item *val)
+                             Item_trigger_field *trg_fld,
+                             Item *val, LEX *lex)
     : sp_instr(ip, ctx),
       trigger_field(trg_fld),
-      value(val)
+      value(val), m_lex_keeper(lex, TRUE)
   {}
 
   virtual ~sp_instr_set_trigger_field()
@@ -482,11 +509,14 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
 private:
   Item_trigger_field *trigger_field;
   Item *value;
+  sp_lex_keeper m_lex_keeper;
 }; // class sp_instr_trigger_field : public sp_instr
 
 
@@ -951,7 +981,5 @@ 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/sql_base.cc b/sql/sql_base.cc
index a1887996d00..3fb80544e07 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1792,16 +1792,13 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
     may be still zero for prelocked statement...
   */
   if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
-      (thd->lex->spfuns.records || thd->lex->spprocs.records))
+      thd->lex->sroutines.records)
   {
-    TABLE_LIST **save_query_tables_last;
-
-    sp_cache_routines(thd, thd->lex);
-    save_query_tables_last= thd->lex->query_tables_last;
+    TABLE_LIST **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) ||
+    if (sp_cache_routines_and_add_tables(thd, thd->lex) ||
         *start)
     {
       query_tables_last_own= save_query_tables_last;
@@ -1837,19 +1834,16 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
           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))
+            tables->view->sroutines.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);
+          sp_cache_routines_and_add_tables_for_view(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);
+        hash_free(&tables->view->sroutines);
 	continue;
       }
 
@@ -1904,9 +1898,6 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
         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 &&
@@ -1914,6 +1905,8 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
       {
         if (!query_tables_last_own)
             query_tables_last_own= thd->lex->query_tables_last;
+        sp_cache_routines_and_add_tables_for_triggers(thd, thd->lex,
+                                                      tables->table->triggers);
       }
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
     }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 1270aab18ae..c82052a39a4 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -172,10 +172,9 @@ void lex_start(THD *thd, uchar *buf,uint length)
   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->sroutines.records)
+    my_hash_reset(&lex->sroutines);
+  lex->sroutines_list.empty();
   DBUG_VOID_RETURN;
 }
 
@@ -1571,6 +1570,28 @@ void st_select_lex::print_limit(THD *thd, String *str)
 
 
 /*
+  Initialize LEX object.
+
+  SYNOPSIS
+    st_lex::st_lex()
+
+  NOTE
+    LEX object initialized with this constructor can be used as part of
+    THD object for which one can safely call open_tables(), lock_tables()
+    and close_thread_tables() functions. But it is not yet ready for
+    statement parsing. On should use lex_start() function to prepare LEX
+    for this.
+*/
+
+st_lex::st_lex()
+  :result(0), sql_command(SQLCOM_END), query_tables_own_last(0)
+{
+  hash_init(&sroutines, system_charset_info, 0, 0, 0, sp_sroutine_key, 0, 0);
+  sroutines_list.empty();
+}
+
+
+/*
   Check whether the merging algorithm can be used on this VIEW
 
   SYNOPSIS
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 8af416f0ce8..160ee77e811 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -797,8 +797,14 @@ typedef struct st_lex
   bool sp_lex_in_use;	/* Keep track on lex usage in SPs for error handling */
   bool all_privileges;
   sp_pcontext *spcont;
-  HASH spfuns;		/* Called functions */
-  HASH spprocs;		/* Called procedures */
+  /* Set of stored routines called by statement. */
+  HASH sroutines;
+  /*
+    List linking elements of 'sroutines' set. Allows you to add new elements
+    to this set as you iterate through the list of existing elements.
+  */
+  SQL_LIST sroutines_list;
+
   st_sp_chistics sp_chistics;
   bool only_view;       /* used for SHOW CREATE TABLE/VIEW */
   /*
@@ -831,17 +837,11 @@ typedef struct st_lex
   */
   uchar *fname_start, *fname_end;
 
-  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);
-    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);
-  }
+  st_lex();
 
   virtual ~st_lex()
   {
-    hash_free(&spfuns);
-    hash_free(&spprocs);
+    hash_free(&sroutines);
   }
 
   inline void uncacheable(uint8 cause)
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 16c429f40b7..441250da31b 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2296,8 +2296,7 @@ mysql_execute_command(THD *thd)
     Don't reset warnings when executing a stored routine.
   */
   if ((all_tables || &lex->select_lex != lex->all_selects_list ||
-       lex->spfuns.records || lex->spprocs.records) &&
-      !thd->spcont)
+       lex->sroutines.records) && !thd->spcont)
     mysql_reset_errors(thd, 0);
 
 #ifdef HAVE_REPLICATION
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 95524a6dfbf..af94cf6f9dd 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -1,3 +1,20 @@
+/* Copyright (C) 2004-2005 MySQL AB
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; either version 2 of the License, or
+   (at your option) any later version.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA */
+
+
 #include "mysql_priv.h"
 #include "sp_head.h"
 #include "sql_trigger.h"
@@ -418,6 +435,18 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
       table->triggers= triggers;
 
       /*
+        Construct key that will represent triggers for this table in the set
+        of routines used by statement.
+      */
+      triggers->sroutines_key.length= 1+strlen(db)+1+strlen(table_name)+1;
+      if (!(triggers->sroutines_key.str=
+              alloc_root(&table->mem_root, triggers->sroutines_key.length)))
+        DBUG_RETURN(1);
+      triggers->sroutines_key.str[0]= TYPE_ENUM_TRIGGER;
+      strmov(strmov(strmov(triggers->sroutines_key.str+1, db), "."),
+             table_name);
+
+      /*
         TODO: This could be avoided if there is no triggers
               for UPDATE and DELETE.
       */
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 0547283d0c5..044219d5ac9 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -1,3 +1,20 @@
+/* Copyright (C) 2004-2005 MySQL AB
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; either version 2 of the License, or
+   (at your option) any later version.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA */
+
+
 /*
   This class holds all information about triggers of table.
 
@@ -28,6 +45,14 @@ class Table_triggers_list: public Sql_alloc
     used in CREATE/DROP TRIGGER for looking up trigger by name.
   */
   List<LEX_STRING>  names_list;
+  /*
+    Key representing triggers for this table in set of all stored
+    routines used by statement.
+    TODO: We won't need this member once triggers namespace will be
+    database-wide instead of table-wide because then we will be able
+    to use key based on sp_name as for other stored routines.
+  */
+  LEX_STRING        sroutines_key;
 
 public:
   /*
@@ -112,6 +137,8 @@ public:
   }
 
   friend class Item_trigger_field;
+  friend void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
+                Table_triggers_list *triggers);
 
 private:
   bool prepare_record1_accessors(TABLE *table);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 892d2516808..72786d180fd 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1532,7 +1532,7 @@ call:
 	    lex->sql_command= SQLCOM_CALL;
 	    lex->spname= $2;
 	    lex->value_list.empty();
-	    sp_add_to_hash(&lex->spprocs, $2);
+	    sp_add_used_routine(lex, YYTHD, $2, TYPE_ENUM_PROCEDURE);
 	  }
           '(' sp_cparam_list ')' {}
 	;
@@ -4682,7 +4682,7 @@ simple_expr:
 	    sp_name *name= new sp_name($1, $3);
 
 	    name->init_qname(YYTHD);
-	    sp_add_to_hash(&lex->spfuns, name);
+	    sp_add_used_routine(lex, YYTHD, name, TYPE_ENUM_FUNCTION);
 	    if ($5)
 	      $$= new Item_func_sp(name, *$5);
 	    else
@@ -4771,7 +4771,7 @@ simple_expr:
 	      LEX *lex= Lex;
               sp_name *name= sp_name_current_db_new(YYTHD, $1);
 
-              sp_add_to_hash(&lex->spfuns, name);
+              sp_add_used_routine(lex, YYTHD, name, TYPE_ENUM_FUNCTION);
               if ($3)
                 $$= new Item_func_sp(name, *$3);
               else
@@ -7684,12 +7684,6 @@ sys_option_value:
               yyerror(ER(ER_SYNTAX_ERROR));
               YYABORT;
             }
-            if (lex->query_tables)
-            {
-              my_message(ER_SP_SUBSELECT_NYI, ER(ER_SP_SUBSELECT_NYI),
-              MYF(0));
-              YYABORT;
-            }
             if ($4)
               it= $4;
             else
@@ -7702,7 +7696,7 @@ sys_option_value:
                                                   $2.base_name.str)) ||
                 !(i= new sp_instr_set_trigger_field(
                            lex->sphead->instructions(), lex->spcont,
-                           trg_fld, it)))
+                           trg_fld, it, lex)))
               YYABORT;
 
             /*