MySQL Bugs: #70358: GET DIAGNOSTICS produces a warning with level 'error', suppressed in procedure

Bug #70358	GET DIAGNOSTICS produces a warning with level 'error', suppressed in procedure
Submitted:	16 Sep 2013 18:50	Modified:	17 Sep 2013 17:34
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.6.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Initially reported and discussed at https://mariadb.atlassian.net/browse/MDEV-4943.

GET DIAGNOSTICS can behave in a strange way, we haven't found proper explanation in the documentation. 

1. GET DIAGNOSTICS CONDITION <wrong condition number> produces a warning, but this warning has level Error. It doesn't look quite right:

MySQL [test]> GET DIAGNOSTICS CONDITION 0 @a = MYSQL_ERRNO;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [test]> SHOW WARNINGS;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Error | 1758 | Invalid condition number |
+-------+------+--------------------------+
1 row in set (0.01 sec)

MySQL [test]> SHOW ERRORS;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Error | 1758 | Invalid condition number |
+-------+------+--------------------------+
1 row in set (0.00 sec)

2. The same statement inside a stored procedure does not produce anything, the error-warning seems to be suppressed somewhere on the way:

MySQL [test]> drop procedure if exists p; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [test]> delimiter || 
MySQL [test]> create procedure p() 
    -> begin 
    ->   GET DIAGNOSTICS CONDITION 0 @a = MYSQL_ERRNO ; 
    -> end || 
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> delimiter ; 
MySQL [test]> call p(); 
Query OK, 0 rows affected (0.01 sec)

Altogether it seems inconsistent. It's either an error, or a warning, or nothing, but now it's all at once. If it's intentional behavior, it would be good to have it described in the manual. 

How to repeat:
GET DIAGNOSTICS CONDITION 0 @a = MYSQL_ERRNO;
SHOW WARNINGS;

drop procedure if exists p; 
delimiter || 
create procedure p() 
begin 
  GET DIAGNOSTICS CONDITION 0 @a = MYSQL_ERRNO ; 
end || 
delimiter ; 

call p();

Hello Elena,

Thank you for the report and test case.
Verified as reported.

Imho the warning seems to be documented behavior "A warning occurs if the condition number is not in the range from 1 to the number of condition areas that have information" - http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html
But again the warning of Level "Error" seems to be bit unusual here, this should be fixed or at least documented.

//

mysql> GET DIAGNOSTICS CONDITION 0 @a = MYSQL_ERRNO;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Error | 1758 | Invalid condition number |
+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> show errors;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Error | 1758 | Invalid condition number |
+-------+------+--------------------------+
1 row in set (0.00 sec)

// Without SHOW statements, warning was supressed..

mysql> drop procedure if exists p;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> delimiter ||
mysql> create procedure p()
    ->  begin
    ->    GET DIAGNOSTICS CONDITION 0 @a = MYSQL_ERRNO ;
    ->    show warnings;
    ->    show errors;
    ->
    ->  end ||
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call p();
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Error | 1758 | Invalid condition number |
+-------+------+--------------------------+
1 row in set (0.00 sec)

+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Error | 1758 | Invalid condition number |
+-------+------+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Thanks,
Umesh

Hi Elena!

The problem with the condition being suppressed inside a stored procedure has
been fixed in 5.7. I have not checked, but I would guess it was fixed as
a consequence of the implementation of GET STACKED DIAGNOSTICS.

So now you get:
CALL p1();
Warnings:
Error	1758	Invalid condition number

That the level is 'error' seems a bit strange, but as far as I can see it was
part of the GET DIAGNOSTICS design. We could downgrade it to 'Warning' as this 
would be more consistent with GET DIAGNOSTICS not failing.

IIRC, the standard says an exception condition should be raised without
disturbing the contents of the DA. It is an error, it just doesn't affect
the DA.