Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19645

Update trigger with stored functions not working on updates with joins

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                sandix Henning Sander
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: