MySQL Bugs: #15058: CONTINUE HANDLER not working correctly

Bug #15058	CONTINUE HANDLER not working correctly
Submitted:	18 Nov 2005 22:06	Modified:	23 Nov 2005 22:19
Reporter:	Matthew Lord	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.0.15, 5.0.17-20051115snapshot	OS:	Linux (linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
We are declaring a continue handler for sqlstate '02000' or NOT FOUND and it is prematurely 
activating.

How to repeat:
I will attach the following files:

mysqladmin create chbug
mysql chbug < ttt.sql
mysql>use chbug;
mysql>load data infile "/tmp/24-7/RefUrl" into table RefUrl;
mysql>load data infile "/tmp/24-7/rSrcUrlCache" into table rSrcUrlCache;
mysql>call ttt (1, 12675, 12699, "D");

This procedure should loop through all 50 rows which are selected at the beginning
of the output.  If you remove the continue handler then it works correctly until it
gets to SQLSTATE 02000 at the end of the data to fetch.

It seems to be triggered by something because the continue handler is executing after the 6th row
every time for me.

I was able to repeat the described behaviour.

I was able to repeat the bug with this small test case:

 drop table if exists t1;
create table t1 (a int);
insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
drop table if exists t2;
create table t2 select * from t1;
drop procedure if exists bug15058;
delimiter |
create procedure bug15058() 
begin
  declare done int default 0;
  declare p_a int;
  declare p_b int;
  declare c cursor for select a from t1;
  declare continue handler for not found set done=1;
  open c;

  c_loop: loop

    fetch c into p_a;
    if (done = 1) then
      leave c_loop;
    end if;
    select p_a;
    select a into p_b from t2 where a=p_a;
    if (p_b is null) then
      set p_b=5;
    end if;
    select p_b;
  end loop;
end|
delimiter ;
call bug15058();
update t2 set a=0 where a=5;
call bug15058();
drop procedure bug15058;
drop table t1,t2;

Here is the output:

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> drop table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t2 select * from t1;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> drop procedure if exists bug15058;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter |
mysql> create procedure bug15058()
    -> begin
    ->   declare done int default 0;
    ->   declare p_a int;
    ->   declare p_b int;
    ->   declare c cursor for select a from t1;
    ->   declare continue handler for not found set done=1;
    ->   open c;
    ->
    ->   c_loop: loop
    ->
    ->     fetch c into p_a;
    ->     if (done = 1) then
    ->       leave c_loop;
    ->     end if;
    ->     select p_a;
    ->     select a into p_b from t2 where a=p_a;
    ->     if (p_b is null) then
    ->       set p_b=5;
    ->     end if;
    ->     select p_b;
    ->   end loop;
    -> end|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call bug15058();
+-----+
| p_a |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_b |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_a |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_b |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_a |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_b |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)
+-----+
| p_a |
+-----+
|   4 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_b |
+-----+
|   4 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_a |
+-----+
|   5 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_b |
+-----+
|   5 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_a |
+-----+
|   6 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_b |
+-----+
|   6 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_a |
+-----+
|   7 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_b |
+-----+
|   7 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_a |
+-----+
|   8 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_b |
+-----+
|   8 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_a |
+-----+
|   9 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_b |
+-----+
|   9 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_a |
+-----+
|  10 |
+-----+
1 row in set (0.01 sec)

+-----+
| p_b |
+-----+
|  10 |
+-----+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> update t2 set a=0 where a=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> call bug15058();
+-----+
| p_a |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_b |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_a |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_b |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_a |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_b |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)
+-----+
| p_a |
+-----+
|   4 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_b |
+-----+
|   4 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_a |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

+-----+
| p_b |
+-----+
|   4 |
+-----+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure bug15058;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

The difficulty is that the SELECT ... INTO statement
is activating the NOT FOUND handler unexpectedly.
This in fact is correct behaviour. I quote the
SQL:2003 standard, Part 2 SQL/Foundation, Section
14.5 <select statement: single row>, General Rules:
"
1) Let Q be the result of <query specification> S.
2) Case:
...
b) If Q is empty, then no SQL-data values are assigned
 to any targets identified by the <select target list>,
 and a completion condition is raised: no data.
"
Later we see that "no data" (SQLSTATE class '02') is
category 'N' (not found), and we're supposed to catch
not-found situations with NOT FOUND handlers.

Here is an example of one way to get around this:
create procedure p ()
 begin
 declare v1,v2 int;
 declare c cursor for select * from t1;
 declare continue handler for not found select 'a';
 begin
  declare continue handler for not found begin end;
  select s1 into v1 from t2;
  end;
 open c;
 fetch c into v2;
 end//
In this case, we put a BEGIN/END within a BEGIN/END.
If the SELECT returns "no data", the inner handler
catches it, and nothing stops.

I believe MySQL has done one thing wrong, though:
the "not found" message is "No data to FETCH".
I have submitted a separate bug report about that,
bug#15206 Misleading message "No data to FETCH"