Merge bk-internal.mysql.com:/home/bk/mysql-5.0

into  mysql.com:/home/dlenev/src/mysql-5.0-is


mysql-test/r/information_schema.result:
  Auto merged
mysql-test/t/information_schema.test:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/sp.cc:
  Auto merged
sql/sp.h:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_prepare.cc:
  Auto merged
sql/sql_show.cc:
  Auto merged
mysql-test/r/sp.result:
  Manual merge
mysql-test/t/sp.test:
  Manual merge
sql/sql_class.h:
  Manual merge

15 files changed, 156 insertions, 106 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 74f0e3d9425..9f127181fc2 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -314,7 +314,7 @@ INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty');
 SHOW INDEX FROM t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t1	0	PRIMARY	1	Host	A	NULL	NULL	NULL		BTREE	
-t1	0	PRIMARY	2	User	A	3	NULL	NULL		BTREE	
+t1	0	PRIMARY	2	User	A	0	NULL	NULL		BTREE	
 ALTER TABLE t1 ENABLE KEYS;
 UNLOCK TABLES;
 CHECK TABLES t1;
@@ -338,7 +338,7 @@ INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
 SHOW INDEX FROM t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t1	0	PRIMARY	1	Host	A	NULL	NULL	NULL		BTREE	
-t1	0	PRIMARY	2	User	A	2	NULL	NULL		BTREE	
+t1	0	PRIMARY	2	User	A	0	NULL	NULL		BTREE	
 t1	1	Host	1	Host	A	NULL	NULL	NULL		BTREE	disabled
 ALTER TABLE t1 ENABLE KEYS;
 SHOW INDEX FROM t1;
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 5688d8c2145..e0fb15a83c5 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1,4 +1,4 @@
-DROP TABLE IF EXISTS t0,t1,t2;
+DROP TABLE IF EXISTS t0,t1,t2,t3,t5;
 show variables where variable_name like "skip_show_database";
 Variable_name	Value
 skip_show_database	OFF
@@ -30,6 +30,8 @@ create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
 create table test.t2(a int);
 create table t3(a int, KEY a_data (a));
 create table mysqltest.t4(a int);
+create table t5 (id int auto_increment primary key);
+insert into t5 values (10);
 create view v1 (c) as select table_name from information_schema.TABLES;
 select * from v1;
 c
@@ -70,6 +72,7 @@ t1
 t4
 t2
 t3
+t5
 v1
 select c,table_name from v1 
 inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
@@ -89,6 +92,7 @@ t1	t1
 t4	t4
 t2	t2
 t3	t3
+t5	t5
 select c,table_name from v1 
 left join information_schema.TABLES v2 on (v1.c=v2.table_name)
 where v1.c like "t%";
@@ -107,6 +111,7 @@ t1	t1
 t4	t4
 t2	t2
 t3	t3
+t5	t5
 select c, v2.table_name from v1
 right join information_schema.TABLES v2 on (v1.c=v2.table_name)
 where v1.c like "t%";
@@ -125,6 +130,7 @@ t1	t1
 t4	t4
 t2	t2
 t3	t3
+t5	t5
 select table_name from information_schema.TABLES
 where table_schema = "mysqltest" and table_name like "t%";
 table_name
@@ -140,10 +146,12 @@ show tables like 't%';
 Tables_in_test (t%)
 t2
 t3
+t5
 show table status;
 Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
 t2	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL		
 t3	MyISAM	10	Fixed	0	0	0	#	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL		
+t5	MyISAM	10	Fixed	1	7	7	#	2048	0	11	#	#	NULL	latin1_swedish_ci	NULL		
 v1	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	NULL	NULL	#	#	NULL	NULL	NULL	NULL	view
 show full columns from t3 like "a%";
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
@@ -177,7 +185,7 @@ where table_schema = 'mysqltest' and table_name = 'v1';
 table_name	column_name	privileges
 v1	c	select
 drop view v1, mysqltest.v1;
-drop tables mysqltest.t4, mysqltest.t1, t2, t3;
+drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
 drop database mysqltest;
 select * from information_schema.CHARACTER_SETS
 where CHARACTER_SET_NAME like 'latin1%';
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index e04f8fce5c4..2aea7be6c12 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -3076,4 +3076,13 @@ v1	v2	v3	v4	v5	v6	v7
 						NULL
 drop procedure bug8692|
 drop table t3|
