MariaDB and stored procedure: Why does the cursor iterate the last row twice using?

I have a table tab which for simplicity contains only one column id with numbers from 1 to 3 (see part 1 of the code below). I created a cursor (3) to retrieve each number from tab and entered the number as IN parameter into a short procedure (sp, part 3). The sp inserts the parameter into the table tab_ctrl (ctrl for controlling).

After runnig the cursor (4) and selecting the content of tab_ctrl I got this result:

 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  3 |
|  3 |  <- why?
 ---- 

The last row of tab has been iterated twice. Why the cursor doesn't stop after the third row?

-- **************************************
-- (1) Prepare tables
-- **************************************

use test0;

-- Table to read each entry
DROP TABLE IF EXISTS tab;
CREATE TABLE tab  (
   id      INTEGER UNSIGNED DEFAULT NULL
 , PRIMARY KEY (id)
 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 ;
 
INSERT INTO tab VALUES
 (1)
,(2)
,(3)
;

-- Table for controlling
DROP TABLE IF EXISTS tab_ctrl;
CREATE TABLE tab_ctrl (
   id      INTEGER UNSIGNED DEFAULT NULL
 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 ;


-- **************************************
-- (4) Run sp
-- **************************************

CALL sp_cursor_add_records();
SELECT * FROM tab_ctrl;

-- **************************************
-- (2) Stored procedure
-- **************************************

DELIMITER //
DROP PROCEDURE IF EXISTS sp_add_records //
CREATE PROCEDURE sp_add_records(IN p_id INTEGER UNSIGNED)
                        
BEGIN
  INSERT INTO tab_ctrl
  SELECT p_id
  ;    
END//

DELIMITER ;


-- **************************************
-- (3) Cursor
-- **************************************

DELIMITER //
DROP PROCEDURE IF EXISTS sp_cursor_add_records //
CREATE PROCEDURE sp_cursor_add_records()
BEGIN 
  -- Local variables
  DECLARE done            BOOLEAN DEFAULT 0;
  DECLARE p_id            INTEGER UNSIGNED;  
  
  -- Cursor
  DECLARE c CURSOR 
  FOR
  SELECT id
  FROM tab
  ORDER BY id
  ;

  -- Declare continue handler
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

  -- Open cursor
  OPEN c;

  -- Loop through all rows
  REPEAT
    -- Get record
    FETCH c INTO p_id;

    -- Call add record procedure
    CALL sp_add_records(p_id);

  -- End of loop
  UNTIL done END REPEAT;

  -- Close cursor
  CLOSE c;

END//

DELIMITER ;
    

CodePudding user response：

If you restructure your loop to only CALL sp_ad_records(p_id) if its not done:

  -- Declare continue handler
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

  -- Open cursor
  OPEN c;

  -- Get First record
  FETCH c INTO p_id;

  -- Loop through all rows
  WHILE NOT done DO

    -- Call add record procedure
    CALL sp_add_records(p_id);

    -- Get next record
    FETCH c INTO p_id;

  -- End of loop
  END WHILE;

  -- Close cursor
  CLOSE c;