dolthub / dolthub/dolt

Stored procedures: cursor loop behavior is incorrect

Open
#8,764 1 comment 0 reactions 1 assignee Claimed by @zachmu View on GitHub
bug correctness customer issue
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.