matrixorigin / matrixorigin/matrixone

[Bug]: CallableStatement OUT parameters fail because procedure metadata views are empty

Open
#27,641 1 comment 0 reactions 1 assignee Claimed by @iamlinjunhong View on GitHub
deferred kind/bug needs-triage
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

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.