[MDEV-19645] Update trigger with stored functions not working on updates with joins Created: 2019-05-30  Updated: 2019-06-03  Resolved: 2019-06-03

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.3.15
Fix Version/s: 10.2.25, 5.5.65, 10.1.41, 10.3.16, 10.4.6

Type: Bug Priority: Minor
Reporter: Henning Sander Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: trigger
Environment:

Suse SLES12 and Debian stretch


Issue Links:
Duplicate
duplicates MDEV-19491 update query stopped working after ma... Closed

 Description   

Description:
Table with update trigger containing a user defined function.
Doing an update on the table with a join produces the error "ERROR 1305 (42000): FUNCTION <db>.<fkt> does not exist".
Calling the function befor doing the update, the update is working.
Only on MariaDB 10.3.15, not in 10.3.14

Preconditions:
MariaDB 10.3.15 is installed from box.
Only information_schema, mysql and performance_schema are available after installation. No user databases are present.
All actions are performed with the user root.

SQL Steps to reproduce:
create database triggertest;
use triggertest;

create table tu (
ID integer not null default '0',
val integer not null default '0'
);

create table tc (
ID integer not null default '0',
val integer not null default '0'
);

delimiter //

create function addone(i integer) returns integer
deterministic
sql security invoker
begin
return i+1;
end//

create trigger tu_au after update on tu
for each row
begin
insert into tc (ID, val)
select NEW.ID, addone(NEW.val) from dual;
end//
delimiter ;

insert into tu (ID, val) values (1,1);

create temporary table t select 1 as ID;

update tu join t using(ID) set val = 2 where ID = 1;
– Error: ERROR 1305 (42000): FUNCTION triggertest.addone does not exist

select addone(1);

update tu join t using(ID) set val = 3 where ID = 1;
– no Error



 Comments   
Comment by Elena Stepanova [ 2019-06-03 ]

Thanks for the report. The problem was fixed in the scope of MDEV-19491, the fix will be included into the next release.

Generated at Thu Feb 08 08:53:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.