Bug #15809 DECLARING cursors inside IF statement doesn't work without BEGIN...END

• Submitted: 16 Dec 2005 9:59
• Modified: 16 Dec 2005 11:56
• Reporter: Gleb Paharenko
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.16
• OS: Linux (Linux)
• CPU Architecture: Any

[16 Dec 2005 9:59] Gleb Paharenko

Description:
According to:
  http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

"statement_list  can consist of one or more statements."

But if I declare cursors inside the IF statement, I shoud surround them with BEGIN...END

How to repeat:
This doesn't work:

drop procedure if exists testcursor;
DELIMITER //
CREATE PROCEDURE testcursor(IN city_name varchar(255))
DETERMINISTIC
BEGIN
declare tval int;
IF (INSTR(city_name, ',') > 0) THEN
--begin
DECLARE cur_neighborhood CURSOR FOR
  SELECT DISTINCT neighborhood
  FROM listing
  WHERE FIND_IN_SET(city, city_name);
open cur_neighborhood;
  FETCH cur_neighborhood into tval;
close cur_neighborhood;
select tval;
--end;
ELSE
begin
DECLARE cur_neighborhood CURSOR FOR
  SELECT DISTINCT neighborhood
  FROM listing WHERE city =city_name;
end;
select "goodby";
END IF;
END;
//
delimiter ;
call testcursor("test1,ts");

CREATE TABLE `listing` (
  `neighborhood` varchar(255) default NULL,
  `city` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin2

mysql> select * from listing;
+--------------+------+
| neighborhood | city |
+--------------+------+
| 1            | ts   |
+--------------+------+

+------------------+
| version()        |
+------------------+
| 5.0.16-debug-log |
+------------------+

:!./bin/mysql --defaults-file=my.cnf -uroot test <p.sql
ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur_neighborhood CURSOR FOR
        SELECT DISTINCT neighborhood
        FROM listin' at line 6

However, if I remove the comments, everything is Ok:

drop procedure if exists testcursor;
DELIMITER //
CREATE PROCEDURE testcursor(IN city_name varchar(255))
DETERMINISTIC
BEGIN
declare tval int;
IF (INSTR(city_name, ',') > 0) THEN
begin
DECLARE cur_neighborhood CURSOR FOR
  SELECT DISTINCT neighborhood
  FROM listing
  WHERE FIND_IN_SET(city, city_name);
open cur_neighborhood;
  FETCH cur_neighborhood into tval;
close cur_neighborhood;
select tval;
end;
ELSE
begin
DECLARE cur_neighborhood CURSOR FOR
  SELECT DISTINCT neighborhood
  FROM listing WHERE city =city_name;
end;
select "goodby";
END IF;
END;
//
delimiter ;
call testcursor("test1,ts");

:!./bin/mysql --defaults-file=my.cnf -uroot test <p.sql
tval
1

Suggested fix:
Please, fix this or make documentation a bit clearer

[16 Dec 2005 11:56] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I do not think it is a bug. The problem is that cursor should be declared, and all declarations are at the beggining of SP or a block.

Please, read (http://dev.mysql.com/doc/refman/5.0/en/cursors.html):

"Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers."

And, even more clearly (http://dev.mysql.com/doc/refman/5.0/en/declare.html):

"The DECLARE statement is used to define various items local to a routine: local variables (see Section 17.2.9, “Variables in Stored Procedures”), conditions and handlers (see Section 17.2.10, “Conditions and Handlers”) and cursors (see Section 17.2.11, “Cursors”). SIGNAL and RESIGNAL  statements are not currently supported.

DECLARE may be used only inside a BEGIN ... END compound statement and must be at its start, before any other statements."

So, it is not a bug, but a documented behaviour.

[14 May 2010 16:34] L R

How do you solve the problem that the cursor is declared inside an if statement?
If you declare the cursor at the begining of SP you can´t set different things into it...
I have the same problem migrating from SQL Server to MySQL and I don't know how to solve this..
Can you help me?

Sorry for my english :(