matrixorigin / matrixorigin/matrixone
[Bug]: Stored procedure DECLARE HANDLER and CURSOR constructs are not supported like MySQL
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### Environment
- Branch: latest `main`
- MO git version: `51656deb6`
- MySQL oracle: `8.0.45`
### Reference behavior
MySQL stored procedures support `DECLARE ... HANDLER` and cursors inside routine bodies:
https://dev.mysql.com/doc/en/cursors.html
https://dev.mysql.com/doc/refman/8.3/en/declare-handler.html
### Reproduce on MO syntax
MO currently accepts procedure bodies as string literals, so this repro uses the syntax already present in `test/distributed/cases/procedure/procedure.sql` to isolate the procedural construct gap from the broader MySQL `BEGIN ... END` syntax gap.
```sql
drop database if exists mysql_compat_model15_mo_routine;
create database mysql_compat_model15_mo_routine;
use mysql_compat_model15_mo_routine;
create table t_routine (
id int primary key,
grp int,
v varchar(20)
);
insert into t_routine values (1,10,'a'),(2,10,'b'),(3,20,'c');
create procedure p_handler()
'begin
declare duplicate_seen int default 0;
declare continue handler for 1062 set duplicate_seen = 1;
insert into t_routine values (1,99,''dup'');
select duplicate_seen as duplicate_seen,
(select count(*) from t_routine) as row_count_after;
end';
call p_handler();
create procedure p_cursor()
'begin
declare done int default 0;
declare x int;
declare s int default 0;
declare cur cursor for select id from t_routine where grp = 10 order by id;
declare continue handler for not found set done = 1;
open cur;
read_loop: loop
fetch cur into x;
if done then
leave read_loop;
end if;
set s = s + x;
end loop;
close cur;
select s as cursor_sum;
end';
call p_cursor();
```
### MySQL behavior for equivalent standard routines
The equivalent MySQL procedures return:
```text
duplicate_seen row_count_after
1 3
cursor_sum
3
```
### MO result
```text
ERROR 1105 (HY000): syntax error ... near " handler for 1062 set duplicate_seen = 1; ..."
ERROR 1105 (HY000): syntax error ... near " cursor for select id from t_routine where grp = 10 order by id; ..."
```
### Expected
Stored procedures should support MySQL-compatible `DECLARE ... HANDLER` and cursor constructs, or at minimum reject unsupported constructs at `CREATE PROCEDURE` time rather than storing a routine that fails only at `CALL`.
Contributor guide
Assessment
This issue has not been assessed yet.