MySQL Bugs: #13560: EXIT HANDLER exits declaring block, not the block raising the condition

Bug #13560	EXIT HANDLER exits declaring block, not the block raising the condition
Submitted:	28 Sep 2005 11:48	Modified:	21 Nov 2005 19:28
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.x	OS:
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The MySQL reference (http://dev.mysql.com/doc/mysql/en/declare-handlers.html) sais: 

"For an EXIT handler, execution of the current BEGIN...END compound statement is terminated".

But what is the current block? Is it the one declaring the handler ("goto" semantics"), or is it the one that raises the handled condition ("gosub" semantics)?

A quick test shows that MySQL uses the "goto" semantics. Is this intentional? I think this should be documented more clearly. 

Clear documentation on this is important, because the choice for the "goto" model makes it virtually impossible to write a generic NOT FOUND handler to deal with cursor loops. Especially when working with nested cursors, you really have to declare a distinct handler for each cursor loop, which can be quite a burden.

How to repeat:
delimiter /

drop procedure if exists p_test;
/
create procedure p_test()
begin
     declare
         csr_schemata cursor for
         select schema_name
         from   information_schema.schemata
         where 1 = 0;
     declare
         exit handler for
         not found
         begin end;
     select 'start';
     open csr_schemata;
     begin
         declare
             v_schema_name varchar(64);
         loop
             fetch csr_schemata
             into  v_schema_name;
             select v_schema_name;
         end loop;
     end;
     -- unreachable, 
     -- the handler exits the outer block, 
     -- not the inner one.
     close csr_schemata ; 
     select 'end';             
end;
/
Query OK, 0 rows affected (0.06 sec)

call p_test();
/
+-------+
| start |
+-------+
| start |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Suggested fix:
Document very clearly that the occurrence of a condition transfers control to the handler, and that in case of an exit handler, the execution is resumed right outside the block declaring the handler rather than the block raising the handled condition. 
Also, document that this precludes writing a generic NOT FOUND handler (something wich would be quite easy with the "gosub" semantics)

Handlers are declared in the block. So, for them "current BEGIN  END block" is the one in which they are declared.

In your block with cursor there is no handler, so exception was propogated to the outer block. You can declare handler here as EXIT, then this block will be exited, or as CONTINUE, then execution will be continued. In the CONTINUE handler you can set some variable and check it in each you block before proceeding...

So, current implementation is flexible enough. In other similar languages, like Oracle PL/SQL, "EXIT-behaviour" and exception propogation described above is also present and used by default. So, I do not think that it is a bug in server - it is the intended behaviour. But I agree, that documentation should be more detailed and include examples similar to yours.

Thanks for your quick reply Valeriy, we seem to agree on most of this.

I agree that the propagation model in MySQL matches the one used in Oracle PLSQL (which is fine). I also agree that by specifying an actual statement within the handler (instead of my void BEGIN..END), you could do useful work, and achieve whatever control you need. It's just that, to me, it seems the MySQL sp language wants a programmer to do quite a bit more work than necessary to write an ordinary loop to traverse a CURSOR. I stumbled upon the EXIT HANDLER thing in an attempt to write a generic handler that takes care of the, in my opinion, quite normal situation of an exhausted cursor. 
So, I gueas I'm saying I'd really like a 

FOR record IN cursor/subquery LOOP 

construct to take care of the opening, fetching and closing sequence you see when working with cursors.

Anyway, thanks!

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The handler exits the block in which the handler is declared,
even if the condition occurs in an inner block.