matrixorigin / matrixorigin/matrixone

[Bug]: ALTER PROCEDURE COMMENT and SQL SECURITY are not supported for stored procedures

Open
#25,412 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 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

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.