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
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