Bug #12383 SELECT of nonexisting function in proc called from other proc returns NULL
Submitted: 4 Aug 2005 13:45 Modified: 5 May 2006 19:16
Reporter: Anders Karlsson Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.10 OS:Linux (Linux)
Assigned to: Antony Curtis CPU Architecture:Any

[4 Aug 2005 13:45] Anders Karlsson
Description:
If a procedure contaings a SELECT of a non-existing function, and that procedure in turn is called from another procedure that in turn has a HANDLER defined for this event, then the called procedure will return NULL to the caller, without any error. This is similar to, but different enough, from 7049. The difference is that in 7049 the error is ignored, whereas here a reult is returned (although it is NULL) returned.

This could cause a lot of confusion cause by a simple typy in a stored procedure. And note that a HANDLER for the failing condition needs to be defined in the calling procedure for this to happen.

How to repeat:
Create the objects like this:
DROP FUNCTION IF EXISTS foobar;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 CHAR(1));
INSERT INTO t1 VALUES('A');
INSERT INTO t1 VALUES('B');
INSERT INTO t1 VALUES('C');

DROP PROCEDURE IF EXISTS myproc1;
delimiter //
CREATE PROCEDURE myproc1()
BEGIN
   SELECT c1, foobar() FROM t1;
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc2a;
delimiter //
CREATE PROCEDURE myproc2a()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @err = 1;

   CALL myproc1();
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc2b;
delimiter //
CREATE PROCEDURE myproc2b()
BEGIN
   CALL myproc1();
END;
//
delimiter ;

And reproduce the problem like this:
mysql> call myproc2a();
+------+----------+
| c1   | foobar() |
+------+----------+
| A    | NULL     |
| B    | NULL     |
| C    | NULL     |
+------+----------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call myproc2b();
ERROR 1305 (42000): FUNCTION test.foobar does not exist
[5 May 2006 19:16] Antony Curtis
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Cannot reproduce. Maybe already fixed by Bug#7049