[MDEV-13554] Stored function does not return warnings generated inside the function Created: 2017-08-16  Updated: 2018-12-17

Status: Confirmed
Project: MariaDB Server
Component/s: Documentation, Stored routines
Affects Version/s: 10.0, 10.1, 10.2.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Robert Humphries Assignee: Ian Gilfillan
Resolution: Unresolved Votes: 0
Labels: function, procedure, signal, warning
Environment:

Operating System: CentOS Linux release 7.3.1611 (Core)
Originally found on an Amazon EC2 Instance, with Plesk as the control panel for the server. MariaDB had been upgraded twice (from the CentOS's own version to 10.1 and then to 10.2)


Attachments: Text File functionSqlAndResults.txt    

 Description   

Note: This affects MySQL 5.7.19 as well (bug report 87442).

When you have a function (e.g. f()) that uses SIGNAL SQLSTATE '01xxx' (where xxx is any three digits), then running SELECT f(); will return a success message (and the return value) or an error message, but in both cases will not return any warnings.

Should you modify the function so it is a procedure (only changing CREATE FUNCTION to CREATE PROCEDURE and removing RETURNS <TYPE> and RETURN <return value>), then run CALL F(); the procedure will return a warning ONLY if the SIGNAL is the last line. (In a function it is impossible for the SIGNAL to be the last line, as you are required to have a RETURN.)

Please see attached file for example function/procedures, and the results.

 Comments   
Comment by Robert Humphries [ 2017-08-17 ]

I have exchanged a couple of messages with one of the MySQL developers within the linked bug report, and they have come to conclusion that it is a documentation issue, and that the behaviour is within the expect design of MySQL.

I still feel that the behaviour I was expecting makes more sense, however that is just my opinion.

Regardless, the MariaDB documentation does not contain the same example in the SIGNAL section, and the diagnostics area works differently in MariaDB to MySQL.

Importantly, then in the Diagnostics Area Manual Page for MariaDB:
"When a table-based statement (like INSERT) is executed, the old data in the diagnostics area is cleared even if the statement doesn't produce any condition. However, statements which don't access any table (like SET, or a SELECT with no FROM clause) is executed and produces no warnings, the diagnostics area remains unchanged."

This does actually hold true for within a stored procedure (my original test case was in a stored function on MariaDB, I then replicated on MySQL and expanded) - in my test case I have attached, both CALL p() and CALL p2() return the expected warning*. However, f() still does not return the warning, and I can see no reason in the manual why the stored function is behaving differently, as I would expect "RETURN 5;" to be treated as a 'statement which don't access any table'.

*A further function, p3 (where `affiliates` is a table that exists):

CREATE PROCEDURE `p3`()
 
BEGIN
 
	SIGNAL SQLSTATE '01234';
 
	SELECT RAND() INTO @unused FROM `affiliates` LIMIT 1;
 
END$$

Will not return a the warning - but then that is behaviour that is expected according to the MariaDB manual (but the behaviour is not expected according to the current MySQL manual).

In light of the information above, I am not sure what to do in regards to this bug report. The upstream issue no longer applies to MariaDB (as the documentation issue is not present in the MariaDB, and even if MySQL re-classes the bug, it appears this is now separate due to the different handling of diagnostic areas). Is the fact the stored function isn't returning the signalled warning a bug, or have I missed something in the manual? If it is a bug, is it best to close this bug report and open a new report, or continue within this bug report? Sorry I have made a bit of a mess with this report!

Comment by Elena Stepanova [ 2017-09-15 ]

I agree there is inconsistency which the current documentation in KB doesn't seem to explain. In the test case below I got rid of SIGNAL, to make sure it's not the cause of the problem. Instead, I use SELECT 1/0 INTO @a to generate a warning.

With procedure, the warning is generated, not cleared up, and is returned after the procedure call:

Procedure

 
CREATE PROCEDURE p() 
BEGIN
 
  SELECT 1/0 INTO @a;
 
  SELECT 1;
 
END $$

Results with the procedure

 
MariaDB [test]> CALL p();
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+
 
1 row in set (0.00 sec)
 
 
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> SHOW WARNINGS;
 
+---------+------+---------------+
 
| Level   | Code | Message       |
 
+---------+------+---------------+
 
| Warning | 1365 | Division by 0 |
 
+---------+------+---------------+
 
1 row in set (0.00 sec)

Function

 
CREATE FUNCTION f() RETURNS INT 
BEGIN
 
  SELECT 1/0 INTO @a;
 
  RETURN 1;
 
END $$

Results with the function

 
MariaDB [test]> SELECT f();
 
+------+
 
| f()  |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.00 sec)

