`PREPARE` / `EXECUTE` in stored procedures is buggy

[coffeegoddd]

Dolt version 0.53.0

The following stored procedure definition does not parse in Dolt, but parses and runs in MySQL:

DELIMITER //
CREATE PROCEDURE resolve_constraints()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tn varchar(255);
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.columns where table_schema = 'dolthubapi' and column_name = 'id';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

read_loop: LOOP
FETCH cur1 INTO tn;
IF done THEN
LEAVE read_loop;
END IF;
PREPARE stmt FROM concat('DELETE FROM ',tn,' WHERE id != id;');
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;

CLOSE cur1;
END //
DELIMITER ;

[timsehn]

@Hydrocharged can you take a look?

[Hydrocharged]

The stored procedure, as posted, does not parse for me in MySQL either. Perhaps I'm missing a configuration option. According to a post from StackOverflow, we should replace:

PREPARE stmt FROM concat('DELETE FROM ',tn,' WHERE id != id;');

With something like:

SET @stmtsql = CONCAT('DELETE FROM ',tn,' WHERE id != id;');
PREPARE stmt FROM @stmtsql;

This also does not parse in Dolt, but it does parse in MySQL. This isn't specific to stored procedures, as using a user variable in a PREPARE statement fails outside of procedures as well. If I substitute the current prepared statement for any one that parses normally, then the stored procedure also parses normally, so procedures handle PREPARE just fine AFAICT. I'll pass this to @max-hoffman as he's more familiar with prepared statements. Once that's fixed, it should "just work" in the stored procedure.

[Hydrocharged]

Correction, @JCOR11599 is looking into it instead

[fulghum]

Looks like we can now correctly PREPARE statements from SQL vars:

> SET @stmtsql = CONCAT('DELETE FROM ', 't' ,' WHERE id != id;');
> PREPARE stmt1 FROM @stmtsql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
> EXECUTE stmt1;
Query OK, 0 rows affected (0.00 sec)

But after that fix, Dolt still has problems dealing with the PREPARE call inside the stored procedure definition above.

> DELIMITER //
> CREATE PROCEDURE resolve_constraints()
   -> BEGIN
   -> DECLARE done INT DEFAULT FALSE;
   -> DECLARE tn varchar(255);
   -> DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.columns where table_schema = 'dolthubapi' and column_name = 'id';
   -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   -> 
   -> OPEN cur1;
   -> 
   -> read_loop: LOOP
   -> FETCH cur1 INTO tn;
   -> IF done THEN
   -> LEAVE read_loop;
   -> END IF;
   -> SET @stmtsql = CONCAT('DELETE FROM ',tn,' WHERE id != id;');
   -> PREPARE stmt FROM @stmtsql;
   -> EXECUTE stmt;
   -> DEALLOCATE PREPARE stmt;
   -> END LOOP;
   -> 
   -> CLOSE cur1;
   -> END //
panic: ExecuteQuery methods shouldn't be used

goroutine 1 [running]:
github.com/dolthub/go-mysql-server/sql/plan.(*ExecuteQuery).Children(0x10494f948?)
	github.com/dolthub/[email protected]/sql/plan/prepare.go:118 +0x2c
github.com/dolthub/go-mysql-server/sql/transform.Node({0x10494ef70, 0x14000113620}, 0x14000fc25b8

It looks like the GMS analyzer does not currently support EXECUTE statements, so when it tries to call methods on the ExecuteNode type, it just blows up and panics.

[Hydrocharged]

I worked on a fix for this, and got it "working" to an extent. https://github.com/dolthub/go-mysql-server/pull/1658 There were several things that it did not consider though, and I felt that the best way to address those items were to completely rewrite the PR. @fulghum is a customer currently running into this limitation? This was initially for an internal use case that we've sinced worked around.

[fulghum]

Ah, thanks for linking that PR – that's good context. Nope, this isn't impacting any customers currently; I was just poking around the open issues tagged for prepared statements while I was in that code for another fix.