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

1 files changed, 13 insertions, 10 deletions

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);
 }