matrixorigin / matrixorigin/matrixone

[Bug]: Stored procedure DECLARE HANDLER and CURSOR constructs are not supported like MySQL

Open
#25,410 0 comments 0 reactions 1 assignee Claimed by @daviszhen View on GitHub
deferred kind/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.