Bug #20713 Functions will not not continue for SQLSTATE VALUE '42S02'
Submitted: 27 Jun 2006 5:28 Modified: 15 Mar 2007 5:40
Reporter: Morgan Tocker
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:5.0.23-BK, 5.0 OS:Any (ALL)
Assigned to: Marc Alff Target Version:

[27 Jun 2006 5: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 15: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)
[22 Aug 2006 1:36] Konstantin Osipov 
Tim, I'm stealing this.
[25 Aug 2006 0: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 2:02] Marc Alff 
This bug has been fixed with Bug#8407.
[7 Mar 2007 22:58] Konstantin Osipov 
Pushed into 5.0.38 and 5.1.17
[15 Mar 2007 5:40] Paul DuBois 
Noted in 5.0.38, 5.1.17 changelogs.