However, neither the function call itself nor RETURN inside the function clear the condition if it's already set. The previously set diagnostics area is still visible inside the function, and it is still there after the function execution:

DROP FUNCTION IF EXISTS f2;
 
 
 
delimiter $$
 
CREATE FUNCTION f2() RETURNS INT 
BEGIN
 
  GET DIAGNOSTICS @num_conditions_inside_function = NUMBER;
 
  GET DIAGNOSTICS CONDITION 1 @errno_inside_function = MYSQL_ERRNO, @message_text_inside_function = MESSAGE_TEXT;
 
  RETURN 1;
 
END $$
 
delimiter ;
 
 
 
# Let us clear the diagnostics area
 
CREATE OR REPLACE TABLE new_table (i INT);
 
 
 
GET DIAGNOSTICS @num_conditions = NUMBER;
 
SELECT @num_conditions;
 
+-----------------+
 
| @num_conditions |
 
+-----------------+
 
|               0 |
 
+-----------------+
 
1 row in set (0.00 sec)


# Now generate a new condition
 
SELECT 1/0 INTO @b;
 
 
 
GET DIAGNOSTICS @num_conditions = NUMBER;
 
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @message_text = MESSAGE_TEXT;
 
SELECT @num_conditions, @errno, @message_text;
 
+-----------------+--------+---------------+
 
| @num_conditions | @errno | @message_text |
 
+-----------------+--------+---------------+
 
|               1 |   1365 | Division by 0 |
 
+-----------------+--------+---------------+
 
1 row in set (0.00 sec)


# Check if f2 can see this condition
 
SELECT f2();
 
+------+
 
| f2() |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.00 sec)
 
 
 
SELECT @num_conditions_inside_function, @errno_inside_function, @message_text_inside_function;
 
+---------------------------------+------------------------+-------------------------------+
 
| @num_conditions_inside_function | @errno_inside_function | @message_text_inside_function |
 
+---------------------------------+------------------------+-------------------------------+
 
|                               1 |                   1365 | Division by 0                 |
 
+---------------------------------+------------------------+-------------------------------+
 
1 row in set (0.00 sec)


# Check that the condition is still there after the call
 
 
 
GET DIAGNOSTICS @num_conditions_new = NUMBER;
 
GET DIAGNOSTICS CONDITION 1 @errno_new = MYSQL_ERRNO, @message_text_new = MESSAGE_TEXT;
 
SELECT @num_conditions_new, @errno_new, @message_text_new;
 
 
 
+---------------------+------------+-------------------+
 
| @num_conditions_new | @errno_new | @message_text_new |
 
+---------------------+------------+-------------------+
 
|                   1 |       1365 | Division by 0     |
 
+---------------------+------------+-------------------+
 
1 row in set (0.00 sec)

Finally, if a condition is generated inside the function (via SIGNAL or otherwise), it does reset the previous diagnostics area, but the new condition is not visible outside the function. The test below first generates ER_BAD_TABLE_ERROR, then calls a function which generates ER_DIVISION_BY_ZERO. After the function call, neither the old nor the new conditions are visible anymore.

DROP FUNCTION IF EXISTS f3;
 
 
 
delimiter $$
 
CREATE FUNCTION f3() RETURNS INT 
BEGIN
 
  SELECT 1/0 INTO @c;
 
  GET DIAGNOSTICS @num_conditions_inside_function = NUMBER;
 
  GET DIAGNOSTICS CONDITION 1 @errno_inside_function = MYSQL_ERRNO, @message_text_inside_function = MESSAGE_TEXT;
 
  RETURN 1;
 
END $$
 
delimiter ;


DROP TABLE IF EXISTS non_existing_table;
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
GET DIAGNOSTICS @num_conditions = NUMBER;
 
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @message_text = MESSAGE_TEXT;
 
SELECT @num_conditions, @errno, @message_text;
 
+-----------------+--------+-----------------------------------------+
 
| @num_conditions | @errno | @message_text                           |
 
