MySQL Bugs: #13914: IFNULL is returning garbage in stored procedure

Bug #13914	IFNULL is returning garbage in stored procedure
Submitted:	11 Oct 2005 7:47	Modified:	18 Oct 2005 16:31
Reporter:	Are Casilla	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.13, 5.0.15-BK	OS:	Linux (Linux)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
SET strunk = IFNULL(strunk,'DEF') is returning garbage in stored procedure.

How to repeat:
DROP PROCEDURE IF EXISTS RateGetTrunk;
delimiter |

CREATE PROCEDURE RateGetTrunk (OUT strunk varchar(128))
  NOT DETERMINISTIC
BEGIN

  DECLARE mydialst    varchar(255);  
  
			
  set strunk = 'Local';

  SET strunk = IFNULL(strunk,'DEF'); 
  /* SET strunk = 'test';   */

END
|
delimiter ;

CALL RateGetTrunk(@a);
select @a;

This was not an issue in 5.0.12

Thank you for a problem report. At the first sight, it looks like you are right:

mysql> delimiter |
mysql> CREATE PROCEDURE RateGetTrunk (OUT strunk varchar(128))
    ->   NOT DETERMINISTIC
    -> BEGIN
    ->   DECLARE mydialst    varchar(255);
    ->
    ->   set strunk = 'Local';
    ->
    ->   SET strunk = IFNULL(strunk,'DEF');
    ->   /* SET strunk = 'test';   */
    ->
    -> END
    -> |
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> call RateGetTrunk(@a);
Query OK, 0 rows affected (0.00 sec)

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

mysql> select IFNULL('Local', 'DEF');
+------------------------+
| IFNULL('Local', 'DEF') |
+------------------------+
| Local                  |
+------------------------+
1 row in set (0.00 sec)

mysql> select IFNULL('', 'DEF');
+-------------------+
| IFNULL('', 'DEF') |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

So, looks like OUT parameter is treated as '' in this case.

I had not found the description of OUT parameter specifics in the manual, but in Oracle you can not use OUT parameters in expressions, only as an "lvalue" - to assign them values. So, I tried a modified version of your procedure:

mysql> delimiter //
mysql> CREATE PROCEDURE RateGetTrunk2 (OUT strunk varchar(128))
    ->   NOT DETERMINISTIC
    -> BEGIN
    ->    DECLARE mydialst    varchar(255);
    ->    set mydialst = 'Local';
    ->   SET strunk = IFNULL(mydialst,'DEF');
    -> END//
Query OK, 0 rows affected (0.01 sec)

mysql> call RateGetTrunk2(@c)//
Query OK, 0 rows affected (0.01 sec)

mysql> select @c//
+-------+
| @c    |
+-------+
| Local |
+-------+
1 row in set (0.00 sec)

mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.0.15-rc |
+-----------+
1 row in set (0.00 sec)

This is how it works, and it should work so. I am changing this report to documentation request, because it is really needed to describe the OUT parameters behaviour explicitely, even if it is intended.

I dont think this is an OUT parameter issue. I do agree about your documentation issue. But this looks like an IFNULL in stored procedure issue. Have a look at my modified procedure. Now I am not using the OUT procedure for processing and we have the same problem.

DROP PROCEDURE IF EXISTS RateGetTrunk;
delimiter |
CREATE PROCEDURE RateGetTrunk (OUT strunk varchar(128))
  NOT DETERMINISTIC
BEGIN
  DECLARE mydialst    varchar(255);  
  set mydialst = 'Local';
  SET mydialst = IFNULL(mydialst,'DEF'); 
  SET strunk = mydialst;
END
|
delimiter ;

CALL RateGetTrunk(@a);
select @a;

PS just notice you are using a higher version than me. The issue may be fixed. :-)
Just have a look again on my new example. It is not behaving on 5.0.13

Are

Sorry. Forgot to give you the output of my last procedure.

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

mysql> select version()
    -> ;
+------------------------+
| version()              |
+------------------------+
| 5.0.13-rc-standard-log |
+------------------------+
1 row in set (0.00 sec)

This is the same bug as BUG#13941