Description:
A function (updateVIEW) updates an updatable view (viewA), that's a view on a table (tableA) that has an update trigger that updates another table (journalA).
The function fails with "table journalA not found".
The same update of the view done outside of a function succeeds.
A similar function (UpdateTable) that updates tableA directly succeeds.
The same update of the view done in a procedure succeeds.
console log:
mysql> DELIMITER ^
mysql> drop database if exists bug ^
Query OK, 3 rows affected (0.00 sec)
mysql> create database bug ^
Query OK, 1 row affected (0.00 sec)
mysql> create table bug.tableA select 1 as keyA,'A' as fieldA ^
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> create view bug.viewA as select * from bug.tableA ^
Query OK, 0 rows affected (0.00 sec)
mysql> create table bug.journalA (keyA int, fieldA char(1)) ^
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE trigger bug.tableA_trigger_update AFTER update on bug.tableA
-> for each row insert into bug.journalA (keyA, fieldA) values (new.keyA, new.fieldA);
-> ^
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE function bug.updateTable() returns int
-> begin
-> update bug.tableA set fieldA='B' where keyA='1';
-> return row_count();
-> end
-> ^
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE function bug.updateView() returns int
-> begin
-> update bug.viewA set fieldA='B' where keyA='1';
-> return row_count();
-> end
-> ^
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE procedure bug.updateView() update bug.viewA set fieldA='B' where keyA='1';
-> ^
Query OK, 0 rows affected (0.00 sec)
mysql> /* update the view in procedure: succeeds */
mysql> call bug.updateView()
-> ^
Query OK, 1 row affected (0.00 sec)
mysql> select bug.updateTable(),version()
-> ^
+-------------------+-----------+
| bug.updateTable() | version() |
+-------------------+-----------+
| 0 | 5.1.73 |
+-------------------+-----------+
1 row in set (0.00 sec)
mysql> /* update the view directly: succeeds */
mysql> update bug.viewA set fieldA='B' where keyA='1';
-> ^
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> /* update the view din function: FAILS with ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist*/
mysql> select bug.updateView()
-> ^
ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist
mysql> /* yes it does exist! */
mysql> show create table bug.journalA^
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| journalA | CREATE TABLE `journala` (
`keyA` int(11) DEFAULT NULL,
`fieldA` char(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
How to repeat:
DELIMITER ^
drop database if exists bug ^
create database bug ^
create table bug.tableA select 1 as keyA,'A' as fieldA ^
create view bug.viewA as select * from bug.tableA ^
create table bug.journalA (keyA int, fieldA char(1)) ^
CREATE trigger bug.tableA_trigger_update AFTER update on bug.tableA
for each row insert into bug.journalA (keyA, fieldA) values (new.keyA, new.fieldA);
^
CREATE function bug.updateTable() returns int
begin
update bug.tableA set fieldA='B' where keyA='1';
return row_count();
end
^
CREATE function bug.updateView() returns int
begin
update bug.viewA set fieldA='B' where keyA='1';
return row_count();
end
^
CREATE procedure bug.updateView() update bug.viewA set fieldA='B' where keyA='1';
^
/* update the view in procedure: succeeds */
call bug.updateView()
^
select bug.updateTable(),version()
^
/* update the view directly: succeeds */
update bug.viewA set fieldA='B' where keyA='1';
^
/* update the view din function: FAILS with ERROR 1146 (42S02): Table 'bug.journalA' doesn't exist*/
select bug.updateView()
^
/* yes it does exist! */
show create table bug.journalA^
Suggested fix:
Find the table!