issue correct error message in case of view presence for duplicated table on update (BUG#10773)

frequently used command sequence replaced with inline function

10 files changed, 112 insertions, 34 deletions

diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 2add9ac9269..28aa9e616b9 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -882,6 +882,9 @@ void add_join_on(TABLE_LIST *b,Item *expr);
 void add_join_natural(TABLE_LIST *a,TABLE_LIST *b);
 bool add_proc_to_list(THD *thd, Item *item);
 TABLE *unlink_open_table(THD *thd,TABLE *list,TABLE *find);
+void update_non_unique_table_error(TABLE_LIST *update,
+                                   const char *operation,
+                                   TABLE_LIST *duplicate);
 
 SQL_SELECT *make_select(TABLE *head, table_map const_tables,
 			table_map read_tables, COND *conds,
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index fcc04c950aa..386688de911 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5358,3 +5358,5 @@ ER_STMT_HAS_NO_OPEN_CURSOR
 	eng "The statement (%lu) has no open cursor."
 ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
         eng "Explicit or implicit commit is not allowed in stored function or trigger."
+ER_VIEW_PREVENT_UPDATE
+        eng "The definition of table '%-.64s' prevents operation %s on table '%-.64s'."
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index e9af684767d..a4753e46bb6 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -775,6 +775,60 @@ TABLE_LIST* unique_table(TABLE_LIST *table, TABLE_LIST *table_list)
 }
 
 
+/*
+  Issue correct error message in case we found 2 duplicate tables which
+  prevent some update operation
+
+  SYNOPSIS
+    update_non_unique_table_error()
+    update      table which we try to update
+    operation   name of update operation
+    duplicate   duplicate table which we found
+
+  NOTE:
+    here we hide view underlying tables if we have them
+*/
+
+void update_non_unique_table_error(TABLE_LIST *update,
+                                   const char *operation,
+                                   TABLE_LIST *duplicate)
+{
+  update= update->top_table();
+  duplicate= duplicate->top_table();
+  if (!update->view || !duplicate->view ||
+      update->view == duplicate->view ||
+      update->view_name.length != duplicate->view_name.length ||
+      update->view_db.length != duplicate->view_db.length ||
+      my_strcasecmp(table_alias_charset,
+                    update->view_name.str, duplicate->view_name.str) != 0 ||
+      my_strcasecmp(table_alias_charset,
+                    update->view_db.str, duplicate->view_db.str) != 0)
+  {
+    /*
+      it is not the same view repeated (but it can be parts of the same copy
+      of view), so we have to hide underlying tables.
+    */
+    if (update->view)
+    {
+      if (update->view == duplicate->view)
+        my_error(ER_NON_UPDATABLE_TABLE, MYF(0), update->alias, operation);
+      else
+        my_error(ER_VIEW_PREVENT_UPDATE, MYF(0),
+                 (duplicate->view ? duplicate->alias : update->alias),
+                 operation, update->alias);
+      return;
+    }
+    if (duplicate->view)
+    {
+      my_error(ER_VIEW_PREVENT_UPDATE, MYF(0), duplicate->alias, operation,
+               update->alias);
+      return;
+    }
+  }
+  my_error(ER_UPDATE_TABLE_USED, MYF(0), update->alias);
+}
+
+
 TABLE **find_temporary_table(THD *thd, const char *db, const char *table_name)
 {
   char	key[MAX_DBKEY_LENGTH];
@@ -3212,9 +3266,7 @@ bool setup_tables(THD *thd, TABLE_LIST *tables, Item **conds,
   {
     TABLE *table= table_list->table;
     if (first_select_table &&
-        (table_list->belong_to_view ?
-         table_list->belong_to_view :
-         table_list) == first_select_table)
+        table_list->top_table() == first_select_table)
     {
       /* new counting for SELECT of INSERT ... SELECT command */
       first_select_table= 0;
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 25fef8b0ad6..ddf5aee0977 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -309,10 +309,13 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
     my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
     DBUG_RETURN(TRUE);
   }
-  if (unique_table(table_list, table_list->next_global))
   {
-    my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
-    DBUG_RETURN(TRUE);
+    TABLE_LIST *duplicate;
+    if ((duplicate= unique_table(table_list, table_list->next_global)))
+    {
+      update_non_unique_table_error(table_list, "DELETE", duplicate);
+      DBUG_RETURN(TRUE);
+    }
   }
   select_lex->fix_prepare_information(thd, conds);
   DBUG_RETURN(FALSE);
@@ -393,11 +396,15 @@ bool mysql_multi_delete_prepare(THD *thd)
       Check that table from which we delete is not used somewhere
       inside subqueries/view.
     */
-    if (unique_table(target_tbl->correspondent_table, lex->query_tables))
     {
-      my_error(ER_UPDATE_TABLE_USED, MYF(0),
-               target_tbl->correspondent_table->table_name);
-      DBUG_RETURN(TRUE);
+      TABLE_LIST *duplicate;
+      if ((duplicate= unique_table(target_tbl->correspondent_table,
+                                   lex->query_tables)))
+      {
+        update_non_unique_table_error(target_tbl->correspondent_table,
+                                      "DELETE", duplicate);
+        DBUG_RETURN(TRUE);
+      }
     }
   }
   DBUG_RETURN(FALSE);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 2ce81d8815e..e4c96bc3dcd 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -778,10 +778,14 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, TABLE *table,
   if (!table)
     table= table_list->table;
 
-  if (!select_insert && unique_table(table_list, table_list->next_global))
+  if (!select_insert)
   {
-    my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
-    DBUG_RETURN(TRUE);
+    TABLE_LIST *duplicate;
+    if ((duplicate= unique_table(table_list, table_list->next_global)))
+    {
+      update_non_unique_table_error(table_list, "INSERT", duplicate);
+      DBUG_RETURN(TRUE);
+    }
   }
   if (duplic == DUP_UPDATE || duplic == DUP_REPLACE)
     table->file->extra(HA_EXTRA_RETRIEVE_PRIMARY_KEY);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 33339cf0882..ffe36f3b6bd 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2845,12 +2845,15 @@ mysql_execute_command(THD *thd)
           Is table which we are changing used somewhere in other parts
           of query
         */
-        if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) &&
-            unique_table(create_table, select_tables))
+        if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
         {
-          my_error(ER_UPDATE_TABLE_USED, MYF(0), create_table->table_name);
-          res= 1;
-          goto end_with_restart_wait;
+          TABLE_LIST *duplicate;
+          if ((duplicate= unique_table(create_table, select_tables)))
+          {
+            update_non_unique_table_error(create_table, "CREATE", duplicate);
+            res= 1;
+            goto end_with_restart_wait;
+          }
         }
         /* If we create merge table, we have to test tables in merge, too */
         if (lex->create_info.used_fields & HA_CREATE_USED_UNION)