+drop function if exists bug10055|
+create function bug10055(v char(255)) returns char(255) return lower(v)|
+select t.column_name, bug10055(t.column_name)
+from information_schema.columns as t
+where t.table_schema = 'test' and t.table_name = 't1'|
+column_name	bug10055(t.column_name)
+id	id
+data	data
+drop function bug10055|
 drop table t1,t2;
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index cf089f0089e..93b200b8a7c 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -5,7 +5,7 @@
 # show databases
 
 --disable_warnings
-DROP TABLE IF EXISTS t0,t1,t2;
+DROP TABLE IF EXISTS t0,t1,t2,t3,t5;
 --enable_warnings
 
 
@@ -30,6 +30,8 @@ create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
 create table test.t2(a int);
 create table t3(a int, KEY a_data (a));
 create table mysqltest.t4(a int);
+create table t5 (id int auto_increment primary key);
+insert into t5 values (10);
 create view v1 (c) as select table_name from information_schema.TABLES;
 select * from v1;
 
@@ -76,7 +78,7 @@ where table_schema = 'mysqltest' and table_name = 'v1';
 connection default;
 
 drop view v1, mysqltest.v1;
-drop tables mysqltest.t4, mysqltest.t1, t2, t3;
+drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
 drop database mysqltest;
 
 # Test for information_schema.CHARACTER_SETS &
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 2cd8ccda90c..017e1654c95 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -3854,6 +3854,20 @@ drop procedure bug8692|
 drop table t3|
 
 #
+# Bug#10055 "Using stored function with information_schema causes empty
+#            result set"
+#
+--disable_warnings
+drop function if exists bug10055|
+--enable_warnings
+create function bug10055(v char(255)) returns char(255) return lower(v)|
+# This select should not crash server and should return all fields in t1
+select t.column_name, bug10055(t.column_name)
+from information_schema.columns as t
+where t.table_schema = 'test' and t.table_name = 't1'|
+drop function bug10055|
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 241566b25c9..dae564a15c0 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -494,8 +494,7 @@ typedef Comp_creator* (*chooser_compare_func_creator)(bool invert);
 void free_items(Item *item);
 void cleanup_items(Item *item);
 class THD;
-void close_thread_tables(THD *thd, bool locked=0, bool skip_derived=0,
-                         TABLE *stopper= 0);
+void close_thread_tables(THD *thd, bool locked=0, bool skip_derived=0);
 bool check_one_table_access(THD *thd, ulong privilege,
 			   TABLE_LIST *tables);
 bool check_routine_access(THD *thd,ulong want_access,char *db,char *name,
@@ -933,10 +932,10 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves,
 int setup_ftfuncs(SELECT_LEX* select);
 int init_ftfuncs(THD *thd, SELECT_LEX* select, bool no_order);
 void wait_for_refresh(THD *thd);
-int open_tables(THD *thd, TABLE_LIST **tables, uint *counter);
+int open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags);
 int simple_open_n_lock_tables(THD *thd,TABLE_LIST *tables);
 bool open_and_lock_tables(THD *thd,TABLE_LIST *tables);
-bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables);
+bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags);
 int lock_tables(THD *thd, TABLE_LIST *tables, uint counter);
 TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
 			    const char *table_name, bool link_in_list);
diff --git a/sql/sp.cc b/sql/sp.cc
index 297e17de689..0eee0ac209c 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -68,13 +68,16 @@ bool mysql_proc_table_exists= 1;
 
   SYNOPSIS
     close_proc_table()
-      thd  Thread context
+      thd     Thread context
+      backup  Pointer to Open_tables_state instance which holds
+              information about tables which were open before we
+              decided to access mysql.proc.
 */
 
-static void close_proc_table(THD *thd)
+void close_proc_table(THD *thd, Open_tables_state *backup)
 {
   close_thread_tables(thd);
-  thd->pop_open_tables_state();
+  thd->restore_backup_open_tables_state(backup);
 }
 
 
@@ -83,7 +86,10 @@ static void close_proc_table(THD *thd)
 
   SYNOPSIS
     open_proc_table_for_read()
-      thd  Thread context
+      thd     Thread context
+      backup  Pointer to Open_tables_state instance where information about
+              currently open tables will be saved, and from which will be
+              restored when we will end work with mysql.proc.
 
   NOTES
     Thanks to restrictions which we put on opening and locking of
