Bug #19111 TRIGGERs selecting from a VIEW on the firing base table fail
Submitted: 14 Apr 2006 23:44 Modified: 13 Nov 2006 4:31
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.20/5.0BK/5.1BK OS:Any (any)
Assigned to: Tomash Brechko CPU Architecture:Any

[14 Apr 2006 23:44] Beat Vontobel
Description:
A TRIGGER that SELECTs from a VIEW on the base table that fires the TRIGGER fails in 5.0.20 with "ERROR 1443 (HY000): The definition of table 'v' prevents operation INSERT on table 't'."

This worked for some situations with nested VIEWs in 5.0.19 and thus breaks an existing application after the update.

How to repeat:
mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE VIEW v AS SELECT MAX(i) FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER tt BEFORE INSERT ON t FOR EACH ROW SET NEW.i = ( SELECT * FROM v ) + 1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (0);
ERROR 1443 (HY000): The definition of table 'v' prevents operation INSERT on table 't'.

The trigger selecting from the view on the same base table fails, note however that the inlined statement works correctly:

mysql> DROP TRIGGER tt;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER tt BEFORE INSERT ON t FOR EACH ROW SET NEW.i = ( SELECT MAX(i) FROM t ) + 1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| i    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

Suggested fix:
Allow TRIGGERs to SELECT from a VIEW on the base table.
[11 May 2006 14:56] MySQL Verification Team
Thank you for the bug report.
[28 Jul 2006 8:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9696
[30 Aug 2006 11:56] Tomash Brechko
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11069

ChangeSet@1.2241, 2006-08-30 15:43:10+04:00, kroki@moonlight.intranet +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).
[10 Oct 2006 9:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13384

ChangeSet@1.2241, 2006-10-10 13:44:04+04:00, kroki@moonlight.intranet +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).  See
  also bug 22427.
[10 Oct 2006 16:26] Tomash Brechko
Queued to 5.0-runtime and 5.1-runtime.
[13 Nov 2006 4:31] Paul DuBois
Noted in 5.0.30 (not 5.0.29), 5.1.13 changelogs.

Within a trigger for a base table, selecting from a view on that base 
table failed.