bk commit into 5.0 tree (dlenev:1.1914) BUG#9841

[dlenev]

Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev 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
  1.1914 05/05/05 19:49:15 dlenev@stripped +3 -0
  Fix for bug #9841 "Unexpected read lock when trying to update a view in
  a stored procedure" (version 2).
  
  To handle updates and inserts into view in SP properly we should set
  lock types for tables of the view's main select when we are opening
  view for prelocking purproses.

  sql/sql_view.cc
    1.44 05/05/05 19:49:10 dlenev@stripped +15 -14
    mysql_make_view():
      To handle updates and inserts into view in SP properly we should set
      lock types for tables of the view's main select when we are opening
      view for prelocking purproses.
      Also it is more correct to use LEX::select_lex::get_table_list() 
      instead of 'view_tables' variable for obtaining list of those
      tables.

  mysql-test/t/sp.test
    1.116 05/05/05 19:49:10 dlenev@stripped +19 -0
    Added test case for bug #9841 "Unexpected read lock when trying to update
    a view in a stored procedure"

  mysql-test/r/sp.result
    1.121 05/05/05 19:49:10 dlenev@stripped +8 -0
    Added test case for bug #9841 "Unexpected read lock when trying to update
    a view in a stored procedure"

# 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:	dlenev
# Host:	brandersnatch.localdomain
# Root:	/home/dlenev/src/mysql-5.0-bg9841

--- 1.43/sql/sql_view.cc	Sat Apr 23 06:55:39 2005
+++ 1.44/sql/sql_view.cc	Thu May  5 19:49:10 2005
@@ -717,6 +717,16 @@
     }
 
     /*
+      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.
+    */
+    if (!lex->select_lex.next_select())
+      for (tbl= lex->select_lex.get_table_list(); 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
       further processing.
@@ -756,22 +766,13 @@
       table->ancestor= view_tables;
 
       /*
-        Process upper level tables of view. As far as we do noy suport union
-        here we can go through local tables of view most upper SELECT
+        Tables of the main select of the view should be marked as belonging
+        to the same select as original view (again we can use LEX::select_lex
+        for this purprose because we don't support MERGE algorithm for views
+        with unions).
       */
-      for(tbl= view_tables;
-          tbl;
-          tbl= tbl->next_local)
-      {
-        /* next table should include SELECT_LEX under this table SELECT_LEX */
+      for (tbl= lex->select_lex.get_table_list(); tbl; tbl= tbl->next_local)
         tbl->select_lex= table->select_lex;
-
-        /*
-          move lock type (TODO: should we issue error in case of TMPTABLE
-          algorithm and non-read locking)?
-        */
-        tbl->lock_type= table->lock_type;
-      }
 
       /* multi table view */
       if (view_tables->next_local)

--- 1.120/mysql-test/r/sp.result	Fri Apr 29 01:46:51 2005
+++ 1.121/mysql-test/r/sp.result	Thu May  5 19:49:10 2005
@@ -3057,4 +3057,12 @@
 yes
 drop procedure bug7293|
 delete from t1|
+drop procedure if exists bug9841|
+drop view if exists v1|
+create view v1 as select * from t1, t2 where id = s|
+create procedure bug9841 ()
+update v1 set data = 10|
+call bug9841()|
+drop view v1|
+drop procedure bug9841|
 drop table t1,t2;

--- 1.115/mysql-test/t/sp.test	Fri Apr 29 01:46:51 2005
+++ 1.116/mysql-test/t/sp.test	Thu May  5 19:49:10 2005
@@ -3741,6 +3741,25 @@
 drop procedure bug7293|
 delete from t1|
 
+
+#
+# BUG#9841: Unexpected read lock when trying to update a view in a
+#           stored procedure
+#
+--disable_warnings
+drop procedure if exists bug9841|
+drop view if exists v1|
+--enable_warnings
+
+create view v1 as select * from t1, t2 where id = s|
+create procedure bug9841 ()
+  update v1 set data = 10| 
+call bug9841()|
+
+drop view v1|
+drop procedure bug9841|
+
+
 #
 # BUG#NNNN: New bug synopsis
 #