bk commit into 5.0 tree (kroki:1.2235) BUG#17591

[kroki]

Below is the list of changes that have just been committed into a local
5.0 repository of tomash. When tomash does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-08-07 20:47:52+04:00, kroki@stripped +3 -0
  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, even if we were trying
  to update the view.
  
  The solution is to set lock type properly.

  mysql-test/r/view.result@stripped, 2006-08-07 20:47:49+04:00, kroki@stripped +27
-0
    Add result for bug#17591: Updatable view not possible with trigger
    or stored function.

  mysql-test/t/view.test@stripped, 2006-08-07 20:47:50+04:00, kroki@stripped +50 -0
    Add test case for bug#17591: Updatable view not possible with trigger
    or stored function.

  sql/sql_view.cc@stripped, 2006-08-07 20:47:50+04:00, kroki@stripped +25 -22
    Move the code that sets requested lock type before the point where
    we exit from mysql_make_view() when in prelocking mode.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.0-bug17591

--- 1.169/mysql-test/r/view.result	2006-08-07 20:48:01 +04:00
+++ 1.170/mysql-test/r/view.result	2006-08-07 20:48:01 +04:00
@@ -2850,3 +2850,30 @@ Tables_in_test
 t1
 DROP TABLE t1;
 DROP VIEW IF EXISTS v1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP VIEW IF EXISTS v1, v2;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+INSERT INTO v1 VALUES (0);
+RETURN 0;
+END |
+SELECT f1();
+f1()
+0
+CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t1;
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+INSERT INTO v2 VALUES (0);
+RETURN 0;
+END |
+SELECT f2();
+ERROR HY000: The target table v2 of the INSERT is not updatable
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP VIEW v1, v2;
+DROP TABLE t1;
+End of 5.0 tests.

--- 1.154/mysql-test/t/view.test	2006-08-07 20:48:01 +04:00
+++ 1.155/mysql-test/t/view.test	2006-08-07 20:48:01 +04:00
@@ -2718,3 +2718,53 @@ DROP TABLE t1;
 --disable_warnings
 DROP VIEW IF EXISTS v1;
 --enable_warnings
+
+
+#
+# BUG#17591: Updatable view not possible with trigger or stored
+# function 
+#
+# During prelocking phase we didn't update lock type of view tables,
+# hence READ lock was always requested.
+#
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP VIEW IF EXISTS v1, v2;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  INSERT INTO v1 VALUES (0);
+  RETURN 0;
+END |
+delimiter ;|
+
+SELECT f1();
+
+CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t1;
+
+delimiter |;
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+  INSERT INTO v2 VALUES (0);
+  RETURN 0;
+END |
+delimiter ;|
+
+--error ER_NON_UPDATABLE_TABLE
+SELECT f2();
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP VIEW v1, v2;
+DROP TABLE t1;
+
+
+--echo End of 5.0 tests.

--- 1.91/sql/sql_view.cc	2006-08-07 20:48:01 +04:00
+++ 1.92/sql/sql_view.cc	2006-08-07 20:48:01 +04:00
@@ -1053,6 +1053,29 @@ bool mysql_make_view(THD *thd, File_pars
     }
 
     /*
+      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;
+
+    /*
+      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= view_main_select_tables; tbl; tbl= tbl->next_local)
+    {
+      tbl->lock_type= table->lock_type;
+      tbl->grant.want_privilege= top_view->grant.orig_want_privilege;
+    }
+
+    /*
       If we are opening this view as part of implicit LOCK TABLES, then
       this view serves as simple placeholder and we should not continue
       further processing.
@@ -1112,37 +1135,17 @@ bool mysql_make_view(THD *thd, File_pars
          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;
-        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);