MySQL Bugs: #87442: using SIGNAL SQLSTATE '01xxx' in a stored function does not return a warning

Bug #87442	using SIGNAL SQLSTATE '01xxx' in a stored function does not return a warning
Submitted:	16 Aug 2017 15:04	Modified:	3 Nov 2018 23:09
Reporter:	Robert Humphries	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.19-log (64bit)	OS:	Windows (Windows 10 64bit)
Assigned to:		CPU Architecture:	Any
Tags:	signal, Stored Functions, warnings

Description:
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.)

Client Software:
Windows 10 64bit, using SQLYog v12.4.2 64bit as a client program
Server Software:
MySQL 5.7.19-log (64bit) running locally on windows

How to repeat:
/* Function/Procedure Setup */

DELIMITER $$

CREATE FUNCTION `f`() RETURNS INT(11)
BEGIN
	SIGNAL SQLSTATE '01234';  -- signal a warning
	RETURN 5;
END$$

CREATE PROCEDURE `p`()
BEGIN
	SIGNAL SQLSTATE '01234';
END$$

CREATE PROCEDURE `p2`()
BEGIN
	SIGNAL SQLSTATE '01234';
	SELECT RAND() INTO @unused;
END$$

DELIMITER ;

/* Queries */
SELECT f();
CALL p();
CALL p2();

/* Result Readout */
3 queries executed, 3 success, 0 errors, 1 warnings

Query: SELECT f()

1 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec
-----------------------------------------------------------

Query: CALL p()

0 row(s) affected, 1 warning(s)

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

Warning Code : 1642
Unhandled user-defined warning condition
-----------------------------------------------------------

Query: CALL p2()

1 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

/* Expected Result */
3 queries executed, 3 success, 0 errors, 3 warnings

Query: SELECT f()

1 row(s) affected, 1 warning(s)

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec

Warning Code : 1642
Unhandled user-defined warning condition
-----------------------------------------------------------

Query: CALL p()

0 row(s) affected, 1 warning(s)

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

Warning Code : 1642
Unhandled user-defined warning condition
-----------------------------------------------------------

Query: CALL p2()

1 row(s) affected, 1 warning(s)

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

Warning Code : 1642
Unhandled user-defined warning condition

Suggested fix:
It would seem that any command after the SIGNAL will overwrite the SIGNAL warning as far as I can tell. This makes it impossible to throw warnings in a stored function (as the RETURN has to be the last command), and makes it harder to throw a WARNING in a procedure, and as far as I can tell, makes it impossible to throw more than one. I would suggest building a list of warnings (both from SIGNAL and native queries) during the routine, and then returning when the routine returns.

Hi!

What you describe above is expected behaviour. That is how MySQL server stored routines where designed to work. All warnings are erased when the next SQL statement or logic command (in stored routines) is executed. On the start of the statement or command, all warnings are fully erased.

This is fully explained in the following two chapters of our manual:

13.6.7.6 Scope Rules for Handlers
13.6.7.7 The MySQL Diagnostics Area

You will find that explanations provided are highly, highly detailed.

Please, let us know if the manual is clear on this issue.

Thanks for the references to those sections of the manual. Before I reply in relation to those sections, then my initial thoughts in regards to how a stored function should work come reading the '13.6.7.5 SIGNAL Syntax' section of the manual. My function 'f' is the function used in the last example on that page - however if you run the function, or the full example (where I thought the issue was that the last statement is the insert, not the function call which throws the warning) then you find that the behaviour is not as the manual states. Of course, I do not know if the manual or intended behaviour is wrong here.

I am afraid that I can not see anything relating to the use of SIGNAL or warning persistence in section 13.6.7.6 Scope Rules for Handlers of the manual. I under about the limited scope of handlers, which (with the added rule of handlers not being in scope for anything arising in themselves or handlers in the same scope - although I am guessing that an issue arising in a handler could be caught by a handler in a higher / outer scope) is basically the same scope rules that apply for routine parameters.

After reading over section 13.6.7.7 The MySQL Diagnostics Area in the manual, then I see where you are coming from in relation to any (non-diagnostic) statement clearing the diagnosis area. However, within section 13.6.7.7.4 How the Diagnostics Area Stack Works, it does state that:
"Any warning or error conditions occurring during stored program execution then are added to the current diagnostics area, except that, for triggers, only errors are added. When the stored program ends, the caller sees these conditions in its current diagonstics area."
I am guessing that this is meaning to refer to any warning or error that is not cleared by a subsequent statement is added to the diagnosis area of the caller, but I feel that is fairly unclear - as I would feel that a warning that occurred during statement 3/5 has occurred during program execution.
Finally, on the note of the above section, then given it is impossible to throw a warning in a stored function (as the RETURN will always be the last statement), then why does it state that only triggers will not return warnings?

