MySQL Bugs: #12384: Stored procedure HANDLER can't handle "table not found" errors

Bug #12384	Stored procedure HANDLER can't handle "table not found" errors
Submitted:	4 Aug 2005 14:26	Modified:	28 Aug 2005 16:55
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.10	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Errors caused by SQL statements in a procedure accessing non-existing tables aren't caught by procedure HANDLERs. Non-existing column conditions are handled appropriately, as are other errors. This is very inconsistent behaviour.

How to repeat:
Create objects like this:

DROP PROCEDURE IF EXISTS myproc3a;
delimiter //
CREATE PROCEDURE myproc3a()
BEGIN
   SELECT nosuchcol FROM t1;
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc3b;
delimiter //
CREATE PROCEDURE myproc3b()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 1;

   SELECT nosuchcol FROM t1;
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc4a;
delimiter //
CREATE PROCEDURE myproc4a()
BEGIN
   SELECT nosuchcol FROM nosuchtab;
END;
//
delimiter ;

DROP PROCEDURE IF EXISTS myproc4b;
delimiter //
CREATE PROCEDURE myproc4b()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 1;

   SELECT nosuchcol FROM nosuchtab;
END;
//
delimiter ;

The reproduce like this:
mysql> call myproc3a();
ERROR 1054 (42S22): Unknown column 'nosuchcol' in 'field list'
mysql> call myproc3b();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call myproc4a();
ERROR 1146 (42S02): Table 'test.nosuchtab' doesn't exist
mysql> call myproc4b();
ERROR 1146 (42S02): Table 'test.nosuchtab' doesn't exist
mysql>

As can be seen, SELECT from non-existing columns is handled nicely, but non-existing tables are not.

Sorry, I forgot that table t1 needs to be created first. But really, you are seeing the problem itself here. The SQLEXCEPTION for a non-existing table isn't caught.

Add the following before creating the procedures and running the reproduction statements:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 CHAR(1));

And you will see that myproc3a() and myproc3b() behave differently, and that the fact that column "nosuchcol" doesn't exist in table t1 is handled by the handler. But the fact that table "nosuchtab" is not handled by the defined handler.

I tried it on 5.0.10. Please check on that. If it reproduces there and not in 5.0.12, then we can close this one. If not, I need to find more reproduction data.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

verified with 5.0.10, ok in 5.0.11