From: Date: July 28 2006 10:36am
Subject: bk commit into 5.0 tree (kroki:1.2241) BUG#19111
List-Archive: http://lists.mysql.com/commits/9696
X-Bug: 19111
Message-Id: <200607280836.k6S8abd2006833@moonlight.intranet>

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-07-28 12:36:32+04:00, kroki@stripped +3 -0
  Bug#19111: TRIGGERs selecting from a VIEW on the firing base table fail.
  
  The problem was that tables used in a view were checked for uniqueness
  along with the tables being modified, while they are simply selected
  from.  Since UPDATE t SET c = (SELECT MAX(c) FROM t) works, select
  encapsulated in a view or a function should also work.
  
  The solution is not to check for uniqueness tables originated from a
  view, thus allowing a select from them (attempt to modify them inside
  a view will give a error, if select from a view is part of another
  modifying command).

  mysql-test/r/view.result@stripped, 2006-07-28 12:36:26+04:00, kroki@stripped +15 -0
    Add result for bug#19111: TRIGGERs selecting from a VIEW on the
    firing base table fail.

  mysql-test/t/view.test@stripped, 2006-07-28 12:36:27+04:00, kroki@stripped +37 -0
    Add test case for bug#19111: TRIGGERs selecting from a VIEW on the
    firing base table fail.

  sql/sql_base.cc@stripped, 2006-07-28 12:36:27+04:00, kroki@stripped +15 -4
    In unique_table() do not check tables that are used in a stored
    function or a trigger.  If such function or a trigger will attempt
    to modify a table, the error will be given, however select is
    allowed there.

# 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-bug19111

--- 1.344/sql/sql_base.cc	2006-07-28 12:36:39 +04:00
+++ 1.345/sql/sql_base.cc	2006-07-28 12:36:39 +04:00
@@ -815,12 +815,19 @@ TABLE_LIST *find_table_in_list(TABLE_LIS
 
 TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list)
 {
-  TABLE_LIST *res;
   const char *d_name, *t_name;
   DBUG_ENTER("unique_table");
   DBUG_PRINT("enter", ("table alias: %s", table->alias));
 
   /*
+    Here and below in the loop we stop once reaching to the tables
+    added from stored functions or triggers, because these tables
+    can't be modified anyway.
+  */
+  if (table_list == thd->lex->first_not_own_table())
+    DBUG_RETURN(0);
+
+  /*
     If this function called for query which update table (INSERT/UPDATE/...)
     then we have in table->table pointer to TABLE object which we are
     updating even if it is VIEW so we need TABLE_LIST of this TABLE object
@@ -846,13 +853,17 @@ TABLE_LIST* unique_table(THD *thd, TABLE
   t_name= table->table_name;
 
   DBUG_PRINT("info", ("real table: %s.%s", d_name, t_name));
-  for (;;)
+  while (table_list != thd->lex->first_not_own_table())
   {
+    TABLE_LIST *res;
     if (((! (res= find_table_in_global_list(table_list, d_name, t_name))) &&
          (! (res= mysql_lock_have_duplicate(thd, table, table_list)))) ||
         ((!res->table || res->table != table->table) &&
          res->select_lex && !res->select_lex->exclude_from_table_unique_test))
-      break;
+    {
+      DBUG_RETURN(res);
+    }
+
     /*
       If we found entry of this table or or table of SELECT which already
       processed in derived table or top select of multi-update/multi-delete
@@ -862,7 +873,7 @@ TABLE_LIST* unique_table(THD *thd, TABLE
     DBUG_PRINT("info",
                ("found same copy of table or table which we should skip"));
   }
-  DBUG_RETURN(res);
+  DBUG_RETURN(0);
 }
 
 

--- 1.162/mysql-test/r/view.result	2006-07-28 12:36:39 +04:00
+++ 1.163/mysql-test/r/view.result	2006-07-28 12:36:39 +04:00
@@ -2736,3 +2736,18 @@ m	e
 1	b
 DROP VIEW v1;
 DROP TABLE IF EXISTS t1,t2;
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+DROP FUNCTION IF EXISTS f1;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1);
+CREATE VIEW v1 AS SELECT MAX(i) FROM t1;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+SET NEW.i = (SELECT * FROM v1) + 1;
+INSERT INTO t1 VALUES (1);
+CREATE FUNCTION f1() RETURNS INT RETURN (SELECT * FROM v1);
+INSERT INTO t1 VALUES (f1());
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1;
+End of 5.0 tests.

--- 1.147/mysql-test/t/view.test	2006-07-28 12:36:39 +04:00
+++ 1.148/mysql-test/t/view.test	2006-07-28 12:36:39 +04:00
@@ -2596,3 +2596,40 @@ SELECT * FROM t2;
 
 DROP VIEW v1;
 DROP TABLE IF EXISTS t1,t2;
+
+
+#
+# Bug#19111: TRIGGERs selecting from a VIEW on the firing base table
+# fail
+#
+# Allow to select from a view on a table being modified in a trigger
+# and stored function, since plain select is allowed there.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1);
+
+CREATE VIEW v1 AS SELECT MAX(i) FROM t1;
+
+# Plain 'SET NEW.i = (SELECT MAX(i) FROM t1) + 1' works, so select
+# from a view should work too.
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  SET NEW.i = (SELECT * FROM v1) + 1;
+INSERT INTO t1 VALUES (1);
+
+# Plain 'RETURN (SELECT MAX(i) FROM t1)' works in INSERT, so select
+# from a view should work too.
+CREATE FUNCTION f1() RETURNS INT RETURN (SELECT * FROM v1);
+UPDATE t1 SET i= f1();
+
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+
+--echo End of 5.0 tests.