MySQL Bugs: #68270: Continue/Exit Handler on NOT FOUND and multiple stored procedures/functions

Bug #68270	Continue/Exit Handler on NOT FOUND and multiple stored procedures/functions
Submitted:	5 Feb 2013 4:12	Modified:	8 Mar 2013 9:27
Reporter:	a m	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.28-0ubuntu0.12.04.2	OS:	Linux (ubuntu 12.04.2)
Assigned to:		CPU Architecture:	Any

Description:
A stored procedure used to work correctly in mysql server 5.1, but in mysql server 5.5, it's doing the wrong thing. Seeing this incorrect behavor on Ubuntu 12 and Windows 8.

What is happening is the following. In one stored procedure, say A, I'm calling a stored function B. 

In B, had the following at the top:

DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;

This way, if a select query returns empty, it was to be ignored. The function B had a single (small) IF statement after the select query.

In, A, had the following:

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN ... END;

Following this, B() is called, and then, there several other sql statements.

In version 5.5, if B hits the continue handler, then even in A, the statements following the call to B are not being executed. 

i.e., the EXIT HANDLER is being invoked.

Changing the EXIT HANDLER to a CONTINUE HANDLER in A causes the problem to go away. 

Turns out, for my logic, I can find a workaround knowing that, but what happened in B shouldn't be visible to A, right?

I just checked the code with the EXIT HANDLER on an older machine which still has mysql 5.1 -- there it works like a charm.

How to repeat:
Create a stored procedure A:

declare within it:

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN ... END;

select B() into someVariable;

do more stuff, execute a select query that returns a non empty resultset

--

Create a stored function B:

declare within it:

DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;

do a select query that returns an empty resultset.

--

A's exit handler will get executed, when only B's continue handler should.

Change A's exit handler to a continue handler, and it will work.

Looks like a duplicate of bug #61392. Please, check.

Can you please try out 5.6.10? We think the problem has been fixed there.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".