Fixed that derived tables are properly droped

9 files changed, 117 insertions, 66 deletions

diff --git a/sql/item_row.cc b/sql/item_row.cc
index c62ab60c0cd..355228e45df 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -18,7 +18,7 @@
 #include "assert.h"
 
 Item_row::Item_row(List<Item> &arg):
-  Item(), array_holder(1), used_tables_cache(0), const_item_cache(1)
+  Item(), used_tables_cache(0), array_holder(1), const_item_cache(1)
 {
 
   //TODO: think placing 2-3 component items in item (as it done for function)
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 1e0f7884176..655e4d7b972 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -579,8 +579,7 @@ bool rm_temporary_table(enum db_type base, char *path);
 void free_io_cache(TABLE *entry);
 void intern_close_table(TABLE *entry);
 bool close_thread_table(THD *thd, TABLE **table_ptr);
-void close_thread_tables(THD *thd,bool locked=0);
-bool close_thread_table(THD *thd, TABLE **table_ptr);
+void close_thread_tables(THD *thd, bool locked=0, bool skip_derived=0);
 void close_temporary_tables(THD *thd);
 TABLE_LIST * find_table_in_list(TABLE_LIST *table,
 				const char *db_name, const char *table_name);
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 08016d2df8c..b5178076258 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -19,6 +19,7 @@
 
 #include "mysql_priv.h"
 #include "sql_acl.h"
+#include "sql_select.h"
 #include <m_ctype.h>
 #include <my_dir.h>
 #include <hash.h>
@@ -307,7 +308,8 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
     close_old_data_files(thd,thd->open_tables,1,1);
     bool found=1;
     /* Wait until all threads has closed all the tables we had locked */
-    DBUG_PRINT("info", ("Waiting for others threads to close their open tables"));
+    DBUG_PRINT("info",
+	       ("Waiting for others threads to close their open tables"));
     while (found && ! thd->killed)
     {
       found=0;
@@ -348,12 +350,40 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
 }
 
 
-/* Put all tables used by thread in free list */
+/*
+  Close all tables used by thread
 
-void close_thread_tables(THD *thd, bool locked)
+  SYNOPSIS
+    close_thread_tables()
+    thd			Thread handler
+    lock_in_use		Set to 1 (0 = default) if caller has a lock on
+			LOCK_open
+    skip_derived	Set to 1 (0 = default) if we should not free derived
+			tables.
+
+  IMPLEMENTATION
+    Unlocks tables and frees derived tables.
+    Put all normal tables used by thread in free list.
+*/
+
+void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived)
 {
   DBUG_ENTER("close_thread_tables");
 
+  if (thd->derived_tables && !skip_derived)
+  {
+    TABLE *table, *next;
+    /*
+      Close all derived tables generated from questions like
+      SELECT * from (select * from t1))
+    */
+    for (table= thd->derived_tables ; table ; table= next)
+    {
+      next= table->next;
+      free_tmp_table(thd, table);
+    }
+    thd->derived_tables= 0;
+  }
   if (thd->locked_tables)
   {
     ha_commit_stmt(thd);			// If select statement
@@ -364,10 +394,11 @@ void close_thread_tables(THD *thd, bool locked)
 
   if (thd->lock)
   {
-    mysql_unlock_tables(thd, thd->lock); thd->lock=0;
+    mysql_unlock_tables(thd, thd->lock);
+    thd->lock=0;
   }
   /* VOID(pthread_sigmask(SIG_SETMASK,&thd->block_signals,NULL)); */
-  if (!locked)
+  if (!lock_in_use)
     VOID(pthread_mutex_lock(&LOCK_open));
   safe_mutex_assert_owner(&LOCK_open);
 
@@ -386,7 +417,7 @@ void close_thread_tables(THD *thd, bool locked)
     /* Tell threads waiting for refresh that something has happened */
     VOID(pthread_cond_broadcast(&COND_refresh));
   }
-  if (!locked)
+  if (!lock_in_use)
     VOID(pthread_mutex_unlock(&LOCK_open));
   /*  VOID(pthread_sigmask(SIG_SETMASK,&thd->signals,NULL)); */
   DBUG_VOID_RETURN;
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 3ca1f4827ff..ab789370660 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -90,7 +90,7 @@ THD::THD():user_time(0), fatal_error(0),
   db_length=query_length=col_access=0;
   query_error=0;
   next_insert_id=last_insert_id=0;
-  open_tables=temporary_tables=handler_tables=0;
+  open_tables= temporary_tables= handler_tables= derived_tables= 0;
   current_tablenr=0;
   handler_items=0;
   tmp_table=0;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index bc72e6324e9..253ec3d2918 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -442,7 +442,7 @@ public:
     handler_tables - list of tables that were opened with HANDLER OPEN
      and are still in use by this thread
   */
-  TABLE   *open_tables,*temporary_tables, *handler_tables;
+  TABLE   *open_tables,*temporary_tables, *handler_tables, *derived_tables;
   // TODO: document the variables below
   MYSQL_LOCK	*lock;				/* Current locks */
   MYSQL_LOCK	*locked_tables;			/* Tables locked with LOCK */
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 1ddaedeb480..0a2e2947add 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1,4 +1,4 @@
-/* Copyright (C) 2000 MySQL AB
+/* Copyright (C) 2002-2003 MySQL AB
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -17,7 +17,7 @@
 
 /*
   Derived tables
-  These were introduced by Monty and Sinisa <sinisa@mysql.com>
+  These were introduced by Sinisa <sinisa@mysql.com>
 */
 
 
@@ -38,17 +38,26 @@ static const char *any_db="*any*";	// Special symbol for check_access
     t                   TABLE_LIST for the upper SELECT
 
   IMPLEMENTATION
-  
-  Derived table is  resolved with temporary table. It is created based on the
-  queries defined. After temporary table is created, if this is not EXPLAIN,
-  then the entire unit / node is deleted. unit is deleted if UNION is used 
-  for derived table and node is deleted is it is a  simple SELECT.
+    Derived table is resolved with temporary table. It is created based on the
+    queries defined. After temporary table is created, if this is not EXPLAIN,
+    then the entire unit / node is deleted. unit is deleted if UNION is used 
+    for derived table and node is deleted is it is a  simple SELECT.
+
+    After table creation, the above TABLE_LIST is updated with a new table.
 
-  After table creation, the above TABLE_LIST is updated with a new table.
+    This function is called before any command containing derived table
+    is executed.
 
-  This function is called before any command containing derived table is executed. 
+    Derived tables is stored in thd->derived_tables and freed in
+    close_thread_tables()
 
-  TODO: To move creation of derived tables IN  open_and_lock_tables()
+  TODO
+    Move creation of derived tables in open_and_lock_tables()
+
+  RETURN
+    0	ok
+    1	Error
+    -1	Error and error message given
 */  
 
 
@@ -57,21 +66,21 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
   SELECT_LEX *sl= unit->first_select();
   List<Item> item_list;
   TABLE *table;
-  int res= 0;
+  int res;
   select_union *derived_result;
   TABLE_LIST *tables= (TABLE_LIST *)sl->table_list.first;
   TMP_TABLE_PARAM tmp_table_param;
   bool is_union=sl->next_select() && sl->next_select()->linkage == UNION_TYPE;
-  DBUG_ENTER("mysql_derived");
   SELECT_LEX_NODE *save_current_select= lex->current_select;
+  DBUG_ENTER("mysql_derived");
   
-
-/*
-  In create_total_list, derived tables have to be treated in case of EXPLAIN, 
-  This is because unit/node is not deleted in that case. Current code in this 
-  function has to be improved to recognize better when this function is called 
-  from derived tables and when from other functions.
-*/
+  /*
+    In create_total_list, derived tables have to be treated in case of
+    EXPLAIN, This is because unit/node is not deleted in that
+    case. Current code in this function has to be improved to
+    recognize better when this function is called from derived tables
+    and when from other functions.
+  */
   if (is_union && unit->create_total_list(thd, lex, &tables))
     DBUG_RETURN(-1);
 
@@ -92,30 +101,21 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
   {
     if (is_union)
     {
-/* 
-   The following code is a re-do of fix_tables_pointers() found in sql_select.cc
-   for UNION's within derived tables. The only difference is in navigation, as in 
-   derived tables we care for this level only.
-
-   fix_tables_pointers makes sure that in UNION's we do not open single table twice 
-   if found in different SELECT's.
-
-*/
-      for (SELECT_LEX *sel= sl;
-	   sel;
-	   sel= sel->next_select())
-      {
-	for (TABLE_LIST *cursor= (TABLE_LIST *)sel->table_list.first;
-	     cursor;
-	     cursor=cursor->next)
-	  cursor->table= cursor->table_list->table;
-      }
+      /* 
+	 The following code is a re-do of fix_tables_pointers() found
+	 in sql_select.cc for UNION's within derived tables. The only
+	 difference is in navigation, as in derived tables we care for
+	 this level only.
+
+      */
+      for (SELECT_LEX *sel= sl; sel; sel= sel->next_select())
+	relink_tables(sel);
     }
 
     lex->current_select= sl;
     if (setup_fields(thd,tables,item_list,0,0,1))
     {
-      res=-1;
+      res= -1;
       goto exit;
     }
     bzero((char*) &tmp_table_param,sizeof(tmp_table_param));
@@ -127,7 +127,7 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
 				  TMP_TABLE_ALL_COLUMNS),
                                  HA_POS_ERROR)))
     {
-      res=-1;
+      res= -1;
       goto exit;
     }
   
