Bug #42904 If..then works badly in stored procedures
Submitted: 16 Feb 2009 20:49 Modified: 18 Feb 2009 23:27
Reporter: David Simon Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1.31 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: if..then, stored procedures

[16 Feb 2009 20:49] David Simon
Description:
During a cursos fetch in stored procedure the if..then behavies strange.
The last row is every time wrongly handled.

How to repeat:
create table `test`.`Test`( `id` int , `row` int , `col` int )  ;
insert into `test`(`id`,`row`,`col`) values ( '0','0',NULL);
insert into `test`(`id`,`row`,`col`) values ( '1','1',NULL);
insert into `test`(`id`,`row`,`col`) values ( '2','1',NULL);
insert into `test`(`id`,`row`,`col`) values ( '3','1',NULL);
insert into `test`(`id`,`row`,`col`) values ( '4','2',NULL);
insert into `test`(`id`,`row`,`col`) values ( '5','2',NULL);
insert into `test`(`id`,`row`,`col`) values ( '6','3',NULL);
insert into `test`(`id`,`row`,`col`) values ( '7','4',NULL);
insert into `test`(`id`,`row`,`col`) values ( '8','4',NULL);
insert into `test`(`id`,`row`,`col`) values ( '9','5',NULL);
insert into `test`(`id`,`row`,`col`) values ( '10','6',NULL);
insert into `test`(`id`,`row`,`col`) values ( '11','7',NULL);

DELIMITER $$

CREATE
   PROCEDURE `test`.`Test_proc`()
   BEGIN
   declare done, i, oldrow, id_, row_, col_ int;
   declare cur cursor for select id, row, col FROM Test order by row, id;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

     set done=0;
     open cur;
     set i=0;
     set oldrow=0;
     d: repeat
     if not done then
        fetch cur into id_, row_, col_;
        if (oldrow = row_) 
          then set i=i+1; 
          else set i=1; 
        end if;
	update Test set col=i where id=id_ and row=row_;
        set oldrow = row_;
     end if;
     until done  end repeat d;
     close cur;
    END$$

DELIMITER ;

Call Test_proc();

mysql> select * from test;
+------+------+------+
| id   | row  | col  |
+------+------+------+
|    0 |    0 |    1 |
|    1 |    1 |    1 |
|    2 |    1 |    2 |
|    3 |    1 |    3 |
|    4 |    2 |    1 |
|    5 |    2 |    2 |
|    6 |    3 |    1 |
|    7 |    4 |    1 |
|    8 |    4 |    2 |
|    9 |    5 |    1 |
|   10 |    6 |    1 |
|   11 |    7 |    2 | <--- The last row's last column has to be 1 not 2!
+------+------+------+
12 rows in set (0.00 sec)
[17 Feb 2009 7:33] Sveta Smirnova
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

If change stored procedure to:

create
   procedure `test_proc`()
   begin
   declare done, i, oldrow, id_, row_, col_ int;
   declare cur cursor for select id, row, col from test order by row, id;
   declare continue handler for not found set done = 1;

     set done=0;
     open cur;
     set i=0;
     set oldrow=0;
     d: repeat
     if not done then
        fetch cur into id_, row_, col_;
		select id_, oldrow, row_;
        if (oldrow = row_) 
          then set i=i+1; 
          else set i=1; 
        end if;
	update test set col=i where id=id_ and row=row_;
        set oldrow = row_;
     end if;
     until done  end repeat d;
     close cur;
    end$$

you will see it selects row with id=11 2 times, so behavior is expected.
[18 Feb 2009 23:27] David Simon
Well, yes. Thanks. It has been "solved". 

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`Test_proc`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`()
BEGIN
   declare done, i, oldrow, id_, row_, col_ int;
   declare cur cursor for select id, row, col FROM Test order by row, id;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
     set done=0;
     open cur;
     set i=0;
     set oldrow=0;
     fetch cur into id_, row_, col_;
     d: repeat
        if (oldrow = row_) 
          then set i=i+1; 
          else set i=1; 
        end if;
      	update Test set col=i where id=id_ and row=row_;
        set oldrow = row_;
      fetch cur into id_, row_, col_;
     until done end repeat d;
     close cur;
    END$$

DELIMITER ;

However it seems more complex than I expected. But working well.