@@ -97,11 +103,10 @@ static void close_proc_table(THD *thd)
     #	Pointer to TABLE object of mysql.proc
 */
 
-static TABLE *open_proc_table_for_read(THD *thd)
+TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
 {
   TABLE_LIST tables;
   TABLE *table;
-  bool old_open_tables= thd->open_tables != 0;
   bool refresh;
   DBUG_ENTER("open_proc_table");
 
@@ -112,8 +117,7 @@ static TABLE *open_proc_table_for_read(THD *thd)
   if (!mysql_proc_table_exists)
     DBUG_RETURN(0);
 
-  if (thd->push_open_tables_state())
-    DBUG_RETURN(0);
+  thd->reset_n_backup_open_tables_state(backup);
 
   bzero((char*) &tables, sizeof(tables));
   tables.db= (char*) "mysql";
@@ -121,7 +125,7 @@ static TABLE *open_proc_table_for_read(THD *thd)
   if (!(table= open_table(thd, &tables, thd->mem_root, &refresh,
                           MYSQL_LOCK_IGNORE_FLUSH)))
   {
-    thd->pop_open_tables_state();
+    thd->restore_backup_open_tables_state(backup);
     mysql_proc_table_exists= 0;
     DBUG_RETURN(0);
   }
@@ -130,15 +134,13 @@ static TABLE *open_proc_table_for_read(THD *thd)
 
   table->reginfo.lock_type= TL_READ;
   /*
-    If we have other tables opened, we have to ensure we are not blocked
-    by a flush tables or global read lock, as this could lead to a deadlock
+    We have to ensure we are not blocked by a flush tables, as this
+    could lead to a deadlock if we have other tables opened.
   */
   if (!(thd->lock= mysql_lock_tables(thd, &table, 1,
-                                     old_open_tables ?
-                                     (MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK |
-                                      MYSQL_LOCK_IGNORE_FLUSH) : 0)))
+                                     MYSQL_LOCK_IGNORE_FLUSH)))
   {
-    close_proc_table(thd);
+    close_proc_table(thd, backup);
     DBUG_RETURN(0);
   }
   DBUG_RETURN(table);
@@ -271,12 +273,13 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
   char buff[65];
   String str(buff, sizeof(buff), &my_charset_bin);
   ulong sql_mode;
+  Open_tables_state open_tables_state_backup;
   DBUG_ENTER("db_find_routine");
   DBUG_PRINT("enter", ("type: %d name: %*s",
 		       type, name->m_name.length, name->m_name.str));
 
   *sphp= 0;                                     // In case of errors
-  if (!(table= open_proc_table_for_read(thd)))
+  if (!(table= open_proc_table_for_read(thd, &open_tables_state_backup)))
     DBUG_RETURN(SP_OPEN_TABLE_FAILED);
 
   if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
@@ -371,7 +374,7 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
   chistics.comment.str= ptr;
   chistics.comment.length= length;
 
-  close_proc_table(thd);
+  close_proc_table(thd, &open_tables_state_backup);
   table= 0;
 
   {
@@ -449,7 +452,7 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
 
  done:
   if (table)
-    close_proc_table(thd);
+    close_proc_table(thd, &open_tables_state_backup);
   DBUG_RETURN(ret);
 }
 
diff --git a/sql/sp.h b/sql/sp.h
index 58244c34761..9f110f87807 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -94,6 +94,13 @@ void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
 
 extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first);
 
+/*
+  Routines which allow open/lock and close mysql.proc table even when
+  we already have some tables open and locked.
+*/
+TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup);
+void close_proc_table(THD *thd, Open_tables_state *backup);
+
 //
 // Utilities...
 //
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 5ff5e580f81..0895901508d 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -404,8 +404,7 @@ static void mark_used_tables_as_free_for_reuse(THD *thd, TABLE *table)
     upper level) and will leave prelocked mode if needed.
 */
 
-void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived, 
-                         TABLE *stopper)
+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;
@@ -512,7 +511,7 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived,
   DBUG_PRINT("info", ("thd->open_tables: %p", thd->open_tables));
 
  found_old_table= 0;
-  while (thd->open_tables != stopper)
+  while (thd->open_tables)
     found_old_table|=close_thread_table(thd, &thd->open_tables);
   thd->some_tables_deleted=0;
 
