Stored procedures: cursor loop behavior is incorrect
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
The loop terminates early in all these repros, returning only the first of 3 expected result rows.
Repro:
```sql
DROP TABLE IF EXISTS PEOPLE;
CREATE TABLE PEOPLE (NAME VARCHAR(30));
INSERT INTO PEOPLE VALUES
('John'), ('Mary'), ('Tim');
DROP PROCEDURE IF EXISTS LoopThroughCursorWithContinueHandlerAndLoop;
DELIMITER //
CREATE PROCEDURE LoopThroughCursorWithContinueHandlerAndLoop()
BEGIN
DECLARE person_name VARCHAR(30) DEFAULT NULL;
DECLARE done INT DEFAULT 0;
DECLARE cur_people CURSOR FOR
SELECT NAME FROM PEOPLE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_people;
people_loop: LOOP
FETCH cur_people INTO person_name;
IF done THEN
LEAVE people_loop;
END IF;
SELECT person_name AS PEOPLE_NAME;
END LOOP;
CLOSE cur_people;
END //
DELIMITER ;
CALL LoopThroughCursorWithContinueHandlerAndLoop();
```
```sql
DROP TABLE IF EXISTS PEOPLE;
CREATE TABLE PEOPLE (NAME VARCHAR(30));
INSERT INTO PEOPLE VALUES
('John'), ('Mary'), ('Tim');
DROP PROCEDURE IF EXISTS LoopThroughCursorWithContinueHandlerAndRepeat;
DELIMITER //
CREATE PROCEDURE LoopThroughCursorWithContinueHandlerAndRepeat()
BEGIN
DECLARE person_name VARCHAR(30) DEFAULT NULL;
DECLARE done INT DEFAULT 0;
DECLARE cur_people CURSOR FOR
SELECT NAME FROM PEOPLE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_people;
REPEAT
FETCH cur_people INTO person_name;
IF NOT done THEN
SELECT person_name AS PEOPLE_NAME;
END IF;
UNTIL done END REPEAT;
CLOSE cur_people;
END //
DELIMITER ;
CALL LoopThroughCursorWithContinueHandlerAndRepeat();
```
```sql
DROP TABLE IF EXISTS PEOPLE;
CREATE TABLE PEOPLE (NAME VARCHAR(30));
INSERT INTO PEOPLE VALUES
('John'), ('Mary'), ('Tim');
DROP PROCEDURE IF EXISTS LoopThroughCursorWithRowCount;
DELIMITER //
CREATE PROCEDURE LoopThroughCursorWithRowCount()
BEGIN
DECLARE person_name VARCHAR(30) DEFAULT NULL;
DECLARE done INT DEFAULT 0;
DECLARE row_count INT DEFAULT 0;
DECLARE current_row INT DEFAULT 0;
DECLARE cur_people CURSOR FOR
SELECT NAME FROM PEOPLE;
SELECT COUNT(*) INTO row_count
FROM PEOPLE;
OPEN cur_people;
WHILE current_row < row_count DO
FETCH cur_people INTO person_name;
SELECT person_name AS PEOPLE_NAME;
SET current_row = current_row + 1;
END WHILE;
CLOSE cur_people;
END //
DELIMITER ;
CALL LoopThroughCursorWithRowCount();
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.