From: Date: August 30 2006 1:43pm
Subject: bk commit into 5.0 tree (kroki:1.2241) BUG#21326
List-Archive: http://lists.mysql.com/commits/11069
X-Bug: 21326
Message-Id: <200608301143.k7UBhHBb009992@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-08-30 15:43:10+04:00, kroki@stripped +3 -0
  Bug#19111: TRIGGERs selecting from a VIEW on the firing base table fail.
  
  In a trigger or a function used in a statement it is possible to do
  SELECT from a table being modified by the statement.  However,
  encapsulation of such SELECT into a view and selecting from a view
  instead of direct SELECT was not possible.
  
  This happened because tables used by views (which in their turn
  were used from functions/triggers) were not excluded from checks
  in unique_table() routine as it happens for the rest of tables
  added to the statement table list for prelocking.
  
  With this fix we ignore all such tables in unique_table(), thus
  providing consistency: inside a trigger or a functions SELECT from
  a view may be used where plain SELECT is allowed.  Modification of
  the same table from function or trigger is still disallowed.  Also,
  this patch doesn't affect the case where SELECT from the table being
  modified is done outside of function of trigger, such SELECTs are
  still disallowed (this limitation and visibility problem when
  function select from a table being modified are subjects of bug
  #21326).

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

  mysql-test/t/view.test@stripped, 2006-08-30 15:43:07+04:00, kroki@stripped +40 -0
    Add test case for bug#19111: TRIGGERs selecting from a VIEW on the
    firing base table fail.

  sql/sql_base.cc@stripped, 2006-08-30 15:43:07+04:00, kroki@stripped +4 -3
    In unique_table() do not check tables that are used in a stored
    function or a trigger ('prelocking_placeholder' is set).  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-08-30 15:43:18 +04:00
+++ 1.345/sql/sql_base.cc	2006-08-30 15:43:18 +04:00
@@ -851,12 +851,13 @@ TABLE_LIST* unique_table(THD *thd, TABLE
     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))
+         res->select_lex && !res->select_lex->exclude_from_table_unique_test &&
+         !res->prelocking_placeholder))
       break;
     /*
-      If we found entry of this table or or table of SELECT which already
+      If we found entry of this table or table of SELECT which already
       processed in derived table or top select of multi-update/multi-delete
-      (exclude_from_table_unique_test).
+      (exclude_from_table_unique_test) or prelocking placeholder.
     */
     table_list= res->next_global;
     DBUG_PRINT("info",

--- 1.162/mysql-test/r/view.result	2006-08-30 15:43:18 +04:00
+++ 1.163/mysql-test/r/view.result	2006-08-30 15:43:18 +04:00
@@ -2736,3 +2736,20 @@ m	e
 1	b
 DROP VIEW v1;
 DROP TABLE IF EXISTS t1,t2;
+DROP FUNCTION IF EXISTS f1;
+DROP VIEW IF EXISTS v1;
+DROP TABLE IF EXISTS t1, t2;
+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);
+UPDATE t1 SET i= f1();
+CREATE TABLE t2 (i INT);
+INSERT INTO t1 VALUES (1), ((SELECT MAX(i) FROM t2));
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1, t2;
+End of 5.0 tests.

--- 1.147/mysql-test/t/view.test	2006-08-30 15:43:18 +04:00
+++ 1.148/mysql-test/t/view.test	2006-08-30 15:43:18 +04:00
@@ -2596,3 +2596,43 @@ 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 FUNCTION IF EXISTS f1;
+DROP VIEW IF EXISTS v1;
+DROP TABLE IF EXISTS t1, t2;
+--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();
+
+CREATE TABLE t2 (i INT);
+INSERT INTO t1 VALUES (1), ((SELECT MAX(i) FROM t2));
+
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1, t2;
+
+
+--echo End of 5.0 tests.