Bug #80732 Output parameter is not assigned when rising SIGNAL

• Submitted: 14 Mar 2016 17:15
• Modified: 25 Oct 2018 12:52
• Reporter: William Chiquito
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.7.11
• OS: Linux
• CPU Architecture: Any
• Tags: out parameter, signal, stored procedure

[14 Mar 2016 17:15] William Chiquito

Description:
Output parameter is not assigned when rising SIGNAL (Class >= '02' (exception)).

How to repeat:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.11    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `sp_bug`;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `sp_bug`(OUT `out_parameter` INT UNSIGNED)
    -> BEGIN
    ->     DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->     BEGIN
    ->         SET `out_parameter` := 2;
    ->         SELECT `out_parameter`;        
    ->         RESIGNAL;
    ->     END;
    ->     SET `out_parameter` := 1;
    ->     SELECT `out_parameter`;
    ->     SIGNAL SQLSTATE '45000' SET
    ->         MYSQL_ERRNO = 2,
    ->         MESSAGE_TEXT = 'Too long';
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SET @`out_parameter` := NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL `sp_bug`(@`out_parameter`);
+-----------------+
| `out_parameter` |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

+-----------------+
| `out_parameter` |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

ERROR 2 (45000): Too long

mysql> SHOW WARNINGS;
+-------+------+----------+
| Level | Code | Message  |
+-------+------+----------+
| Error |    2 | Too long |
+-------+------+----------+
1 row in set (0.01 sec)

mysql> SELECT @`out_parameter`;
+------------------+
| @`out_parameter` |
+------------------+
| NULL             |
+------------------+
1 row in set (0.00 sec)

[28 Jul 2017 14:46] MySQL Verification Team

Hi!

This is truly a bug and I have fully repeated your test case, with some small changes:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.7.18-debug |
+--------------+
1 row in set (0.00 sec)

mysql> drop procedure if exists spbug;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> delimiter $$
mysql> CREATE PROCEDURE `spbug`(OUT `outpar` INT UNSIGNED)
    -> BEGIN
    ->   DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->   BEGIN
    ->     SET `outpar` := 2;
    ->     SELECT `outpar`;
    ->     RESIGNAL;
    ->   END;
    ->   SET `outpar` := 1;
    ->   SELECT `outpar`;
    ->   SIGNAL SQLSTATE '45000' SET    MYSQL_ERRNO = 2, MESSAGE_TEXT = 'Too long';
    -> END$$
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> set @param := NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> call spbug(@param);
+----------+
| `outpar` |
+----------+
|        1 |
+----------+
1 row in set (0.03 sec)

+----------+
| `outpar` |
+----------+
|        2 |
+----------+
1 row in set (0.03 sec)

ERROR 2 (45000): Too long
mysql> show warnings;
+-------+------+----------+
| Level | Code | Message  |
+-------+------+----------+
| Error |    2 | Too long |
+-------+------+----------+
1 row in set (0.00 sec)

mysql> select @param;
+--------+
| @param |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

Thank you for your contribution.

[25 Oct 2018 12:52] Paul DuBois

Posted by developer:
 
Added new section with appropriate cross references elsewhere, in 5.5 through 8.0 reference manuals.
8.0 link: https://dev.mysql.com/doc/refman/8.0/en/conditions-and-parameters.html