matrixorigin / matrixorigin/matrixone
[Bug]: Prepared CALL statements are rejected before stored procedure execution
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
MatrixOne accepts a stored procedure invoked through ordinary `CALL`, but rejects the same `CALL` when it is submitted through the MySQL binary prepared-statement protocol. The failure occurs during `COM_STMT_PREPARE`, before the procedure is invoked. SQL-text `PREPARE ... FROM 'CALL ...'` is rejected as well.
## Environment
- Branch: `main`
- Commit: `ab49099c0f487255df131f45c3aecf4a4650fed7`
- Deployment: isolated local one-LOG/one-TN/two-CN cluster; each CN was tested independently.
## Steps to reproduce
Create a procedure whose ordinary invocation has a visible result:
```sql
create database prepared_call_repro;
use prepared_call_repro;
create table t(id int primary key, v int);
insert into t values (1,10),(2,20),(3,30);
create procedure p_read(in delta int)
'begin select id, v + delta as shifted from t order by id; end';
call p_read(5);
-- expected and actual rows: (1,15), (2,25), (3,35)
set @arg = 5;
prepare text_call from 'call p_read(?)';
```
For the MySQL binary protocol, prepare the same SQL with any MySQL driver, for example Go `database/sql`:
```go
stmt, err := db.PrepareContext(ctx, "call p_read(?)")
```
The same error is also returned when preparing `call p_read(5)` (no parameter marker) and `call p_write(?,?)` for a procedure that performs an `INSERT`.
## Actual behavior
Ordinary `call p_read(5)` returns all three expected rows. However:
```text
COM_STMT_PREPARE call p_read(5)
ERROR 1064 (HY000): ... syntax error at line 1 column 34 near " call p_read(5)"
COM_STMT_PREPARE call p_read(?)
ERROR 1064 (HY000): ... syntax error at line 1 column 34 near " call p_read(?)"
PREPARE text_call FROM 'call p_read(?)'
ERROR 20101 (HY000): internal error: statement: 'call p_read(?)'
```
## Expected behavior
Prepared `CALL` should accept a supported stored procedure invocation. In particular, a prepared `CALL` with an `IN` parameter should be executable and return the procedure result set. MySQL documents prepared `CALL` support, including parameter markers for `IN` parameters: https://dev.mysql.com/doc/refman/8.4/en/sql-prepared-statements.html
## Stability and controls
- Reproducer: 3/3 on CN1 and 3/3 on CN2. All three binary forms (`CALL` with literal input, `CALL` with `?`, and a write procedure with `?`) failed on every run.
- Control: ordinary `CALL p_read(5)` returned `(1,15),(2,25),(3,35)` on all six runs.
- Data safety: the rejected write-procedure prepare failed before execution; the fixture was recreated for every run and no row was written by the rejected statement.
## Evidence
The test used a real `COM_STMT_PREPARE` through Go `database/sql` and the MySQL driver, rather than emulating parameter substitution in text SQL. Both CN frontends were SQL-health-checked before each group of runs.
## Code analysis
`pkg/frontend/mysql_cmd_executor.go` wraps a binary prepare payload as `prepare from `. In `pkg/sql/parsers/dialect/mysql/mysql_sql.y`, the `prepareable_stmt` grammar admits DDL/DML/SELECT and other statements but omits `call_stmt`, although `call_stmt` is parsed and handled by the normal frontend path. This explains why even `call p_read(5)` fails before parameter binding. This is a confirmed parser admission gap; result-set handling for a fixed prepared `CALL` still needs coverage.
## Regression coverage
After the product fix, add a motr binary-protocol scenario that prepares and executes an `IN`-parameter read procedure, asserts all result rows, verifies a prepared write procedure, and confirms rejected invalid arguments leave the table unchanged. Do not add a failing expectation to current motr.
Contributor guide
Assessment
This issue has not been assessed yet.