@@ -142,7 +142,7 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
 	sl->options&= ~OPTION_FOUND_ROWS;
 
       if (is_union)
-	res=mysql_union(thd,lex,derived_result,unit);
+	res= mysql_union(thd,lex,derived_result,unit);
       else
 	res= mysql_select(thd, tables,  sl->item_list,
 			sl->where, (ORDER *) sl->order_list.first,
@@ -153,9 +153,12 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
 
       if (!res)
       {
-// Here we entirely fix both TABLE_LIST and list of SELECT's as there were no derived tables
+	/*
+	  Here we entirely fix both TABLE_LIST and list of SELECT's as
+	  there were no derived tables
+	*/
 	if (derived_result->flush())
-	  res=1;
+	  res= 1;
 	else
 	{
 	  t->real_name=table->real_name;
@@ -164,23 +167,29 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit, TABLE_LIST *t)
 	  table->tmp_table=TMP_TABLE;
 	  if (lex->describe)
 	  {
+	    // to fix a problem in EXPLAIN
 	    if (tables)
-	      tables->table_list->table=tables->table; // to fix a problem in EXPLAIN
+	      tables->table_list->table=tables->table;
 	  }
 	  else
 	    unit->exclude();
-	  t->db=(char *)"";
-	  t->derived=(SELECT_LEX *)1; // just in case ...
+	  t->db= (char *)"";
+	  t->derived=(SELECT_LEX *) 1; // just in case ...
 	  table->file->info(HA_STATUS_VARIABLE);
 	}
       }
       delete derived_result;
     }
     if (res)
