fixed mechanism of detection selection from table wich we update

(BUG##9398, BUG#8703)
fixed wrong join view detection in multi-delete which lead to server crash


mysql-test/r/lowercase_view.result:
  added new tests of updation and selection from the same table
mysql-test/r/view.result:
  added new tests of updation and selection from the same table
  added test of multidelete command over join view which lead to server crash
  test suite from bugs #9398 and #8703
mysql-test/t/lowercase_view.test:
  added new tests of updation and selection from the same table
mysql-test/t/view.test:
  added new tests of updation and selection from the same table
  added test of multidelete command over join view which lead to server crash
  test suite from bugs #9398 and #8703
sql/sql_base.cc:
  changed procedure of finding tables
sql/sql_class.cc:
  added derived table procession detection
sql/sql_class.h:
  added derived table procession detection
sql/sql_delete.cc:
  fixed detection of selection from table which update for multidelete
sql/sql_derived.cc:
  added derived table procession detection
sql/sql_lex.cc:
  added detection os SELECTs processed inside derived tables
  removed old mechanism of multidelete/multiupdate table duplication detection (which can't work with views)
sql/sql_lex.h:
  added detection os SELECTs processed inside derived tables
  removed old mechanism of multidelete/multiupdate table duplication detection (which can't work with views)
sql/sql_parse.cc:
  removed wrong test of join view (for multidelete in can be not only first table)
sql/sql_prepare.cc:
  added detection os SELECTs processed inside derived tables (reset it for reusing in PS/SP)
sql/sql_select.cc:
  added detection os SELECTs processed inside derived tables
sql/sql_update.cc:
  fixed detection of selection from table which update for multiupdate

11 files changed, 93 insertions, 147 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 4750fe1386f..28235ba48a3 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -680,33 +680,12 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table,
                                const char *db_name,
                                const char *table_name)
 {
-  if (lower_case_table_names)
+  for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
   {
-    for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
-    {
-      if ((table->table == 0 || table->table->s->tmp_table == NO_TMP_TABLE) &&
-          ((!strcmp(table->db, db_name) &&
-            !strcmp(table->table_name, table_name)) ||
-           (table->view &&
-            !my_strcasecmp(table_alias_charset,
-                           table->db, db_name) &&
-            !my_strcasecmp(table_alias_charset,
-                           table->table->alias, table_name))))
-        break;
-    }
-  }
-  else
-  {
-    for (; table; table= *(TABLE_LIST **) ((char*) table + offset))
-    {
-      if ((table->table == 0 || table->table->s->tmp_table == NO_TMP_TABLE) &&
-          ((!strcmp(table->db, db_name) &&
-            !strcmp(table->table_name, table_name)) ||
-           (table->view &&
-            !strcmp(table->table->s->db, db_name) &&
-            !strcmp(table->table->alias, table_name))))
-        break;
-    }
+    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;
 }
@@ -717,8 +696,25 @@ TABLE_LIST *find_table_in_list(TABLE_LIST *table,
 
   SYNOPSIS
     unique_table()
-    table       table which should be chaked
-    table_list  list of tables
+    table                 table which should be chaked
+    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
     found duplicate
@@ -758,11 +754,17 @@ TABLE_LIST* unique_table(TABLE_LIST *table, TABLE_LIST *table_list)
   for(;;)
   {
     if (!(res= find_table_in_global_list(table_list, d_name, t_name)) ||
-        !res->table || res->table != table->table)
+        (!res->table || res->table != table->table) &&
+        (res->select_lex && !res->select_lex->exclude_from_table_unique_test))
       break;
-    /* if we found entry of this table try again. */
+    /*
+      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"));
+    DBUG_PRINT("info",
+               ("found same copy of table or table which we should skip"));
   }
   DBUG_RETURN(res);
 }
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index cf7240e4dba..95d436069e2 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -161,7 +161,8 @@ THD::THD()
   :user_time(0), global_read_lock(0), is_fatal_error(0),
    rand_used(0), time_zone_used(0),
    last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0),
-   in_lock_tables(0), bootstrap(0), spcont(NULL)
+   in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE),
+   spcont(NULL)
 {
   current_arena= this;
 #ifndef DBUG_OFF
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 6d6ac810fbf..39d5c37462b 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1207,6 +1207,8 @@ public:
   bool	     no_trans_update, abort_on_warning;
   bool 	     got_warning;       /* Set on call to push_warning() */
   bool	     no_warnings_for_error; /* no warnings on call to my_error() */
+  /* set during loop of derived table processing */
+  bool       derived_tables_processing;
   longlong   row_count_func;	/* For the ROW_COUNT() function */
   sp_rcontext *spcont;		// SP runtime context
   sp_cache   *sp_proc_cache;
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 642564f5d7a..eb3775f66ea 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -356,12 +356,28 @@ bool mysql_multi_delete_prepare(THD *thd)
                    &lex->select_lex.leaf_tables, FALSE, FALSE))
     DBUG_RETURN(TRUE);
 
+
+  /*
+    Multi-delete can't be constructed over-union => we always have
+    single SELECT on top and have to check underlying SELECTs of it
+  */
+  lex->select_lex.exclude_from_table_unique_test= TRUE;
   /* Fix tables-to-be-deleted-from list to point at opened tables */
   for (target_tbl= (TABLE_LIST*) aux_tables;
        target_tbl;
        target_tbl= target_tbl->next_local)
   {
-    target_tbl->table= target_tbl->correspondent_table->table;
+    if (!(target_tbl->table= target_tbl->correspondent_table->table))
+    {
+      DBUG_ASSERT(target_tbl->correspondent_table->view &&
+                  target_tbl->correspondent_table->ancestor &&
+                  target_tbl->correspondent_table->ancestor->next_local);
+      my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
+               target_tbl->correspondent_table->view_db.str,
+               target_tbl->correspondent_table->view_name.str);
+      DBUG_RETURN(TRUE);
+    }
+
     if (!target_tbl->correspondent_table->updatable ||
         check_key_in_view(thd, target_tbl->correspondent_table))
     {
@@ -370,23 +386,14 @@ bool mysql_multi_delete_prepare(THD *thd)
       DBUG_RETURN(TRUE);
     }
     /*
-      Check are deleted table used somewhere inside subqueries.
-
-      Multi-delete can't be constructed over-union => we always have
-      single SELECT on top and have to check underlying SELECTs of it
+      Check that table from which we delete is not used somewhere
+      inside subqueries/view.
     */
-    for (SELECT_LEX_UNIT *un= lex->select_lex.first_inner_unit();
-         un;
-         un= un->next_unit())
+    if (unique_table(target_tbl->correspondent_table, lex->query_tables))
     {
-      if (un->first_select()->linkage != DERIVED_TABLE_TYPE &&
-          un->check_updateable(target_tbl->correspondent_table->db,
-                               target_tbl->correspondent_table->table_name))
-      {
-        my_error(ER_UPDATE_TABLE_USED, MYF(0),
-                 target_tbl->correspondent_table->table_name);
-        DBUG_RETURN(TRUE);
-      }
+      my_error(ER_UPDATE_TABLE_USED, MYF(0),
+               target_tbl->correspondent_table->table_name);
+      DBUG_RETURN(TRUE);
     }
   }
   DBUG_RETURN(FALSE);
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index eb7b3e8a319..4fcde212716 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -43,8 +43,10 @@
 int
 mysql_handle_derived(LEX *lex, int (*processor)(THD*, LEX*, TABLE_LIST*))
 {
+  int res= 0;
   if (lex->derived_tables)
   {
+    lex->thd->derived_tables_processing= TRUE;
     for (SELECT_LEX *sl= lex->all_selects_list;
 	 sl;
 	 sl= sl->next_select_in_list())
@@ -53,9 +55,8 @@ mysql_handle_derived(LEX *lex, int (*processor)(THD*, LEX*, TABLE_LIST*))
 	   cursor;
 	   cursor= cursor->next_local)
       {
-	int res;
 	if ((res= (*processor)(lex->thd, lex, cursor)))
-	  return res;
+	  goto out;
       }
       if (lex->describe)
       {
@@ -68,7 +69,9 @@ mysql_handle_derived(LEX *lex, int (*processor)(THD*, LEX*, TABLE_LIST*))
       }
     }
   }
