BUG#17591: Updatable view not possible with trigger or stored function

When a view was used inside a trigger or a function, lock type for
tables used in a view was always set to READ (thus making the view
non-updatable), even if we were trying to update the view.

The solution is to set lock type properly.


mysql-test/r/view.result:
  Add result for bug#17591: Updatable view not possible with trigger
  or stored function.
mysql-test/t/view.test:
  Add test case for bug#17591: Updatable view not possible with trigger
  or stored function.
sql/sql_view.cc:
  Move the code that sets requested lock type before the point where
  we exit from mysql_make_view() when we process a placeholder for
  prelocked table.

1 files changed, 30 insertions, 22 deletions

diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 637d2cc3684..361e86fb048 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1052,6 +1052,31 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table)
       table->next_global= view_tables;
     }
 
+    bool view_is_mergeable= (table->algorithm != VIEW_ALGORITHM_TMPTABLE &&
+                             lex->can_be_merged());
+    TABLE_LIST *view_main_select_tables;
+    if (view_is_mergeable)
+    {
+      /*
+        Currently 'view_main_select_tables' differs from 'view_tables'
+        only then view has CONVERT_TZ() function in its select list.
+        This may change in future, for example if we enable merging of
+        views with subqueries in select list.
+      */
+      view_main_select_tables=
+        (TABLE_LIST*)lex->select_lex.table_list.first;
+
+      /*
+        Let us set proper lock type for tables of the view's main
+        select since we may want to perform update or insert on
+        view. This won't work for view containing union. But this is
+        ok since we don't allow insert and update on such views
+        anyway.
+      */
+      for (tbl= view_main_select_tables; tbl; tbl= tbl->next_local)
+        tbl->lock_type= table->lock_type;
+    }
+
     /*
       If we are opening this view as part of implicit LOCK TABLES, then
       this view serves as simple placeholder and we should not continue
@@ -1106,43 +1131,26 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table)
       - VIEW SELECT allow merging
       - VIEW used in subquery or command support MERGE algorithm
     */
-    if (table->algorithm != VIEW_ALGORITHM_TMPTABLE &&
-	lex->can_be_merged() &&
+    if (view_is_mergeable &&
         (table->select_lex->master_unit() != &old_lex->unit ||
          old_lex->can_use_merged()) &&
         !old_lex->can_not_use_merged())
     {
-      List_iterator_fast<TABLE_LIST> ti(view_select->top_join_list);
-      /*
-        Currently 'view_main_select_tables' differs from 'view_tables'
-        only then view has CONVERT_TZ() function in its select list.
-        This may change in future, for example if we enable merging
-        of views with subqueries in select list.
-      */
-      TABLE_LIST *view_main_select_tables=
-                    (TABLE_LIST*)lex->select_lex.table_list.first;
       /* lex should contain at least one table */
       DBUG_ASSERT(view_main_select_tables != 0);
 
+      List_iterator_fast<TABLE_LIST> ti(view_select->top_join_list);
+
       table->effective_algorithm= VIEW_ALGORITHM_MERGE;
       DBUG_PRINT("info", ("algorithm: MERGE"));
       table->updatable= (table->updatable_view != 0);
       table->effective_with_check=
         old_lex->get_effective_with_check(table);
       table->merge_underlying_list= view_main_select_tables;
-      /*
-        Let us set proper lock type for tables of the view's main select
-        since we may want to perform update or insert on view. This won't
-        work for view containing union. But this is ok since we don't
-        allow insert and update on such views anyway.
 
-        Also we fill correct wanted privileges.
-      */
-      for (tbl= table->merge_underlying_list; tbl; tbl= tbl->next_local)
-      {
-        tbl->lock_type= table->lock_type;
+      /* Fill correct wanted privileges. */
+      for (tbl= view_main_select_tables; tbl; tbl= tbl->next_local)
         tbl->grant.want_privilege= top_view->grant.orig_want_privilege;
-      }
 
       /* prepare view context */
       lex->select_lex.context.resolve_in_table_list_only(view_main_select_tables);