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

User defined function with SIGNAL SQLSTATE is always evaluated inside built-in IF() function

    XMLWordPrintable

Details

    Description

      This behavior is found in Community and Enterprise editions. One of my databases is not with the unexpected behavior (green), see image "two-different-installs-of-MariaDB-on-DEBIAN". The globals of these two machines are identical. The mariadbd files are identical on these machines. This is what I believe should be the expected behavior:

      MariaDB [green]> delimiter //
      MariaDB [green]> CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
          -> BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
          -> //
      MariaDB [green]> delimiter ;
      MariaDB [green]> SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
      IF(1=2, preconditionerror('there is a problem'), 'no problem')
      no problem
      MariaDB [green]>
      

      This is the behavior most often seen:

      MariaDB [purple]> delimiter //
      MariaDB [purple]> CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
          ->   BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
          -> //
      MariaDB [purple]> delimiter ;
      MariaDB [purple]> SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
      ERROR 1644 (45000): there is a problem
      MariaDB [purple]>
      

      SQL_MODE for these two are identical: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            edward Edward Stoever
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.