dolthub / dolthub/dolt

DECLARE CONTINUE HANDLER failure in dolt

Closed
#6,742 1 comment 0 reactions 1 assignee Claimed by @zachmu View on GitHub
bug correctness sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

I have a stored procedure which works fine in MySQL, but results in an infinite loop in dolt:

Setup:
```
mysql> create table tbl (id int auto_increment primary key, checksum varchar(40));
mysql> insert into tbl (1, sha("macneale"));
```

Add a stored procedure. A little convoluted, but the idea is that is calculates a new checksum from all existing values and inserts a new value

```sql
DELIMITER //

CREATE PROCEDURE calculate_and_insert_checksum()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE current_checksum VARCHAR(40);
DECLARE concat_string VARCHAR(10000);
DECLARE new_checksum VARCHAR(40);

DECLARE cur CURSOR FOR SELECT checksum FROM tbl;

-- Stop loop at end of set.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET concat_string = '';
OPEN cur;
-- Loop through the rows and concatenate checksum values
read_loop: LOOP
FETCH cur INTO current_checksum;

IF done THEN
LEAVE read_loop;
END IF;

SET concat_string = CONCAT(concat_string, current_checksum);
END LOOP;

CLOSE cur;

SET new_checksum = SHA1(concat_string);

INSERT INTO tbl (checksum) VALUES (new_checksum);

SELECT new_checksum as calculated_checksum;
END //

DELIMITER ;
```

This works fine in mysql:
```
mysql> call calculate_and_insert_checksum();
+------------------------------------------+
| calculated_checksum |
+------------------------------------------+
| 89fa71febbc9effd2fa58c7441ad2ed899fcdcf1 |
+------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tbl;
+----+------------------------------------------+
| id | checksum |
+----+------------------------------------------+
| 1 | ca530ba53d2e3b54206e62c7ab257657b7367cc7 |
| 2 | 89fa71febbc9effd2fa58c7441ad2ed899fcdcf1 |
+----+------------------------------------------+
2 rows in set (0.00 sec)
```

But in Dolt, it loops for ever. `DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;` is not actually having an effect, and done always equals 0 as a result. Infinite loop!

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.