[MDEV-26602] User defined function with SIGNAL SQLSTATE is always evaluated inside built-in IF() function Created: 2021-08-27  Updated: 2021-09-14  Resolved: 2021-09-14

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.3.31, 10.4.21, 10.5.12, 10.6.4

Type: Bug Priority: Critical
Reporter: Edward Stoever Assignee: Oleksandr Byelkin
Resolution: Not a Bug Votes: 0
Labels: None

Attachments: PNG File two-different-installs-of-MariaDB-on-DEBIAN.png    

 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

 Comments   
Comment by Roel Van de Paar [ 2021-08-30 ]

10.2 Also produces a different result than 10.3-10.7:

SET sql_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
DELIMITER //
 
CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
//
 
DELIMITER ;
 
SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');

Leads to:

10.2.41 (Debug)

 
10.2.41-dbg>SET sql_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
10.2.41-dbg>DELIMITER //
 
10.2.41-dbg>CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
    -> BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
    -> //
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
10.2.41-dbg>DELIMITER ;
 
10.2.41-dbg>SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
 
+----------------------------------------------------------------+
 
| IF(1=2, preconditionerror('there is a problem'), 'no problem') |
 
+----------------------------------------------------------------+
 
| no problem                                                     |
 
+----------------------------------------------------------------+
 
1 row in set (0.00 sec)

10.3.32 d9526ae60820d8b8d511f94edfff3ea2888766ca (Debug)

 
10.3.32-dbg>SET sql_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10.3.32-dbg>DELIMITER //
 
10.3.32-dbg>CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
    -> BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
    -> //
 
Query OK, 0 rows affected (0.002 sec)
 
 
 
10.3.32-dbg>DELIMITER ;
 
10.3.32-dbg>SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
 
ERROR 1644 (45000): there is a problem

10.4.22 46c3e7e3537c31a94289033bfeccf3faf8d4069e (Debug)

 
10.4.22-dbg>SET sql_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10.4.22-dbg>DELIMITER //
 
10.4.22-dbg>CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
    -> BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
    -> //
 
Query OK, 0 rows affected (0.007 sec)
 
 
 
10.4.22-dbg>DELIMITER ;
 
10.4.22-dbg>SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
 
ERROR 1644 (45000): there is a problem

10.5.13 0268b8712288d46fbd8a43fdef6bada399b68dff (Debug)

 
10.5.13-dbg>SET sql_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
Query OK, 0 rows affected (0.003 sec)
 
 
 
10.5.13-dbg>DELIMITER //
 
10.5.13-dbg>CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
    -> BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
    -> //
 
Query OK, 0 rows affected (0.012 sec)
 
 
 
10.5.13-dbg>DELIMITER ;
 
10.5.13-dbg>SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
 
ERROR 1644 (45000): there is a problem

10.6.5 9ac1ac006197c8979db1dc73f4e983f623e831e8 (Debug)

 
 
 
10.6.5-dbg>SET sql_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10.6.5-dbg>DELIMITER //
 
10.6.5-dbg>CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
    -> BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
    -> //
 
Query OK, 0 rows affected (0.005 sec)
 
 
 
10.6.5-dbg>DELIMITER ;
 
10.6.5-dbg>SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
 
ERROR 1644 (45000): there is a problem

10.7.0 05e29e177df243b700392b797e26cae43fd3181e (Debug)

 
10.7.0-dbg>SET sql_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
10.7.0-dbg>DELIMITER //
 
10.7.0-dbg>CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
    -> BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
    -> //
 
Query OK, 0 rows affected (0.010 sec)
 
 
 
10.7.0-dbg>DELIMITER ;
 
10.7.0-dbg>SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
 
ERROR 1644 (45000): there is a problem

Not immediately sure what causes the differences (all settings seem identical) nor what the correct outcome is in this case, nor why the issue happens on the same version on the original reporter's two instances.

Comment by Roel Van de Paar [ 2021-09-07 ]

sanja Thanks for checking. My testing was done in CS.

Comment by Oleksandr Byelkin [ 2021-09-13 ] 

SET sql_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
 
DELIMITER //;
 
CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
//
 
DELIMITER ;//
 
SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
 
DROP FUNCTION preconditionerror;

Comment by Oleksandr Byelkin [ 2021-09-13 ]

test suite does not allow to reproduce, only command line does, probably not all relevant settings are mentioned in the test suite (usually charsets)

Comment by Oleksandr Byelkin [ 2021-09-13 ]

SQL_MODE is irrelivant, but character_set_connection is relevant to the bug, correct test case:

SET character_set_connection="utf8";
 
DELIMITER //;
 
CREATE FUNCTION preconditionerror (msg VARCHAR(128)) RETURNS INT DETERMINISTIC
 
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; RETURN 0; END;
 
//
 
DELIMITER ;//
 
SELECT IF(1=2, preconditionerror('there is a problem'), 'no problem');
 
DROP FUNCTION preconditionerror;

Comment by Roel Van de Paar [ 2021-09-13 ]

For anyone wanting to test the updated testcase on the command line, change the delimiter syntax from //; to // and from ;// to ; to make it work there.

Comment by Oleksandr Byelkin [ 2021-09-14 ]

The function is evalueted because description of the function is wrong, it should be NOT DETERMINISTIC (it has side effect of breaking execution and issuing the error).

So it is not a but at all.

Comment by Roel Van de Paar [ 2021-09-14 ]

sanja Why is the output on 10.2 different? Thanks

Comment by Oleksandr Byelkin [ 2021-09-14 ]

Roel I mentioned you because you have checked it

It is not on 10.2, because 10.2 process constants in other way

Comment by Roel Van de Paar [ 2021-09-14 ]

Moved to MDEV as suggested.

Generated at Thu Feb 08 09:46:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.