Bug #50202 MySQL Cursor Returns Incorrect number of rows

• Submitted: 9 Jan 2010 4:42
• Modified: 9 Jan 2010 5:35
• Reporter: Petrus Darmawan HS
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.1.30
• OS: Any
• CPU Architecture: Any
• Tags: compound statement, cursor

[9 Jan 2010 4:42] Petrus Darmawan HS

Description:
I have a simple MySQL Function specified below:
CREATE DEFINER=`root`@`localhost` FUNCTION `tryCursor`() RETURNS int(11)
BEGIN    
    DECLARE myvar INT;
    DECLARE c_done INT DEFAULT 0;
    DECLARE ctr INT;
    DECLARE mycursor CURSOR FOR
        SELECT myid FROM mytab;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = 1;
    OPEN mycursor;
    SET c_done = 0;
    SET ctr = 0;
    WHILE c_done = 0 DO
        FETCH mycursor INTO myvar;
		SET ctr = ctr + 1;
    END WHILE;
    CLOSE mycursor;
    RETURN ctr;
END

The function returns the number of Table "mytab".
I use MySQL's cursor to do that, but the function always returns (row number) +1.
So, it the table has 6 records, the function returns 7.

In other cases (with more complex cursor), the cursor fetched (row number) -1 rows.

How to repeat:
Used Table
CREATE TABLE mytab (
  myid int(11) NOT NULL AUTO_INCREMENT,
  myval int(11) NOT NULL,
  PRIMARY KEY (myid)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Used Function
as mentioned above

[9 Jan 2010 4:50] Todd Farmer

After the sixth row in your example, the following happens:

    WHILE c_done = 0 DO # True, haven't yet tried to get the (non-existant) 7th row
        FETCH mycursor INTO myvar;  # no 7th row, invoke HANDLER set c_done = 1, continue
		SET ctr = ctr + 1;  # increment ctr to 7
    END WHILE;  # loop back

    WHILE c_done = 0 DO # False, c_done is now 1, skip DO

    CLOSE mycursor;
    RETURN ctr;  # returns 7

I don't see a bug here.  If you have other examples, please let us know.

[9 Jan 2010 5:35] Petrus Darmawan HS

Thanks 4 ur explaination..

well, I modified the function
WHILE c_done = 0 DO
    FETCH mycursor INTO myvar;
    SET ctr = ctr + 1;
    SELECT COUNT(*) INTO myvar FROM mytab; #try to add query execution
END WHILE;
the function still works well.

But the function doesn't work like that on my other function. The cursor always stopped after it fetched 1st row. My other function is similar, but involve more tables and use WHERE conditions.