BUG#21142859: FUNCTION UPDATING A VIEW FAILS TO FIND TABLE

karthik-kamath · karthik-kamath · commit 90b9c957ba63 · 2016-05-18T11:07:29.000+05:30
THAT ACTUALLY EXISTS

ANALYSIS:
=========
Stored functions updating a view where the view table has a
trigger defined that updates another table, fails reporting
an error that the table doesn't exist.

If there is a trigger defined on a table, a variable
'trg_event_map' will be set to a non-zero value after the
parsed tree creation. This indicates what triggers we need to
pre-load for the TABLE_LIST when opening an associated table.

During the prelocking phase, the variable 'trg_event_map'
will not be set for the view table. This value will be set
after the processing of triggers defined on the table. During
the processing of sub-statements, 'locked_tables_mode' will be
set to 'LTM_PRELOCKED' which denotes that further locking
of tables/functions cannot be done. This results in the other
table not being locked and thus further processing results in
an error getting reported.

FIX:
====
During the prelocking of view, the value of 'trg_event_map'
of the view is copied to 'trg_event_map' of the next table
in the TABLE_LIST. This results in the locking of tables
associated with the trigger as well.
diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result
@@ -320,3 +320,23 @@ c2
 DROP TRIGGER t1_ai;
 DROP TABLE t1, t2;
 End of 5.0 tests
+#
+# Bug#21142859: FUNCTION UPDATING A VIEW FAILS TO FIND TABLE THAT ACTUALLY EXISTS
+#
+CREATE TABLE t1 SELECT 1 AS fld1, 'A' AS fld2;
+CREATE TABLE t2 (fld3 INT, fld4 CHAR(1));
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1
+FOR EACH ROW INSERT INTO t2 VALUES (new.fld1, new.fld2);
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+UPDATE v1 SET fld2='B' WHERE fld1=1;
+RETURN row_count();
+END !
+# Without the patch, an error was getting reported.
+SELECT f1();
+f1()
+1
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test
@@ -388,3 +388,29 @@ DROP TABLE t1, t2;
 
 --echo End of 5.0 tests
 
+--echo #
+--echo # Bug#21142859: FUNCTION UPDATING A VIEW FAILS TO FIND TABLE THAT ACTUALLY EXISTS
+--echo #
+
+CREATE TABLE t1 SELECT 1 AS fld1, 'A' AS fld2;
+CREATE TABLE t2 (fld3 INT, fld4 CHAR(1));
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+CREATE TRIGGER t1_au AFTER UPDATE ON t1
+FOR EACH ROW INSERT INTO t2 VALUES (new.fld1, new.fld2);
+
+DELIMITER !;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+ UPDATE v1 SET fld2='B' WHERE fld1=1;
+ RETURN row_count();
+END !
+DELIMITER ;!
+
+--echo # Without the patch, an error was getting reported.
+SELECT f1();
+
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1,t2;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -5211,6 +5211,15 @@ handle_view(THD *thd, Query_tables_list *prelocking_ctx,
                                  &table_list->view->sroutines_list,
                                  table_list->top_table());
   }
+
+  /*
+    If a trigger was defined on one of the associated tables then assign the
+    'trg_event_map' value of the view to the next table in table_list. When a
+    Stored function is invoked, all the associated tables including the tables
+    associated with the trigger are prelocked.
+  */
+  if (table_list->trg_event_map && table_list->next_global)
+    table_list->next_global->trg_event_map= table_list->trg_event_map;
   return FALSE;
 }
 
