Bug #3485 DECLARE Handler for NOT FOUND fails

• Submitted: 16 Apr 2004 16:36
• Modified: 29 Apr 2004 2:19
• Reporter: Karl Niemeier
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.0-alpha-nt
• OS: Windows (XP Pro 2002)
• CPU Architecture: Any

[16 Apr 2004 16:36] Karl Niemeier

Description:
A stored procedure handler for a NOT FOUND condition doesn't appear to execute.

How to repeat:
delimiter //
drop table if exists t1//
create table t1 (id int)//
insert into t1 values(1)//
insert into t1 values(2)//

drop procedure if exists p2//
create procedure p2(id_parm int, out pout int)
begin
 -- declare continue handler for sqlstate '42000' set pout=42;
 declare continue handler for not found set pout=42;
  select id into pout from t1 where id=id_parm; 
end
//
call p2(1, @x)// -- this call should find a row
select @x//

call p2(88, @y)// -- this call should trigger the handler and y should be 42
select @y//  -- Observed: y is NULL

[23 Apr 2004 16:43] Karl Niemeier

I found that using SQLSTATE '02000' (rather than NOT FOUND) works. 

Is the SQLSTATE code given in DECLARE HANDLER used as a bit mask? Maybe it is clear to someone who has used the C API but I had not. Although there is the statement in the manual that NOT FOUND matches all codes that start with 02, when I looked at the table of SQLSTATE codes in the Error Handling chapter, I didn't see any that started with 02 and didn't know to try 02000 until I read the Cursors section.

[29 Apr 2004 2:19] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

C:\servers\5.0.1\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.1-alpha-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> drop table if exists t1//
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (id int)//
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values(1)//
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(2)//
Query OK, 1 row affected (0.00 sec)

mysql> drop procedure if exists p2//
Query OK, 0 rows affected (0.02 sec)

mysql> create procedure p2(id_parm int, out pout int)
    -> begin
    ->  declare continue handler for not found set pout=42;
    ->   select id into pout from t1 where id=id_parm;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call p2(1, @x)//
Query OK, 0 rows affected (0.01 sec)

mysql> select @x//
+------+
| @x   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> call p2(88, @y)//
Query OK, 0 rows affected (0.01 sec)

mysql> select @y//
+------+
| @y   |
+------+
| 42   |
+------+
1 row in set (0.00 sec)