MySQL Bugs: #38386: Cursor exits ahead of time if it uses assignment with INTO

Bug #38386	Cursor exits ahead of time if it uses assignment with INTO
Submitted:	25 Jul 2008 20:42	Modified:	28 Jul 2008 18:03
Reporter:	Snezhana Senchuk	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Windows (XP Pro)
Assigned to:		CPU Architecture:	Any
Tags:	cursor, INTO

Description:
Bug: 
Cursor does not loop through all the values if the select statement inside assigns the value to the variable using INTO.

Description: The stored procedure testdummy() has the cursor that compares the records of tables dummy and dummy2.
Important: The data in the table dummy2 must be not sequential.
						
Error: The output of the stored procedure should be 999., i.e. the number of records in table dummy. By reason that the cursor breaks the output is 501.	 	

How to repeat:
Stored Procedure 1:
--------------------------
	
DELIMITER $$

DROP PROCEDURE IF EXISTS testtables $$
CREATE PROCEDURE testtables()

BEGIN

   declare my_count int;

   drop table if exists dummy;
   drop table if exists dummy2;

   create table dummy
   (
      id INT
   );

   create table dummy2
   (
      id INT
   );

   set my_count = 1;

   while (my_count < 1000) do

      insert into dummy(id) values (my_count);
      insert into dummy2(id) values (my_count);
      set my_count = my_count +1;

   end while;
		
   delete from dummy2 where id> 500 and id < 550;
	
END $$

DELIMITER ;

Stored Procedure 2:
--------------------------
	
DELIMITER $$

DROP PROCEDURE IF EXISTS testdummy $$
CREATE PROCEDURE testdummy()

BEGIN

   DECLARE tmp_id INT DEFAULT 0;
   DECLARE csr_var INT DEFAULT 0;
   DECLARE counter INT;
   DECLARE no_more_data INT DEFAULT 0;

   DECLARE dummy_csr CURSOR FOR
   SELECT id FROM dummy;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data = 1;

   set counter = 0;

   OPEN dummy_csr;

   dummy_loop: LOOP

      FETCH dummy_csr INTO csr_var;

      IF no_more_data = 1 THEN
	LEAVE dummy_loop;
      END IF;

      select id into tmp_id FROM dummy2 WHERE id = csr_var;
	
      set counter = counter + 1;

   END LOOP;

   close dummy_csr;

   select counter;

END $$

DELIMITER ;

Call Stored Procedures
-------------------------------

call testtables();
call testdummy();

Suggested fix:
Assign the value to the variable using SET:
set tmp_id = (select id FROM dummy2 WHERE id = csr_var);

Another solution is to use a while loop instead of a cursor.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You removed records from dummy2 where id > 500 and id < 550, so since id = 501 select from dummy2 returns NULL which leads to the not found error when you try to select into a variable. But empty result of select statement is OK, so workaround provided works.

Should not 'not found' result for INTO statement return a different error code number? As it says on the site: 
'NOT FOUND is shorthand for all SQLSTATE codes that begin with 02. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set.'
I would expect 'not found' for INTO to return an error that starts with anything other than 02, since I expect the cursor to terminate only if there is no more data to fetch and not because of some event inside the cursor loop.