Bug #76808 function updating a view fails to find table that actually exists
Submitted: 23 Apr 2015 12:33 Modified: 25 May 2016 17:25
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.73 OS:Linux
Assigned to: MySQL Verification Team CPU Architecture:Any

[23 Apr 2015 12:33] Dave Pullin
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!
[25 May 2015 13:47] MySQL Verification Team
Verified as presented on
  5.1.73
  5.6.24
  5.7.7-rc

Thank you for the bug report

kind regards
Bogdan Kecman
[25 May 2016 17:25] Paul DuBois
Posted by developer:
 
Noted in 5.5.51, 5.6.32, 5.7.13 changelogs.

If a stored function updated a view for which the view table had a
trigger defined that updated another table, it could fail and report
an error that an existing table did not exist.
[25 May 2016 17:28] Paul DuBois
Posted by developer:
 
Correction: 5.7.14 changelog, not 5.7.13.