manual merge use local

2 files changed, 2945 insertions, 891 deletions

diff --git a/mysql-test/t/rpl_temporary.test b/mysql-test/t/rpl_temporary.test
index 71d7b32b7c9..2400eac76ba 100644
--- a/mysql-test/t/rpl_temporary.test
+++ b/mysql-test/t/rpl_temporary.test
@@ -82,6 +82,7 @@ drop temporary table t3;
 select * from t2;
 
 --replace_result $VERSION VERSION
+--replace_column 2 # 5 #
 show binlog events;
 
 drop table t1, t2;
@@ -129,31 +130,30 @@ create temporary table t3 (f int);
 sync_with_master;
 
 #
-# BUG#17263 incorrect generation DROP temp tables
-# Temporary tables of connection are dropped in batches
-# where a batch correspond to pseudo_thread_id
-# value was set up at the moment of temp table creation
+# Bug#17284 erroneous temp table cleanup on slave
 #
-connection con1;
-set @session.pseudo_thread_id=100;
-create temporary table t101 (id int);
-create temporary table t102 (id int);
-set @session.pseudo_thread_id=200;
-create temporary table t201 (id int);
-create temporary table `#not_user_table_prefixed_with_hash_sign_no_harm` (id int);
-set @con1_id=connection_id();
-kill @con1_id;
-
-#now do something to show that slave is ok after DROP temp tables
+
 connection master;
-create table t1(f int);
-insert into t1 values (1);
+create temporary table t4 (f int);
+create table t5 (f int);
+sync_with_master;
+# find dumper's $id
+source include/get_binlog_dump_thread_id.inc;
+insert into t4 values (1);
+# a hint how to do that in 5.1
+--replace_result $id "`select id from information_schema.processlist where command='Binlog Dump'`"
+eval kill $id; # to stimulate reconnection by slave w/o timeout
+insert into t5 select * from t4;
+save_master_pos;
 
-sync_slave_with_master;
-#connection slave;
-select * from t1 /* must be 1 */;
+connection slave;
+sync_with_master;
+select * from t5 /* must be 1 after reconnection */;
+
+connection master;
+drop temporary table t4;
+drop table t5;
 
-connection master; 
-drop table t1;
+# The server will now close done
 
-# End of 4.1 tests
+# End of 5.0 tests
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 756fb8189dc..2b5a3d1f38d 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -19,30 +19,36 @@
 
 #include "mysql_priv.h"
 #include "sql_select.h"
+#include "sp_head.h"
+#include "sp.h"
+#include "sql_trigger.h"
 #include <m_ctype.h>
 #include <my_dir.h>
 #include <hash.h>
-#include <nisam.h>
 #ifdef	__WIN__
 #include <io.h>
 #endif
 
 TABLE *unused_tables;				/* Used by mysql_test */
 HASH open_cache;				/* Used by mysql_test */
-HASH assign_cache;
 
-static int open_unireg_entry(THD *thd,TABLE *entry,const char *db,
-			     const char *name, const char *alias);
+static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
+			     const char *name, const char *alias,
+			     TABLE_LIST *table_list, MEM_ROOT *mem_root);
 static void free_cache_entry(TABLE *entry);
 static void mysql_rm_tmp_tables(void);
-
+static bool open_new_frm(THD *thd, const char *path, const char *alias,
+                         const char *db, const char *table_name,
+                         uint db_stat, uint prgflag,
+                         uint ha_open_flags, TABLE *outparam,
+                         TABLE_LIST *table_desc, MEM_ROOT *mem_root);
 
 extern "C" byte *table_cache_key(const byte *record,uint *length,
 				 my_bool not_used __attribute__((unused)))
 {
   TABLE *entry=(TABLE*) record;
-  *length=entry->key_length;
-  return (byte*) entry->table_cache_key;
+  *length= entry->s->key_length;
+  return (byte*) entry->s->table_cache_key;
 }
 
 bool table_cache_init(void)
@@ -123,12 +129,11 @@ static void check_unused(void)
     #		Pointer to list of names of open tables.
 */
 
-OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild)
+OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *db, const char *wild)
 {
   int result = 0;
   OPEN_TABLE_LIST **start_list, *open_list;
   TABLE_LIST table_list;
-  char name[NAME_LEN*2];
   DBUG_ENTER("list_open_tables");
 
   VOID(pthread_mutex_lock(&LOCK_open));
@@ -140,20 +145,19 @@ OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild)
   {
     OPEN_TABLE_LIST *table;
     TABLE *entry=(TABLE*) hash_element(&open_cache,idx);
+    TABLE_SHARE *share= entry->s;
 
-    DBUG_ASSERT(entry->real_name);
-    if ((!entry->real_name))			// To be removed
+    DBUG_ASSERT(share->table_name != 0);
+    if ((!share->table_name))			// To be removed
       continue;					// Shouldn't happen
-    if (wild)
-    {
-      strxmov(name,entry->table_cache_key,".",entry->real_name,NullS);
-      if (wild_compare(name,wild,0))
-	continue;
-    }
+    if (db && my_strcasecmp(system_charset_info, db, share->db))
+      continue;
+    if (wild && wild_compare(share->table_name,wild,0))
+      continue;
 
     /* Check if user has SELECT privilege for any column in the table */
-    table_list.db= (char*) entry->table_cache_key;
-    table_list.real_name= entry->real_name;
+    table_list.db=        (char*) share->db;
+    table_list.table_name= (char*) share->table_name;
     table_list.grant.privilege=0;
 
     if (check_table_access(thd,SELECT_ACL | EXTRA_ACL,&table_list,1))
@@ -161,8 +165,8 @@ OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild)
     /* need to check if we haven't already listed it */
     for (table= open_list  ; table ; table=table->next)
     {
-      if (!strcmp(table->table,entry->real_name) &&
-	  !strcmp(table->db,entry->table_cache_key))
+      if (!strcmp(table->table,share->table_name) &&
+	  !strcmp(table->db,entry->s->db))
       {
 	if (entry->in_use)
 	  table->in_use++;
@@ -174,15 +178,15 @@ OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild)
     if (table)
       continue;
     if (!(*start_list = (OPEN_TABLE_LIST *)
-	  sql_alloc(sizeof(**start_list)+entry->key_length)))
+	  sql_alloc(sizeof(**start_list)+share->key_length)))
     {
       open_list=0;				// Out of memory
       break;
     }
     strmov((*start_list)->table=
 	   strmov(((*start_list)->db= (char*) ((*start_list)+1)),
-		  entry->table_cache_key)+1,
-	   entry->real_name);
+		  entry->s->db)+1,
+	   entry->s->table_name);
     (*start_list)->in_use= entry->in_use ? 1 : 0;
     (*start_list)->locked= entry->locked_by_name ? 1 : 0;
     start_list= &(*start_list)->next;
@@ -200,6 +204,7 @@ OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild)
 void intern_close_table(TABLE *table)
 {						// Free all structures
   free_io_cache(table);
+  delete table->triggers;
   if (table->file)
     VOID(closefrm(table));			// close file
 }
@@ -282,9 +287,9 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
   else
   {
     bool found=0;
-    for (TABLE_LIST *table=tables ; table ; table=table->next)
+    for (TABLE_LIST *table= tables; table; table= table->next_local)
     {
-      if (remove_table_from_cache(thd, table->db, table->real_name,
+      if (remove_table_from_cache(thd, table->db, table->table_name,
                                   RTFC_OWNED_BY_THD_FLAG))
 	found=1;
     }
@@ -318,7 +323,7 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
       for (uint idx=0 ; idx < open_cache.records ; idx++)
       {
 	TABLE *table=(TABLE*) hash_element(&open_cache,idx);
-	if ((table->version) < refresh_version && table->db_stat)
+	if ((table->s->version) < refresh_version && table->db_stat)
 	{
 	  found=1;
 	  pthread_cond_wait(&COND_refresh,&LOCK_open);
@@ -335,8 +340,8 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
     result=reopen_tables(thd,1,1);
     thd->in_lock_tables=0;
     /* Set version for table */
-    for (TABLE *table=thd->open_tables; table ; table=table->next)
-      table->version=refresh_version;
+    for (TABLE *table=thd->open_tables; table ; table= table->next)
+      table->s->version= refresh_version;
   }
   VOID(pthread_mutex_unlock(&LOCK_open));
   if (if_wait_for_refresh)
@@ -352,7 +357,39 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
 
 
 /*
-  Close all tables used by thread
+  Mark all tables in the list which were used by current substatement
+  as free for reuse.
+
+  SYNOPSIS
+    mark_used_tables_as_free_for_reuse()
+      thd   - thread context
+      table - head of the list of tables
+
+  DESCRIPTION
+    Marks all tables in the list which were used by current substatement
+    (they are marked by its query_id) as free for reuse.
+
+  NOTE
+    The reason we reset query_id is that it's not enough to just test
+    if table->query_id != thd->query_id to know if a table is in use.
+
+    For example
+    SELECT f1_that_uses_t1() FROM t1;
+    In f1_that_uses_t1() we will see one instance of t1 where query_id is
+    set to query_id of original query.
+*/
+
+static void mark_used_tables_as_free_for_reuse(THD *thd, TABLE *table)
+{
+  for (; table ; table= table->next)
+    if (table->query_id == thd->query_id)
+      table->query_id= 0;
+}
+
+
+/*
+  Close all tables used by the current substatement, or all tables
+  used by this thread if we are on the upper level.
 
   SYNOPSIS
     close_thread_tables()
@@ -361,23 +398,42 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
 			LOCK_open
     skip_derived	Set to 1 (0 = default) if we should not free derived
 			tables.
+    stopper             When closing tables from thd->open_tables(->next)*, 
+                        don't close/remove tables starting from stopper.
 
   IMPLEMENTATION
     Unlocks tables and frees derived tables.
     Put all normal tables used by thread in free list.
+
+    When in prelocked mode it will only close/mark as free for reuse
+    tables opened by this substatement, it will also check if we are
+    closing tables after execution of complete query (i.e. we are on
+    upper level) and will leave prelocked mode if needed.
 */
 
 void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived)
 {
   bool found_old_table;
+  prelocked_mode_type prelocked_mode= thd->prelocked_mode;
   DBUG_ENTER("close_thread_tables");
 
+  /*
+    We are assuming here that thd->derived_tables contains ONLY derived
+    tables for this substatement. i.e. instead of approach which uses
+    query_id matching for determining which of the derived tables belong
+    to this substatement we rely on the ability of substatements to
+    save/restore thd->derived_tables during their execution.
+
+    TODO: Probably even better approach is to simply associate list of
+          derived tables with (sub-)statement instead of thread and destroy
+          them at the end of its execution.
+  */
   if (thd->derived_tables && !skip_derived)
   {
     TABLE *table, *next;
     /*
-      Close all derived tables generated from questions like
-      SELECT * from (select * from t1))
+      Close all derived tables generated in queries like
+      SELECT * FROM (SELECT * FROM t1)
     */
     for (table= thd->derived_tables ; table ; table= next)
     {
@@ -386,10 +442,55 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived)
     }
     thd->derived_tables= 0;
   }
-  if (thd->locked_tables)
+
+  if (prelocked_mode)
   {
-    ha_commit_stmt(thd);			// If select statement
-    DBUG_VOID_RETURN;				// LOCK TABLES in use
+    /*
+      Mark all temporary tables used by this substatement as free for reuse.
+    */
+    mark_used_tables_as_free_for_reuse(thd, thd->temporary_tables);
+  }
+
+  if (thd->locked_tables || prelocked_mode)
+  {
+    /*
+      Let us commit transaction for statement. Since in 5.0 we only have
+      one statement transaction and don't allow several nested statement
+      transactions this call will do nothing if we are inside of stored
+      function or trigger (i.e. statement transaction is already active and
+      does not belong to statement for which we do close_thread_tables()).
+      TODO: This should be fixed in later releases.
+    */
+    ha_commit_stmt(thd);
+
+    /* We are under simple LOCK TABLES so should not do anything else. */
+    if (!prelocked_mode)
+      DBUG_VOID_RETURN;
+
+    if (!thd->lex->requires_prelocking())
+    {
+      /*
+        If we are executing one of substatements we have to mark
+        all tables which it used as free for reuse.
+      */
+      mark_used_tables_as_free_for_reuse(thd, thd->open_tables);
+      DBUG_VOID_RETURN;
+    }
+
+    DBUG_ASSERT(prelocked_mode);
+    /*
+      We are in prelocked mode, so we have to leave it now with doing
+      implicit UNLOCK TABLES if need.
+    */
+    DBUG_PRINT("info",("thd->prelocked_mode= NON_PRELOCKED"));
+    thd->prelocked_mode= NON_PRELOCKED;
+
+    if (prelocked_mode == PRELOCKED_UNDER_LOCK_TABLES)
+      DBUG_VOID_RETURN;
+
+    thd->lock= thd->locked_tables;
+    thd->locked_tables= 0;
+    /* Fallthrough */
   }
 
   if (thd->lock)
@@ -397,12 +498,24 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived)
     mysql_unlock_tables(thd, thd->lock);
     thd->lock=0;
   }
+  /*
+    assume handlers auto-commit (if some doesn't - transaction handling
+    in MySQL should be redesigned to support it; it's a big change,
+    and it's not worth it - better to commit explicitly only writing
+    transactions, read-only ones should better take care of themselves.
+    saves some work in 2pc too)
+    see also sql_parse.cc - dispatch_command()
+  */
+  bzero(&thd->transaction.stmt, sizeof(thd->transaction.stmt));
+  if (!thd->active_transaction())
+    thd->transaction.xid_state.xid.null();
+
   /* VOID(pthread_sigmask(SIG_SETMASK,&thd->block_signals,NULL)); */
   if (!lock_in_use)
     VOID(pthread_mutex_lock(&LOCK_open));
   safe_mutex_assert_owner(&LOCK_open);
 
-  DBUG_PRINT("info", ("thd->open_tables=%p", thd->open_tables));
+  DBUG_PRINT("info", ("thd->open_tables: %p", thd->open_tables));
 
  found_old_table= 0;
   while (thd->open_tables)
@@ -421,6 +534,17 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived)
   if (!lock_in_use)
     VOID(pthread_mutex_unlock(&LOCK_open));
   /*  VOID(pthread_sigmask(SIG_SETMASK,&thd->signals,NULL)); */
+
+  if (prelocked_mode == PRELOCKED)
+  {
+    /*
+      If we are here then we are leaving normal prelocked mode, so it is
+      good idea to turn off OPTION_TABLE_LOCK flag.
+    */
+    DBUG_ASSERT(thd->lex->requires_prelocking());
+    thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+  }
+
   DBUG_VOID_RETURN;
 }
 
