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

Update trigger with stored functions not working on updates with joins

    XMLWordPrintable

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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.