MySQL Bugs: #20713: Functions will not not continue for SQLSTATE VALUE '42S02'

Bug #20713	Functions will not not continue for SQLSTATE VALUE '42S02'
Submitted:	27 Jun 2006 3:28	Modified:	15 Mar 2007 4:40
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.23-BK, 5.0	OS:	Any (ALL)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
Functions will not continue when a table does not exist, despite a handler for that SQLSTATE.  This doesn't seem to be described as a limitation.

Verified in 5.0.22-standard-log

How to repeat:
DROP TABLE IF EXISTS bogus_table;
CREATE TABLE bogus_table (id INT(10) NOT NULL PRIMARY KEY);
INSERT INTO bogus_table VALUES (1), (2), (3);

DROP FUNCTION IF EXISTS a;
delimiter //
CREATE FUNCTION a ()
RETURNS int(11)
BEGIN
DECLARE id INT;
DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '42S02' SET id=NULL;
SET id = (SELECT id FROM bogus_table WHERE id = 3);
RETURN id;
END//
delimiter ;

mysql> select a(); -- works
mysql> DROP TABLE bogus_table;
mysql> select a(); -- fails, should work.

Verified just as described with 5.0.23-BK on Linux:

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

mysql> DROP TABLE bogus_table;
Query OK, 0 rows affected (0.00 sec)

mysql> select a();
ERROR 1146 (42S02): Table 'test.bogus_table' doesn't exist
mysql> show variables like 'sql%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
| sql_mode        |       |
| sql_notes       | ON    |
| sql_warnings    | OFF   |
+-----------------+-------+
4 rows in set (0.01 sec)

Tim, I'm stealing this.

This is a duplicate of Bug#8407
42S02 correspond to "Table not found", which is the main issue with Bug#8407,
as the pre-locking code is reporting the error before any handler has been installed at runtime.

This bug has been fixed with Bug#8407.

Pushed into 5.0.38 and 5.1.17

Noted in 5.0.38, 5.1.17 changelogs.