matrixorigin / matrixorigin/matrixone
[Bug]: ALTER PROCEDURE COMMENT and SQL SECURITY are not supported for stored procedures
- 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 supports `ALTER PROCEDURE` for routine metadata such as `COMMENT`, and stored routines can include `SQL SECURITY DEFINER` / `INVOKER`:
- https://dev.mysql.com/doc/mysql/en/alter-procedure.html
- https://dev.mysql.com/doc/refman/8.4/en/stored-objects-security.html
### Reproduce
```sql
drop database if exists mysql_compat_model15_second;
create database mysql_compat_model15_second;
use mysql_compat_model15_second;
create procedure p_alter() 'begin select 1 as v; end';
alter procedure p_alter comment 'changed comment';
show procedure status where db = database() and name = 'p_alter';
create procedure p_sec_definer() sql security definer 'begin select 1 as v; end';
create procedure p_sec_invoker() sql security invoker 'begin select 1 as v; end';
show procedure status where db = database() and name in ('p_sec_definer','p_sec_invoker');
```
### MySQL result
Using equivalent MySQL standard routine bodies:
```text
-- ALTER PROCEDURE updates the routine comment
Name Security_type Comment
p_alter DEFINER changed comment
-- SQL SECURITY is reflected in metadata
Name Security_type
p_sec_definer DEFINER
p_sec_invoker INVOKER
```
### MO result
```text
ERROR 1064 (HY000): syntax error near " procedure p_alter comment 'changed comment'";
ERROR 1064 (HY000): syntax error near " sql security definer 'begin select 1 as v; end'";
ERROR 1064 (HY000): syntax error near " sql security invoker 'begin select 1 as v; end'";
```
`SHOW PROCEDURE STATUS` still reports `p_alter` with an empty comment because the `ALTER PROCEDURE` statement failed.
### Expected
MO should support MySQL-compatible routine metadata statements:
- `ALTER PROCEDURE ... COMMENT ...`
- `SQL SECURITY DEFINER`
- `SQL SECURITY INVOKER`
Contributor guide
Assessment
This issue has not been assessed yet.