@@ -428,14 +552,14 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived)
 
 bool close_thread_table(THD *thd, TABLE **table_ptr)
 {
-  DBUG_ENTER("close_thread_table");
-
   bool found_old_table= 0;
   TABLE *table= *table_ptr;
+  DBUG_ENTER("close_thread_table");
   DBUG_ASSERT(table->key_read == 0);
+  DBUG_ASSERT(table->file->inited == handler::NONE);
 
   *table_ptr=table->next;
-  if (table->version != refresh_version ||
+  if (table->s->version != refresh_version ||
       thd->version != refresh_version || !table->db_stat)
   {
     VOID(hash_delete(&open_cache,(byte*) table));
@@ -443,9 +567,9 @@ bool close_thread_table(THD *thd, TABLE **table_ptr)
   }
   else
   {
-    if (table->flush_version != flush_version)
+    if (table->s->flush_version != flush_version)
     {
-      table->flush_version=flush_version;
+      table->s->flush_version= flush_version;
       table->file->extra(HA_EXTRA_FLUSH);
     }
     else
@@ -473,8 +597,8 @@ void close_temporary(TABLE *table,bool delete_table)
 {
   DBUG_ENTER("close_temporary");
   char path[FN_REFLEN];
-  db_type table_type=table->db_type;
-  strmov(path,table->path);
+  db_type table_type=table->s->db_type;
+  strmov(path,table->s->path);
   free_io_cache(table);
   closefrm(table);
   my_free((char*) table,MYF(0));
@@ -483,23 +607,13 @@ void close_temporary(TABLE *table,bool delete_table)
   DBUG_VOID_RETURN;
 }
 
-/* close_temporary_tables' internal */
-static inline uint  tmpkeyval(THD *thd, TABLE *table)
-{
-  return uint4korr(table->table_cache_key + table->key_length - 
-                   sizeof(thd->variables.pseudo_thread_id));
-}
-
-/* Creates one DROP TEMPORARY TABLE binlog event for each pseudo-thread */
 
 void close_temporary_tables(THD *thd)
 {
-  TABLE *next,
-    *prev_table /* prev link is not maintained in TABLE's double-linked list */,
-    *table;
-  char *query= (gptr) 0, *end;
-  uint query_buf_size, max_names_len; 
-  bool found_user_tables;
+  TABLE *table,*next;
+  char *query, *end;
+  uint query_buf_size; 
+  bool found_user_tables = 0;
 
   if (!thd->temporary_tables)
     return;
@@ -507,176 +621,225 @@ void close_temporary_tables(THD *thd)
   LINT_INIT(end);
   query_buf_size= 50;   // Enough for DROP ... TABLE IF EXISTS
 
-  /* 
-     insertion sort of temp tables by pseudo_thread_id to build ordered list 
-     of sublists of equal pseudo_thread_id
-  */
-  for (prev_table= thd->temporary_tables, 
-         table= prev_table->next,
-         found_user_tables= (prev_table->table_name[0] != '#'); 
-       table;
-       prev_table= table, table= table->next)
-  {
-    TABLE *prev_sorted /* same as for prev_table */,
-      *sorted;
+  for (table=thd->temporary_tables ; table ; table=table->next)
     /*
-      table not created directly by the user is moved to the tail. 
-      Fixme/todo: nothing (I checked the manual) prevents user to create temp
-      with `#' 
+      We are going to add 4 ` around the db/table names, so 1 does not look
+      enough; indeed it is enough, because table->key_length is greater (by 8,
+      because of server_id and thread_id) than db||table.
     */
-    if (table->real_name[0] == '#')
-      continue;
-    else 
-    {
-      found_user_tables = 1;
-    }
-    for (prev_sorted= NULL, sorted= thd->temporary_tables; sorted != table; 
-         prev_sorted= sorted, sorted= sorted->next)
-    {
-      if (sorted->real_name[0] == '#' || tmpkeyval(thd, sorted) > tmpkeyval(thd, table))
-      {
-        /* move into the sorted part of the list from the unsorted */
-        prev_table->next= table->next;
-        table->next= sorted;
-        if (prev_sorted) 
-        {
-          prev_sorted->next= table;
-        }
-        else
-        {
-          thd->temporary_tables= table;
-        }
-        table= prev_table;
-        break;
-      }
-    }
-  }  
-  /* 
-     calc query_buf_size as max per sublists, one sublist per pseudo thread id.
-     Also stop at first occurence of `#'-named table that starts 
-     all implicitly created temp tables
-  */
-  for (max_names_len= 0, table=thd->temporary_tables; 
-       table && table->real_name[0] != '#';
-       table=table->next)
+    query_buf_size+= table->s->key_length+1;
+
+  if ((query = alloc_root(thd->mem_root, query_buf_size)))
+    // Better add "if exists", in case a RESET MASTER has been done
+    end=strmov(query, "DROP /*!40005 TEMPORARY */ TABLE IF EXISTS ");
+
+  for (table=thd->temporary_tables ; table ; table=next)
   {
-    uint tmp_names_len;
-    for (tmp_names_len= table->key_length + 1;
-         table->next && table->real_name[0] != '#' &&
-           tmpkeyval(thd, table) == tmpkeyval(thd, table->next);
-         table=table->next)
+    if (query) // we might be out of memory, but this is not fatal
     {
-      /*
-        We are going to add 4 ` around the db/table names, so 1 might not look
-        enough; indeed it is enough, because table->key_length is greater (by 8,
-        because of server_id and thread_id) than db||table.
-      */
-      tmp_names_len += table->next->key_length + 1;
+      // skip temporary tables not created directly by the user
+      if (table->s->table_name[0] != '#')
+	found_user_tables = 1;
+      end = strxmov(end,"`",table->s->db,"`.`",
+                    table->s->table_name,"`,", NullS);
     }
-    if (tmp_names_len > max_names_len) max_names_len= tmp_names_len;
+    next=table->next;
+    close_temporary(table, 1);
   }
-  
-  /* allocate */
-  if (found_user_tables && mysql_bin_log.is_open() &&
-      (query = alloc_root(thd->mem_root, query_buf_size+= max_names_len)))
-    // Better add "if exists", in case a RESET MASTER has been done
-    end= strmov(query, "DROP /*!40005 TEMPORARY */ TABLE IF EXISTS ");
-
-  /* scan sorted tmps to generate sequence of DROP */
-  for (table=thd->temporary_tables; table; table= next)
-  {
-    if (query // we might be out of memory, but this is not fatal 
-        && table->real_name[0] != '#') 
-    {
-      char *end_cur;
-      /* Set pseudo_thread_id to be that of the processed table */
-      thd->variables.pseudo_thread_id= tmpkeyval(thd, table);
-      /* Loop forward through all tables within the sublist of
-         common pseudo_thread_id to create single DROP query */
-      for (end_cur= end;
-           table && table->real_name[0] != '#' &&
-             tmpkeyval(thd, table) == thd->variables.pseudo_thread_id;
-           table= next)
-      {
-        end_cur= strxmov(end_cur, "`", table->table_cache_key, "`.`",
-                      table->real_name, "`,", NullS);
-        next= table->next;
-        close_temporary(table, 1);
-      }
-      thd->clear_error();
-      /* The -1 is to remove last ',' */
-      Query_log_event qinfo(thd, query, (ulong)(end_cur - query) - 1, 0, FALSE);
-      /*
-        Imagine the thread had created a temp table, then was doing a SELECT, and
-        the SELECT was killed. Then it's not clever to mark the statement above as
-        "killed", because it's not really a statement updating data, and there
-        are 99.99% chances it will succeed on slave.
-        If a real update (one updating a persistent table) was killed on the
-        master, then this real update will be logged with error_code=killed,
-        rightfully causing the slave to stop.
-      */
-      qinfo.error_code= 0;
-      mysql_bin_log.write(&qinfo);
-    }
-    else 
-    {
-      next= table->next;
-      close_temporary(table, 1);
-    }
+  if (query && found_user_tables && mysql_bin_log.is_open())
+  {
+    /* The -1 is to remove last ',' */
+    thd->clear_error();
+    Query_log_event qinfo(thd, query, (ulong)(end-query)-1, 0, FALSE);
+    /*
+      Imagine the thread had created a temp table, then was doing a SELECT, and
+      the SELECT was killed. Then it's not clever to mark the statement above as
+      "killed", because it's not really a statement updating data, and there
+      are 99.99% chances it will succeed on slave.
+      If a real update (one updating a persistent table) was killed on the
+      master, then this real update will be logged with error_code=killed,
+      rightfully causing the slave to stop.
+    */
+    qinfo.error_code= 0;
+    mysql_bin_log.write(&qinfo);
   }
   thd->temporary_tables=0;
 }
 
+
 /*
-  Find first suitable table by alias in given list.
+  Find table in list.
 
   SYNOPSIS
     find_table_in_list()
-    table - pointer to table list
-    db_name - data base name or 0 for any
-    table_name - table name or 0 for any
+    table		Pointer to table list
+    offset		Offset to which list in table structure to use
+    db_name		Data base name
+    table_name		Table name
+
+  NOTES:
+    This is called by find_table_in_local_list() and
+    find_table_in_global_list().
 
   RETURN VALUES
     NULL	Table not found
     #		Pointer to found table.
 */
 
-TABLE_LIST * find_table_in_list(TABLE_LIST *table,
-				const char *db_name, const char *table_name)
+TABLE_LIST *find_table_in_list(TABLE_LIST *table,
+                               st_table_list *TABLE_LIST::*link,
+                               const char *db_name,
+                               const char *table_name)
 {
-  for (; table; table= table->next)
-    if ((!db_name || !strcmp(table->db, db_name)) &&
-	(!table_name || !my_strcasecmp(table_alias_charset,
-				       table->alias, table_name)))
+  for (; table; table= table->*link )
+  {
+    if ((table->table == 0 || table->table->s->tmp_table == NO_TMP_TABLE) &&
+        strcmp(table->db, db_name) == 0 &&
+        strcmp(table->table_name, table_name) == 0)
       break;
+  }
   return table;
 }
 
+
 /*
-  Find real table in given list.
+  Test that table is unique (It's only exists once in the table list)
 
   SYNOPSIS
-    find_real_table_in_list()
-    table - pointer to table list
-    db_name - data base name
-    table_name - table name
+    unique_table()
+    thd                   thread handle
+    table                 table which should be checked
+    table_list            list of tables
+
+  NOTE: to exclude derived tables from check we use following mechanism:
+    a) during derived table processing set THD::derived_tables_processing
+    b) JOIN::prepare set SELECT::exclude_from_table_unique_test if
+       THD::derived_tables_processing set. (we can't use JOIN::execute
+       because for PS we perform only JOIN::prepare, but we can't set this
+       flag in JOIN::prepare if we are not sure that we are in derived table
+       processing loop, because multi-update call fix_fields() for some its
+       items (which mean JOIN::prepare for subqueries) before unique_table
+       call to detect which tables should be locked for write).
+    c) unique_table skip all tables which belong to SELECT with
+       SELECT::exclude_from_table_unique_test set.
+    Also SELECT::exclude_from_table_unique_test used to exclude from check
+    tables of main SELECT of multi-delete and multi-update
+
+    TODO: when we will have table/view change detection we can do this check
+          only once for PS/SP
 
-  RETURN VALUES
-    NULL	Table not found
-    #		Pointer to found table.
+  RETURN
+    found duplicate
+    0 if table is unique
 */
 
-TABLE_LIST * find_real_table_in_list(TABLE_LIST *table,
-				     const char *db_name,
-				     const char *table_name)
+TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list)
 {
-  for (; table; table= table->next)
-    if (!strcmp(table->db, db_name) &&
-	!strcmp(table->real_name, table_name))
+  TABLE_LIST *res;
+  const char *d_name, *t_name;
+  DBUG_ENTER("unique_table");
+  DBUG_PRINT("enter", ("table alias: %s", table->alias));
+
+  /*
+    If this function called for query which update table (INSERT/UPDATE/...)
+    then we have in table->table pointer to TABLE object which we are
+    updating even if it is VIEW so we need TABLE_LIST of this TABLE object
+    to get right names (even if lower_case_table_names used).
+
+    If this function called for CREATE command that we have not opened table
+    (table->table equal to 0) and right names is in current TABLE_LIST
+    object.
+  */
+  if (table->table)
+  {
+    /* temporary table is always unique */
+    if (table->table && table->table->s->tmp_table != NO_TMP_TABLE)
+      DBUG_RETURN(0);
+    table= table->find_underlying_table(table->table);
+    /*
+      as far as we have table->table we have to find real TABLE_LIST of
+      it in underlying tables
+    */
+    DBUG_ASSERT(table);
+  }
+  d_name= table->db;
+  t_name= table->table_name;
+
+  DBUG_PRINT("info", ("real table: %s.%s", d_name, t_name));
+  for (;;)
+  {
+    if (((! (res= find_table_in_global_list(table_list, d_name, t_name))) &&
+         (! (res= mysql_lock_have_duplicate(thd, table, table_list)))) ||
+        ((!res->table || res->table != table->table) &&
+         res->select_lex && !res->select_lex->exclude_from_table_unique_test))
       break;
-  return table;
+    /*
+      If we found entry of this table or or table of SELECT which already
+      processed in derived table or top select of multi-update/multi-delete
+      (exclude_from_table_unique_test).
+    */
+    table_list= res->next_global;
+    DBUG_PRINT("info",
+               ("found same copy of table or table which we should skip"));
+  }
+  DBUG_RETURN(res);
 }
 
+
+/*
+  Issue correct error message in case we found 2 duplicate tables which
+  prevent some update operation
+
+  SYNOPSIS
+    update_non_unique_table_error()
+    update      table which we try to update
+    operation   name of update operation
+    duplicate   duplicate table which we found
+
+  NOTE:
+    here we hide view underlying tables if we have them
+*/
+
+void update_non_unique_table_error(TABLE_LIST *update,
+                                   const char *operation,
+                                   TABLE_LIST *duplicate)
+{
+  update= update->top_table();
+  duplicate= duplicate->top_table();
+  if (!update->view || !duplicate->view ||
+      update->view == duplicate->view ||
+      update->view_name.length != duplicate->view_name.length ||
+      update->view_db.length != duplicate->view_db.length ||
+      my_strcasecmp(table_alias_charset,
+                    update->view_name.str, duplicate->view_name.str) != 0 ||
+      my_strcasecmp(table_alias_charset,
+                    update->view_db.str, duplicate->view_db.str) != 0)
+  {
+    /*
+      it is not the same view repeated (but it can be parts of the same copy
+      of view), so we have to hide underlying tables.
+    */
+    if (update->view)
+    {
+      if (update->view == duplicate->view)
+        my_error(ER_NON_UPDATABLE_TABLE, MYF(0), update->alias, operation);
+      else
+        my_error(ER_VIEW_PREVENT_UPDATE, MYF(0),
+                 (duplicate->view ? duplicate->alias : update->alias),
+                 operation, update->alias);
+      return;
+    }
+    if (duplicate->view)
+    {
+      my_error(ER_VIEW_PREVENT_UPDATE, MYF(0), duplicate->alias, operation,
+               update->alias);
+      return;
+    }
+  }
+  my_error(ER_UPDATE_TABLE_USED, MYF(0), update->alias);
+}
+
+
 TABLE **find_temporary_table(THD *thd, const char *db, const char *table_name)
 {
   char	key[MAX_DBKEY_LENGTH];
@@ -691,8 +854,8 @@ TABLE **find_temporary_table(THD *thd, const char *db, const char *table_name)
   prev= &thd->temporary_tables;
   for (table=thd->temporary_tables ; table ; table=table->next)
   {
-    if (table->key_length == key_length &&
-	!memcmp(table->table_cache_key,key,key_length))
+    if (table->s->key_length == key_length &&
+	!memcmp(table->s->table_cache_key,key,key_length))
       return prev;
     prev= &table->next;
   }
@@ -707,7 +870,7 @@ bool close_temporary_table(THD *thd, const char *db, const char *table_name)
     return 1;
   table= *prev;
   *prev= table->next;
-  close_temporary(table);
+  close_temporary(table, 1);
   if (thd->slave_thread)
     --slave_open_temp_tables;
   return 0;
@@ -720,22 +883,26 @@ bool close_temporary_table(THD *thd, const char *db, const char *table_name)
   Prepares a table cache key, which is the concatenation of db, table_name and
   thd->slave_proxy_id, separated by '\0'.
 */
+
 bool rename_temporary_table(THD* thd, TABLE *table, const char *db,
 			    const char *table_name)
 {
   char *key;
+  TABLE_SHARE *share= table->s;
+
   if (!(key=(char*) alloc_root(&table->mem_root,
 			       (uint) strlen(db)+
 			       (uint) strlen(table_name)+6+4)))
     return 1;				/* purecov: inspected */
-  table->key_length=(uint)
-    (strmov((table->real_name=strmov(table->table_cache_key=key,
-				     db)+1),
-	    table_name) - table->table_cache_key)+1;
-  int4store(key+table->key_length,thd->server_id);
-  table->key_length += 4;
-  int4store(key+table->key_length,thd->variables.pseudo_thread_id);
-  table->key_length += 4;
+  share->key_length= (uint)
+    (strmov((char*) (share->table_name= strmov(share->table_cache_key= key,
+                                               db)+1),
+	    table_name) - share->table_cache_key)+1;
+  share->db= share->table_cache_key;
+  int4store(key+share->key_length, thd->server_id);
+  share->key_length+= 4;
+  int4store(key+share->key_length, thd->variables.pseudo_thread_id);
+  share->key_length+= 4;
   return 0;
 }
 
@@ -766,15 +933,16 @@ static void relink_unused(TABLE *table)
 TABLE *unlink_open_table(THD *thd, TABLE *list, TABLE *find)
 {
   char key[MAX_DBKEY_LENGTH];
-  uint key_length=find->key_length;
+  uint key_length= find->s->key_length;
   TABLE *start=list,**prev,*next;
   prev= &start;
-  memcpy(key,find->table_cache_key,key_length);
+
+  memcpy(key, find->s->table_cache_key, key_length);
   for (; list ; list=next)
   {
     next=list->next;
-    if (list->key_length == key_length &&
-	!memcmp(list->table_cache_key,key,key_length))
+    if (list->s->key_length == key_length &&
+	!memcmp(list->s->table_cache_key, key, key_length))
     {
       if (thd->locked_tables)
 	mysql_lock_remove(thd, thd->locked_tables,list);
@@ -820,113 +988,254 @@ void wait_for_refresh(THD *thd)
 }
 
 
-TABLE *reopen_name_locked_table(THD* thd, TABLE_LIST* table_list)
+/*
+  Open table which is already name-locked by this thread.
+
+  SYNOPSIS
+    reopen_name_locked_table()
+      thd         Thread handle
+      table_list  TABLE_LIST object for table to be open, TABLE_LIST::table
+                  member should point to TABLE object which was used for
+                  name-locking.
+
+  NOTE
+    This function assumes that its caller already acquired LOCK_open mutex.
+
+  RETURN VALUE
+    FALSE - Success
+    TRUE  - Error
+*/
+
+bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list)
 {
+  TABLE *table= table_list->table;
+  TABLE_SHARE *share;
+  char *db= table_list->db;
+  char *table_name= table_list->table_name;
+  char key[MAX_DBKEY_LENGTH];
+  uint key_length;
+  TABLE orig_table;
   DBUG_ENTER("reopen_name_locked_table");
-  if (thd->killed)
-    DBUG_RETURN(0);
-  TABLE* table;
-  if (!(table = table_list->table))
-    DBUG_RETURN(0);
 
-  char* db = thd->db ? thd->db : table_list->db;
-  char* table_name = table_list->real_name;
-  char	key[MAX_DBKEY_LENGTH];
-  uint	key_length;
+  safe_mutex_assert_owner(&LOCK_open);
+
+  if (thd->killed || !table)
+    DBUG_RETURN(TRUE);
+
+  orig_table= *table;
   key_length=(uint) (strmov(strmov(key,db)+1,table_name)-key)+1;
 
-  pthread_mutex_lock(&LOCK_open);
-  if (open_unireg_entry(thd, table, db, table_name, table_name) ||
-      !(table->table_cache_key =memdup_root(&table->mem_root,(char*) key,
-					    key_length)))
+  if (open_unireg_entry(thd, table, db, table_name, table_name, 0,
+                        thd->mem_root) ||
+      !(table->s->table_cache_key= memdup_root(&table->mem_root, (char*) key,
+                                               key_length)))
   {
-    closefrm(table);
-    pthread_mutex_unlock(&LOCK_open);
-    DBUG_RETURN(0);
+    intern_close_table(table);
+    /*
+      If there was an error during opening of table (for example if it
+      does not exist) '*table' object can be wiped out. To be able
+      properly release name-lock in this case we should restore this
+      object to its original state.
+    */
+    *table= orig_table;
+    DBUG_RETURN(TRUE);
   }
 
-  table->key_length=key_length;
-  table->version=0;
-  table->flush_version=0;
+  share= table->s;
+  share->db= share->table_cache_key;
+  share->key_length=key_length;
+  share->version=0;
+  share->flush_version=0;
   table->in_use = thd;
   check_unused();
-  pthread_mutex_unlock(&LOCK_open);
   table->next = thd->open_tables;
   thd->open_tables = table;
   table->tablenr=thd->current_tablenr++;
   table->used_fields=0;
   table->const_table=0;
-  table->outer_join= table->null_row= table->maybe_null= table->force_index= 0;
+  table->null_row= table->maybe_null= table->force_index= 0;
   table->status=STATUS_NO_RECORD;
-  table->keys_in_use_for_query= table->keys_in_use;
-  table->used_keys= table->keys_for_keyread;
-  DBUG_RETURN(table);
+  table->keys_in_use_for_query= share->keys_in_use;
+  table->used_keys= share->keys_for_keyread;
+  DBUG_RETURN(FALSE);
 }
 
 
-/******************************************************************************
-** open a table
-** Uses a cache of open tables to find a table not in use.
-** If refresh is a NULL pointer, then the is no version number checking and
-** the table is not put in the thread-open-list
-** If the return value is NULL and refresh is set then one must close
-** all tables and retry the open
-******************************************************************************/
+/*
+  Open a table.
+
+  SYNOPSIS
+    open_table()
+    thd                 Thread context.
+    table_list          Open first table in list.
+    refresh      INOUT  Pointer to memory that will be set to 1 if
+                        we need to close all tables and reopen them.
+                        If this is a NULL pointer, then the table is not
+                        put in the thread-open-list.
+    flags               Bitmap of flags to modify how open works:
+                          MYSQL_LOCK_IGNORE_FLUSH - Open table even if
+                          someone has done a flush or namelock on it.
+                          No version number checking is done.
+
+  IMPLEMENTATION
+    Uses a cache of open tables to find a table not in use.
+
+  RETURN
+    NULL  Open failed.  If refresh is set then one should close
+          all other tables and retry the open.
+    #     Success. Pointer to TABLE object for open table.
+*/
 
 
-TABLE *open_table(THD *thd,const char *db,const char *table_name,
-		  const char *alias,bool *refresh)
+TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
+		  bool *refresh, uint flags)
 {
   reg1	TABLE *table;
   char	key[MAX_DBKEY_LENGTH];
   uint	key_length;
+  char	*alias= table_list->alias;
   HASH_SEARCH_STATE state;
   DBUG_ENTER("open_table");
 
   /* find a unused table in the open table cache */
   if (refresh)
     *refresh=0;
+
+  /* an open table operation needs a lot of the stack space */
+  if (check_stack_overrun(thd, STACK_MIN_SIZE_FOR_OPEN, (char *)&alias))
+    return 0;
+
   if (thd->killed)
     DBUG_RETURN(0);
-  key_length= (uint) (strmov(strmov(key,db)+1,table_name)-key)+1;
+  key_length= (uint) (strmov(strmov(key, table_list->db)+1,
+			     table_list->table_name)-key)+1;
   int4store(key + key_length, thd->server_id);
   int4store(key + key_length + 4, thd->variables.pseudo_thread_id);
 
-  for (table=thd->temporary_tables; table ; table=table->next)
+  if (!table_list->skip_temporary)
   {
-    if (table->key_length == key_length + TMP_TABLE_KEY_EXTRA &&
-	!memcmp(table->table_cache_key, key,
-                key_length + TMP_TABLE_KEY_EXTRA))
+    for (table= thd->temporary_tables; table ; table=table->next)
     {
-      if (table->query_id == thd->query_id)
+      if (table->s->key_length == key_length + TMP_TABLE_KEY_EXTRA &&
+	  !memcmp(table->s->table_cache_key, key,
+		  key_length + TMP_TABLE_KEY_EXTRA))
       {
-	my_printf_error(ER_CANT_REOPEN_TABLE,
-			ER(ER_CANT_REOPEN_TABLE),MYF(0),table->table_name);
-	DBUG_RETURN(0);
+	if (table->query_id == thd->query_id ||
+            thd->prelocked_mode && table->query_id)
+	{
+	  my_error(ER_CANT_REOPEN_TABLE, MYF(0), table->alias);
+	  DBUG_RETURN(0);
+	}
+	table->query_id= thd->query_id;
+	table->clear_query_id= 1;
+	thd->tmp_table_used= 1;
+        DBUG_PRINT("info",("Using temporary table"));
+        goto reset;
       }
-      table->query_id=thd->query_id;
-      table->clear_query_id=1;
-      thd->tmp_table_used= 1;
-      DBUG_PRINT("info",("Using temporary table"));
-      goto reset;
     }
   }
 
-  if (thd->locked_tables)
+  if (thd->locked_tables || thd->prelocked_mode)
   {						// Using table locks
+    TABLE *best_table= 0;
+    int best_distance= INT_MIN;
+    bool check_if_used= thd->prelocked_mode &&
+                        ((int) table_list->lock_type >=
+                         (int) TL_WRITE_ALLOW_WRITE);
     for (table=thd->open_tables; table ; table=table->next)
     {
-      if (table->key_length == key_length &&
-	  !memcmp(table->table_cache_key,key,key_length) &&
-	  !my_strcasecmp(system_charset_info, table->table_name, alias) &&
-	  table->query_id != thd->query_id)
+      if (table->s->key_length == key_length &&
+          !memcmp(table->s->table_cache_key, key, key_length))
       {
-	table->query_id=thd->query_id;
-        DBUG_PRINT("info",("Using locked table"));
-	goto reset;
+        if (check_if_used && table->query_id &&
+            table->query_id != thd->query_id)
+        {
+          /*
+            If we are in stored function or trigger we should ensure that
+            we won't change table that is already used by calling statement.
+            So if we are opening table for writing, we should check that it
+            is not already open by some calling stamement.
+          */
+          my_error(ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, MYF(0),
+                   table->s->table_name);
+          DBUG_RETURN(0);
+        }
+        if (!my_strcasecmp(system_charset_info, table->alias, alias) &&
+            table->query_id != thd->query_id && /* skip tables already used */
+            !(thd->prelocked_mode && table->query_id))
+        {
+          int distance= ((int) table->reginfo.lock_type -
+                         (int) table_list->lock_type);
+          /*
+            Find a table that either has the exact lock type requested,
+            or has the best suitable lock. In case there is no locked
+            table that has an equal or higher lock than requested,
+            we us the closest matching lock to be able to produce an error
+            message about wrong lock mode on the table. The best_table
+            is changed if bd < 0 <= d or bd < d < 0 or 0 <= d < bd.
+
+            distance <  0 - No suitable lock found
+            distance >  0 - we have lock mode higher then we require
+            distance == 0 - we have lock mode exactly which we need
+          */
+          if (best_distance < 0 && distance > best_distance ||
+              distance >= 0 && distance < best_distance)
+          {
+            best_distance= distance;
+            best_table= table;
+            if (best_distance == 0 && !check_if_used)
+            {
+              /*
+                If we have found perfect match and we don't need to check that
+                table is not used by one of calling statements (assuming that
+                we are inside of function or trigger) we can finish iterating
+                through open tables list.
+              */
+              break;
+            }
+          }
+        }
       }
     }
-    my_printf_error(ER_TABLE_NOT_LOCKED,ER(ER_TABLE_NOT_LOCKED),MYF(0),alias);
+    if (best_table)
+    {
+      table= best_table;
+      table->query_id= thd->query_id;
+      DBUG_PRINT("info",("Using locked table"));
+      goto reset;
+    }
+    /*
+      is it view?
+      (it is work around to allow to open view with locked tables,
+      real fix will be made after definition cache will be made)
+    */
+    {
+      char path[FN_REFLEN];
+      db_type not_used;
+      strxnmov(path, FN_REFLEN, mysql_data_home, "/", table_list->db, "/",
+               table_list->table_name, reg_ext, NullS);
+      (void) unpack_filename(path, path);
+      if (mysql_frm_type(thd, path, &not_used) == FRMTYPE_VIEW)
+      {
+        /*
+          Will not be used (because it's VIEW) but has to be passed.
+          Also we will not free it (because it is a stack variable).
+        */
+        TABLE tab;
+        table= &tab;
+        VOID(pthread_mutex_lock(&LOCK_open));
+        if (!open_unireg_entry(thd, table, table_list->db,
+                               table_list->table_name,
+                               alias, table_list, mem_root))
+        {
+          DBUG_ASSERT(table_list->view != 0);
+          VOID(pthread_mutex_unlock(&LOCK_open));
+          DBUG_RETURN(0); // VIEW
+        }
+        VOID(pthread_mutex_unlock(&LOCK_open));
+      }
+    }
+    my_error(ER_TABLE_NOT_LOCKED, MYF(0), alias);
     DBUG_RETURN(0);
   }
 
@@ -934,16 +1243,19 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
 
   if (!thd->open_tables)
     thd->version=refresh_version;
-  else if (thd->version != refresh_version && refresh)
+  else if ((thd->version != refresh_version) &&
+           ! (flags & MYSQL_LOCK_IGNORE_FLUSH))
   {
     /* Someone did a refresh while thread was opening tables */
-    *refresh=1;
+    if (refresh)
+      *refresh=1;
     VOID(pthread_mutex_unlock(&LOCK_open));
     DBUG_RETURN(0);
   }
 
   /* close handler tables which are marked for flush */
-  mysql_ha_flush(thd, (TABLE_LIST*) NULL, MYSQL_HA_REOPEN_ON_USAGE, TRUE);
+  if (thd->handler_tables)
+    mysql_ha_flush(thd, (TABLE_LIST*) NULL, MYSQL_HA_REOPEN_ON_USAGE, TRUE);
 
   for (table= (TABLE*) hash_first(&open_cache, (byte*) key, key_length,
                                   &state);
@@ -951,24 +1263,26 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
        table= (TABLE*) hash_next(&open_cache, (byte*) key, key_length,
                                  &state))
   {
-    if (table->version != refresh_version)
+    if (table->s->version != refresh_version)
     {
-      if (! refresh)
+      if (flags & MYSQL_LOCK_IGNORE_FLUSH)
       {
-        /* Ignore flush for now, but force close after usage. */
-        thd->version= table->version;
+        /* Force close at once after usage */
+        thd->version= table->s->version;
         continue;
       }
 
       /*
-      ** There is a refresh in progress for this table
-      ** Wait until the table is freed or the thread is killed.
+        There is a refresh in progress for this table
+        Wait until the table is freed or the thread is killed.
       */
       close_old_data_files(thd,thd->open_tables,0,0);
       if (table->in_use != thd)
 	wait_for_refresh(thd);
       else
+      {
 	VOID(pthread_mutex_unlock(&LOCK_open));
+      }
       if (refresh)
 	*refresh=1;
       DBUG_RETURN(0);
@@ -984,10 +1298,11 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
     }
     table->prev->next=table->next;		/* Remove from unused list */
     table->next->prev=table->prev;
-
+    table->in_use= thd;
   }
   else
   {
+    TABLE_SHARE *share;
     /* Free cache if too big */
     while (open_cache.records > table_cache_size && unused_tables)
       VOID(hash_delete(&open_cache,(byte*) unused_tables)); /* purecov: tested */
@@ -998,25 +1313,35 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
       VOID(pthread_mutex_unlock(&LOCK_open));
       DBUG_RETURN(NULL);
     }
-    if (open_unireg_entry(thd, table,db,table_name,alias) ||
-	!(table->table_cache_key=memdup_root(&table->mem_root,(char*) key,
-					     key_length)))
+    if (open_unireg_entry(thd, table, table_list->db, table_list->table_name,
+			  alias, table_list, mem_root) ||
+	(!table_list->view && 
+	 !(table->s->table_cache_key= memdup_root(&table->mem_root,
+                                                  (char*) key,
+                                                  key_length))))
     {
       table->next=table->prev=table;
       free_cache_entry(table);
       VOID(pthread_mutex_unlock(&LOCK_open));
       DBUG_RETURN(NULL);
     }
-    table->key_length=key_length;
-    table->version=refresh_version;
-    table->flush_version=flush_version;
+    if (table_list->view)
+    {
+      my_free((gptr)table, MYF(0));
+      VOID(pthread_mutex_unlock(&LOCK_open));
+      DBUG_RETURN(0); // VIEW
+    }
+    share= table->s;
+    share->db=            share->table_cache_key;
+    share->key_length=    key_length;
+    share->version=       refresh_version;
+    share->flush_version= flush_version;
     DBUG_PRINT("info", ("inserting table %p into the cache", table));
     VOID(my_hash_insert(&open_cache,(byte*) table));
   }
 
-  table->in_use=thd;
   check_unused();				// Debugging call
-       
+
   VOID(pthread_mutex_unlock(&LOCK_open));
   if (refresh)
   {
@@ -1026,55 +1351,32 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
   table->reginfo.lock_type=TL_READ;		/* Assume read */
 
  reset:
+  if (thd->lex->need_correct_ident())
+    table->alias_name_used= my_strcasecmp(table_alias_charset,
+                                          table->s->table_name, alias);
   /* Fix alias if table name changes */
-  if (strcmp(table->table_name,alias))
+  if (strcmp(table->alias, alias))
   {
     uint length=(uint) strlen(alias)+1;
-    table->table_name= (char*) my_realloc(table->table_name,length,
-					  MYF(MY_WME));
-    memcpy(table->table_name,alias,length);
-    for (uint i=0 ; i < table->fields ; i++)
-      table->field[i]->table_name=table->table_name;
-  }
-#if MYSQL_VERSION_ID < 40100
-  /*
-    If per-connection "new" variable (represented by variables.new_mode)
-    is set then we should pretend that the length of TIMESTAMP field is 19.
-    The cheapest (from perfomance viewpoint) way to achieve that is to set
-    field_length of all Field_timestamp objects in a table after opening
-    it (to 19 if new_mode is true or to original field length otherwise).
-    We save value of new_mode variable in TABLE::timestamp_mode to
-    not perform this setup if new_mode value is the same between sequential
-    table opens.
-  */
-  my_bool new_mode= thd->variables.new_mode;
-  if (table->timestamp_mode != new_mode)
-  {
-    for (uint i=0 ; i < table->fields ; i++)
-    {
-      Field *field= table->field[i];
-
-      if (field->type() == FIELD_TYPE_TIMESTAMP)
-        field->field_length= new_mode ? 19 :
-                             ((Field_timestamp *)(field))->orig_field_length;
-    }
-    table->timestamp_mode= new_mode;
+    table->alias= (char*) my_realloc((char*) table->alias, length,
+                                     MYF(MY_WME));
+    memcpy((char*) table->alias, alias, length);
   }
-#endif
   /* These variables are also set in reopen_table() */
   table->tablenr=thd->current_tablenr++;
   table->used_fields=0;
   table->const_table=0;
-  table->outer_join= table->null_row= table->maybe_null= table->force_index= 0;
+  table->null_row= table->maybe_null= table->force_index= 0;
   table->status=STATUS_NO_RECORD;
-  table->keys_in_use_for_query= table->keys_in_use;
-  table->used_keys= table->keys_for_keyread;
-  table->file->ft_handler=0;
-  table->fulltext_searched=0;
+  table->keys_in_use_for_query= table->s->keys_in_use;
+  table->insert_values= 0;
+  table->used_keys= table->s->keys_for_keyread;
+  table->fulltext_searched= 0;
+  table->file->ft_handler= 0;
   if (table->timestamp_field)
     table->timestamp_field_type= table->timestamp_field->get_auto_set_type();
+  table_list->updatable= 1; // It is not derived table nor non-updatable VIEW
   DBUG_ASSERT(table->key_read == 0);
-  DBUG_ASSERT(table->insert_values == 0);
   DBUG_RETURN(table);
 }
 
@@ -1086,8 +1388,8 @@ TABLE *find_locked_table(THD *thd, const char *db,const char *table_name)
 
   for (TABLE *table=thd->open_tables; table ; table=table->next)
   {
-    if (table->key_length == key_length &&
-	!memcmp(table->table_cache_key,key,key_length))
+    if (table->s->key_length == key_length &&
+	!memcmp(table->s->table_cache_key,key,key_length))
       return table;
   }
   return(0);
@@ -1114,9 +1416,9 @@ TABLE *find_locked_table(THD *thd, const char *db,const char *table_name)
 bool reopen_table(TABLE *table,bool locked)
 {
   TABLE tmp;
-  char *db=table->table_cache_key;
-  char *table_name=table->real_name;
-  bool error=1;
+  char *db= table->s->table_cache_key;
+  const char *table_name= table->s->table_name;
+  bool error= 1;
   Field **field;
   uint key,part;
   DBUG_ENTER("reopen_table");
@@ -1124,64 +1426,70 @@ bool reopen_table(TABLE *table,bool locked)
 #ifdef EXTRA_DEBUG
   if (table->db_stat)
     sql_print_error("Table %s had a open data handler in reopen_table",
-		    table->table_name);
+		    table->alias);
 #endif
   if (!locked)
     VOID(pthread_mutex_lock(&LOCK_open));
   safe_mutex_assert_owner(&LOCK_open);
 
-  if (open_unireg_entry(current_thd,&tmp,db,table_name,table->table_name))
+  if (open_unireg_entry(table->in_use, &tmp, db, table_name,
+			table->alias, 0, table->in_use->mem_root))
     goto end;
   free_io_cache(table);
 
-  if (!(tmp.table_cache_key= memdup_root(&tmp.mem_root,db,
-					 table->key_length)))
+  if (!(tmp.s->table_cache_key= memdup_root(&tmp.mem_root,db,
+                                            table->s->key_length)))
   {
+    delete tmp.triggers;
     closefrm(&tmp);				// End of memory
     goto end;
   }
+  tmp.s->db= tmp.s->table_cache_key;
 
   /* This list copies variables set by open_table */
   tmp.tablenr=		table->tablenr;
   tmp.used_fields=	table->used_fields;
   tmp.const_table=	table->const_table;
-  tmp.outer_join=	table->outer_join;
   tmp.null_row=		table->null_row;
   tmp.maybe_null=	table->maybe_null;
   tmp.status=		table->status;
-  tmp.keys_in_use_for_query= tmp.keys_in_use;
-  tmp.used_keys= 	tmp.keys_for_keyread;
-  tmp.force_index=	tmp.force_index;
+  tmp.keys_in_use_for_query= tmp.s->keys_in_use;
+  tmp.used_keys= 	tmp.s->keys_for_keyread;
 
   /* Get state */
-  tmp.key_length=	table->key_length;
+  tmp.s->key_length=	table->s->key_length;
   tmp.in_use=    	table->in_use;
   tmp.reginfo.lock_type=table->reginfo.lock_type;
-  tmp.version=		refresh_version;
-  tmp.tmp_table=	table->tmp_table;
+  tmp.s->version=	refresh_version;
+  tmp.s->tmp_table=	table->s->tmp_table;
   tmp.grant=		table->grant;
 
   /* Replace table in open list */
   tmp.next=		table->next;
   tmp.prev=		table->prev;
 
+  delete table->triggers;
   if (table->file)
     VOID(closefrm(table));		// close file, free everything
 
-  *table=tmp;
+  *table= tmp;
+  table->s= &table->share_not_to_be_used;
   table->file->change_table_ptr(table);
 
-  DBUG_ASSERT(table->table_name);
+  DBUG_ASSERT(table->alias != 0);
   for (field=table->field ; *field ; field++)
   {
     (*field)->table= (*field)->orig_table= table;
-    (*field)->table_name=table->table_name;
+    (*field)->table_name= &table->alias;
   }
-  for (key=0 ; key < table->keys ; key++)
+  for (key=0 ; key < table->s->keys ; key++)
   {
     for (part=0 ; part < table->key_info[key].usable_key_parts ; part++)
       table->key_info[key].key_part[part].field->table= table;
   }
+  if (table->triggers)
+    table->triggers->set_table(table);
+
   VOID(pthread_cond_broadcast(&COND_refresh));
   error=0;
 
@@ -1203,8 +1511,8 @@ bool close_data_tables(THD *thd,const char *db, const char *table_name)
   TABLE *table;
   for (table=thd->open_tables; table ; table=table->next)
   {
-    if (!strcmp(table->real_name,table_name) &&
-	!strcmp(table->table_cache_key,db))
+    if (!strcmp(table->s->table_name, table_name) &&
+	!strcmp(table->s->db, db))
     {
       mysql_lock_remove(thd, thd->locked_tables,table);
       table->file->close();
@@ -1230,7 +1538,7 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
 
   TABLE *table,*next,**prev;
   TABLE **tables,**tables_ptr;			// For locks
-  bool error=0;
+  bool error=0, not_used;
   if (get_locks)
   {
     /* The ptr is checked later */
@@ -1249,7 +1557,7 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
     next=table->next;
     if (!tables || (!db_stat && reopen_table(table,1)))
     {
-      my_error(ER_CANT_REOPEN_TABLE,MYF(0),table->table_name);
+      my_error(ER_CANT_REOPEN_TABLE, MYF(0), table->alias);
       VOID(hash_delete(&open_cache,(byte*) table));
       error=1;
     }
@@ -1261,7 +1569,7 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
 	*tables_ptr++= table;			// need new lock on this
       if (in_refresh)
       {
-	table->version=0;
+	table->s->version=0;
 	table->locked_by_flush=0;
       }
     }
@@ -1271,7 +1579,8 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
     MYSQL_LOCK *lock;
     /* We should always get these locks */
     thd->some_tables_deleted=0;
-    if ((lock= mysql_lock_tables(thd, tables, (uint) (tables_ptr-tables), 0)))
+    if ((lock= mysql_lock_tables(thd, tables, (uint) (tables_ptr - tables),
+                                 0, &not_used)))
     {
       thd->locked_tables=mysql_lock_merge(thd->locked_tables,lock);
     }
@@ -1300,11 +1609,11 @@ void close_old_data_files(THD *thd, TABLE *table, bool abort_locks,
   bool found=send_refresh;
   for (; table ; table=table->next)
   {
-    if (table->version != refresh_version)
+    if (table->s->version != refresh_version)
     {
       found=1;
       if (!abort_locks)				// If not from flush tables
-	table->version = refresh_version;	// Let other threads use table
+	table->s->version= refresh_version;	// Let other threads use table
       if (table->db_stat)
       {
 	if (abort_locks)
@@ -1334,18 +1643,18 @@ bool table_is_used(TABLE *table, bool wait_for_name_lock)
 {
   do
   {
+    char *key= table->s->table_cache_key;
+    uint key_length= table->s->key_length;
     HASH_SEARCH_STATE state;
-    char *key= table->table_cache_key;
-    uint key_length=table->key_length;
     for (TABLE *search= (TABLE*) hash_first(&open_cache, (byte*) key,
-                                            key_length, &state);
+                                             key_length, &state);
 	 search ;
          search= (TABLE*) hash_next(&open_cache, (byte*) key,
                                     key_length, &state))
     {
       if (search->locked_by_flush ||
 	  search->locked_by_name && wait_for_name_lock ||
-	  search->db_stat && search->version < refresh_version)
+	  search->db_stat && search->s->version < refresh_version)
 	return 1;				// Table is used
     }
   } while ((table=table->next));
@@ -1393,11 +1702,11 @@ bool drop_locked_tables(THD *thd,const char *db, const char *table_name)
   TABLE *table,*next,**prev;
   bool found=0;
   prev= &thd->open_tables;
-  for (table=thd->open_tables; table ; table=next)
+  for (table= thd->open_tables; table ; table=next)
   {
     next=table->next;
-    if (!strcmp(table->real_name,table_name) &&
-	!strcmp(table->table_cache_key,db))
+    if (!strcmp(table->s->table_name, table_name) &&
+	!strcmp(table->s->db, db))
     {
       mysql_lock_remove(thd, thd->locked_tables,table);
       VOID(hash_delete(&open_cache,(byte*) table));
@@ -1432,8 +1741,8 @@ void abort_locked_tables(THD *thd,const char *db, const char *table_name)
   TABLE *table;
   for (table= thd->open_tables; table ; table= table->next)
   {
-    if (!strcmp(table->real_name,table_name) &&
-	!strcmp(table->table_cache_key,db))
+    if (!strcmp(table->s->table_name,table_name) &&
+	!strcmp(table->s->db, db))
     {
       mysql_lock_abort(thd,table);
       break;
@@ -1452,6 +1761,8 @@ void abort_locked_tables(THD *thd,const char *db, const char *table_name)
     db			Database name
     name		Table name
     alias		Alias name
+    table_desc		TABLE_LIST descriptor (used with views)
+    mem_root		temporary mem_root for parsing
 
   NOTES
    Extra argument for open is taken from thd->open_options
@@ -1460,9 +1771,9 @@ void abort_locked_tables(THD *thd,const char *db, const char *table_name)
     0	ok
     #	Error
 */
-
 static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
-			     const char *name, const char *alias)
+			     const char *name, const char *alias,
+			     TABLE_LIST *table_desc, MEM_ROOT *mem_root)
 {
   char path[FN_REFLEN];
   int error;
@@ -1470,19 +1781,28 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
   DBUG_ENTER("open_unireg_entry");
 
   strxmov(path, mysql_data_home, "/", db, "/", name, NullS);
-  while (openfrm(path,alias,
-	       (uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE | HA_GET_INDEX |
-		       HA_TRY_READ_ONLY),
-	       READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD,
-	      thd->open_options, entry))
+  while ((error= openfrm(thd, path, alias,
+		         (uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE |
+			         HA_GET_INDEX | HA_TRY_READ_ONLY |
+                                 NO_ERR_ON_NEW_FRM),
+		      READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD,
+		      thd->open_options, entry)) &&
+      (error != 5 ||
+       (fn_format(path, path, 0, reg_ext, MY_UNPACK_FILENAME),
+        open_new_frm(thd, path, alias, db, name,
+                     (uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE |
+                             HA_GET_INDEX | HA_TRY_READ_ONLY),
+                     READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD,
+                     thd->open_options, entry, table_desc, mem_root))))
+
   {
-    if (!entry->crashed)
+    if (!entry->s || !entry->s->crashed)
     {
       /*
-       Frm file could not be found on disk
-       Since it does not exist, no one can be using it
-       LOCK_open has been locked to protect from someone else
-       trying to discover the table at the same time.
+        Frm file could not be found on disk
+        Since it does not exist, no one can be using it
+        LOCK_open has been locked to protect from someone else
+        trying to discover the table at the same time.
       */
       if (discover_retry_count++ != 0)
         goto err;
@@ -1490,7 +1810,7 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
       {
         /* Give right error message */
         thd->clear_error();
-        DBUG_PRINT("error", ("Dicovery of %s/%s failed", db, name));
+        DBUG_PRINT("error", ("Discovery of %s/%s failed", db, name));
         my_printf_error(ER_UNKNOWN_ERROR,
                         "Failed to open '%-.64s', error while "
                         "unpacking from engine",
@@ -1499,7 +1819,8 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
         goto err;
       }
 
-      thd->clear_error(); // Clear error message
+      mysql_reset_errors(thd, 1);    // Clear warnings
+      thd->clear_error();            // Clear error message
       continue;
     }
 
@@ -1507,7 +1828,7 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
     TABLE_LIST table_list;
     bzero((char*) &table_list, sizeof(table_list)); // just for safe
     table_list.db=(char*) db;
-    table_list.real_name=(char*) name;
+    table_list.table_name=(char*) name;
 
     safe_mutex_assert_owner(&LOCK_open);
 
@@ -1526,7 +1847,7 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
     pthread_mutex_unlock(&LOCK_open);
     thd->clear_error();				// Clear error message
     error= 0;
-    if (openfrm(path,alias,
+    if (openfrm(thd, path, alias,
 		(uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE | HA_GET_INDEX |
 			 HA_TRY_READ_ONLY),
 		READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD,
@@ -1551,6 +1872,22 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
       goto err;
     break;
   }
+
+  if (error == 5)
+    DBUG_RETURN(0);	// we have just opened VIEW
+
+  /*
+    We can't mark all tables in 'mysql' database as system since we don't
+    allow to lock such tables for writing with any other tables (even with
+    other system tables) and some privilege tables need this.
+  */
+  if (!my_strcasecmp(system_charset_info, db, "mysql") &&
+      !my_strcasecmp(system_charset_info, name, "proc"))
+    entry->s->system_table= 1;
+
+  if (Table_triggers_list::check_n_load(thd, db, name, entry, 0))
+    goto err;
+
   /*
     If we are here, there was no fatal error (but error may be still
     unitialized).
@@ -1579,6 +1916,7 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
         */
         sql_print_error("When opening HEAP table, could not allocate \
 memory to write 'DELETE FROM `%s`.`%s`' to the binary log",db,name);
+        delete entry->triggers;
         if (entry->file)
           closefrm(entry);
         goto err;
@@ -1587,91 +1925,251 @@ memory to write 'DELETE FROM `%s`.`%s`' to the binary log",db,name);
   }
   DBUG_RETURN(0);
 err:
+  /* Hide "Table doesn't exist" errors if table belong to view */
+  if (thd->net.last_errno == ER_NO_SUCH_TABLE &&
+      table_desc && table_desc->belong_to_view)
+  {
+    TABLE_LIST *view= table_desc->belong_to_view;
+    thd->clear_error();
+    my_error(ER_VIEW_INVALID, MYF(0), view->view_db.str, view->view_name.str);
+  }
   DBUG_RETURN(1);
 }
 
+
 /*
   Open all tables in list
 
   SYNOPSIS
     open_tables()
     thd - thread handler
-    start - list of tables
+    start - list of tables in/out
     counter - number of opened tables will be return using this parameter
+    flags   - bitmap of flags to modify how the tables will be open:
+              MYSQL_LOCK_IGNORE_FLUSH - open table even if someone has
+              done a flush or namelock on it.
+
+  NOTE
+    Unless we are already in prelocked mode, this function will also precache
+    all SP/SFs explicitly or implicitly (via views and triggers) used by the
+    query and add tables needed for their execution to table list. If resulting
+    tables list will be non empty it will mark query as requiring precaching.
+    Prelocked mode will be enabled for such query during lock_tables() call.
+
+    If query for which we are opening tables is already marked as requiring
+    prelocking it won't do such precaching and will simply reuse table list
+    which is already built.
 
   RETURN
     0  - OK
     -1 - error
 */
 
-int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
+int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
 {
   TABLE_LIST *tables;
   bool refresh;
   int result=0;
+  MEM_ROOT new_frm_mem;
+  /* Also used for indicating that prelocking is need */
+  TABLE_LIST **query_tables_last_own;
   DBUG_ENTER("open_tables");
+  /*
+    temporary mem_root for new .frm parsing.
+    TODO: variables for size
+  */
+  init_alloc_root(&new_frm_mem, 8024, 8024);
 
   thd->current_tablenr= 0;
  restart:
   *counter= 0;
+  query_tables_last_own= 0;
   thd->proc_info="Opening tables";
-  for (tables=start ; tables ; tables=tables->next)
+
+  /*
+    If we are not already executing prelocked statement and don't have
+    statement for which table list for prelocking is already built, let
+    us cache routines and try to build such table list.
+
+    NOTE: We will mark statement as requiring prelocking only if we will
+    have non empty table list. But this does not guarantee that in prelocked
+    mode we will have some locked tables, because queries which use only
+    derived/information schema tables and views possible. Thus "counter"
+    may be still zero for prelocked statement...
+  */
+
+  if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
+      thd->lex->sroutines_list.elements)
+  {
+    bool first_no_prelocking, need_prelocking, tabs_changed;
+    TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
+
+    DBUG_ASSERT(thd->lex->query_tables == *start);
+    sp_get_prelocking_info(thd, &need_prelocking, &first_no_prelocking);
+
+    if (sp_cache_routines_and_add_tables(thd, thd->lex,
+                                         first_no_prelocking,
+                                         &tabs_changed))
+    {
+      /*
+        Serious error during reading stored routines from mysql.proc table.
+        Something's wrong with the table or its contents, and an error has
+        been emitted; we must abort.
+      */
+      result= -1;
+      goto err;
+    }
+    else if ((tabs_changed || *start) && need_prelocking)
+    {
+      query_tables_last_own= save_query_tables_last;
+      *start= thd->lex->query_tables;
+    }
+  }
+
+  for (tables= *start; tables ;tables= tables->next_global)
   {
     /*
       Ignore placeholders for derived tables. After derived tables
       processing, link to created temporary table will be put here.
+      If this is derived table for view then we still want to process
+      routines used by this view.
      */
     if (tables->derived)
+    {
+      if (tables->view)
+        goto process_view_routines;
       continue;
+    }
+    if (tables->schema_table)
+    {
+      if (!mysql_schema_table(thd, thd->lex, tables))
+        continue;
+      DBUG_RETURN(-1);
+    }
     (*counter)++;
+    
     if (!tables->table &&
-	!(tables->table= open_table(thd,
-				    tables->db,
-				    tables->real_name,
-				    tables->alias, &refresh)))
+	!(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, flags)))
     {
+      free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
+
+      if (tables->view)
+      {
+        /* VIEW placeholder */
+	(*counter)--;
+
+        /*
+          tables->next_global list consists of two parts:
+          1) Query tables and underlying tables of views.
+          2) Tables used by all stored routines that this statement invokes on
+             execution.
+          We need to know where the bound between these two parts is. If we've
+          just opened a view, which was the last table in part #1, and it
+          has added its base tables after itself, adjust the boundary pointer
+          accordingly.
+        */
+        if (query_tables_last_own == &(tables->next_global) &&
+            tables->view->query_tables)
+          query_tables_last_own= tables->view->query_tables_last;
+        /*
+          Let us free memory used by 'sroutines' hash here since we never
+          call destructor for this LEX.
+        */
+        hash_free(&tables->view->sroutines);
+	goto process_view_routines;
+      }
+
       if (refresh)				// Refresh in progress
       {
-	/* close all 'old' tables used by this thread */
-	pthread_mutex_lock(&LOCK_open);
-	// if query_id is not reset, we will get an error
-	// re-opening a temp table
-	thd->version=refresh_version;
-	TABLE **prev_table= &thd->open_tables;
-	bool found=0;
-	for (TABLE_LIST *tmp=start ; tmp ; tmp=tmp->next)
-	{
-	  /* Close normal (not temporary) changed tables */
-	  if (tmp->table && ! tmp->table->tmp_table)
-	  {
-	    if (tmp->table->version != refresh_version ||
-		! tmp->table->db_stat)
-	    {
-	      VOID(hash_delete(&open_cache,(byte*) tmp->table));
-	      tmp->table=0;
-	      found=1;
-	    }
-	    else
-	    {
-	      *prev_table= tmp->table;		// Relink open list
-	      prev_table= &tmp->table->next;
-	    }
-	  }
-	}
-	*prev_table=0;
-	pthread_mutex_unlock(&LOCK_open);
-	if (found)
-	  VOID(pthread_cond_broadcast(&COND_refresh)); // Signal to refresh
+        /*
+          We have met name-locked or old version of table. Now we have
+          to close all tables which are not up to date. We also have to
+          throw away set of prelocked tables (and thus close tables from
+          this set that were open by now) since it possible that one of
+          tables which determined its content was changed.
+
+          Instead of implementing complex/non-robust logic mentioned
+          above we simply close and then reopen all tables.
+
+          In order to prepare for recalculation of set of prelocked tables
+          we pretend that we have finished calculation which we were doing
+          currently.
+        */
+        if (query_tables_last_own)
+          thd->lex->mark_as_requiring_prelocking(query_tables_last_own);
+        close_tables_for_reopen(thd, start);
 	goto restart;
       }
       result= -1;				// Fatal error
       break;
     }
+    else
+    {
+      /*
+        If we are not already in prelocked mode and extended table list is not
+        yet built and we have trigger for table being opened then we should
+        cache all routines used by its triggers and add their tables to
+        prelocking list.
+        If we lock table for reading we won't update it so there is no need to
+        process its triggers since they never will be activated.
+      */
+      if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
+          tables->table->triggers &&
+          tables->lock_type >= TL_WRITE_ALLOW_WRITE)
+      {
+        if (!query_tables_last_own)
+          query_tables_last_own= thd->lex->query_tables_last;
+        if (sp_cache_routines_and_add_tables_for_triggers(thd, thd->lex,
+                                                          tables))
+        {
+          /*
+            Serious error during reading stored routines from mysql.proc table.
+            Something's wrong with the table or its contents, and an error has
+            been emitted; we must abort.
+          */
+          result= -1;
+          goto err;
+        }
+      }
+      free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
+    }
+
     if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables)
       tables->table->reginfo.lock_type=tables->lock_type;
     tables->table->grant= tables->grant;
+
+process_view_routines:
+    /*
+      Again we may need cache all routines used by this view and add
+      tables used by them to table list.
+    */
+    if (tables->view && !thd->prelocked_mode &&
+        !thd->lex->requires_prelocking() &&
+        tables->view->sroutines_list.elements)
+    {
+      /* We have at least one table in TL here. */
+      if (!query_tables_last_own)
+        query_tables_last_own= thd->lex->query_tables_last;
+      if (sp_cache_routines_and_add_tables_for_view(thd, thd->lex, tables))
+      {
+        /*
+          Serious error during reading stored routines from mysql.proc table.
+          Something's wrong with the table or its contents, and an error has
+          been emitted; we must abort.
+        */
+        result= -1;
+        goto err;
+      }
+    }
   }
+
+ err:
   thd->proc_info=0;
+  free_root(&new_frm_mem, MYF(0));              // Free pre-alloced block
+
+  if (query_tables_last_own)
+    thd->lex->mark_as_requiring_prelocking(query_tables_last_own);
+
   DBUG_RETURN(result);
 }
 
@@ -1699,12 +2197,10 @@ static bool check_lock_and_start_stmt(THD *thd, TABLE *table,
   if ((int) lock_type >= (int) TL_WRITE_ALLOW_READ &&
       (int) table->reginfo.lock_type < (int) TL_WRITE_ALLOW_READ)
   {
-    my_printf_error(ER_TABLE_NOT_LOCKED_FOR_WRITE,
-		    ER(ER_TABLE_NOT_LOCKED_FOR_WRITE),
-		    MYF(0),table->table_name);
+    my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0),table->alias);
     DBUG_RETURN(1);
   }
-  if ((error=table->file->start_stmt(thd)))
+  if ((error=table->file->start_stmt(thd, lock_type)))
   {
     table->file->print_error(error,MYF(0));
     DBUG_RETURN(1);
@@ -1722,6 +2218,11 @@ static bool check_lock_and_start_stmt(THD *thd, TABLE *table,
     table_list		Table to open is first table in this list
     lock_type		Lock to use for open
 
+  NOTE
+    This function don't do anything like SP/SF/views/triggers analysis done
+    in open_tables(). It is intended for opening of only one concrete table.
+    And used only in special contexts.
+
   RETURN VALUES
     table		Opened table
     0			Error
@@ -1739,9 +2240,11 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
 
   thd->proc_info="Opening table";
   thd->current_tablenr= 0;
-  while (!(table=open_table(thd,table_list->db,
-			    table_list->real_name,table_list->alias,
-			    &refresh)) && refresh) ;
+  /* open_ltable can be used only for BASIC TABLEs */
+  table_list->required_type= FRMTYPE_TABLE;
+  while (!(table= open_table(thd, table_list, thd->mem_root, &refresh, 0)) &&
+         refresh)
+    ;
 
   if (table)
   {
@@ -1764,7 +2267,8 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
     {
       DBUG_ASSERT(thd->lock == 0);	// You must lock everything at once
       if ((table->reginfo.lock_type= lock_type) != TL_UNLOCK)
-	if (! (thd->lock= mysql_lock_tables(thd, &table_list->table, 1, 0)))
+	if (! (thd->lock= mysql_lock_tables(thd, &table_list->table, 1, 0,
+                                            &refresh)))
 	  table= 0;
     }
   }
@@ -1787,15 +2291,25 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
     -1 - error
 
   NOTE
-    The lock will automaticly be freed by close_thread_tables()
+    The lock will automaticaly be freed by close_thread_tables()
 */
 
 int simple_open_n_lock_tables(THD *thd, TABLE_LIST *tables)
 {
-  DBUG_ENTER("simple_open_n_lock_tables");
   uint counter;
-  if (open_tables(thd, tables, &counter) || lock_tables(thd, tables, counter))
-    DBUG_RETURN(-1);				/* purecov: inspected */
+  bool need_reopen;
+  DBUG_ENTER("simple_open_n_lock_tables");
+
+  for ( ; ; ) 
+  {
+    if (open_tables(thd, &tables, &counter, 0))
+      DBUG_RETURN(-1);
+    if (!lock_tables(thd, tables, counter, &need_reopen))
+      break;
+    if (!need_reopen)
+      DBUG_RETURN(-1);
+    close_tables_for_reopen(thd, &tables);
+  }
   DBUG_RETURN(0);
 }
 
@@ -1810,21 +2324,34 @@ int simple_open_n_lock_tables(THD *thd, TABLE_LIST *tables)
     tables	- list of tables for open&locking
 
   RETURN
-    0  - ok
-    -1 - error
+    FALSE - ok
+    TRUE  - error
 
   NOTE
-    The lock will automaticly be freed by close_thread_tables()
+    The lock will automaticaly be freed by close_thread_tables()
 */
 
-int open_and_lock_tables(THD *thd, TABLE_LIST *tables)
+bool open_and_lock_tables(THD *thd, TABLE_LIST *tables)
 {
-  DBUG_ENTER("open_and_lock_tables");
   uint counter;
-  if (open_tables(thd, tables, &counter) || lock_tables(thd, tables, counter))
-    DBUG_RETURN(-1);				/* purecov: inspected */
-  relink_tables_for_derived(thd);
-  DBUG_RETURN(mysql_handle_derived(thd->lex));
+  bool need_reopen;
+  DBUG_ENTER("open_and_lock_tables");
+
+  for ( ; ; ) 
+  {
+    if (open_tables(thd, &tables, &counter, 0))
+      DBUG_RETURN(-1);
+    if (!lock_tables(thd, tables, counter, &need_reopen))
+      break;
+    if (!need_reopen)
+      DBUG_RETURN(-1);
+    close_tables_for_reopen(thd, &tables);
+  }
+  if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+      (thd->fill_derived_tables() &&
+       mysql_handle_derived(thd->lex, &mysql_derived_filling)))
+    DBUG_RETURN(TRUE); /* purecov: inspected */
+  DBUG_RETURN(0);
 }
 
 
@@ -1835,6 +2362,9 @@ int open_and_lock_tables(THD *thd, TABLE_LIST *tables)
     open_normal_and_derived_tables
     thd		- thread handler
     tables	- list of tables for open
+    flags       - bitmap of flags to modify how the tables will be open:
+                  MYSQL_LOCK_IGNORE_FLUSH - open table even if someone has
+                  done a flush or namelock on it.
 
   RETURN
     FALSE - ok
@@ -1845,36 +2375,36 @@ int open_and_lock_tables(THD *thd, TABLE_LIST *tables)
     data from the tables.
 */
 
-int open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables)
+bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags)
 {
   uint counter;
   DBUG_ENTER("open_normal_and_derived_tables");
-  if (open_tables(thd, tables, &counter))
-    DBUG_RETURN(-1);				/* purecov: inspected */
-  relink_tables_for_derived(thd);
-  DBUG_RETURN(mysql_handle_derived(thd->lex));
+  DBUG_ASSERT(!thd->fill_derived_tables());
+  if (open_tables(thd, &tables, &counter, flags) ||
+      mysql_handle_derived(thd->lex, &mysql_derived_prepare))
+    DBUG_RETURN(TRUE); /* purecov: inspected */
+  DBUG_RETURN(0);
 }
 
 
 /*
-  Let us propagate pointers to open tables from global table list
-  to table lists in particular selects if needed.
+  Mark all real tables in the list as free for reuse.
+
+  SYNOPSIS
+    mark_real_tables_as_free_for_reuse()
+      thd   - thread context
+      table - head of the list of tables
+
+  DESCRIPTION
+    Marks all real tables in the list (i.e. not views, derived
+    or schema tables) as free for reuse.
 */
 
-void relink_tables_for_derived(THD *thd)
+static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table)
 {
-  if (thd->lex->all_selects_list->next_select_in_list() ||
-      thd->lex->time_zone_tables_used)
-  {
-    for (SELECT_LEX *sl= thd->lex->all_selects_list;
-	 sl;
-	 sl= sl->next_select_in_list())
-      for (TABLE_LIST *cursor= (TABLE_LIST *) sl->table_list.first;
-           cursor;
-           cursor=cursor->next)
-        if (cursor->table_list)
-          cursor->table= cursor->table_list->table;
-  }
+  for (; table; table= table->next_global)
+    if (!table->placeholder() && !table->schema_table)
+      table->table->query_id= 0;
 }
 
 
@@ -1885,51 +2415,183 @@ void relink_tables_for_derived(THD *thd)
     lock_tables()
     thd			Thread handler
     tables		Tables to lock
-    count		umber of opened tables
+    count		Number of opened tables
+    need_reopen         Out parameter which if TRUE indicates that some
+                        tables were dropped or altered during this call
+                        and therefore invoker should reopen tables and
+                        try to lock them once again (in this case
+                        lock_tables() will also return error).
 
   NOTES
     You can't call lock_tables twice, as this would break the dead-lock-free
     handling thr_lock gives us.  You most always get all needed locks at
     once.
 
+    If query for which we are calling this function marked as requring
+    prelocking, this function will do implicit LOCK TABLES and change
+    thd::prelocked_mode accordingly.
+
   RETURN VALUES
    0	ok
    -1	Error
 */
 
-int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
+int lock_tables(THD *thd, TABLE_LIST *tables, uint count, bool *need_reopen)
 {
   TABLE_LIST *table;
+
+  DBUG_ENTER("lock_tables");
+  /*
+    We can't meet statement requiring prelocking if we already
+    in prelocked mode.
+  */
+  DBUG_ASSERT(!thd->prelocked_mode || !thd->lex->requires_prelocking());
+  /*
+    If statement requires prelocking then it has non-empty table list.
+    So it is safe to shortcut.
+  */
+  DBUG_ASSERT(!thd->lex->requires_prelocking() || tables);
+
+  *need_reopen= FALSE;
+
   if (!tables)
-    return 0;
+    DBUG_RETURN(0);
 
-  if (!thd->locked_tables)
+  /*
+    We need this extra check for thd->prelocked_mode because we want to avoid
+    attempts to lock tables in substatements. Checking for thd->locked_tables
+    is not enough in some situations. For example for SP containing
+    "drop table t3; create temporary t3 ..; insert into t3 ...;"
+    thd->locked_tables may be 0 after drop tables, and without this extra
+    check insert will try to lock temporary table t3, that will lead
+    to memory leak...
+  */
+  if (!thd->locked_tables && !thd->prelocked_mode)
   {
     DBUG_ASSERT(thd->lock == 0);	// You must lock everything at once
     TABLE **start,**ptr;
-    if (!(ptr=start=(TABLE**) sql_alloc(sizeof(TABLE*)*count)))
-      return -1;
-    for (table = tables ; table ; table=table->next)
+
+    if (!(ptr=start=(TABLE**) thd->alloc(sizeof(TABLE*)*count)))
+      DBUG_RETURN(-1);
+    for (table= tables; table; table= table->next_global)
     {
-      if (!table->derived)
+      if (!table->placeholder() && !table->schema_table)
 	*(ptr++)= table->table;
     }
-    if (! (thd->lock= mysql_lock_tables(thd, start, (uint) (ptr - start), 0)))
-      return -1;				/* purecov: inspected */
+
+    /* We have to emulate LOCK TABLES if we are statement needs prelocking. */
+    if (thd->lex->requires_prelocking())
+    {
+      thd->in_lock_tables=1;
+      thd->options|= OPTION_TABLE_LOCK;
+    }
+
+    if (! (thd->lock= mysql_lock_tables(thd, start, (uint) (ptr - start),
+                                        MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN,
+                                        need_reopen)))
+    {
+      if (thd->lex->requires_prelocking())
+      {
+        thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+        thd->in_lock_tables=0;
+      }
+      DBUG_RETURN(-1);
+    }
+    if (thd->lex->requires_prelocking() &&
+        thd->lex->sql_command != SQLCOM_LOCK_TABLES)
+    {
+      TABLE_LIST *first_not_own= thd->lex->first_not_own_table();
+      /*
+        We just have done implicit LOCK TABLES, and now we have
+        to emulate first open_and_lock_tables() after it.
+
+        Note that "LOCK TABLES" can also be marked as requiring prelocking
+        (e.g. if one locks view which uses functions). We should not emulate
+        such open_and_lock_tables() in this case. We also should not set
+        THD::prelocked_mode or first close_thread_tables() call will do
+        "UNLOCK TABLES".
+      */
+      thd->locked_tables= thd->lock;
+      thd->lock= 0;
+      thd->in_lock_tables=0;
+
+      for (table= tables; table != first_not_own; table= table->next_global)
+      {
+        if (!table->placeholder() && !table->schema_table)
+        {
+          table->table->query_id= thd->query_id;
+          if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
+          {
+            ha_rollback_stmt(thd);
+            mysql_unlock_tables(thd, thd->locked_tables);
+            thd->locked_tables= 0;
+            thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+            DBUG_RETURN(-1);
+          }
+        }
+      }
+      /*
+        Let us mark all tables which don't belong to the statement itself,
+        and was marked as occupied during open_tables() as free for reuse.
+      */
+      mark_real_tables_as_free_for_reuse(first_not_own);
+      DBUG_PRINT("info",("prelocked_mode= PRELOCKED"));
+      thd->prelocked_mode= PRELOCKED;
+    }
   }
   else
   {
-    for (table = tables ; table ; table=table->next)
+    TABLE_LIST *first_not_own= thd->lex->first_not_own_table();
+    for (table= tables; table != first_not_own; table= table->next_global)
     {
-      if (!table->derived && 
+      if (!table->placeholder() && !table->schema_table &&
 	  check_lock_and_start_stmt(thd, table->table, table->lock_type))
       {
 	ha_rollback_stmt(thd);
-	return -1;
+	DBUG_RETURN(-1);
       }
     }
+    /*
+      If we are under explicit LOCK TABLES and our statement requires
+      prelocking, we should mark all "additional" tables as free for use
+      and enter prelocked mode.
+    */
+    if (thd->lex->requires_prelocking())
+    {
+      mark_real_tables_as_free_for_reuse(first_not_own);
+      DBUG_PRINT("info", ("thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES"));
+      thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES;
+    }
   }
-  return 0;
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Prepare statement for reopening of tables and recalculation of set of
+  prelocked tables.
+
+  SYNOPSIS
+    close_tables_for_reopen()
+      thd    in     Thread context
+      tables in/out List of tables which we were trying to open and lock
+
+*/
+
+void close_tables_for_reopen(THD *thd, TABLE_LIST **tables)
+{
+  /*
+    If table list consists only from tables from prelocking set, table list
+    for new attempt should be empty, so we have to update list's root pointer.
+  */
+  if (thd->lex->first_not_own_table() == *tables)
+    *tables= 0;
+  thd->lex->chop_off_not_own_tables();
+  sp_remove_not_own_routines(thd->lex);
+  for (TABLE_LIST *tmp= *tables; tmp; tmp= tmp->next_global)
+    tmp->table= 0;
+  mark_used_tables_as_free_for_reuse(thd, thd->temporary_tables);
+  close_thread_tables(thd);
 }
 
 
@@ -1943,6 +2605,7 @@ TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
 			    const char *table_name, bool link_in_list)
 {
   TABLE *tmp_table;
+  TABLE_SHARE *share;
   DBUG_ENTER("open_temporary_table");
 
   /*
@@ -1957,7 +2620,7 @@ TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
 				     MYF(MY_WME))))
     DBUG_RETURN(0);				/* purecov: inspected */
 
-  if (openfrm(path, table_name,
+  if (openfrm(thd, path, table_name,
 	      (uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE | HA_GET_INDEX),
 	      READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD,
 	      ha_open_options,
@@ -1967,21 +2630,22 @@ TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
     DBUG_RETURN(0);
   }
 
+  share= tmp_table->s;
   tmp_table->reginfo.lock_type=TL_WRITE;	 // Simulate locked
-  tmp_table->in_use= thd;
-  tmp_table->tmp_table = (tmp_table->file->has_transactions() ? 
-			  TRANSACTIONAL_TMP_TABLE : TMP_TABLE);
-  tmp_table->table_cache_key=(char*) (tmp_table+1);
-  tmp_table->key_length= (uint) (strmov((tmp_table->real_name=
-					 strmov(tmp_table->table_cache_key,db)
-					 +1), table_name)
-				 - tmp_table->table_cache_key)+1;
-  int4store(tmp_table->table_cache_key + tmp_table->key_length,
-	    thd->server_id);
-  tmp_table->key_length += 4;
-  int4store(tmp_table->table_cache_key + tmp_table->key_length,
+  share->tmp_table= (tmp_table->file->has_transactions() ? 
+                     TRANSACTIONAL_TMP_TABLE : TMP_TABLE);
+  share->table_cache_key= (char*) (tmp_table+1);
+  share->db= share->table_cache_key;
+  share->key_length= (uint) (strmov(((char*) (share->table_name=
+                                              strmov(share->table_cache_key,
+                                                     db)+1)),
+                                    table_name) -
+                             share->table_cache_key) +1;
+  int4store(share->table_cache_key + share->key_length, thd->server_id);
+  share->key_length+= 4;
+  int4store(share->table_cache_key + share->key_length,
 	    thd->variables.pseudo_thread_id);
-  tmp_table->key_length += 4;
+  share->key_length+= 4;
 
   if (link_in_list)
   {
@@ -2004,7 +2668,7 @@ bool rm_temporary_table(enum db_type base, char *path)
   if (my_delete(path,MYF(0)))
     error=1; /* purecov: inspected */
   *fn_ext(path)='\0';				// remove extension
-  handler *file=get_new_handler((TABLE*) 0, base);
+  handler *file= get_new_handler((TABLE*) 0, current_thd->mem_root, base);
   if (file && file->delete_table(path))
   {
     error=1;
@@ -2017,31 +2681,255 @@ bool rm_temporary_table(enum db_type base, char *path)
 
 
 /*****************************************************************************
-** find field in list or tables. if field is unqualifed and unique,
-** return unique field
+* The following find_field_in_XXX procedures implement the core of the
+* name resolution functionality. The entry point to resolve a column name in a
+* list of tables is 'find_field_in_tables'. It calls 'find_field_in_table_ref'
+* for each table reference. In turn, depending on the type of table reference,
+* 'find_field_in_table_ref' calls one of the 'find_field_in_XXX' procedures
+* below specific for the type of table reference.
 ******************************************************************************/
 
+/* Special Field pointers as return values of find_field_in_XXX functions. */
+Field *not_found_field= (Field*) 0x1;
+Field *view_ref_found= (Field*) 0x2; 
+
 #define WRONG_GRANT (Field*) -1
 
-Field *find_field_in_table(THD *thd,TABLE *table,const char *name,uint length,
-                           bool check_grants, bool allow_rowid, 
-                           uint *cached_field_index_ptr)
+static void update_field_dependencies(THD *thd, Field *field, TABLE *table)
+{
+  if (thd->set_query_id)
+  {
+    if (field->query_id != thd->query_id)
+    {
+      field->query_id= thd->query_id;
+      table->used_fields++;
+      table->used_keys.intersect(field->part_of_key);
+    }
+    else
+      thd->dupp_field= field;
+  }
+}
+
+
+/*
+  Find a field by name in a view that uses merge algorithm.
+
+  SYNOPSIS
+    find_field_in_view()
+    thd				thread handler
+    table_list			view to search for 'name'
+    name			name of field
+    length			length of name
+    item_name                   name of item if it will be created (VIEW)
+    ref				expression substituted in VIEW should be passed
+                                using this reference (return view_ref_found)
+    register_tree_change        TRUE if ref is not stack variable and we
+                                need register changes in item tree
+
+  RETURN
+    0			field is not found
+    view_ref_found	found value in VIEW (real result is in *ref)
+    #			pointer to field - only for schema table fields
+*/
+
+static Field *
+find_field_in_view(THD *thd, TABLE_LIST *table_list,
+                   const char *name, uint length,
+                   const char *item_name, Item **ref,
+                   bool register_tree_change)
+{
+  DBUG_ENTER("find_field_in_view");
+  DBUG_PRINT("enter",
+             ("view: '%s', field name: '%s', item name: '%s', ref 0x%lx",
+              table_list->alias, name, item_name, (ulong) ref));
+  Field_iterator_view field_it;
+  field_it.set(table_list);
+  Query_arena *arena, backup;  
+  
+  DBUG_ASSERT(table_list->schema_table_reformed ||
+              (ref != 0 && table_list->view != 0));
+  for (; !field_it.end_of_fields(); field_it.next())
+  {
+    if (!my_strcasecmp(system_charset_info, field_it.name(), name))
+    {
+      // in PS use own arena or data will be freed after prepare
+      if (register_tree_change)
+        arena= thd->activate_stmt_arena_if_needed(&backup);
+      /*
+        create_item() may, or may not create a new Item, depending on
+        the column reference. See create_view_field() for details.
+      */
+      Item *item= field_it.create_item(thd);
+      if (register_tree_change && arena)
+        thd->restore_active_arena(arena, &backup);
+      
+      if (!item)
+        DBUG_RETURN(0);
+      /*
+       *ref != NULL means that *ref contains the item that we need to
+       replace. If the item was aliased by the user, set the alias to
+       the replacing item.
+       We need to set alias on both ref itself and on ref real item.
+      */
+      if (*ref && !(*ref)->is_autogenerated_name)
+      {
+        item->set_name((*ref)->name, (*ref)->name_length,
+                       system_charset_info);
+        item->real_item()->set_name((*ref)->name, (*ref)->name_length,
+                       system_charset_info);
+      }
+      if (register_tree_change)
+        thd->change_item_tree(ref, item);
+      else
+        *ref= item;
+      DBUG_RETURN((Field*) view_ref_found);
+    }
+  }
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Find field by name in a NATURAL/USING join table reference.
+
+  SYNOPSIS
+    find_field_in_natural_join()
+    thd			 [in]  thread handler
+    table_ref            [in]  table reference to search
+    name		 [in]  name of field
+    length		 [in]  length of name
+    ref                  [in/out] if 'name' is resolved to a view field, ref is
+                               set to point to the found view field
+    register_tree_change [in]  TRUE if ref is not stack variable and we
+                               need register changes in item tree
+    actual_table         [out] the original table reference where the field
+                               belongs - differs from 'table_list' only for
+                               NATURAL/USING joins
+
+  DESCRIPTION
+    Search for a field among the result fields of a NATURAL/USING join.
+    Notice that this procedure is called only for non-qualified field
+    names. In the case of qualified fields, we search directly the base
+    tables of a natural join.
+
+  RETURN
+    NULL        if the field was not found
+    WRONG_GRANT if no access rights to the found field
+    #           Pointer to the found Field
+*/
+
+static Field *
+find_field_in_natural_join(THD *thd, TABLE_LIST *table_ref, const char *name,
+                           uint length, Item **ref, bool register_tree_change,
+                           TABLE_LIST **actual_table)
+{
+  List_iterator_fast<Natural_join_column>
+    field_it(*(table_ref->join_columns));
+  Natural_join_column *nj_col;
+  Field *found_field;
+  Query_arena *arena, backup;
+  DBUG_ENTER("find_field_in_natural_join");
+  DBUG_PRINT("enter", ("field name: '%s', ref 0x%lx",
+		       name, (ulong) ref));
+  DBUG_ASSERT(table_ref->is_natural_join && table_ref->join_columns);
+  DBUG_ASSERT(*actual_table == NULL);
+
+  LINT_INIT(found_field);
+
+  for (;;)
+  {
+    if (!(nj_col= field_it++))
+      DBUG_RETURN(NULL);
+
+    if (!my_strcasecmp(system_charset_info, nj_col->name(), name))
+      break;
+  }
+
+  if (nj_col->view_field)
+  {
+    Item *item;
+    if (register_tree_change)
+      arena= thd->activate_stmt_arena_if_needed(&backup);
+    /*
+      create_item() may, or may not create a new Item, depending on the
+      column reference. See create_view_field() for details.
+    */
+    item= nj_col->create_item(thd);
+    if (register_tree_change && arena)
+      thd->restore_active_arena(arena, &backup);
+
+    if (!item)
+      DBUG_RETURN(NULL);
+    DBUG_ASSERT(nj_col->table_field == NULL);
+    if (nj_col->table_ref->schema_table_reformed)
+    {
+      /*
+        Translation table items are always Item_fields and fixed
+        already('mysql_schema_table' function). So we can return
+        ->field. It is used only for 'show & where' commands.
+      */
+      DBUG_RETURN(((Item_field*) (nj_col->view_field->item))->field);
+    }
+    if (register_tree_change)
+      thd->change_item_tree(ref, item);
+    else
+      *ref= item;
+    found_field= (Field*) view_ref_found;
+  }
+  else
+  {
+    /* This is a base table. */
+    DBUG_ASSERT(nj_col->view_field == NULL);
+    DBUG_ASSERT(nj_col->table_ref->table == nj_col->table_field->table);
+    found_field= nj_col->table_field;
+    update_field_dependencies(thd, found_field, nj_col->table_ref->table);
+  }
+
+  *actual_table= nj_col->table_ref;
+  
+  DBUG_RETURN(found_field);
+}
+
+
+/*
+  Find field by name in a base table or a view with temp table algorithm.
+
+  SYNOPSIS
+    find_field_in_table()
+    thd				thread handler
+    table			table where to search for the field
+    name			name of field
+    length			length of name
+    allow_rowid			do allow finding of "_rowid" field?
+    cached_field_index_ptr	cached position in field list (used to speedup
+                                lookup for fields in prepared tables)
+
+  RETURN
+    0	field is not found
+    #	pointer to field
+*/
+
+Field *
+find_field_in_table(THD *thd, TABLE *table, const char *name, uint length,
+                    bool allow_rowid, uint *cached_field_index_ptr)
 {
   Field **field_ptr, *field;
   uint cached_field_index= *cached_field_index_ptr;
+  DBUG_ENTER("find_field_in_table");
+  DBUG_PRINT("enter", ("table: '%s', field name: '%s'", table->alias, name));
 
   /* We assume here that table->field < NO_CACHED_FIELD_INDEX = UINT_MAX */
-  if (cached_field_index < table->fields &&
-      !my_strcasecmp(system_charset_info, 
+  if (cached_field_index < table->s->fields &&
+      !my_strcasecmp(system_charset_info,
                      table->field[cached_field_index]->field_name, name))
     field_ptr= table->field + cached_field_index;
-  else if (table->name_hash.records)
-    field_ptr= (Field**)hash_search(&table->name_hash,(byte*) name,
-                                    length);
+  else if (table->s->name_hash.records)
+    field_ptr= (Field**) hash_search(&table->s->name_hash, (byte*) name,
+                                     length);
   else
   {
     if (!(field_ptr= table->field))
-      return (Field *)0;
+      DBUG_RETURN((Field *)0);
     for (; *field_ptr; ++field_ptr)
       if (!my_strcasecmp(system_charset_info, (*field_ptr)->field_name, name))
         break;
@@ -2057,25 +2945,163 @@ Field *find_field_in_table(THD *thd,TABLE *table,const char *name,uint length,
     if (!allow_rowid ||
         my_strcasecmp(system_charset_info, name, "_rowid") ||
         !(field=table->rowid_field))
-      return (Field*) 0;
+      DBUG_RETURN((Field*) 0);
   }
 
-  if (thd->set_query_id)
+  update_field_dependencies(thd, field, table);
+
+  DBUG_RETURN(field);
+}
+
+
+/*
+  Find field in a table reference.
+
+  SYNOPSIS
+    find_field_in_table_ref()
+    thd			   [in]  thread handler
+    table_list		   [in]  table reference to search
+    name		   [in]  name of field
+    length		   [in]  field length of name
+    item_name              [in]  name of item if it will be created (VIEW)
+    db_name                [in]  optional database name that qualifies the
+    table_name             [in]  optional table name that qualifies the field
+    ref		       [in/out] if 'name' is resolved to a view field, ref
+                                 is set to point to the found view field
+    check_privileges       [in]  check privileges
+    allow_rowid		   [in]  do allow finding of "_rowid" field?
+    cached_field_index_ptr [in]  cached position in field list (used to
+                                 speedup lookup for fields in prepared tables)
+    register_tree_change   [in]  TRUE if ref is not stack variable and we
+                                 need register changes in item tree
+    actual_table           [out] the original table reference where the field
+                                 belongs - differs from 'table_list' only for
+                                 NATURAL_USING joins.
+
+  DESCRIPTION
+    Find a field in a table reference depending on the type of table
+    reference. There are three types of table references with respect
+    to the representation of their result columns:
+    - an array of Field_translator objects for MERGE views and some
+      information_schema tables,
+    - an array of Field objects (and possibly a name hash) for stored
+      tables,
+    - a list of Natural_join_column objects for NATURAL/USING joins.
+    This procedure detects the type of the table reference 'table_list'
+    and calls the corresponding search routine.
+
+  RETURN
+    0			field is not found
+    view_ref_found	found value in VIEW (real result is in *ref)
+    #			pointer to field
+*/
+
+Field *
+find_field_in_table_ref(THD *thd, TABLE_LIST *table_list,
+                        const char *name, uint length,
+                        const char *item_name, const char *db_name,
+                        const char *table_name, Item **ref,
+                        bool check_privileges, bool allow_rowid,
+                        uint *cached_field_index_ptr,
+                        bool register_tree_change, TABLE_LIST **actual_table)
+{
+  Field *fld;
+  DBUG_ENTER("find_field_in_table_ref");
+  DBUG_PRINT("enter",
+             ("table: '%s'  field name: '%s'  item name: '%s'  ref 0x%lx",
+              table_list->alias, name, item_name, (ulong) ref));
+
+  /*
+    Check that the table and database that qualify the current field name
+    are the same as the table reference we are going to search for the field.
+
+    Exclude from the test below nested joins because the columns in a
+    nested join generally originate from different tables. Nested joins
+    also have no table name, except when a nested join is a merge view
+    or an information schema table.
+
+    We include explicitly table references with a 'field_translation' table,
+    because if there are views over natural joins we don't want to search
+    inside the view, but we want to search directly in the view columns
+    which are represented as a 'field_translation'.
+
+    TODO: Ensure that table_name, db_name and tables->db always points to
+          something !
+  */
+  if (/* Exclude nested joins. */
+      (!table_list->nested_join ||
+       /* Include merge views and information schema tables. */
+       table_list->field_translation) &&
+      /*
+        Test if the field qualifiers match the table reference we plan
+        to search.
+      */
+      table_name && table_name[0] &&
+      (my_strcasecmp(table_alias_charset, table_list->alias, table_name) ||
+       (db_name && db_name[0] && table_list->db && table_list->db[0] &&
+        strcmp(db_name, table_list->db))))
+    DBUG_RETURN(0);
+
+  *actual_table= NULL;
+
+  if (table_list->field_translation)
   {
-    if (field->query_id != thd->query_id)
+    /* 'table_list' is a view or an information schema table. */
+    if ((fld= find_field_in_view(thd, table_list, name, length, item_name, ref,
+                                 register_tree_change)))
+      *actual_table= table_list;
+  }
+  else if (!table_list->nested_join)
+  {
+    /* 'table_list' is a stored table. */
+    DBUG_ASSERT(table_list->table);
+    if ((fld= find_field_in_table(thd, table_list->table, name, length,
+                                  allow_rowid,
+                                  cached_field_index_ptr)))
+      *actual_table= table_list;
+  }
+  else
+  {
+    /*
+      'table_list' is a NATURAL/USING join, or an operand of such join that
+      is a nested join itself.
+
+      If the field name we search for is qualified, then search for the field
+      in the table references used by NATURAL/USING the join.
+    */
+    if (table_name && table_name[0])
     {
-      field->query_id=thd->query_id;
-      table->used_fields++;
-      table->used_keys.intersect(field->part_of_key);
+      List_iterator<TABLE_LIST> it(table_list->nested_join->join_list);
+      TABLE_LIST *table;
+      while ((table= it++))
+      {
+        if ((fld= find_field_in_table_ref(thd, table, name, length, item_name,
+                                          db_name, table_name, ref,
+                                          check_privileges, allow_rowid,
+                                          cached_field_index_ptr,
+                                          register_tree_change, actual_table)))
+          DBUG_RETURN(fld);
+      }
+      DBUG_RETURN(0);
     }
-    else
-      thd->dupp_field=field;
+    /*
+      Non-qualified field, search directly in the result columns of the
+      natural join. The condition of the outer IF is true for the top-most
+      natural join, thus if the field is not qualified, we will search
+      directly the top-most NATURAL/USING join.
+    */
+    fld= find_field_in_natural_join(thd, table_list, name, length, ref,
+                                    register_tree_change, actual_table);
   }
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
-  if (check_grants && check_grant_column(thd,table,name,length))
-    return WRONG_GRANT;
+  /* Check if there are sufficient access rights to the found field. */
+  if (fld && check_privileges &&
+      check_column_grant_in_table_ref(thd, *actual_table, name, length))
+    fld= WRONG_GRANT;
 #endif
-  return field;
+
+  DBUG_RETURN(fld);
 }
 
 
@@ -2084,58 +3110,100 @@ Field *find_field_in_table(THD *thd,TABLE *table,const char *name,uint length,
 
   SYNOPSIS
     find_field_in_tables()
-    thd			Pointer to current thread structure
-    item		Field item that should be found
-    tables		Tables for scanning
-    where		Table where field found will be returned via
-			this parameter
-    report_error	If FALSE then do not report error if item not found
-			and return not_found_field
+    thd			  pointer to current thread structure
+    item		  field item that should be found
+    first_table           list of tables to be searched for item
+    last_table            end of the list of tables to search for item. If NULL
+                          then search to the end of the list 'first_table'.
+    ref			  if 'item' is resolved to a view field, ref is set to
+                          point to the found view field
+    report_error	  Degree of error reporting:
+                          - IGNORE_ERRORS then do not report any error
+                          - IGNORE_EXCEPT_NON_UNIQUE report only non-unique
+                            fields, suppress all other errors
+                          - REPORT_EXCEPT_NON_UNIQUE report all other errors
+                            except when non-unique fields were found
+                          - REPORT_ALL_ERRORS
+    check_privileges      need to check privileges
+    register_tree_change  TRUE if ref is not a stack variable and we
+                          to need register changes in item tree
 
   RETURN VALUES
-    0			Field is not found or field is not unique- error
-			message is reported
-    not_found_field	Function was called with report_error == FALSE and
-			field was not found. no error message reported.
-    found field
+    0			If error: the found field is not unique, or there are
+                        no sufficient access priviliges for the found field,
+                        or the field is qualified with non-existing table.
+    not_found_field	The function was called with report_error ==
+                        (IGNORE_ERRORS || IGNORE_EXCEPT_NON_UNIQUE) and a
+			field was not found.
+    view_ref_found	View field is found, item passed through ref parameter
+    found field         If a item was resolved to some field
 */
 
-// Special Field pointer for find_field_in_tables returning
-const Field *not_found_field= (Field*) 0x1;
-
 Field *
-find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
-		     TABLE_LIST **where, bool report_error)
+find_field_in_tables(THD *thd, Item_ident *item,
+                     TABLE_LIST *first_table, TABLE_LIST *last_table,
+		     Item **ref, find_item_error_report_type report_error,
+                     bool check_privileges, bool register_tree_change)
 {
   Field *found=0;
-  const char *db=item->db_name;
-  const char *table_name=item->table_name;
-  const char *name=item->field_name;
+  const char *db= item->db_name;
+  const char *table_name= item->table_name;
+  const char *name= item->field_name;
   uint length=(uint) strlen(name);
   char name_buff[NAME_LEN+1];
+  TABLE_LIST *cur_table= first_table;
+  TABLE_LIST *actual_table;
   bool allow_rowid;
 
+  if (!table_name || !table_name[0])
+  {
+    table_name= 0;                              // For easier test
+    db= 0;
+  }
+
+  allow_rowid= table_name || (cur_table && !cur_table->next_local);
+
   if (item->cached_table)
   {
     /*
-      This shortcut is used by prepared statements. We assuming that 
-      TABLE_LIST *tables is not changed during query execution (which 
-      is true for all queries except RENAME but luckily RENAME doesn't 
+      This shortcut is used by prepared statements. We assume that
+      TABLE_LIST *first_table is not changed during query execution (which
+      is true for all queries except RENAME but luckily RENAME doesn't
       use fields...) so we can rely on reusing pointer to its member.
-      With this optimisation we also miss case when addition of one more
-      field makes some prepared query ambiguous and so erronous, but we 
+      With this optimization we also miss case when addition of one more
+      field makes some prepared query ambiguous and so erroneous, but we
       accept this trade off.
     */
-    found= find_field_in_table(thd, item->cached_table->table, name, length,
-                               test(item->cached_table->
-				    table->grant.want_privilege),
-                               1, &(item->cached_field_index));
-
+    TABLE_LIST *table_ref= item->cached_table;
+    /*
+      The condition (table_ref->view == NULL) ensures that we will call
+      find_field_in_table even in the case of information schema tables
+      when table_ref->field_translation != NULL.
+      */
+    if (table_ref->table && !table_ref->view)
+      found= find_field_in_table(thd, table_ref->table, name, length,
+                                 TRUE, &(item->cached_field_index));
+    else
+      found= find_field_in_table_ref(thd, table_ref, name, length, item->name,
+                                     NULL, NULL, ref, check_privileges,
+                                     TRUE, &(item->cached_field_index),
+                                     register_tree_change,
+                                     &actual_table);
     if (found)
     {
-      (*where)= tables;
       if (found == WRONG_GRANT)
-        return (Field*) 0;
+	return (Field*) 0;
+      {
+        SELECT_LEX *current_sel= thd->lex->current_select;
+        SELECT_LEX *last_select= table_ref->select_lex;
+        /*
+          If the field was an outer referencee, mark all selects using this
+          sub query as dependent on the outer query
+        */
+        if (current_sel != last_select)
+          mark_select_range_as_dependent(thd, last_select, current_sel,
+                                         found, *ref, item);
+      }
       return found;
     }
   }
@@ -2143,7 +3211,7 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
   if (db && lower_case_table_names)
   {
     /*
-      convert database to lower case for comparision.
+      convert database to lower case for comparison.
       We can't do this in Item_field as this would change the
       'name' of the item which may be used in the select list
     */
@@ -2152,99 +3220,81 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
     db= name_buff;
   }
 
-  if (table_name && table_name[0])
-  {						/* Qualified field */
-    bool found_table=0;
-    for (; tables ; tables=tables->next)
-    {
-      if (!my_strcasecmp(table_alias_charset, tables->alias, table_name) &&
-	  (!db || !tables->db ||  !tables->db[0] || !strcmp(db,tables->db)))
-      {
-	found_table=1;
-	Field *find=find_field_in_table(thd,tables->table,name,length,
-					test(tables->table->grant.
-					     want_privilege),
-					1, &(item->cached_field_index));
-	if (find)
-	{
-	  (*where)= item->cached_table= tables;
-	  if (!tables->cacheable_table)
-	    item->cached_table= 0;
-	  if (find == WRONG_GRANT)
-	    return (Field*) 0;
-	  if (db || !thd->where)
-	    return find;
-	  if (found)
-	  {
-	    my_printf_error(ER_NON_UNIQ_ERROR,ER(ER_NON_UNIQ_ERROR),MYF(0),
-			    item->full_name(),thd->where);
-	    return (Field*) 0;
-	  }
-	  found=find;
-	}
-      }
-    }
-    if (found)
-      return found;
-    if (!found_table && report_error)
-    {
-      char buff[NAME_LEN*2+1];
-      if (db && db[0])
-      {
-	strxnmov(buff,sizeof(buff)-1,db,".",table_name,NullS);
-	table_name=buff;
-      }
-      my_printf_error(ER_UNKNOWN_TABLE, ER(ER_UNKNOWN_TABLE), MYF(0),
-                      table_name, thd->where);
-    }
-    else
-      if (report_error)
-	my_printf_error(ER_BAD_FIELD_ERROR,ER(ER_BAD_FIELD_ERROR),MYF(0),
-			item->full_name(),thd->where);
-      else
-	return (Field*) not_found_field;
-    return (Field*) 0;
-  }
-  allow_rowid= tables && !tables->next;         // Only one table
-  for (; tables ; tables=tables->next)
-  {
-    if (!tables->table)
-    {
-      if (report_error)
-	my_printf_error(ER_BAD_FIELD_ERROR,ER(ER_BAD_FIELD_ERROR),MYF(0),
-			item->full_name(),thd->where);
-      return (Field*) not_found_field;
-    }
+  if (last_table)
+    last_table= last_table->next_name_resolution_table;
 
-    Field *field=find_field_in_table(thd,tables->table,name,length,
-				     test(tables->table->grant.want_privilege),
-				     allow_rowid, &(item->cached_field_index));
-    if (field)
+  for (; cur_table != last_table ;
+       cur_table= cur_table->next_name_resolution_table)
+  {
+    Field *cur_field= find_field_in_table_ref(thd, cur_table, name, length,
+                                              item->name, db, table_name, ref,
+                                              check_privileges, allow_rowid,
+                                              &(item->cached_field_index),
+                                              register_tree_change,
+                                              &actual_table);
+    if (cur_field)
     {
-      if (field == WRONG_GRANT)
+      if (cur_field == WRONG_GRANT)
 	return (Field*) 0;
-      (*where)= item->cached_table= tables;
-      if (!tables->cacheable_table)
-	item->cached_table= 0;
+
+      /*
+        Store the original table of the field, which may be different from
+        cur_table in the case of NATURAL/USING join.
+      */
+      item->cached_table= (!actual_table->cacheable_table || found) ?
+                          0 : actual_table;
+
+      DBUG_ASSERT(thd->where);
+      /*
+        If we found a fully qualified field we return it directly as it can't
+        have duplicates.
+       */
+      if (db)
+        return cur_field;
+
       if (found)
       {
-	if (!thd->where)			// Returns first found
-	  break;
-	my_printf_error(ER_NON_UNIQ_ERROR,ER(ER_NON_UNIQ_ERROR),MYF(0),
-			name,thd->where);
+        if (report_error == REPORT_ALL_ERRORS ||
+            report_error == IGNORE_EXCEPT_NON_UNIQUE)
+          my_error(ER_NON_UNIQ_ERROR, MYF(0),
+                   table_name ? item->full_name() : name, thd->where);
 	return (Field*) 0;
       }
-      found= field;
+      found= cur_field;
     }
   }
+
   if (found)
     return found;
-  if (report_error)
-    my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR),
-		    MYF(0), item->full_name(), thd->where);
+
+  /*
+    If the field was qualified and there were no tables to search, issue
+    an error that an unknown table was given. The situation is detected
+    as follows: if there were no tables we wouldn't go through the loop
+    and cur_table wouldn't be updated by the loop increment part, so it
+    will be equal to the first table.
+  */
+  if (table_name && (cur_table == first_table) &&
+      (report_error == REPORT_ALL_ERRORS ||
+       report_error == REPORT_EXCEPT_NON_UNIQUE))
+  {
+    char buff[NAME_LEN*2+1];
+    if (db && db[0])
+    {
+      strxnmov(buff,sizeof(buff)-1,db,".",table_name,NullS);
+      table_name=buff;
+    }
+    my_error(ER_UNKNOWN_TABLE, MYF(0), table_name, thd->where);
+  }
   else
-    return (Field*) not_found_field;
-  return (Field*) 0;
+  {
+    if (report_error == REPORT_ALL_ERRORS ||
+        report_error == REPORT_EXCEPT_NON_UNIQUE)
+      my_error(ER_BAD_FIELD_ERROR, MYF(0), item->full_name(), thd->where);
+    else
+      found= not_found_field;
+  }
+  return found;
 }
 
 
@@ -2278,8 +3328,8 @@ find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *tables,
                         found field
 */
 
-// Special Item pointer for find_item_in_list returning
-const Item **not_found_item= (const Item**) 0x1;
+/* Special Item pointer to serve as a return value from find_item_in_list(). */
+Item **not_found_item= (Item**) 0x1;
 
 
 Item **
@@ -2294,7 +3344,8 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
   bool found_unaliased_non_uniq= 0;
   uint unaliased_counter;
 
-  LINT_INIT(unaliased_counter);
+  LINT_INIT(unaliased_counter);                 // Dependent on found_unaliased
+
   *unaliased= FALSE;
 
   if (find->type() == Item::FIELD_ITEM	|| find->type() == Item::REF_ITEM)
@@ -2306,9 +3357,9 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
 
   for (uint i= 0; (item=li++); i++)
   {
-    if (field_name && item->type() == Item::FIELD_ITEM)
+    if (field_name && item->real_item()->type() == Item::FIELD_ITEM)
     {
-      Item_field *item_field= (Item_field*) item;
+      Item_ident *item_field= (Item_ident*) item;
 
       /*
 	In case of group_concat() with ORDER BY condition in the QUERY
@@ -2354,8 +3405,8 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
               unaliased names only and will have duplicate error anyway.
             */
             if (report_error != IGNORE_ERRORS)
-              my_printf_error(ER_NON_UNIQ_ERROR, ER(ER_NON_UNIQ_ERROR),
-                              MYF(0), find->full_name(), current_thd->where);
+              my_error(ER_NON_UNIQ_ERROR, MYF(0),
+                       find->full_name(), current_thd->where);
             return (Item**) 0;
           }
           found_unaliased= li.ref();
@@ -2379,8 +3430,8 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
           if ((*found)->eq(item, 0))
             continue;                           // Same field twice
           if (report_error != IGNORE_ERRORS)
-            my_printf_error(ER_NON_UNIQ_ERROR, ER(ER_NON_UNIQ_ERROR),
-                            MYF(0), find->full_name(), current_thd->where);
+            my_error(ER_NON_UNIQ_ERROR, MYF(0),
+                     find->full_name(), current_thd->where);
           return (Item**) 0;
         }
         found= li.ref();
@@ -2408,7 +3459,7 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
         }
       }
     }
-    else if (!table_name && (item->eq(find,0) ||
+    else if (!table_name && (find->eq(item,0) ||
 			     find->name && item->name &&
 			     !my_strcasecmp(system_charset_info, 
 					    item->name,find->name)))
@@ -2423,8 +3474,8 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
     if (found_unaliased_non_uniq)
     {
       if (report_error != IGNORE_ERRORS)
-        my_printf_error(ER_NON_UNIQ_ERROR, ER(ER_NON_UNIQ_ERROR), MYF(0),
-                        find->full_name(), current_thd->where);
+        my_error(ER_NON_UNIQ_ERROR, MYF(0),
+                 find->full_name(), current_thd->where);
       return (Item **) 0;
     }
     if (found_unaliased)
@@ -2439,14 +3490,655 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
   if (report_error != REPORT_EXCEPT_NOT_FOUND)
   {
     if (report_error == REPORT_ALL_ERRORS)
-      my_printf_error(ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR), MYF(0),
-		      find->full_name(), current_thd->where);
+      my_error(ER_BAD_FIELD_ERROR, MYF(0),
+               find->full_name(), current_thd->where);
     return (Item **) 0;
   }
   else
     return (Item **) not_found_item;
 }
 
+
+/*
+  Test if a string is a member of a list of strings.
+
+  SYNOPSIS
+    test_if_string_in_list()
+    find      the string to look for
+    str_list  a list of strings to be searched
+
+  DESCRIPTION
+    Sequentially search a list of strings for a string, and test whether
+    the list contains the same string.
+
+  RETURN
+    TRUE  if find is in str_list
+    FALSE otherwise
+*/
+
+static bool
+test_if_string_in_list(const char *find, List<String> *str_list)
+{
+  List_iterator<String> str_list_it(*str_list);
+  String *curr_str;
+  size_t find_length= strlen(find);
+  while ((curr_str= str_list_it++))
+  {
+    if (find_length != curr_str->length())
+      continue;
+    if (!my_strcasecmp(system_charset_info, find, curr_str->ptr()))
+      return TRUE;
+  }
+  return FALSE;
+}
+
+
+/*
+  Create a new name resolution context for an item so that it is
+  being resolved in a specific table reference.
+
+  SYNOPSIS
+    set_new_item_local_context()
+    thd        pointer to current thread
+    item       item for which new context is created and set
+    table_ref  table ref where an item showld be resolved
+
+  DESCRIPTION
+    Create a new name resolution context for an item, so that the item
+    is resolved only the supplied 'table_ref'.
+
+  RETURN
+    FALSE  if all OK
+    TRUE   otherwise
+*/
+
+static bool
+set_new_item_local_context(THD *thd, Item_ident *item, TABLE_LIST *table_ref)
+{
+  Name_resolution_context *context;
+  if (!(context= new (thd->mem_root) Name_resolution_context))
+    return TRUE;
+  context->init();
+  context->first_name_resolution_table=
+    context->last_name_resolution_table= table_ref;
+  item->context= context;
+  return FALSE;
+}
+
+
+/*
+  Find and mark the common columns of two table references.
+
+  SYNOPSIS
+    mark_common_columns()
+    thd                [in] current thread
+    table_ref_1        [in] the first (left) join operand
+    table_ref_2        [in] the second (right) join operand
+    using_fields       [in] if the join is JOIN...USING - the join columns,
+                            if NATURAL join, then NULL
+    found_using_fields [out] number of fields from the USING clause that were
+                             found among the common fields
+
+  DESCRIPTION
+    The procedure finds the common columns of two relations (either
+    tables or intermediate join results), and adds an equi-join condition
+    to the ON clause of 'table_ref_2' for each pair of matching columns.
+    If some of table_ref_XXX represents a base table or view, then we
+    create new 'Natural_join_column' instances for each column
+    reference and store them in the 'join_columns' of the table
+    reference.
+
+  IMPLEMENTATION
+    The procedure assumes that store_natural_using_join_columns() was
+    called for the previous level of NATURAL/USING joins.
+
+  RETURN
+    TRUE   error when some common column is non-unique, or out of memory
+    FALSE  OK
+*/
+
+static bool
+mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2,
+                    List<String> *using_fields, uint *found_using_fields)
+{
+  Field_iterator_table_ref it_1, it_2;
+  Natural_join_column *nj_col_1, *nj_col_2;
+  Query_arena *arena, backup;
+  bool result= TRUE;
+  bool first_outer_loop= TRUE;
+  /*
+    Leaf table references to which new natural join columns are added
+    if the leaves are != NULL.
+  */
+  TABLE_LIST *leaf_1= (table_ref_1->nested_join &&
+                       !table_ref_1->is_natural_join) ?
+                      NULL : table_ref_1;
+  TABLE_LIST *leaf_2= (table_ref_2->nested_join &&
+                       !table_ref_2->is_natural_join) ?
+                      NULL : table_ref_2;
+
+  DBUG_ENTER("mark_common_columns");
+  DBUG_PRINT("info", ("operand_1: %s  operand_2: %s",
+                      table_ref_1->alias, table_ref_2->alias));
+
+  *found_using_fields= 0;
+  arena= thd->activate_stmt_arena_if_needed(&backup);
+
+  for (it_1.set(table_ref_1); !it_1.end_of_fields(); it_1.next())
+  {
+    bool found= FALSE;
+    const char *field_name_1;
+    if (!(nj_col_1= it_1.get_or_create_column_ref(leaf_1)))
+      goto err;
+    field_name_1= nj_col_1->name();
+
+    /*
+      Find a field with the same name in table_ref_2.
+
+      Note that for the second loop, it_2.set() will iterate over
+      table_ref_2->join_columns and not generate any new elements or
+      lists.
+    */
+    nj_col_2= NULL;
+    for (it_2.set(table_ref_2); !it_2.end_of_fields(); it_2.next())
+    {
+      Natural_join_column *cur_nj_col_2;
+      const char *cur_field_name_2;
+      if (!(cur_nj_col_2= it_2.get_or_create_column_ref(leaf_2)))
+        goto err;
+      cur_field_name_2= cur_nj_col_2->name();
+
+      /*
+        Compare the two columns and check for duplicate common fields.
+        A common field is duplicate either if it was already found in
+        table_ref_2 (then found == TRUE), or if a field in table_ref_2
+        was already matched by some previous field in table_ref_1
+        (then cur_nj_col_2->is_common == TRUE).
+      */
+      if (!my_strcasecmp(system_charset_info, field_name_1, cur_field_name_2))
+      {
+        if (found || cur_nj_col_2->is_common)
+        {
+          my_error(ER_NON_UNIQ_ERROR, MYF(0), field_name_1, thd->where);
+          goto err;
+        }
+        nj_col_2= cur_nj_col_2;
+        found= TRUE;
+      }
+    }
+    if (first_outer_loop && leaf_2)
+    {
+      /*
+        Make sure that the next inner loop "knows" that all columns
+        are materialized already.
+      */
+      leaf_2->is_join_columns_complete= TRUE;
+      first_outer_loop= FALSE;
+    }
+    if (!found)
+      continue;                                 // No matching field
+
+    /*
+      field_1 and field_2 have the same names. Check if they are in the USING
+      clause (if present), mark them as common fields, and add a new
+      equi-join condition to the ON clause.
+    */
+    if (nj_col_2 &&
+        (!using_fields ||
+          test_if_string_in_list(field_name_1, using_fields)))
+    {
+      Item *item_1=   nj_col_1->create_item(thd);
+      Item *item_2=   nj_col_2->create_item(thd);
+      Field *field_1= nj_col_1->field();
+      Field *field_2= nj_col_2->field();
+      Item_ident *item_ident_1, *item_ident_2;
+      Item_func_eq *eq_cond;
+
+      if (!item_1 || !item_2)
+        goto err;                               // out of memory
+
+      /*
+        The following assert checks that the two created items are of
+        type Item_ident.
+      */
+      DBUG_ASSERT(!thd->lex->current_select->no_wrap_view_item);
+      /*
+        In the case of no_wrap_view_item == 0, the created items must be
+        of sub-classes of Item_ident.
+      */
+      DBUG_ASSERT(item_1->type() == Item::FIELD_ITEM ||
+                  item_1->type() == Item::REF_ITEM);
+      DBUG_ASSERT(item_2->type() == Item::FIELD_ITEM ||
+                  item_2->type() == Item::REF_ITEM);
+
+      /*
+        We need to cast item_1,2 to Item_ident, because we need to hook name
+        resolution contexts specific to each item.
+      */
+      item_ident_1= (Item_ident*) item_1;
+      item_ident_2= (Item_ident*) item_2;
+      /*
+        Create and hook special name resolution contexts to each item in the
+        new join condition . We need this to both speed-up subsequent name
+        resolution of these items, and to enable proper name resolution of
+        the items during the execute phase of PS.
+      */
+      if (set_new_item_local_context(thd, item_ident_1, nj_col_1->table_ref) ||
+          set_new_item_local_context(thd, item_ident_2, nj_col_2->table_ref))
+        goto err;
+
+      if (!(eq_cond= new Item_func_eq(item_ident_1, item_ident_2)))
+        goto err;                               /* Out of memory. */
+
+      /*
+        Add the new equi-join condition to the ON clause. Notice that
+        fix_fields() is applied to all ON conditions in setup_conds()
+        so we don't do it here.
+       */
+      add_join_on((table_ref_1->outer_join & JOIN_TYPE_RIGHT ?
+                   table_ref_1 : table_ref_2),
+                  eq_cond);
+
+      nj_col_1->is_common= nj_col_2->is_common= TRUE;
+
+      if (field_1)
+      {
+        /* Mark field_1 used for table cache. */
+        field_1->query_id= thd->query_id;
+        nj_col_1->table_ref->table->used_keys.intersect(field_1->part_of_key);
+      }
+      if (field_2)
+      {
+        /* Mark field_2 used for table cache. */
+        field_2->query_id= thd->query_id;
+        nj_col_2->table_ref->table->used_keys.intersect(field_2->part_of_key);
+      }
+
+      if (using_fields != NULL)
+        ++(*found_using_fields);
+    }
+  }
+  if (leaf_1)
+    leaf_1->is_join_columns_complete= TRUE;
+
+  /*
+    Everything is OK.
+    Notice that at this point there may be some column names in the USING
+    clause that are not among the common columns. This is an SQL error and
+    we check for this error in store_natural_using_join_columns() when
+    (found_using_fields < length(join_using_fields)).
+  */
+  result= FALSE;
+
+err:
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  DBUG_RETURN(result);
+}
+
+
+
+/*
+  Materialize and store the row type of NATURAL/USING join.
+
+  SYNOPSIS
+    store_natural_using_join_columns()
+    thd                current thread
+    natural_using_join the table reference of the NATURAL/USING join
+    table_ref_1        the first (left) operand (of a NATURAL/USING join).
+    table_ref_2        the second (right) operand (of a NATURAL/USING join).
+    using_fields       if the join is JOIN...USING - the join columns,
+                       if NATURAL join, then NULL
+    found_using_fields number of fields from the USING clause that were
+                       found among the common fields
+
+  DESCRIPTION
+    Iterate over the columns of both join operands and sort and store
+    all columns into the 'join_columns' list of natural_using_join
+    where the list is formed by three parts:
+      part1: The coalesced columns of table_ref_1 and table_ref_2,
+             sorted according to the column order of the first table.
+      part2: The other columns of the first table, in the order in
+             which they were defined in CREATE TABLE.
+      part3: The other columns of the second table, in the order in
+             which they were defined in CREATE TABLE.
+    Time complexity - O(N1+N2), where Ni = length(table_ref_i).
+
+  IMPLEMENTATION
+    The procedure assumes that mark_common_columns() has been called
+    for the join that is being processed.
+
+  RETURN
+    TRUE    error: Some common column is ambiguous
+    FALSE   OK
+*/
+
+static bool
+store_natural_using_join_columns(THD *thd, TABLE_LIST *natural_using_join,
+                                 TABLE_LIST *table_ref_1,
+                                 TABLE_LIST *table_ref_2,
+                                 List<String> *using_fields,
+                                 uint found_using_fields)
+{
+  Field_iterator_table_ref it_1, it_2;
+  Natural_join_column *nj_col_1, *nj_col_2;
+  Query_arena *arena, backup;
+  bool result= TRUE;
+  List<Natural_join_column> *non_join_columns;
+  DBUG_ENTER("store_natural_using_join_columns");
+
+  DBUG_ASSERT(!natural_using_join->join_columns);
+
+  arena= thd->activate_stmt_arena_if_needed(&backup);
+
+  if (!(non_join_columns= new List<Natural_join_column>) ||
+      !(natural_using_join->join_columns= new List<Natural_join_column>))
+    goto err;
+
+  /* Append the columns of the first join operand. */
+  for (it_1.set(table_ref_1); !it_1.end_of_fields(); it_1.next())
+  {
+    nj_col_1= it_1.get_natural_column_ref();
+    if (nj_col_1->is_common)
+    {
+      natural_using_join->join_columns->push_back(nj_col_1);
+      /* Reset the common columns for the next call to mark_common_columns. */
+      nj_col_1->is_common= FALSE;
+    }
+    else
+      non_join_columns->push_back(nj_col_1);
+  }
+
+  /*
+    Check that all columns in the USING clause are among the common
+    columns. If this is not the case, report the first one that was
+    not found in an error.
+  */
+  if (using_fields && found_using_fields < using_fields->elements)
+  {
+    String *using_field_name;
+    List_iterator_fast<String> using_fields_it(*using_fields);
+    while ((using_field_name= using_fields_it++))
+    {
+      const char *using_field_name_ptr= using_field_name->c_ptr();
+      List_iterator_fast<Natural_join_column>
+        it(*(natural_using_join->join_columns));
+      Natural_join_column *common_field;
+
+      for (;;)
+      {
+        /* If reached the end of fields, and none was found, report error. */
+        if (!(common_field= it++))
+        {
+          my_error(ER_BAD_FIELD_ERROR, MYF(0), using_field_name_ptr,
+                   current_thd->where);
+          goto err;
+        }
+        if (!my_strcasecmp(system_charset_info,
+                           common_field->name(), using_field_name_ptr))
+          break;                                // Found match
+      }
+    }
+  }
+
+  /* Append the non-equi-join columns of the second join operand. */
+  for (it_2.set(table_ref_2); !it_2.end_of_fields(); it_2.next())
+  {
+    nj_col_2= it_2.get_natural_column_ref();
+    if (!nj_col_2->is_common)
+      non_join_columns->push_back(nj_col_2);
+    else
+    {
+      /* Reset the common columns for the next call to mark_common_columns. */
+      nj_col_2->is_common= FALSE;
+    }
+  }
+
+  if (non_join_columns->elements > 0)
+    natural_using_join->join_columns->concat(non_join_columns);
+  natural_using_join->is_join_columns_complete= TRUE;
+
+  result= FALSE;
+
+err:
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  DBUG_RETURN(result);
+}
+
+
+/*
+  Precompute and store the row types of the top-most NATURAL/USING joins.
+
+  SYNOPSIS
+    store_top_level_join_columns()
+    thd            current thread
+    table_ref      nested join or table in a FROM clause
+    left_neighbor  neighbor table reference to the left of table_ref at the
+                   same level in the join tree
+    right_neighbor neighbor table reference to the right of table_ref at the
+                   same level in the join tree
+
+  DESCRIPTION
+    The procedure performs a post-order traversal of a nested join tree
+    and materializes the row types of NATURAL/USING joins in a
+    bottom-up manner until it reaches the TABLE_LIST elements that
+    represent the top-most NATURAL/USING joins. The procedure should be
+    applied to each element of SELECT_LEX::top_join_list (i.e. to each
+    top-level element of the FROM clause).
+
+  IMPLEMENTATION
+    Notice that the table references in the list nested_join->join_list
+    are in reverse order, thus when we iterate over it, we are moving
+    from the right to the left in the FROM clause.
+
+  RETURN
+    TRUE   Error
+    FALSE  OK
+*/
+
+static bool
+store_top_level_join_columns(THD *thd, TABLE_LIST *table_ref,
+                             TABLE_LIST *left_neighbor,
+                             TABLE_LIST *right_neighbor)
+{
+  Query_arena *arena, backup;
+  bool result= TRUE;
+
+  DBUG_ENTER("store_top_level_join_columns");
+
+  arena= thd->activate_stmt_arena_if_needed(&backup);
+
+  /* Call the procedure recursively for each nested table reference. */
+  if (table_ref->nested_join)
+  {
+    List_iterator_fast<TABLE_LIST> nested_it(table_ref->nested_join->join_list);
+    TABLE_LIST *cur_left_neighbor= nested_it++;
+    TABLE_LIST *cur_right_neighbor= NULL;
+
+    while (cur_left_neighbor)
+    {
+      TABLE_LIST *cur_table_ref= cur_left_neighbor;
+      cur_left_neighbor= nested_it++;
+      /*
+        The order of RIGHT JOIN operands is reversed in 'join list' to
+        transform it into a LEFT JOIN. However, in this procedure we need
+        the join operands in their lexical order, so below we reverse the
+        join operands. Notice that this happens only in the first loop, and
+        not in the second one, as in the second loop cur_left_neighbor == NULL.
+        This is the correct behavior, because the second loop
+        sets cur_table_ref reference correctly after the join operands are
+        swapped in the first loop.
+      */
+      if (cur_left_neighbor &&
+          cur_table_ref->outer_join & JOIN_TYPE_RIGHT)
+      {
+        /* This can happen only for JOIN ... ON. */
+        DBUG_ASSERT(table_ref->nested_join->join_list.elements == 2);
+        swap_variables(TABLE_LIST*, cur_left_neighbor, cur_table_ref);
+      }
+
+      if (cur_table_ref->nested_join &&
+          store_top_level_join_columns(thd, cur_table_ref,
+                                       cur_left_neighbor, cur_right_neighbor))
+        goto err;
+      cur_right_neighbor= cur_table_ref;
+    }
+  }
+
+  /*
+    If this is a NATURAL/USING join, materialize its result columns and
+    convert to a JOIN ... ON.
+  */
+  if (table_ref->is_natural_join)
+  {
+    DBUG_ASSERT(table_ref->nested_join &&
+                table_ref->nested_join->join_list.elements == 2);
+    List_iterator_fast<TABLE_LIST> operand_it(table_ref->nested_join->join_list);
+    /*
+      Notice that the order of join operands depends on whether table_ref
+      represents a LEFT or a RIGHT join. In a RIGHT join, the operands are
+      in inverted order.
+     */
+    TABLE_LIST *table_ref_2= operand_it++; /* Second NATURAL join operand.*/
+    TABLE_LIST *table_ref_1= operand_it++; /* First NATURAL join operand. */
+    List<String> *using_fields= table_ref->join_using_fields;
+    uint found_using_fields;
+
+    /*
+      The two join operands were interchanged in the parser, change the order
+      back for 'mark_common_columns'.
+    */
+    if (table_ref_2->outer_join & JOIN_TYPE_RIGHT)
+      swap_variables(TABLE_LIST*, table_ref_1, table_ref_2);
+    if (mark_common_columns(thd, table_ref_1, table_ref_2,
+                            using_fields, &found_using_fields))
+      goto err;
+
+    /*
+      Swap the join operands back, so that we pick the columns of the second
+      one as the coalesced columns. In this way the coalesced columns are the
+      same as of an equivalent LEFT JOIN.
+    */
+    if (table_ref_1->outer_join & JOIN_TYPE_RIGHT)
+      swap_variables(TABLE_LIST*, table_ref_1, table_ref_2);
+    if (store_natural_using_join_columns(thd, table_ref, table_ref_1,
+                                         table_ref_2, using_fields,
+                                         found_using_fields))
+      goto err;
+
+    /*
+      Change NATURAL JOIN to JOIN ... ON. We do this for both operands
+      because either one of them or the other is the one with the
+      natural join flag because RIGHT joins are transformed into LEFT,
+      and the two tables may be reordered.
+    */
+    table_ref_1->natural_join= table_ref_2->natural_join= NULL;
+
+    /* Add a TRUE condition to outer joins that have no common columns. */
+    if (table_ref_2->outer_join &&
+        !table_ref_1->on_expr && !table_ref_2->on_expr)
+      table_ref_2->on_expr= new Item_int((longlong) 1,1);   /* Always true. */
+
+    /* Change this table reference to become a leaf for name resolution. */
+    if (left_neighbor)
+    {
+      TABLE_LIST *last_leaf_on_the_left;
+      last_leaf_on_the_left= left_neighbor->last_leaf_for_name_resolution();
+      last_leaf_on_the_left->next_name_resolution_table= table_ref;
+    }
+    if (right_neighbor)
+    {
+      TABLE_LIST *first_leaf_on_the_right;
+      first_leaf_on_the_right= right_neighbor->first_leaf_for_name_resolution();
+      table_ref->next_name_resolution_table= first_leaf_on_the_right;
+    }
+    else
+      table_ref->next_name_resolution_table= NULL;
+  }
+  result= FALSE; /* All is OK. */
+
+err:
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  DBUG_RETURN(result);
+}
+
+
+/*
+  Compute and store the row types of the top-most NATURAL/USING joins
+  in a FROM clause.
+
+  SYNOPSIS
+    setup_natural_join_row_types()
+    thd          current thread
+    from_clause  list of top-level table references in a FROM clause
+
+  DESCRIPTION
+    Apply the procedure 'store_top_level_join_columns' to each of the
+    top-level table referencs of the FROM clause. Adjust the list of tables
+    for name resolution - context->first_name_resolution_table to the
+    top-most, lef-most NATURAL/USING join.
+
+  IMPLEMENTATION
+    Notice that the table references in 'from_clause' are in reverse
+    order, thus when we iterate over it, we are moving from the right
+    to the left in the FROM clause.
+
+  RETURN
+    TRUE   Error
+    FALSE  OK
+*/
+static bool setup_natural_join_row_types(THD *thd,
+                                         List<TABLE_LIST> *from_clause,
+                                         Name_resolution_context *context)
+{
+  thd->where= "from clause";
+  if (from_clause->elements == 0)
+    return FALSE; /* We come here in the case of UNIONs. */
+
+  /* For stored procedures do not redo work if already done. */
+  if (!context->select_lex->first_execution)
+    return FALSE;
+
+  List_iterator_fast<TABLE_LIST> table_ref_it(*from_clause);
+  TABLE_LIST *table_ref; /* Current table reference. */
+  /* Table reference to the left of the current. */
+  TABLE_LIST *left_neighbor;
+  /* Table reference to the right of the current. */
+  TABLE_LIST *right_neighbor= NULL;
+
+  /* Note that tables in the list are in reversed order */
+  for (left_neighbor= table_ref_it++; left_neighbor ; )
+  {
+    table_ref= left_neighbor;
+    left_neighbor= table_ref_it++;
+    if (store_top_level_join_columns(thd, table_ref,
+                                     left_neighbor, right_neighbor))
+      return TRUE;
+    if (left_neighbor)
+    {
+      TABLE_LIST *first_leaf_on_the_right;
+      first_leaf_on_the_right= table_ref->first_leaf_for_name_resolution();
+      left_neighbor->next_name_resolution_table= first_leaf_on_the_right;
+    }
+    right_neighbor= table_ref;
+  }
+
+  /*
+    Store the top-most, left-most NATURAL/USING join, so that we start
+    the search from that one instead of context->table_list. At this point
+    right_neighbor points to the left-most top-level table reference in the
+    FROM clause.
+  */
+  DBUG_ASSERT(right_neighbor);
+  context->first_name_resolution_table=
+    right_neighbor->first_leaf_for_name_resolution();
+
+  return FALSE;
+}
+
+
 /****************************************************************************
 ** Expand all '*' in given fields
 ****************************************************************************/
@@ -2455,27 +4147,29 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
 	       List<Item> *sum_func_list,
 	       uint wild_num)
 {
-  DBUG_ENTER("setup_wild");
   if (!wild_num)
-    DBUG_RETURN(0);
+    return(0);
 
-  reg2 Item *item;
+  Item *item;
   List_iterator<Item> it(fields);
-  Item_arena *arena, backup;
+  Query_arena *arena, backup;
+  DBUG_ENTER("setup_wild");
+
   /*
-    If we are in preparing prepared statement phase then we have change
-    temporary mem_root to statement mem root to save changes of SELECT list
+    Don't use arena if we are not in prepared statements or stored procedures
+    For PS/SP we have to use arena to remember the changes
   */
-  arena= thd->change_arena_if_needed(&backup);
+  arena= thd->activate_stmt_arena_if_needed(&backup);
 
   while (wild_num && (item= it++))
-  {    
+  {
     if (item->type() == Item::FIELD_ITEM &&
         ((Item_field*) item)->field_name &&
 	((Item_field*) item)->field_name[0] == '*' &&
 	!((Item_field*) item)->field)
     {
       uint elem= fields.elements;
+      bool any_privileges= ((Item_field *) item)->any_privileges;
       Item_subselect *subsel= thd->lex->current_select->master_unit()->item;
       if (subsel &&
           subsel->substype() == Item_subselect::EXISTS_SUBS)
@@ -2487,11 +4181,13 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
         */
         it.replace(new Item_int("Not_used", (longlong) 1, 21));
       }
-      else if (insert_fields(thd,tables,((Item_field*) item)->db_name,
-                             ((Item_field*) item)->table_name, &it))
+      else if (insert_fields(thd, ((Item_field*) item)->context,
+                             ((Item_field*) item)->db_name,
+                             ((Item_field*) item)->table_name, &it,
+                             any_privileges))
       {
-        if (arena)
-	  thd->restore_backup_item_arena(arena, &backup);
+	if (arena)
+	  thd->restore_active_arena(arena, &backup);
 	DBUG_RETURN(-1);
       }
       if (sum_func_list)
@@ -2507,7 +4203,14 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
     }
   }
   if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
+  {
+    /* make * substituting permanent */
+    SELECT_LEX *select_lex= thd->lex->current_select;
+    select_lex->with_wild= 0;
+    select_lex->item_list= fields;
+
+    thd->restore_active_arena(arena, &backup);
+  }
   DBUG_RETURN(0);
 }
 
@@ -2515,17 +4218,20 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
 ** Check that all given fields exists and fill struct with current data
 ****************************************************************************/
 
-int setup_fields(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, 
-		 List<Item> &fields, bool set_query_id,
-		 List<Item> *sum_func_list, bool allow_sum_func)
+bool setup_fields(THD *thd, Item **ref_pointer_array,
+                  List<Item> &fields, bool set_query_id,
+                  List<Item> *sum_func_list, bool allow_sum_func)
 {
   reg2 Item *item;
+  bool save_set_query_id= thd->set_query_id;
+  nesting_map save_allow_sum_func= thd->lex->allow_sum_func;
   List_iterator<Item> it(fields);
   DBUG_ENTER("setup_fields");
 
   thd->set_query_id=set_query_id;
-  thd->allow_sum_func= allow_sum_func;
-  thd->where="field list";
+  if (allow_sum_func)
+    thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
+  thd->where= THD::DEFAULT_WHERE;
 
   /*
     To prevent fail on forward lookup we fill it with zerows,
@@ -2544,51 +4250,119 @@ int setup_fields(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
   Item **ref= ref_pointer_array;
   while ((item= it++))
   {
-    if (!item->fixed && item->fix_fields(thd, tables, it.ref()) ||
+    if (!item->fixed && item->fix_fields(thd, it.ref()) ||
 	(item= *(it.ref()))->check_cols(1))
-      DBUG_RETURN(-1); /* purecov: inspected */
+    {
+      thd->lex->allow_sum_func= save_allow_sum_func;
+      thd->set_query_id= save_set_query_id;
+      DBUG_RETURN(TRUE); /* purecov: inspected */
+    }
     if (ref)
       *(ref++)= item;
     if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM &&
 	sum_func_list)
       item->split_sum_func(thd, ref_pointer_array, *sum_func_list);
-    thd->used_tables|=item->used_tables();
+    thd->used_tables|= item->used_tables();
   }
+  thd->lex->allow_sum_func= save_allow_sum_func;
+  thd->set_query_id= save_set_query_id;
   DBUG_RETURN(test(thd->net.report_error));
 }
 
 
 /*
+  make list of leaves of join table tree
+
+  SYNOPSIS
+    make_leaves_list()
+    list    pointer to pointer on list first element
+    tables  table list
+
+  RETURN pointer on pointer to next_leaf of last element
+*/
+
+TABLE_LIST **make_leaves_list(TABLE_LIST **list, TABLE_LIST *tables)
+{
+  for (TABLE_LIST *table= tables; table; table= table->next_local)
+  {
+    if (table->merge_underlying_list)
+    {
+      DBUG_ASSERT(table->view &&
+                  table->effective_algorithm == VIEW_ALGORITHM_MERGE);
+      list= make_leaves_list(list, table->merge_underlying_list);
+    }
+    else
+    {
+      *list= table;
+      list= &table->next_leaf;
+    }
+  }
+  return list;
+}
+
+/*
   prepare tables
 
   SYNOPSIS
     setup_tables()
-    tables	table list
-
+    thd		  Thread handler
+    context       name resolution contest to setup table list there
+    from_clause   Top-level list of table references in the FROM clause
+    tables	  Table list (select_lex->table_list)
+    conds	  Condition of current SELECT (can be changed by VIEW)
+    leaves        List of join table leaves list (select_lex->leaf_tables)
+    refresh       It is onle refresh for subquery
+    select_insert It is SELECT ... INSERT command
 
- NOTE
-   Remap table numbers if INSERT ... SELECT
-   Check also that the 'used keys' and 'ignored keys' exists and set up the
-   table structure accordingly
+  NOTE
+    Check also that the 'used keys' and 'ignored keys' exists and set up the
+    table structure accordingly.
+    Create a list of leaf tables. For queries with NATURAL/USING JOINs,
+    compute the row types of the top most natural/using join table references
+    and link these into a list of table references for name resolution.
 
-   This has to be called for all tables that are used by items, as otherwise
-   table->map is not set and all Item_field will be regarded as const items.
+    This has to be called for all tables that are used by items, as otherwise
+    table->map is not set and all Item_field will be regarded as const items.
 
- RETURN
-   0	ok;  In this case *map will includes the choosed index
-   1	error
+  RETURN
+    FALSE ok;  In this case *map will includes the chosen index
+    TRUE  error
 */
 
-bool setup_tables(TABLE_LIST *tables)
+bool setup_tables(THD *thd, Name_resolution_context *context,
+                  List<TABLE_LIST> *from_clause, TABLE_LIST *tables,
+                  Item **conds, TABLE_LIST **leaves, bool select_insert)
 {
+  uint tablenr= 0;
   DBUG_ENTER("setup_tables");
-  uint tablenr=0;
-  for (TABLE_LIST *table_list=tables ; table_list ;
-       table_list=table_list->next,tablenr++)
+
+  context->table_list= context->first_name_resolution_table= tables;
+
+  /*
+    this is used for INSERT ... SELECT.
+    For select we setup tables except first (and its underlying tables)
+  */
+  TABLE_LIST *first_select_table= (select_insert ?
+                                   tables->next_local:
+                                   0);
+  if (!(*leaves))
+    make_leaves_list(leaves, tables);
+
+  TABLE_LIST *table_list;
+  for (table_list= *leaves;
+       table_list;
+       table_list= table_list->next_leaf, tablenr++)
   {
     TABLE *table= table_list->table;
+    if (first_select_table &&
+        table_list->top_table() == first_select_table)
+    {
+      /* new counting for SELECT of INSERT ... SELECT command */
+      first_select_table= 0;
+      tablenr= 0;
+    }
     setup_table_map(table, table_list, tablenr);
-    table->used_keys= table->keys_for_keyread;
+    table->used_keys= table->s->keys_for_keyread;
     if (table_list->use_index)
     {
       key_map map;
@@ -2612,6 +4386,32 @@ bool setup_tables(TABLE_LIST *tables)
     my_error(ER_TOO_MANY_TABLES,MYF(0),MAX_TABLES);
     DBUG_RETURN(1);
   }
+  for (table_list= tables;
+       table_list;
+       table_list= table_list->next_local)
+  {
+    if (table_list->merge_underlying_list)
+    {
+      DBUG_ASSERT(table_list->view &&
+                  table_list->effective_algorithm == VIEW_ALGORITHM_MERGE);
+      Query_arena *arena= thd->stmt_arena, backup;
+      bool res;
+      if (arena->is_conventional())
+        arena= 0;                                   // For easier test
+      else
+        thd->set_n_backup_active_arena(arena, &backup);
+      res= table_list->setup_underlying(thd);
+      if (arena)
+        thd->restore_active_arena(arena, &backup);
+      if (res)
+        DBUG_RETURN(1);
+    }
+  }
+
+  /* Precompute and store the row types of NATURAL/USING joins. */
+  if (setup_natural_join_row_types(thd, from_clause, context))
+    DBUG_RETURN(1);
+
   DBUG_RETURN(0);
 }
 
@@ -2640,11 +4440,13 @@ bool get_key_map_from_key_list(key_map *map, TABLE *table,
   map->clear_all();
   while ((name=it++))
   {
-    if ((pos= find_type(&table->keynames, name->ptr(), name->length(), 1)) <=
-	0)
+    if (table->s->keynames.type_names == 0 ||
+        (pos= find_type(&table->s->keynames, name->ptr(),
+                        name->length(), 1)) <=
+        0)
     {
       my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), name->c_ptr(),
-	       table->real_name);
+	       table->s->table_name);
       map->set_all();
       return 1;
     }
@@ -2654,18 +4456,34 @@ bool get_key_map_from_key_list(key_map *map, TABLE *table,
 }
 
 
-/****************************************************************************
-  This just drops in all fields instead of current '*' field
-  Returns pointer to last inserted field if ok
-****************************************************************************/
+/*
+  Drops in all fields instead of current '*' field
+
+  SYNOPSIS
+    insert_fields()
+    thd			Thread handler
+    context             Context for name resolution
+    db_name		Database name in case of 'database_name.table_name.*'
+    table_name		Table name in case of 'table_name.*'
+    it			Pointer to '*'
+    any_privileges	0 If we should ensure that we have SELECT privileges
+		          for all columns
+                        1 If any privilege is ok
+  RETURN
+    0	ok     'it' is updated to point at last inserted
+    1	error.  Error message is generated but not sent to client
+*/
 
 bool
-insert_fields(THD *thd,TABLE_LIST *tables, const char *db_name,
-	      const char *table_name, List_iterator<Item> *it)
+insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
+	      const char *table_name, List_iterator<Item> *it,
+              bool any_privileges)
 {
+  Field_iterator_table_ref field_iterator;
+  bool found;
   char name_buff[NAME_LEN+1];
-  uint found;
   DBUG_ENTER("insert_fields");
+  DBUG_PRINT("arena", ("stmt arena: 0x%lx", (ulong)thd->stmt_arena));
 
   if (db_name && lower_case_table_names)
   {
@@ -2679,216 +4497,285 @@ insert_fields(THD *thd,TABLE_LIST *tables, const char *db_name,
     db_name= name_buff;
   }
 
+  found= FALSE;
 
-  found=0;
-  for (; tables ; tables=tables->next)
+  /*
+    If table names are qualified, then loop over all tables used in the query,
+    else treat natural joins as leaves and do not iterate over their underlying
+    tables.
+  */
+  for (TABLE_LIST *tables= (table_name ? context->table_list :
+                            context->first_name_resolution_table);
+       tables;
+       tables= (table_name ? tables->next_local :
+                tables->next_name_resolution_table)
+       )
   {
-    TABLE *table=tables->table;
-    if (!table_name || (!my_strcasecmp(table_alias_charset, table_name,
-				       tables->alias) &&
-			(!db_name || !strcmp(tables->db,db_name))))
-    {
+    Field *field;
+    TABLE *table= tables->table;
+
+    DBUG_ASSERT(tables->is_leaf_for_name_resolution());
+
+    if (table_name && my_strcasecmp(table_alias_charset, table_name,
+                                    tables->alias) ||
+        (db_name && strcmp(tables->db,db_name)))
+      continue;
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
-      /* Ensure that we have access right to all columns */
-      if (!(table->grant.privilege & SELECT_ACL) &&
-	  check_grant_all_columns(thd,SELECT_ACL,table))
-	DBUG_RETURN(-1);
+    /* Ensure that we have access rights to all fields to be inserted. */
+    if (!((table && (table->grant.privilege & SELECT_ACL) ||
+           tables->view && (tables->grant.privilege & SELECT_ACL))) &&
+        !any_privileges)
+    {
+      field_iterator.set(tables);
+      if (check_grant_all_columns(thd, SELECT_ACL, field_iterator.grant(),
+                                  field_iterator.db_name(),
+                                  field_iterator.table_name(),
+                                  &field_iterator))
+        DBUG_RETURN(TRUE);
+    }
 #endif
-      Field **ptr=table->field,*field;
-      TABLE *natural_join_table= 0;
 
-      thd->used_tables|=table->map;
-      if (!table->outer_join &&
-          tables->natural_join &&
-          !tables->natural_join->table->outer_join)
-        natural_join_table= tables->natural_join->table;
 
-      while ((field = *ptr++))
+    /*
+      Update the tables used in the query based on the referenced fields. For
+      views and natural joins this update is performed inside the loop below.
+    */
+    if (table)
+      thd->used_tables|= table->map;
+
+    /*
+      Initialize a generic field iterator for the current table reference.
+      Notice that it is guaranteed that this iterator will iterate over the
+      fields of a single table reference, because 'tables' is a leaf (for
+      name resolution purposes).
+    */
+    field_iterator.set(tables);
+
+    for (; !field_iterator.end_of_fields(); field_iterator.next())
+    {
+      Item *item;
+
+      if (!(item= field_iterator.create_item(thd)))
+        DBUG_RETURN(TRUE);
+
+      if (!found)
       {
-        uint not_used_field_index= NO_CACHED_FIELD_INDEX;
-        /* Skip duplicate field names if NATURAL JOIN is used */
-        if (!natural_join_table ||
-            !find_field_in_table(thd, natural_join_table, field->field_name, 
-                                 strlen(field->field_name), 0, 0,
-                                 &not_used_field_index))
+        found= TRUE;
+        it->replace(item); /* Replace '*' with the first found item. */
+      }
+      else
+        it->after(item);   /* Add 'item' to the SELECT list. */
+
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+      /*
+        Set privilege information for the fields of newly created views.
+        We have that (any_priviliges == TRUE) if and only if we are creating
+        a view. In the time of view creation we can't use the MERGE algorithm,
+        therefore if 'tables' is itself a view, it is represented by a
+        temporary table. Thus in this case we can be sure that 'item' is an
+        Item_field.
+      */
+      if (any_privileges)
+      {
+        DBUG_ASSERT(tables->field_translation == NULL && table ||
+                    tables->is_natural_join);
+        DBUG_ASSERT(item->type() == Item::FIELD_ITEM);
+        Item_field *fld= (Item_field*) item;
+        const char *field_table_name= field_iterator.table_name();
+
+        if (!tables->schema_table && 
+            !(fld->have_privileges=
+              (get_column_grant(thd, field_iterator.grant(),
+                                field_iterator.db_name(),
+                                field_table_name, fld->field_name) &
+               VIEW_ANY_ACL)))
         {
-          Item_field *item= new Item_field(thd, field);
-          if (!found++)
-            (void) it->replace(item);		// Replace '*'
-          else
-            it->after(item);
+          my_error(ER_COLUMNACCESS_DENIED_ERROR, MYF(0), "ANY",
+                   thd->security_ctx->priv_user,
+                   thd->security_ctx->host_or_ip,
+                   fld->field_name, field_table_name);
+          DBUG_RETURN(TRUE);
         }
-	/*
-	  Mark if field used before in this select.
-	  Used by 'insert' to verify if a field name is used twice
-	*/
-	if (field->query_id == thd->query_id)
-	  thd->dupp_field=field;
-	field->query_id=thd->query_id;
-	table->used_keys.intersect(field->part_of_key);
       }
-      /* All fields are used */
-      table->used_fields=table->fields;
+#endif
+
+      if ((field= field_iterator.field()))
+      {
+        /*
+          Mark if field used before in this select.
+          Used by 'insert' to verify if a field name is used twice.
+        */
+        if (field->query_id == thd->query_id)
+          thd->dupp_field= field;
+        field->query_id= thd->query_id;
+
+        if (table)
+          table->used_keys.intersect(field->part_of_key);
+
+        if (tables->is_natural_join)
+        {
+          TABLE *field_table;
+          /*
+            In this case we are sure that the column ref will not be created
+            because it was already created and stored with the natural join.
+          */
+          Natural_join_column *nj_col;
+          if (!(nj_col= field_iterator.get_natural_column_ref()))
+            DBUG_RETURN(TRUE);
+          DBUG_ASSERT(nj_col->table_field);
+          field_table= nj_col->table_ref->table;
+          if (field_table)
+          {
+            thd->used_tables|= field_table->map;
+            field_table->used_keys.intersect(field->part_of_key);
+            field_table->used_fields++;
+          }
+        }
+      }
+      else
+      {
+        thd->used_tables|= item->used_tables();
+        item->walk(&Item::reset_query_id_processor,
+                   (byte *)(&thd->query_id));
+      }
     }
+    /*
+      In case of stored tables, all fields are considered as used,
+      while in the case of views, the fields considered as used are the
+      ones marked in setup_tables during fix_fields of view columns.
+      For NATURAL joins, used_tables is updated in the IF above.
+    */
+    if (table)
+      table->used_fields= table->s->fields;
   }
-  if (!found)
-  {
-    if (!table_name)
-      my_error(ER_NO_TABLES_USED,MYF(0));
-    else
-      my_error(ER_BAD_TABLE_ERROR,MYF(0),table_name);
-  }
-  DBUG_RETURN(!found);
+  if (found)
+    DBUG_RETURN(FALSE);
+
+  /*
+    TODO: in the case when we skipped all columns because there was a
+    qualified '*', and all columns were coalesced, we have to give a more
+    meaningful message than ER_BAD_TABLE_ERROR.
+  */
+  if (!table_name)
+    my_message(ER_NO_TABLES_USED, ER(ER_NO_TABLES_USED), MYF(0));
+  else
+    my_error(ER_BAD_TABLE_ERROR, MYF(0), table_name);
+
+  DBUG_RETURN(TRUE);
 }
 
 
 /*
-** Fix all conditions and outer join expressions
+  Fix all conditions and outer join expressions.
+
+  SYNOPSIS
+    setup_conds()
+    thd     thread handler
+    tables  list of tables for name resolving (select_lex->table_list)
+    leaves  list of leaves of join table tree (select_lex->leaf_tables)
+    conds   WHERE clause
+
+  DESCRIPTION
+    TODO
+
+  RETURN
+    TRUE  if some error occured (e.g. out of memory)
+    FALSE if all is OK
 */
 
-int setup_conds(THD *thd,TABLE_LIST *tables,COND **conds)
+int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves,
+                COND **conds)
 {
-  table_map not_null_tables= 0;
-  Item_arena *arena= 0, backup;
+  SELECT_LEX *select_lex= thd->lex->current_select;
+  Query_arena *arena= thd->stmt_arena, backup;
+  TABLE_LIST *table= NULL;	// For HP compilers
+  /*
+    it_is_update set to TRUE when tables of primary SELECT_LEX (SELECT_LEX
+    which belong to LEX, i.e. most up SELECT) will be updated by
+    INSERT/UPDATE/LOAD
+    NOTE: using this condition helps to prevent call of prepare_check_option()
+    from subquery of VIEW, because tables of subquery belongs to VIEW
+    (see condition before prepare_check_option() call)
+  */
+  bool it_is_update= (select_lex == &thd->lex->select_lex) &&
+    thd->lex->which_check_option_applicable();
   DBUG_ENTER("setup_conds");
 
+  if (select_lex->conds_processed_with_permanent_arena ||
+      arena->is_conventional())
+    arena= 0;                                   // For easier test
+
   thd->set_query_id=1;
-  thd->lex->current_select->cond_count= 0;
+  select_lex->cond_count= 0;
+
+  for (table= tables; table; table= table->next_local)
+  {
+    if (table->prepare_where(thd, conds, FALSE))
+      goto err_no_arena;
+  }
+
   if (*conds)
   {
     thd->where="where clause";
-    if (!(*conds)->fixed && (*conds)->fix_fields(thd, tables, conds) ||
+    if (!(*conds)->fixed && (*conds)->fix_fields(thd, conds) ||
 	(*conds)->check_cols(1))
-      DBUG_RETURN(1);
-    not_null_tables= (*conds)->not_null_tables();
+      goto err_no_arena;
   }
 
-
-  /* Check if we are using outer joins */
-  for (TABLE_LIST *table=tables ; table ; table=table->next)
+  /*
+    Apply fix_fields() to all ON clauses at all levels of nesting,
+    including the ones inside view definitions.
+  */
+  for (table= leaves; table; table= table->next_leaf)
   {
-    if (table->on_expr)
+    TABLE_LIST *embedded; /* The table at the current level of nesting. */
+    TABLE_LIST *embedding= table; /* The parent nested table reference. */
+    do
     {
-      /* Make a join an a expression */
-      thd->where="on clause";
-      
-      if (!table->on_expr->fixed &&
-	  table->on_expr->fix_fields(thd, tables, &table->on_expr) ||
-	  table->on_expr->check_cols(1))
-	DBUG_RETURN(1);
-      thd->lex->current_select->cond_count++;
-
-      /*
-	If it's a normal join or a LEFT JOIN which can be optimized away
-	add the ON/USING expression to the WHERE
-      */
-      if (!table->outer_join ||
-	  ((table->table->map & not_null_tables) &&
-	   !(specialflag & SPECIAL_NO_NEW_FUNC)))
+      embedded= embedding;
+      if (embedded->on_expr)
       {
-	table->outer_join= 0;
-        arena= thd->change_arena_if_needed(&backup);
-	*conds= and_conds(*conds, table->on_expr);
-	table->on_expr=0;
-	if (arena)
-        {
-	  thd->restore_backup_item_arena(arena, &backup);
-          arena= 0;                             // Safety if goto err
-        }
-	if ((*conds) && !(*conds)->fixed &&
-	    (*conds)->fix_fields(thd, tables, conds))
-	  DBUG_RETURN(1);
+        /* Make a join an a expression */
+        thd->where="on clause";
+        if (!embedded->on_expr->fixed &&
+            embedded->on_expr->fix_fields(thd, &embedded->on_expr) ||
+	    embedded->on_expr->check_cols(1))
+	  goto err_no_arena;
+        select_lex->cond_count++;
       }
+      embedding= embedded->embedding;
     }
-    if (table->natural_join)
-    {
-      arena= thd->change_arena_if_needed(&backup);
-      /* Make a join of all fields with have the same name */
-      TABLE *t1= table->table;
-      TABLE *t2= table->natural_join->table;
-      Item_cond_and *cond_and= new Item_cond_and();
-      if (!cond_and)				// If not out of memory
-	goto err;
-      cond_and->top_level_item();
+    while (embedding &&
+           embedding->nested_join->join_list.head() == embedded);
 
-      Field **t1_field, *t2_field;
-      for (t1_field= t1->field; (*t1_field); t1_field++)
-      {
-        const char *t1_field_name= (*t1_field)->field_name;
-        uint not_used_field_index= NO_CACHED_FIELD_INDEX;
-
-        if ((t2_field= find_field_in_table(thd, t2, t1_field_name,
-                                           strlen(t1_field_name), 0, 0,
-                                           &not_used_field_index)))
-        {
-          Item_func_eq *tmp=new Item_func_eq(new Item_field(thd, *t1_field),
-                                             new Item_field(thd, t2_field));
-          if (!tmp)
-            goto err;
-          /* Mark field used for table cache */
-          (*t1_field)->query_id= t2_field->query_id= thd->query_id;
-          cond_and->list.push_back(tmp);
-          t1->used_keys.intersect((*t1_field)->part_of_key);
-          t2->used_keys.intersect(t2_field->part_of_key);
-        }
-      }
-      thd->lex->current_select->cond_count+= cond_and->list.elements;
-
-      // to prevent natural join processing during PS re-execution
-      table->natural_join= 0;
-
-      if (cond_and->list.elements)
-      {
-        if (!table->outer_join)			// Not left join
-        {
-          *conds= and_conds(*conds, cond_and);
-          // fix_fields() should be made with temporary memory pool
-          if (arena)
-            thd->restore_backup_item_arena(arena, &backup);
-          if (*conds && !(*conds)->fixed)
-          {
-            if (!(*conds)->fixed && 
-                (*conds)->fix_fields(thd, tables, conds))
-              DBUG_RETURN(1);
-          }
-        }
-        else
-        {
-          table->on_expr= and_conds(table->on_expr, cond_and);
-          // fix_fields() should be made with temporary memory pool
-          if (arena)
-            thd->restore_backup_item_arena(arena, &backup);
-          if (table->on_expr && !table->on_expr->fixed)
-          {
-            if (!table->on_expr->fixed && 
-                table->on_expr->fix_fields(thd, tables, &table->on_expr))
-             DBUG_RETURN(1);
-          }
-        }
-      }
-      else if (arena)
+    /* process CHECK OPTION */
+    if (it_is_update)
+    {
+      TABLE_LIST *view= table->top_table();
+      if (view->effective_with_check)
       {
-        thd->restore_backup_item_arena(arena, &backup);
-        arena= 0;                               // Safety if goto err
+        if (view->prepare_check_option(thd))
+          goto err_no_arena;
+        thd->change_item_tree(&table->check_option, view->check_option);
       }
     }
   }
 
-  if (thd->current_arena->is_stmt_prepare())
+  if (!thd->stmt_arena->is_conventional())
   {
     /*
       We are in prepared statement preparation code => we should store
       WHERE clause changing for next executions.
 
-      We do this ON -> WHERE transformation only once per PS statement.
+      We do this ON -> WHERE transformation only once per PS/SP statement.
     */
-    thd->lex->current_select->where= *conds;
+    select_lex->where= *conds;
+    select_lex->conds_processed_with_permanent_arena= 1;
   }
   DBUG_RETURN(test(thd->net.report_error));
 
-err:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
+err_no_arena:
   DBUG_RETURN(1);
 }
 
@@ -2898,8 +4785,25 @@ err:
 ** Returns : 1 if some field has wrong type
 ******************************************************************************/
 
-int
-fill_record(List<Item> &fields,List<Item> &values, bool ignore_errors)
+
+/*
+  Fill fields with given items.
+
+  SYNOPSIS
+    fill_record()
+    thd           thread handler
+    fields        Item_fields list to be filled
+    values        values to fill with
+    ignore_errors TRUE if we should ignore errors
+
+  RETURN
+    FALSE   OK
+    TRUE    error occured
+*/
+
+static bool
+fill_record(THD * thd, List<Item> &fields, List<Item> &values,
+            bool ignore_errors)
 {
   List_iterator_fast<Item> f(fields),v(values);
   Item *value;
@@ -2914,14 +4818,67 @@ fill_record(List<Item> &fields,List<Item> &values, bool ignore_errors)
     if (rfield == table->next_number_field)
       table->auto_increment_field_not_null= TRUE;
     if ((value->save_in_field(rfield, 0) < 0) && !ignore_errors)
-      DBUG_RETURN(1);
+    {
+      my_message(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR), MYF(0));
+      DBUG_RETURN(TRUE);
+    }
   }
-  DBUG_RETURN(0);
+  DBUG_RETURN(thd->net.report_error);
 }
 
 
-int
-fill_record(Field **ptr,List<Item> &values, bool ignore_errors)
+/*
+  Fill fields in list with values from the list of items and invoke
+  before triggers.
+
+  SYNOPSIS
+    fill_record_n_invoke_before_triggers()
+      thd           thread context
+      fields        Item_fields list to be filled
+      values        values to fill with
+      ignore_errors TRUE if we should ignore errors
+      triggers      object holding list of triggers to be invoked
+      event         event type for triggers to be invoked
+
+  NOTE
+    This function assumes that fields which values will be set and triggers
+    to be invoked belong to the same table, and that TABLE::record[0] and
+    record[1] buffers correspond to new and old versions of row respectively.
+
+  RETURN
+    FALSE   OK
+    TRUE    error occured
+*/
+
+bool
+fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields,
+                                     List<Item> &values, bool ignore_errors,
+                                     Table_triggers_list *triggers,
+                                     enum trg_event_type event)
+{
+  return (fill_record(thd, fields, values, ignore_errors) ||
+          triggers && triggers->process_triggers(thd, event,
+                                                 TRG_ACTION_BEFORE, TRUE));
+}
+
+
+/*
+  Fill field buffer with values from Field list
+
+  SYNOPSIS
+    fill_record()
+    thd           thread handler
+    ptr           pointer on pointer to record
+    values        list of fields
+    ignore_errors TRUE if we should ignore errors
+
+  RETURN
+    FALSE   OK
+    TRUE    error occured
+*/
+
+bool
+fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors)
 {
   List_iterator_fast<Item> v(values);
   Item *value;
@@ -2934,10 +4891,45 @@ fill_record(Field **ptr,List<Item> &values, bool ignore_errors)
     TABLE *table= field->table;
     if (field == table->next_number_field)
       table->auto_increment_field_not_null= TRUE;
-    if ((value->save_in_field(field, 0) < 0) && !ignore_errors)
-      DBUG_RETURN(1);
+    if (value->save_in_field(field, 0) == -1)
+      DBUG_RETURN(TRUE);
   }
-  DBUG_RETURN(0);
+  DBUG_RETURN(thd->net.report_error);
+}
+
+
+/*
+  Fill fields in array with values from the list of items and invoke
+  before triggers.
+
+  SYNOPSIS
+    fill_record_n_invoke_before_triggers()
+      thd           thread context
+      ptr           NULL-ended array of fields to be filled
+      values        values to fill with
+      ignore_errors TRUE if we should ignore errors
+      triggers      object holding list of triggers to be invoked
+      event         event type for triggers to be invoked
+
+  NOTE
+    This function assumes that fields which values will be set and triggers
+    to be invoked belong to the same table, and that TABLE::record[0] and
+    record[1] buffers correspond to new and old versions of row respectively.
+
+  RETURN
+    FALSE   OK
+    TRUE    error occured
+*/
+
+bool
+fill_record_n_invoke_before_triggers(THD *thd, Field **ptr,
+                                     List<Item> &values, bool ignore_errors,
+                                     Table_triggers_list *triggers,
+                                     enum trg_event_type event)
+{
+  return (fill_record(thd, ptr, values, ignore_errors) ||
+          triggers && triggers->process_triggers(thd, event,
+                                                 TRG_ACTION_BEFORE, TRUE));
 }
 
 
@@ -2985,34 +4977,31 @@ static void mysql_rm_tmp_tables(void)
 *****************************************************************************/
 
 /*
-** Invalidate any cache entries that are for some DB
-** We can't use hash_delete when looping hash_elements. We mark them first
-** and afterwards delete those marked unused.
+  Invalidate any cache entries that are for some DB
+
+  SYNOPSIS
+    remove_db_from_cache()
+    db		Database name. This will be in lower case if
+		lower_case_table_name is set
+
+  NOTE:
+  We can't use hash_delete when looping hash_elements. We mark them first
+  and afterwards delete those marked unused.
 */
 
 void remove_db_from_cache(const char *db)
 {
-  char name_buff[NAME_LEN+1];
-  if (db && lower_case_table_names)
-  {
-    /*
-      convert database to lower case for comparision.
-    */
-    strmake(name_buff, db, sizeof(name_buff)-1);
-    my_casedn_str(files_charset_info, name_buff);
-    db= name_buff;
-  }
   for (uint idx=0 ; idx < open_cache.records ; idx++)
   {
     TABLE *table=(TABLE*) hash_element(&open_cache,idx);
-    if (!strcmp(table->table_cache_key,db))
+    if (!strcmp(table->s->db, db))
     {
-      table->version=0L;			/* Free when thread is ready */
+      table->s->version= 0L;			/* Free when thread is ready */
       if (!table->in_use)
 	relink_unused(table);
     }
   }
-  while (unused_tables && !unused_tables->version)
+  while (unused_tables && !unused_tables->s->version)
     VOID(hash_delete(&open_cache,(byte*) unused_tables));
 }
 
@@ -3054,7 +5043,6 @@ bool remove_table_from_cache(THD *thd, const char *db, const char *table_name,
   bool result=0, signalled= 0;
   DBUG_ENTER("remove_table_from_cache");
 
-
   key_length=(uint) (strmov(strmov(key,db)+1,table_name)-key)+1;
   for (;;)
   {
@@ -3068,7 +5056,7 @@ bool remove_table_from_cache(THD *thd, const char *db, const char *table_name,
                                    &state))
     {
       THD *in_use;
-      table->version=0L;		/* Free when thread is ready */
+      table->s->version=0L;		/* Free when thread is ready */
       if (!(in_use=table->in_use))
       {
         DBUG_PRINT("info",("Table was not in use"));
@@ -3083,7 +5071,7 @@ bool remove_table_from_cache(THD *thd, const char *db, const char *table_name,
         if ((in_use->system_thread & SYSTEM_THREAD_DELAYED_INSERT) &&
             ! in_use->killed)
         {
-  	  in_use->killed=1;
+	  in_use->killed= THD::KILL_CONNECTION;
 	  pthread_mutex_lock(&in_use->mysys_var->mutex);
 	  if (in_use->mysys_var->current_cond)
 	  {
@@ -3109,7 +5097,7 @@ bool remove_table_from_cache(THD *thd, const char *db, const char *table_name,
       else
         result= result || (flags & RTFC_OWNED_BY_THD_FLAG);
     }
-    while (unused_tables && !unused_tables->version)
+    while (unused_tables && !unused_tables->s->version)
       VOID(hash_delete(&open_cache,(byte*) unused_tables));
     if (result && (flags & RTFC_WAIT_OTHER_THREAD_FLAG))
     {
@@ -3179,3 +5167,69 @@ int init_ftfuncs(THD *thd, SELECT_LEX *select_lex, bool no_order)
   }
   return 0;
 }
+
+
+/*
+  open new .frm format table
+
+  SYNOPSIS
+    open_new_frm()
+    THD		  thread handler
+    path	  path to .frm
+    alias	  alias for table
+    db            database
+    table_name    name of table
+    db_stat	  open flags (for example HA_OPEN_KEYFILE|HA_OPEN_RNDFILE..)
+		  can be 0 (example in ha_example_table)
+    prgflag	  READ_ALL etc..
+    ha_open_flags HA_OPEN_ABORT_IF_LOCKED etc..
+    outparam	  result table
+    table_desc	  TABLE_LIST descriptor
+    mem_root	  temporary MEM_ROOT for parsing
+*/
+
+static bool
+open_new_frm(THD *thd, const char *path, const char *alias,
+             const char *db, const char *table_name,
+             uint db_stat, uint prgflag,
+	     uint ha_open_flags, TABLE *outparam, TABLE_LIST *table_desc,
+	     MEM_ROOT *mem_root)
+{
+  LEX_STRING pathstr;
+  File_parser *parser;
+  DBUG_ENTER("open_new_frm");
+
+  pathstr.str=    (char*) path;
+  pathstr.length= strlen(path);
+
+  if ((parser= sql_parse_prepare(&pathstr, mem_root, 1)))
+  {
+    if (is_equal(&view_type, parser->type()))
+    {
+      if (table_desc == 0 || table_desc->required_type == FRMTYPE_TABLE)
+      {
+        my_error(ER_WRONG_OBJECT, MYF(0), db, table_name, "BASE TABLE");
+        goto err;
+      }
+      if (mysql_make_view(thd, parser, table_desc))
+        goto err;
+    }
+    else
+    {
+      /* only VIEWs are supported now */
+      my_error(ER_FRM_UNKNOWN_TYPE, MYF(0), path,  parser->type()->str);
+      goto err;
+    }
+    DBUG_RETURN(0);
+  }
+ 
+err:
+  bzero(outparam, sizeof(TABLE));	// do not run repair
+  DBUG_RETURN(1);
+}
+
+
+bool is_equal(const LEX_STRING *a, const LEX_STRING *b)
+{
+  return a->length == b->length && !strncmp(a->str, b->str, a->length);
+}