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

[27 Jun 2006 3:28] Morgan Tocker
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.
[27 Jun 2006 13:52] Valeriy Kravchuk
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)
[21 Aug 2006 23:36] Konstantin Osipov
Tim, I'm stealing this.
[24 Aug 2006 22:40] Marc Alff
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.
[7 Mar 2007 1:02] Marc Alff
This bug has been fixed with Bug#8407.
[7 Mar 2007 21:58] Konstantin Osipov
Pushed into 5.0.38 and 5.1.17
[15 Mar 2007 4:40] Paul Dubois
Noted in 5.0.38, 5.1.17 changelogs.