+-----------------+--------+-----------------------------------------+
 
|               1 |   1051 | Unknown table 'test.non_existing_table' |
 
+-----------------+--------+-----------------------------------------+
 
1 row in set (0.00 sec)


SELECT f3();
 
+------+
 
| f3() |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.00 sec)
 
 
 
SELECT @num_conditions_inside_function, @errno_inside_function, @message_text_inside_function;
 
+---------------------------------+------------------------+-------------------------------+
 
| @num_conditions_inside_function | @errno_inside_function | @message_text_inside_function |
 
+---------------------------------+------------------------+-------------------------------+
 
|                               1 |                   1365 | Division by 0                 |
 
+---------------------------------+------------------------+-------------------------------+
 
1 row in set (0.00 sec)


# Check whether any of the conditions are there after the call
 
 
 
GET DIAGNOSTICS @num_conditions_new = NUMBER;
 
GET DIAGNOSTICS CONDITION 1 @errno_new = MYSQL_ERRNO, @message_text_new = MESSAGE_TEXT;
 
SELECT @num_conditions_new, @errno_new, @message_text_new;
 
+---------------------+------------+-------------------+
 
| @num_conditions_new | @errno_new | @message_text_new |
 
+---------------------+------------+-------------------+
 
|                   0 | NULL       | NULL              |
 
+---------------------+------------+-------------------+
 
1 row in set (0.00 sec)

I'll leave it to serg to decide whether this difference between functions and procedures is expected, and if it is, it should be clarified somewhere here: https://mariadb.com/kb/en/library/diagnostics-area/ , in "How the diagnostics area is populated and cleared".

Note: the examples below work only with sql_mode=ERROR_FOR_DIVISION_BY_ZERO.

In regard to MariaDB versions, for 5.5 does not seem to be affected, 10.x are.

Comment by Sergei Golubchik [ 2018-12-17 ]

It's intentional.

According to the SQL standard, any statement (excluding GET DIAGNOSTICS) should empty the diagnostics area. SQL:2016, Part 2, subclause 4.36 Diagnostics area:
> At the beginning of the execution of any <SQL procedure statement> that is not an <SQL diagnostics statement>, the first diagnostics area is emptied.

In MariaDB (and in historically in MySQL, although apparently not anymore) additionally any statement that does not use tables and does not generate warnings also preserves the diagnostics area. Otherwise every

SELECT @num_conditions_new, @errno_new, @message_text_new;

in your test above would've reset all warnings.
Compare with:

drop table if exists foobar;
 
Warnings:
 
Note    1051    Unknown table 'test.foobar'
 
GET DIAGNOSTICS @num_conditions = NUMBER;
 
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @message_text = MESSAGE_TEXT;
 
SELECT @num_conditions, @errno, @message_text;
 
@num_conditions @errno  @message_text
 
1       1051    Unknown table 'test.foobar'
 
select 1;
 
1
 
1
 
GET DIAGNOSTICS @num_conditions = NUMBER;
 
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @message_text = MESSAGE_TEXT;
 
SELECT @num_conditions, @errno, @message_text;
 
@num_conditions @errno  @message_text
 
1       1051    Unknown table 'test.foobar'
 
select 1/0;
 
1/0
 
NULL
 
Warnings:
 
Warning 1365    Division by 0
 
GET DIAGNOSTICS @num_conditions = NUMBER;
 
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @message_text = MESSAGE_TEXT;
 
SELECT @num_conditions, @errno, @message_text;
 
@num_conditions @errno  @message_text
 
1       1365    Division by 0

Note that select 1 preserves the warning (because it doesn't use tables), and so does SELECT @num_conditions, @errno, @message_text itself. But select 1/0 empties the diagnostics area first, it does not append a warning to the existing list.

Comment by Robert Humphries [ 2018-12-17 ]

Hi Sergei,

Would you mind just confirming why there is the difference between Stored Procedures and Functions, in MariaDB specifically?

As far as I can tell:

  • The diagnostic area is not cleared upon a statement-less return, as a stored procedure where the last statement produces a warning, will return the warning.
  • The RETURN statement does however clear the diagnostic area. I understand this is inline with the SQL standard, but isn't this inconsistent with MariaDB's behaviour, in that the RETURN statement doesn't use tables or generate any warnings/errors of its own?

Thanks,
Robert

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