-  return 0;
+out:
+  lex->thd->derived_tables_processing= FALSE;
+  return res;
 }
 
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 91c4dc40c01..61f710a2fe5 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1112,7 +1112,7 @@ void st_select_lex::init_query()
   first_execution= 1;
   first_cond_optimization= 1;
   parsing_place= NO_MATTER;
-  no_wrap_view_item= 0;
+  exclude_from_table_unique_test= no_wrap_view_item= FALSE;
   link_next= 0;
 }
 
@@ -1493,77 +1493,6 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num)
 }
 
 
-/*
-  Find db.table which will be updated in this unit
-
-  SYNOPSIS
-    st_select_lex_unit::check_updateable()
-    db		- data base name
-    table	- real table name
-
-  RETURN
-    1 - found
-    0 - OK (table did not found)
-*/
-
-bool st_select_lex_unit::check_updateable(char *db, char *table)
-{
-  for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
-    if (sl->check_updateable(db, table))
-      return 1;
-  return 0;
-}
-
-
-/*
-  Find db.table which will be updated in this select and
-  underlying ones (except derived tables)
-
-  SYNOPSIS
-    st_select_lex::check_updateable()
-    db		- data base name
-    table	- real table name
-
-  RETURN
-    1 - found
-    0 - OK (table did not found)
-*/
-
-bool st_select_lex::check_updateable(char *db, char *table)
-{
-  if (find_table_in_local_list(get_table_list(), db, table))
-    return 1;
-
-  return check_updateable_in_subqueries(db, table);
-}
-
-/*
-   Find db.table which will be updated in underlying subqueries
-
-   SYNOPSIS
-    st_select_lex::check_updateable_in_subqueries()
-    db		- data base name
-    table	- real table name
-
-  RETURN
-    1 - found
-    0 - OK (table did not found)
-*/
-
-bool st_select_lex::check_updateable_in_subqueries(char *db, char *table)
-{
-  for (SELECT_LEX_UNIT *un= first_inner_unit();
-       un;
-       un= un->next_unit())
-  {
-    if (un->first_select()->linkage != DERIVED_TABLE_TYPE &&
-	un->check_updateable(db, table))
-      return 1;
-  }
-  return 0;
-}
-
-
 void st_select_lex_unit::print(String *str)
 {
   for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 00e30bd320b..92d77520f7f 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -442,7 +442,6 @@ public:
   inline void unclean() { cleaned= 0; }
   void reinit_exec_mechanism();
 
-  bool check_updateable(char *db, char *table);
   void print(String *str);
 
   ulong init_prepare_fake_select_lex(THD *thd);
@@ -525,6 +524,8 @@ public:
   bool first_cond_optimization;
   /* do not wrap view fields with Item_ref */
   bool no_wrap_view_item;
+  /* exclude this select from check of unique_table() */
+  bool exclude_from_table_unique_test;
 
   /* 
      SELECT for SELECT command st_select_lex. Used to privent scaning
@@ -615,8 +616,6 @@ public:
     init_select();
   }
   bool setup_ref_array(THD *thd, uint order_group_num);
-  bool check_updateable(char *db, char *table);
-  bool check_updateable_in_subqueries(char *db, char *table);
   void print(THD *thd, String *str);
   static void print_order(String *str, ORDER *order);
   void print_limit(THD *thd, String *str);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index d765561f61e..467de9587ec 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3234,16 +3234,6 @@ unsent_create_error:
     if ((res= open_and_lock_tables(thd, all_tables)))
       break;
 
-    if (!first_table->table)
-    {
-      DBUG_ASSERT(first_table->view &&
-                  first_table->ancestor && first_table->ancestor->next_local);
-      my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
-               first_table->view_db.str, first_table->view_name.str);
-      res= FALSE;
-      break;
-    }
-
     if ((res= mysql_multi_delete_prepare(thd)))
       goto error;
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 7e2c37f130e..e4c50f7c1ef 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1871,6 +1871,9 @@ void reset_stmt_for_execute(THD *thd, LEX *lex)
       /* remove option which was put by mysql_explain_union() */
       sl->options&= ~SELECT_DESCRIBE;
 
+      /* see unique_table() */
+      sl->exclude_from_table_unique_test= FALSE;
+
       /*
         Copy WHERE clause pointers to avoid damaging they by optimisation
       */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 461a0f8b9d6..e63cc1ab3df 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -316,6 +316,13 @@ JOIN::prepare(Item ***rref_pointer_array,
   join_list= &select_lex->top_join_list;
   union_part= (unit_arg->first_select()->next_select() != 0);
 
+  /*
+    If we have already executed SELECT, then it have not sense to prevent
+    its table from update (see unique_table())
+  */
+  if (thd->derived_tables_processing)
+    select_lex->exclude_from_table_unique_test= TRUE;
+
   /* Check that all tables, fields, conds and order are ok */
 
   if ((!(select_options & OPTION_SETUP_TABLES_DONE) &&
@@ -1157,7 +1164,7 @@ JOIN::exec()
 {
   int      tmp_error;
   DBUG_ENTER("JOIN::exec");
-  
+
   error= 0;
   if (procedure)
   {
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index bb0ac31bdc7..8b8dd32b22d 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -691,16 +691,6 @@ bool mysql_multi_update_prepare(THD *thd)
         DBUG_RETURN(TRUE);
       }
 
-      /*
-        Multi-update can't be constructed over-union => we always have
-        single SELECT on top and have to check underlying SELECTs of it
-      */
-      if (lex->select_lex.check_updateable_in_subqueries(tl->db,
-                                                         tl->table_name))
-      {
-        my_error(ER_UPDATE_TABLE_USED, MYF(0), tl->table_name);
-        DBUG_RETURN(TRUE);
-      }
       DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
       tl->lock_type= lex->multi_lock_option;
       tl->updating= 1;
@@ -781,6 +771,11 @@ bool mysql_multi_update_prepare(THD *thd)
       DBUG_RETURN(TRUE);
   }
 
+  /*
+    Check that we are not using table that we are updating, but we should
+    skip all tables of UPDATE SELECT itself
+  */
+  lex->select_lex.exclude_from_table_unique_test= TRUE;
   /* We only need SELECT privilege for columns in the values list */
   for (tl= leaves; tl; tl= tl->next_leaf)
   {
@@ -794,11 +789,19 @@ bool mysql_multi_update_prepare(THD *thd)
     }
     DBUG_PRINT("info", ("table: %s  want_privilege: %u", tl->alias,
                         (uint) table->grant.want_privilege));
+    if (tl->lock_type != TL_READ &&
+        tl->lock_type != TL_READ_NO_INSERT &&
+        unique_table(tl, table_list))
+    {
+      my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
+      DBUG_RETURN(TRUE);
+    }
   }
 
   if (thd->fill_derived_tables() &&
       mysql_handle_derived(lex, &mysql_derived_filling))
     DBUG_RETURN(TRUE);
+
   DBUG_RETURN (FALSE);
 }