I am sorry if I am still not interpreting the manual correctly here, and therefore am feeling that MySQL should be doing something it is not intended to, but I hope the above explanation explains where I am coming from a bit more.

I fail to grasp your reasoning.

Every SQL statement or any language construct in the stored routines will delete all the warnings at its start.

What is unclear here ???? RETURN is a logical construct and it will delete the warnings. Everything is clear and well explained. If you wish to see the warnings after certain statement , you should run:

SHOW WARNINGS;

after it and before RETURN, whatever it was. You will see your warnings and function will return. That will prove that our manual is correct and that stored function can return warnings. Of course, the stored function should have some SQL statements that generate warnings at all.

Also, triggers do not RETURN anything.

I believe the parts of the manual I quoted such that, within a stored procedure/function, there is an exception to the rule that 'Every SQL statement or any language construct in the stored routines will delete all the warnings at its start.'

"Any warning or error conditions occurring during stored program execution then are added to the current diagnostics area, except that, for triggers, only errors are added. When the stored program ends, the caller sees these conditions in its current diagonstics area."

The above is taken from section 13.6.7.7.4 "How the Diagnostics Area Stack Works". It does not state that Any warning or error conditions that occur during the final statement of a stored program's execution are then added to the current diagnostics area (which are then seen by the caller in its diagnositics area). It states that any warning or error conditions occurring during stored program execution, which I interpret as during the entire execution. This would make sense from a logically viewpoint, as the caller has run one command (CALL p; OR SELECT f();).

In relation to the stored function example, then the following code will not work:
mysql> delimiter //
mysql> CREATE FUNCTION f () RETURNS INT
    -> BEGIN
    ->   SIGNAL SQLSTATE '01234';  -- signal a warning
    ->   RETURN 5;
    -> END//
mysql> delimiter ;
mysql> CREATE TABLE t (s1 INT);
mysql> INSERT INTO t VALUES (f());
mysql> SHOW WARNINGS;

The final line will return an empty result set - but the manual says it will return the warning SIGNALed by the first line in the BEGIN ... END block.

Further, should you edit the function as such:
mysql> delimiter //
mysql> CREATE FUNCTION f () RETURNS INT
    -> BEGIN
    ->   SIGNAL SQLSTATE '01234';  -- signal a warning
    ->   SHOW WARNINGS;
    ->   RETURN 5;
    -> END//
mysql> delimiter ;
mysql> CREATE TABLE t (s1 INT);
mysql> INSERT INTO t VALUES (f());

You will get the following error:
Error Code: 1415
Not allowed to return a result set from a function

Which is correct - you can not use SHOW WARNINGS; in a stored function, as you can't return a result set, and there is no way to capture the output. You could use GET DIAGNOSTICS, but the whole point of using SIGNAL is to cause a warning to appear to the caller.

Finally, I am aware that triggers do not 'RETURN' anything, but they can return errors, in the sense that if an error is SIGNALed or thrown by a statement, and is not handled, the caller will be aware of the error. I try to use upper case for SQL syntax, and lowercase (as when I was talking about triggers) to refer to the English meaning of a word.

True.

Manual can be perfected further in the manner that you described.

Posted by developer:
 
The difference you see with the warning not being returned from a stored function occur as of MySQL 5.6.4, which implemented GET DIAGNOSTICS and fixed many condition-handling issues. For details, see:
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html

I've made some clarifications in the reference manual:

For https://dev.mysql.com/doc/refman/5.5/en/signal.html

At the end, added this note:

The preceding example works as described in MySQL 5.5 because RETURN does not clear diagnostics as prescribed by standard SQL. In MySQL 5.6 and higher, no warning is returned: RETURN clears the diagnostics area, so there is no way to return an SQL warning from a stored function. This change is not backward compatible, but the resulting behavior is more like standard SQL.

For https://dev.mysql.com/doc/refman/5.6/en/signal.html (also 5.7, 8.0)
For "Class = '01' (warning)" bullet, added this:

Warnings cannot be returned from stored functions because the RETURN statement that causes the function to return clears the diagnostic area. The statement thus clears any warnings that may have been present there (and resets warning_count to 0).

For "13.6.7.7.4 How the Diagnostics Area Stack Works", where it said this:
"Any warning or error conditions occurring during stored program execution
then are added to the current diagnostics area, except that, for triggers,
only errors are added. When the stored program ends, the caller sees these
conditions in its current diagonstics area."

I've changed that to:

Any warning or error conditions in the popped diagnostics areas then are added to the current diagnostics area, except that, for triggers, only errors are added. When the stored program ends, the caller sees these conditions in its current diagonstics area.

(Note: that occurs only in 5.7, 8.0, since 5.6 doesn't have stacked DAs.
See https://dev.mysql.com/doc/refman/5.7/en/diagnostics-area.html#diagnostics-area-stack)