Bug#39953 Triggers are not working properly with multi table

updates

Attempt to execute trigger or stored function with multi-UPDATE
which used - but didn't update - a table that was also used by
the calling statement led to an error. Read-only reference to
tables used in the calling statement should be allowed.
 
This problem was caused by the fact that check for conflicting
use of tables in SP/triggers was performed in open_tables(),
and in case of multi-UPDATE we didn't know exact lock type at
this stage.

We solve the problem by moving this check to lock_tables(), so
it can be performed after exact lock types for tables used by
multi-UPDATE are determined.


mysql-test/r/trigger.result:
  Results for the added test case is added.
mysql-test/t/trigger.test:
  A new test case is added, verifying correct table multi-update
  conflict resolution, both read-only and write.
sql/sql_base.cc:
  The check for conflicting use of tables in SP/triggers is moved
  to lock_tables(), to be performed after the exact lock types
  have been determined. Also, an assert is added to open_ltable()
  to ensure this func is not used in a prelocked context.

1 files changed, 20 insertions, 0 deletions

diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index a07318435f6..a88a6973d61 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1961,4 +1961,24 @@ select * from t2;
 s1
 drop table t1;
 drop temporary table t2;
+#------------------------------------------------------------------------
+# Bug#39953 Triggers are not working properly with multi table updates
+#------------------------------------------------------------------------
+DROP TABLE IF EXISTS t1;
+DROP TRIGGER IF EXISTS t_insert;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a));
+INSERT INTO t1 (a) VALUES (2),(5);
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET
+date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END |
+INSERT INTO t2 (a,b) VALUES (1,2);
+DROP TRIGGER t_insert;
+CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET
+date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END |
+INSERT INTO t2 (a,b) VALUES (3,5);
+ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+DROP TABLE t1;
+DROP TRIGGER t_insert;
+DROP TABLE t2;
 End of 5.0 tests