-      free_tmp_table(thd,table);
+      free_tmp_table(thd, table);
+    else
+    {
+      table->next= thd->derived_tables;
+      thd->derived_tables= table;
+    }
 exit:
     lex->current_select= save_current_select;
-    close_thread_tables(thd);
+    close_thread_tables(thd, 0, 1);
   }
   DBUG_RETURN(res);
 }
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index b2f08b4e0d4..f6e21e421d9 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1390,7 +1390,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     send_error(thd, ER_UNKNOWN_COM_ERROR);
     break;
   }
-  if (thd->lock || thd->open_tables)
+  if (thd->lock || thd->open_tables || thd->derived_tables)
   {
     thd->proc_info="closing tables";
     close_thread_tables(thd);			/* Free tables */
@@ -1534,9 +1534,11 @@ mysql_execute_command(THD *thd)
     for (SELECT_LEX *sl= lex->all_selects_list;
 	 sl;
 	 sl= sl->next_select_in_list())
+    {
       for (TABLE_LIST *cursor= sl->get_table_list();
 	   cursor;
 	   cursor= cursor->next)
+      {
 	if (cursor->derived && (res=mysql_derived(thd, lex,
 						  (SELECT_LEX_UNIT *)
 						  cursor->derived,
@@ -1546,6 +1548,8 @@ mysql_execute_command(THD *thd)
 	    send_error(thd,thd->killed ? ER_SERVER_SHUTDOWN : 0);
 	  DBUG_VOID_RETURN;
 	}
+      }
+    }
   }
   if ((&lex->select_lex != lex->all_selects_list &&
        lex->unit.create_total_list(thd, lex, &tables)) ||
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b57e45a1a52..c5a4362c71e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -184,6 +184,16 @@ int handle_select(THD *thd, LEX *lex, select_result *result)
   return res;
 }
 
+
+void relink_tables(SELECT_LEX *select_lex)
+{
+  for (TABLE_LIST *cursor= (TABLE_LIST *) select_lex->table_list.first;
+       cursor;
+       cursor=cursor->next)
+    cursor->table= cursor->table_list->table;
+}
+
+
 void fix_tables_pointers(SELECT_LEX *select_lex)
 {
   if (select_lex->next_select_in_list())
@@ -192,18 +202,15 @@ void fix_tables_pointers(SELECT_LEX *select_lex)
     for (SELECT_LEX *sl= select_lex;
 	 sl;
 	 sl= sl->next_select_in_list())
-    {
-      for (TABLE_LIST *cursor= (TABLE_LIST *)sl->table_list.first;
-          cursor;
-	   cursor=cursor->next)
-	cursor->table= cursor->table_list->table;
-    }
+      relink_tables(sl);
   }
 }
 
+
 /*
-  Inline function to setup clauses without sum functions
+  Function to setup clauses without sum functions
 */
+
 inline int setup_without_group(THD *thd, TABLE_LIST *tables,
 			       List<Item> &fields,
 			       List<Item> &all_fields,
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 1fbe2052831..e30bba830eb 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -374,3 +374,4 @@ public:
 
 bool cp_buffer_from_ref(TABLE_REF *ref);
 bool error_if_full_join(JOIN *join);
+void relink_tables(SELECT_LEX *select_lex);