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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.059 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.