matrixorigin / matrixorigin/matrixone
[Bug]: CallableStatement OUT parameters fail because procedure metadata views are empty
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
MatrixOne supports stored procedures with `OUT` and `INOUT` parameters, but the corresponding rows are absent from `information_schema.ROUTINES` and `information_schema.PARAMETERS`. MySQL Connector/J therefore cannot discover parameter modes and rejects standard JDBC `CallableStatement.registerOutParameter(...)` calls under its default configuration.
## Environment
- Branch: `main`
- Commit: `7792fcaa09cfd24abd6d1070ec7213c229b6e02b`
- Deployment: local single-node launch (`etc/launch/launch.toml`)
- OS: macOS arm64
- Drivers: MySQL Connector/J 8.0.15, 8.4.0, and 9.7.0
## Steps to reproduce
Create and inspect a procedure:
```sql
create database if not exists jdbc_prepare_explore;
use jdbc_prepare_explore;
drop procedure if exists jdbc_out_value;
create procedure jdbc_out_value(in p int,out q int) 'begin set q=p+7; end';
show procedure status;
select * from information_schema.routines
where routine_schema='jdbc_prepare_explore';
select specific_name,ordinal_position,parameter_mode,parameter_name,data_type
from information_schema.parameters
where specific_schema='jdbc_prepare_explore';
```
`SHOW PROCEDURE STATUS` returns `jdbc_out_value`, while both information-schema queries return zero rows.
Execute the procedure through JDBC with default Connector/J settings:
```java
Properties p = new Properties();
p.setProperty("useServerPrepStmts", "true");
p.setProperty("useInformationSchema", "true");
Connection c = DriverManager.getConnection(url, p);
try (CallableStatement call = c.prepareCall("{call jdbc_out_value(?,?)}")) {
call.setInt(1, 35);
call.registerOutParameter(2, Types.INTEGER);
call.execute();
assert call.getInt(2) == 42;
}
```
## Actual behavior
All three tested Connector/J versions reject `registerOutParameter(2, ...)` before execution:
```text
java.sql.SQLException: Parameter number 2 is not an OUT parameter
at com.mysql.cj.jdbc.CallableStatement.checkIsOutputParam(...)
at com.mysql.cj.jdbc.CallableStatement.registerOutParameter(...)
```
The same metadata gap also prevents normal discovery of `INOUT` modes.
## Expected behavior
`information_schema.ROUTINES` should expose visible stored procedures and `information_schema.PARAMETERS` should expose their ordered `IN`/`OUT`/`INOUT` parameter metadata. Standard Connector/J `CallableStatement` calls should work without requiring a compatibility workaround.
## Stability and controls
- Reproducer: 3/3 with Connector/J 8.4.0; also reproduced with 8.0.15 and 9.7.0.
- Passing control: setting Connector/J `noAccessToProcedureBodies=true` makes it conservatively treat parameters as INOUT; the same test then passes 16 assertions on all three driver versions, including OUT, INOUT, NULL, and two procedure ResultSets.
- SQL control: direct `CALL` with user variables works, confirming the procedure execution path itself is functional.
- No data corruption, panic, hang, or CN restart was observed.
## Evidence
```text
SHOW PROCEDURE STATUS: jdbc_multi_result, jdbc_out_value, jdbc_inout_value
information_schema.parameters rows: 0
Connector/J default: Parameter number 2 is not an OUT parameter
Connector/J workaround: PASS JdbcCallablePrepareExplore assertions=16
```
## Code analysis
`pkg/util/sysview/predefined.go` currently defines `information_schema.ROUTINES` and `information_schema.PARAMETERS` as empty tables rather than views backed by `mo_catalog.mo_stored_procedure`. The stored procedure definitions and argument metadata are persisted in `mo_catalog.mo_stored_procedure`, so the JDBC-visible metadata has no populated source.
## Regression coverage
After the product fix, add a JDBC/motr regression covering `DatabaseMetaData.getProcedures()`, `getProcedureColumns()`, `CallableStatement` OUT/INOUT registration, NULL values, and multiple ResultSets across supported Connector/J versions. This does not require big-data coverage.
Contributor guide
Assessment
This issue has not been assessed yet.