@@ -1804,6 +1803,9 @@ err:
     thd - thread handler
     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
@@ -1821,7 +1823,7 @@ err:
     -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;
@@ -1900,7 +1902,7 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
     (*counter)++;
     
     if (!tables->table &&
-	!(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, 0)))
+	!(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, flags)))
     {
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
 
@@ -2143,7 +2145,8 @@ int simple_open_n_lock_tables(THD *thd, TABLE_LIST *tables)
 {
   DBUG_ENTER("simple_open_n_lock_tables");
   uint counter;
-  if (open_tables(thd, &tables, &counter) || lock_tables(thd, tables, counter))
+  if (open_tables(thd, &tables, &counter, 0) ||
+      lock_tables(thd, tables, counter))
     DBUG_RETURN(-1);				/* purecov: inspected */
   DBUG_RETURN(0);
 }
@@ -2170,7 +2173,7 @@ bool open_and_lock_tables(THD *thd, TABLE_LIST *tables)
 {
   uint counter;
   DBUG_ENTER("open_and_lock_tables");
-  if (open_tables(thd, &tables, &counter) ||
+  if (open_tables(thd, &tables, &counter, 0) ||
       lock_tables(thd, tables, counter) ||
       mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
       (thd->fill_derived_tables() &&
@@ -2187,6 +2190,9 @@ bool 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
@@ -2197,12 +2203,12 @@ bool open_and_lock_tables(THD *thd, TABLE_LIST *tables)
     data from the tables.
 */
 
-bool 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");
   DBUG_ASSERT(!thd->fill_derived_tables());
-  if (open_tables(thd, &tables, &counter) ||
+  if (open_tables(thd, &tables, &counter, flags) ||
       mysql_handle_derived(thd->lex, &mysql_derived_prepare))
     DBUG_RETURN(TRUE); /* purecov: inspected */
   DBUG_RETURN(0);
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 56d3194765b..21df4640f3f 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -157,8 +157,8 @@ bool foreign_key_prefix(Key *a, Key *b)
 ** Thread specific functions
 ****************************************************************************/
 
-Open_tables_state::Open_tables_state()
-  :version(refresh_version)
+Open_tables_state::Open_tables_state(ulong version_arg)
+  :version(version_arg)
 {
   reset_open_tables_state();
 }
@@ -172,7 +172,7 @@ Open_tables_state::Open_tables_state()
 
 THD::THD()
   :Statement(CONVENTIONAL_EXECUTION, 0, ALLOC_ROOT_MIN_BLOCK_SIZE, 0),
-   Open_tables_state(),
+   Open_tables_state(refresh_version),
    lock_id(&main_lock_id),
    user_time(0), in_sub_stmt(FALSE), global_read_lock(0), is_fatal_error(0),
    rand_used(0), time_zone_used(0),
@@ -1813,31 +1813,26 @@ void THD::set_status_var_init()
   access to mysql.proc table to find definitions of stored routines.
 ****************************************************************************/
 
-bool THD::push_open_tables_state()
+void THD::reset_n_backup_open_tables_state(Open_tables_state *backup)
 {
-  Open_tables_state *state;
-  DBUG_ENTER("push_open_table_state");
-  /* Currently we only push things one level */
-  DBUG_ASSERT(open_state_list.elements == 0);
-
-  if (!(state= (Open_tables_state*) alloc(sizeof(*state))))
-    DBUG_RETURN(1);                             // Fatal error is set
-  /* Store state for currently open tables */
-  state->set_open_tables_state(this);
-  if (open_state_list.push_back(state, mem_root))
-    DBUG_RETURN(1);                             // Fatal error is set
+  DBUG_ENTER("reset_n_backup_open_tables_state");
+  backup->set_open_tables_state(this);
   reset_open_tables_state();
-  DBUG_RETURN(0);
+  DBUG_VOID_RETURN;
 }
 
-void THD::pop_open_tables_state()
-{
-  Open_tables_state *state;
-  DBUG_ENTER("pop_open_table_state");
-  /* Currently we only push things one level */
-  DBUG_ASSERT(open_state_list.elements == 1);
 
-  state= open_state_list.pop();
-  set_open_tables_state(state);
+void THD::restore_backup_open_tables_state(Open_tables_state *backup)
+{
+  DBUG_ENTER("restore_backup_open_tables_state");
+  /*
+    Before we will throw away current open tables state we want
+    to be sure that it was properly cleaned up.
+  */
+  DBUG_ASSERT(open_tables == 0 && temporary_tables == 0 &&
+              handler_tables == 0 && derived_tables == 0 &&
+              lock == 0 && locked_tables == 0 &&
+              prelocked_mode == NON_PRELOCKED);
+  set_open_tables_state(backup);
   DBUG_VOID_RETURN;
 }
diff --git a/sql/sql_class.h b/sql/sql_class.h
index ad4f38946a3..3900b25626f 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1009,7 +1009,13 @@ public:
   ulong	version;
   uint current_tablenr;
 
-  Open_tables_state();
+  /*
+    This constructor serves for creation of Open_tables_state instances
+    which are used as backup storage.
+  */
+  Open_tables_state() {};
+
+  Open_tables_state(ulong version_arg);
 
   void set_open_tables_state(Open_tables_state *state)
   {
@@ -1231,7 +1237,6 @@ public:
   List	     <MYSQL_ERROR> warn_list;
   uint	     warn_count[(uint) MYSQL_ERROR::WARN_LEVEL_END];
   uint	     total_warn_count;
-  List	     <Open_tables_state> open_state_list;
   /*
     Id of current query. Statement can be reused to execute several queries
     query_id is global in context of the whole MySQL server.
@@ -1487,8 +1492,8 @@ public:
   void set_status_var_init();
   bool is_context_analysis_only()
     { return current_arena->is_stmt_prepare() || lex->view_prepare_mode; }
-  bool push_open_tables_state();
-  void pop_open_tables_state();
+  void reset_n_backup_open_tables_state(Open_tables_state *backup);
+  void restore_backup_open_tables_state(Open_tables_state *backup);
 };
 
 
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index e109600bcd0..169132e2185 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -187,7 +187,7 @@ bool mysql_ha_open(THD *thd, TABLE_LIST *tables, bool reopen)
 
   /* for now HANDLER can be used only for real TABLES */
   tables->required_type= FRMTYPE_TABLE;
-  error= open_tables(thd, &tables, &counter);
+  error= open_tables(thd, &tables, &counter, 0);
 
   HANDLER_TABLES_HACK(thd);
   if (error)
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 0861bd1b0b2..c196bf76dda 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -928,7 +928,7 @@ static bool mysql_test_insert(Prepared_statement *stmt,
     If we would use locks, then we have to ensure we are not using
     TL_WRITE_DELAYED as having two such locks can cause table corruption.
   */
-  if (open_normal_and_derived_tables(thd, table_list))
+  if (open_normal_and_derived_tables(thd, table_list, 0))
     goto error;
 
   if ((values= its++))
@@ -1008,7 +1008,7 @@ static int mysql_test_update(Prepared_statement *stmt,
   if (update_precheck(thd, table_list))
     goto error;
 
-  if (open_tables(thd, &table_list, &table_count))
+  if (open_tables(thd, &table_list, &table_count, 0))
     goto error;
 
   if (table_list->multitable_view)
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index d2d453e55dd..1c0519475b4 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -20,6 +20,7 @@
 #include "mysql_priv.h"
 #include "sql_select.h"                         // For select_describe
 #include "repl_failsafe.h"
+#include "sp.h"
 #include "sp_head.h"
 #include "sql_trigger.h"
 #include <my_dir.h>
@@ -352,7 +353,7 @@ mysqld_show_create(THD *thd, TABLE_LIST *table_list)
   thd->lex->view_prepare_mode= TRUE;
 
   /* Only one table for now, but VIEW can involve several tables */
-  if (open_normal_and_derived_tables(thd, table_list))
+  if (open_normal_and_derived_tables(thd, table_list, 0))
   {
     DBUG_RETURN(TRUE);
   }
@@ -551,7 +552,7 @@ mysqld_list_fields(THD *thd, TABLE_LIST *table_list, const char *wild)
   DBUG_ENTER("mysqld_list_fields");
   DBUG_PRINT("enter",("table: %s",table_list->table_name));
 
-  if (open_normal_and_derived_tables(thd, table_list))
+  if (open_normal_and_derived_tables(thd, table_list, 0))
     DBUG_VOID_RETURN;
   table= table_list->table;
 
@@ -1936,6 +1937,8 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
   TABLE *table= tables->table;
   SELECT_LEX *select_lex= &lex->select_lex;
   SELECT_LEX *old_all_select_lex= lex->all_selects_list;
+  TABLE_LIST **save_query_tables_last= lex->query_tables_last;
+  enum_sql_command save_sql_command= lex->sql_command;
   SELECT_LEX *lsel= tables->schema_select_lex;
   ST_SCHEMA_TABLE *schema_table= tables->schema_table;
   SELECT_LEX sel;
@@ -1944,40 +1947,49 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
   uint len;
   bool with_i_schema;
   enum enum_schema_tables schema_table_idx;
-  thr_lock_type lock_type;
   List<char> bases;
   List_iterator_fast<char> it(bases);
   COND *partial_cond; 
   uint derived_tables= lex->derived_tables; 
   int error= 1;
+  Open_tables_state open_tables_state_backup;
   DBUG_ENTER("get_all_tables");
 
   LINT_INIT(end);
   LINT_INIT(len);
 
+  /*
+    Let us set fake sql_command so views won't try to merge
+    themselves into main statement.
+  */
+  lex->sql_command= SQLCOM_SHOW_FIELDS;
+
+  /*
+    We should not introduce deadlocks even if we already have some
+    tables open and locked, since we won't lock tables which we will
+    open and will ignore possible name-locks for these tables.
+  */
+  thd->reset_n_backup_open_tables_state(&open_tables_state_backup);
+
   if (lsel)
   {
-    TABLE *old_open_tables= thd->open_tables;
     TABLE_LIST *show_table_list= (TABLE_LIST*) lsel->table_list.first;
     bool res;
 
     lex->all_selects_list= lsel;
-    res= open_normal_and_derived_tables(thd, show_table_list);
+    res= open_normal_and_derived_tables(thd, show_table_list,
+                                        MYSQL_LOCK_IGNORE_FLUSH);
     if (schema_table->process_table(thd, show_table_list,
                                     table, res, show_table_list->db,
                                     show_table_list->alias))
       goto err;
-    close_thread_tables(thd, 0, 0, old_open_tables);
+    close_thread_tables(thd);
     show_table_list->table= 0;
     error= 0;
     goto err;
   }
 
   schema_table_idx= get_schema_table_idx(schema_table);
-  lock_type= TL_UNLOCK;
-
-  if (schema_table_idx == SCH_TABLES)
-    lock_type= TL_READ;
 
   if (make_db_list(thd, &bases, &idx_field_vals,
                    &with_i_schema, 0))
@@ -2060,19 +2072,18 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
           else
           {
             int res;
-            TABLE *old_open_tables= thd->open_tables;
             if (make_table_list(thd, &sel, base_name, file_name))
               goto err;
             TABLE_LIST *show_table_list= (TABLE_LIST*) sel.table_list.first;
-            show_table_list->lock_type= lock_type;
             lex->all_selects_list= &sel;
             lex->derived_tables= 0;
-            res= open_normal_and_derived_tables(thd, show_table_list);
+            res= open_normal_and_derived_tables(thd, show_table_list,
+                                                MYSQL_LOCK_IGNORE_FLUSH);
             if (schema_table->process_table(thd, show_table_list, table,
                                             res, base_name,
                                             show_table_list->alias))
               goto err;
-            close_thread_tables(thd, 0, 0, old_open_tables);
+            close_thread_tables(thd);
           }
         }
       }
@@ -2086,8 +2097,12 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
 
   error= 0;
 err:
+  thd->restore_backup_open_tables_state(&open_tables_state_backup);
   lex->derived_tables= derived_tables;
   lex->all_selects_list= old_all_select_lex;
+  lex->query_tables_last= save_query_tables_last;
+  *save_query_tables_last= 0;
+  lex->sql_command= save_sql_command;
   DBUG_RETURN(error);
 }
 
@@ -2192,7 +2207,8 @@ static int get_schema_tables_record(THD *thd, struct st_table_list *tables,
     TABLE_SHARE *share= show_table->s;
     handler *file= show_table->file;
 
-    file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK);
+    file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_AUTO |
+               HA_STATUS_NO_LOCK);
     if (share->tmp_table == TMP_TABLE)
       table->field[3]->store("TEMPORARY", 9, cs);
     else
@@ -2248,13 +2264,8 @@ static int get_schema_tables_record(THD *thd, struct st_table_list *tables,
     table->field[12]->store((longlong) file->delete_length);
     if (show_table->found_next_number_field)
     {
-      show_table->next_number_field=show_table->found_next_number_field;
-      show_table->next_number_field->reset();
-      file->update_auto_increment();
-      table->field[13]->store((longlong) show_table->
-                              next_number_field->val_int());
+      table->field[13]->store((longlong) file->auto_increment_value);
       table->field[13]->set_notnull();
-      show_table->next_number_field=0;
     }
     if (file->create_time)
     {
@@ -2727,12 +2738,14 @@ int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
   TABLE_LIST proc_tables;
   const char *wild= thd->lex->wild ? thd->lex->wild->ptr() : NullS;
   int res= 0;
-  TABLE *table= tables->table, *old_open_tables= thd->open_tables;
+  TABLE *table= tables->table;
   bool full_access;
   char definer[HOSTNAME_LENGTH+USERNAME_LENGTH+2];
+  Open_tables_state open_tables_state_backup;
   DBUG_ENTER("fill_schema_proc");
 
   strxmov(definer, thd->priv_user, "@", thd->priv_host, NullS);
+  /* We use this TABLE_LIST instance only for checking of privileges. */
   bzero((char*) &proc_tables,sizeof(proc_tables));
   proc_tables.db= (char*) "mysql";
   proc_tables.db_length= 5;
@@ -2740,7 +2753,7 @@ int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
   proc_tables.table_name_length= 4;
   proc_tables.lock_type= TL_READ;
   full_access= !check_table_access(thd, SELECT_ACL, &proc_tables, 1);
-  if (!(proc_table= open_ltable(thd, &proc_tables, TL_READ)))
+  if (!(proc_table= open_proc_table_for_read(thd, &open_tables_state_backup)))
   {
     DBUG_RETURN(1);
   }
@@ -2766,7 +2779,7 @@ int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
 
 err:
   proc_table->file->ha_index_end();
-  close_thread_tables(thd, 0, 0, old_open_tables);
+  close_proc_table(thd, &open_tables_state_backup);
   DBUG_RETURN(res);
 }
 
@@ -3664,11 +3677,6 @@ bool get_schema_tables_result(JOIN *join)
     TABLE_LIST *table_list= tab->table->pos_in_table_list;
     if (table_list->schema_table && thd->fill_derived_tables())
     {
-      TABLE_LIST **query_tables_last= lex->query_tables_last;
-      TABLE *old_derived_tables= thd->derived_tables;
-      MYSQL_LOCK *sql_lock= thd->lock;
-      lex->sql_command= SQLCOM_SHOW_FIELDS;
-      DBUG_ASSERT(!*query_tables_last);
       if (&lex->unit != lex->current_select->master_unit()) // is subselect
       {
         table_list->table->file->extra(HA_EXTRA_RESET_STATE);
@@ -3679,16 +3687,9 @@ bool get_schema_tables_result(JOIN *join)
       else
         table_list->table->file->records= 0;
 
-      thd->derived_tables= 0;
-      thd->lock=0;
       if (table_list->schema_table->fill_table(thd, table_list,
                                                tab->select_cond))
         result= 1;
-      thd->lock= sql_lock;
-      lex->sql_command= SQLCOM_SELECT;
-      thd->derived_tables= old_derived_tables;
-      lex->query_tables_last= query_tables_last;
-      *query_tables_last= 0;
     }
   }
   thd->no_warnings_for_error= 0;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 8a5b4ad8eae..6deff6b0040 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -138,7 +138,7 @@ int mysql_update(THD *thd,
 
   LINT_INIT(timestamp_query_id);
 
-  if (open_tables(thd, &table_list, &table_count))
+  if (open_tables(thd, &table_list, &table_count, 0))
     DBUG_RETURN(1);
 
   if (table_list->multitable_view)
@@ -632,7 +632,8 @@ bool mysql_multi_update_prepare(THD *thd)
   thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
 
   /* open tables and create derived ones, but do not lock and fill them */
-  if ((original_multiupdate && open_tables(thd, &table_list, & table_count)) ||
+  if ((original_multiupdate &&
+       open_tables(thd, &table_list, &table_count, 0)) ||
       mysql_handle_derived(lex, &mysql_derived_prepare))
     DBUG_RETURN(TRUE);
   /*