Fix for MDEV-5531: double call procedure in one session - hard shutdown the server

Main fix was to not cache derivied tables as they may be temporary tables that are deleted before the next query.
This was a bit tricky as Item_field::fix_fields depended on cached_tables to be set to resolve some columns.



mysql-test/r/sp-bugs.result:
  Added test case
mysql-test/t/sp-bugs.test:
  Added test case
sql/item.cc:
  Fixed fix_outer_field to handle case where found field did not have in cached_table
  Idea is that if cached_table is not avaliable, use from_field->table->pos_in_table_list instead
sql/records.cc:
  Also accept INTERNAL_TMP_TABLE for memmap
sql/sql_base.cc:
  More DBUG_PRINT
  Fixed that setup_natural_join_row_types() is not run twice.
  Original code modified context->first_name_resolution_table also for second executions.
  This was wrong as this could give wrong results if some joins had been optimized away between calls.
sql/sql_derived.cc:
  Mark derived tables as internal temporary tables (INTERNAL_TMP_TABLE), not as NON_TRANSACTIONAL_TMP_TABLE.
  This is more correct as the tables are not visible by the end user.
sql/sql_insert.cc:
  Reset pos_in_table_list before calling fix_fields.
  One of the consequences of the change of not caching all generated tables in Item_ident is that
  pos_in_table_list needs to be correct in calls to fix_fields.
sql/sql_lex.cc:
  More DBUG_PRINT
sql/sql_parse.cc:
  Don't cache derivied tables as they may be temporary tables that are deleted before the next query
sql/sql_select.cc:
  Reset table_vector. This was required as some code checked the vector to see if temporary tables had already been created.
sql/table.cc:
  Mark tables with field translations as cacheable (as these will not disapper between stmt executions.

1 files changed, 37 insertions, 23 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index f689d96b4cf..84e5ee49fc4 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -235,6 +235,7 @@ static void check_unused(THD *thd)
   uint count= 0, open_files= 0, idx= 0;
   TABLE *cur_link, *start_link, *entry;
   TABLE_SHARE *share;
+  DBUG_ENTER("check_unused");
 
   if ((start_link=cur_link=unused_tables))
   {
@@ -243,7 +244,7 @@ static void check_unused(THD *thd)
       if (cur_link != cur_link->next->prev || cur_link != cur_link->prev->next)
       {
 	DBUG_PRINT("error",("Unused_links aren't linked properly")); /* purecov: inspected */
-	return; /* purecov: inspected */
+	DBUG_VOID_RETURN; /* purecov: inspected */
       }
     } while (count++ < table_cache_count &&
 	     (cur_link=cur_link->next) != start_link);
@@ -291,6 +292,7 @@ static void check_unused(THD *thd)
     DBUG_PRINT("error",("Unused_links doesn't match open_cache: diff: %d", /* purecov: inspected */
 			count)); /* purecov: inspected */
   }
+  DBUG_VOID_RETURN;
 }
 #else
 #define check_unused(A)
@@ -3103,6 +3105,7 @@ retry_share:
       MDL_deadlock_handler mdl_deadlock_handler(ot_ctx);
       bool wait_result;
 
+      DBUG_PRINT("info", ("old version of table share found"));
       release_table_share(share);
       mysql_mutex_unlock(&LOCK_open);
 
@@ -3127,6 +3130,7 @@ retry_share:
         and try to reopen them. Note: refresh_version is currently
         changed only during FLUSH TABLES.
       */
+      DBUG_PRINT("info", ("share version differs between tables"));
       release_table_share(share);
       mysql_mutex_unlock(&LOCK_open);
       (void)ot_ctx->request_backoff_action(Open_table_context::OT_REOPEN_TABLES,
@@ -3139,12 +3143,17 @@ retry_share:
   {
     table= share->free_tables.front();
     table_def_use_table(thd, table);
-    /* We need to release share as we have EXTRA reference to it in our hands. */
+    /*
+      We need to release share as we have EXTRA reference to it in our hands.
+    */
+    DBUG_PRINT("info", ("release temporarily acquired table share"));
     release_table_share(share);
   }
   else
   {
-    /* We have too many TABLE instances around let us try to get rid of them. */
+    /*
+      We have too many TABLE instances around let us try to get rid of them.
+     */
     while (table_cache_count > table_cache_size && unused_tables)
       free_cache_entry(unused_tables);
 
@@ -3217,6 +3226,7 @@ err_unlock:
   release_table_share(share);
   mysql_mutex_unlock(&LOCK_open);
 
+  DBUG_PRINT("exit", ("failed"));
   DBUG_RETURN(TRUE);
 }
 
@@ -6973,7 +6983,7 @@ find_field_in_tables(THD *thd, Item_ident *item,
        */
       if (db)
         return cur_field;
-
+      
       if (found)
       {
         if (report_error == REPORT_ALL_ERRORS ||
@@ -6988,7 +6998,7 @@ find_field_in_tables(THD *thd, Item_ident *item,
 
   if (found)
     return found;
-
+  
   /*
     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
@@ -7919,6 +7929,7 @@ err:
     TRUE   Error
     FALSE  OK
 */
+
 static bool setup_natural_join_row_types(THD *thd,
                                          List<TABLE_LIST> *from_clause,
                                          Name_resolution_context *context)
@@ -7928,6 +7939,18 @@ static bool setup_natural_join_row_types(THD *thd,
   if (from_clause->elements == 0)
     DBUG_RETURN(false); /* We come here in the case of UNIONs. */
 
+  /* 
+     Do not redo work if already done:
+     1) for stored procedures,
+     2) for multitable update after lock failure and table reopening.
+  */
+  if (!context->select_lex->first_natural_join_processing)
+  {
+    DBUG_PRINT("info", ("using cached store_top_level_join_columns"));
+    DBUG_RETURN(false);
+  }
+  context->select_lex->first_natural_join_processing= 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. */
@@ -7944,22 +7967,15 @@ static bool setup_natural_join_row_types(THD *thd,
       left_neighbor= table_ref_it++;
     }
     while (left_neighbor && left_neighbor->sj_subq_pred);
-    /* 
-      Do not redo work if already done:
-      1) for stored procedures,
-      2) for multitable update after lock failure and table reopening.
-    */
-    if (context->select_lex->first_natural_join_processing)
+
+    if (store_top_level_join_columns(thd, table_ref,
+                                     left_neighbor, right_neighbor))
+      DBUG_RETURN(true);
+    if (left_neighbor)
     {
-      if (store_top_level_join_columns(thd, table_ref,
-                                       left_neighbor, right_neighbor))
-        DBUG_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;
-      }
+      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;
   }
@@ -7973,8 +7989,6 @@ static bool setup_natural_join_row_types(THD *thd,
   DBUG_ASSERT(right_neighbor);
   context->first_name_resolution_table=
     right_neighbor->first_leaf_for_name_resolution();
-  context->select_lex->first_natural_join_processing= false;
-
   DBUG_RETURN (false);
 }
 
@@ -8576,7 +8590,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
 
       if (!(item= field_iterator.create_item(thd)))
         DBUG_RETURN(TRUE);
-//      DBUG_ASSERT(item->fixed);
+
       /* cache the table for the Item_fields inserted by expanding stars */
       if (item->type() == Item::FIELD_ITEM && tables->cacheable_table)
         ((Item_field *)item)->cached_table= tables;