@@ -2860,9 +2863,10 @@ mysql_execute_command(THD *thd)
                tab;
                tab= tab->next_local)
           {
-            if (unique_table(tab, select_tables))
+            TABLE_LIST *duplicate;
+            if ((duplicate= unique_table(tab, select_tables)))
             {
-              my_error(ER_UPDATE_TABLE_USED, MYF(0), tab->table_name);
+              update_non_unique_table_error(tab, "CREATE", duplicate);
               res= 1;
               goto end_with_restart_wait;
             }
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 6b9a8ddfcb6..b448d56c254 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -566,10 +566,14 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
     DBUG_RETURN(TRUE);
 
   /* Check that we are not using table that we are updating in a sub select */
-  if (unique_table(table_list, table_list->next_global))
   {
-    my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
-    DBUG_RETURN(TRUE);
+    TABLE_LIST *duplicate;
+    if ((duplicate= unique_table(table_list, table_list->next_global)))
+    {
+      update_non_unique_table_error(table_list, "UPDATE", duplicate);
+      my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
+      DBUG_RETURN(TRUE);
+    }
   }
   select_lex->fix_prepare_information(thd, conds);
   DBUG_RETURN(FALSE);
@@ -781,7 +785,7 @@ bool mysql_multi_update_prepare(THD *thd)
   {
     TABLE *table= tl->table;
     TABLE_LIST *tlist;
-    if (!(tlist= tl->belong_to_view ? tl->belong_to_view : tl)->derived)
+    if (!(tlist= tl->top_table())->derived)
     {
       tlist->grant.want_privilege=
         (SELECT_ACL & ~tlist->grant.privilege);
@@ -790,11 +794,14 @@ 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))
+        tl->lock_type != TL_READ_NO_INSERT)
     {
-      my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
-      DBUG_RETURN(TRUE);
+      TABLE_LIST *duplicate;
+      if ((duplicate= unique_table(tl, table_list)))
+      {
+        update_non_unique_table_error(table_list, "UPDATE", duplicate);
+        DBUG_RETURN(TRUE);
+      }
     }
   }
 
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 0b351407c13..cfd882f5308 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -720,9 +720,7 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
   }
   if (!res && !thd->is_fatal_error)
   {
-    TABLE_LIST *top_view= (table->belong_to_view ?
-                           table->belong_to_view :
-                           table);
+    TABLE_LIST *top_view= table->top_table();
     TABLE_LIST *view_tables= lex->query_tables;
     TABLE_LIST *view_tables_tail= 0;
     TABLE_LIST *tbl;
@@ -1072,8 +1070,7 @@ bool check_key_in_view(THD *thd, TABLE_LIST *view)
       thd->lex->select_lex.select_limit == 0)
     DBUG_RETURN(FALSE); /* it is normal table or query without LIMIT */
   table= view->table;
-  if (view->belong_to_view)
-    view= view->belong_to_view;
+  view= view->top_table();
   trans= view->field_translation;
   key_info_end= (key_info= table->key_info)+ table->s->keys;
 
diff --git a/sql/table.cc b/sql/table.cc
index 6677453969b..11b2b9d7e48 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -2118,7 +2118,7 @@ int st_table_list::view_check_option(THD *thd, bool ignore_failure)
 {
   if (check_option && check_option->val_int() == 0)
   {
-    TABLE_LIST *view= (belong_to_view ? belong_to_view : this);
+    TABLE_LIST *view= top_table();
     if (ignore_failure)
     {
       push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_ERROR,
diff --git a/sql/table.h b/sql/table.h
index 8bc4e01852f..c6a74c67ea8 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -459,6 +459,8 @@ typedef struct st_table_list
                           st_table_list *view);
   bool set_insert_values(MEM_ROOT *mem_root);
   st_table_list *find_underlying_table(TABLE *table);
+  inline st_table_list *top_table()
+    { return belong_to_view ? belong_to_view : this; }
 } TABLE_LIST;
 
 class Item;