matrixorigin / matrixorigin/matrixone

[Bug]: Prepared statements execute dropped SQL UDF bodies and keep stale return types after recreate

Open
#26,886 0 comments 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

SQL-level and binary prepared statements inline a SQL UDF body at PREPARE time but do not track the UDF as a catalog dependency. After the UDF is dropped, the old handles continue executing the deleted body. After the same signature is recreated with a different body and return type, the old handles continue returning the old value and old type metadata until explicitly re-prepared.

## Environment

- Branch: `main`
- Commit: `8be242b25bf9a44d73a3b1cc1db75fd0264a293c`
- Deployment: local `mo-service`, 1 Log / 1 TN / 2 CN / Proxy; tested both same-session DDL and DDL from another CN

## Steps to reproduce

Connection A:

```sql
CREATE DATABASE prepared_udf_stale;
USE prepared_udf_stale;

CREATE FUNCTION f(a INT)
RETURNS INT LANGUAGE SQL AS '$1 + 1';

PREPARE s FROM 'SELECT f(?)';
SET @p = 1;
EXECUTE s USING @p;
-- 2
```

The equivalent binary path is a persistent `COM_STMT_PREPARE` handle for `SELECT f(?)` with `interpolateParams=false`.

Connection B, or connection A itself:

```sql
DROP FUNCTION f(INT);
```

Connection A:

```sql
EXECUTE s USING @p;
-- actual: 2

SELECT f(1);
-- correct fresh control: function or operator 'f' is not supported
```

Recreate the same signature with a different body and return type:

```sql
CREATE FUNCTION f(a INT)
RETURNS VARCHAR LANGUAGE SQL AS 'concat("new-",$1)';
```

Connection A:

```sql
EXECUTE s USING @p;
-- actual: 2

SELECT f(1);
-- fresh control: new-1

DEALLOCATE PREPARE s;
PREPARE s FROM 'SELECT f(?)';
EXECUTE s USING @p;
-- new-1
```

## Actual behavior

- Both SQL-level `PREPARE/EXECUTE` and binary `COM_STMT_PREPARE/COM_STMT_EXECUTE` return `2` after the function has been dropped.
- Recreating the same `(INT)` signature as `RETURNS VARCHAR` with body `concat("new-",$1)` does not refresh either old handle: both still return integer `2`.
- A fresh text query and newly prepared handles return `new-1`.
- The failure also occurs when `DROP FUNCTION` and `CREATE FUNCTION` execute on the same physical connection that owns the prepared handles.

Representative latest-main output:

```text
round=1 drop sql="2"/ binary="2"/ freshErr=true recreate sql="2"/ binary="2"/ fresh="new-1"/ reprepared="new-1"/ newbinary="new-1"/
round=2 drop sql="2"/ binary="2"/ freshErr=true recreate sql="2"/ binary="2"/ fresh="new-1"/ reprepared="new-1"/ newbinary="new-1"/
round=3 drop sql="2"/ binary="2"/ freshErr=true recreate sql="2"/ binary="2"/ fresh="new-1"/ reprepared="new-1"/ newbinary="new-1"/
```

## Expected behavior

A prepared statement that calls a SQL UDF must validate the function identity/version before each execution. Dropping the UDF must make the old handle fail. Recreating the signature must rebuild the plan and result metadata so the old handle either transparently uses the new body/type or returns a deterministic reprepare-required error; it must never execute the deleted body.

## Stability and controls

- Cross-CN DDL: 3/3 for SQL-level and binary protocols.
- Same-session DDL: 3/3 for SQL-level and binary protocols.
- Fresh text query after DROP: 3/3 correctly rejected the missing UDF.
- Fresh text query after recreate: 3/3 returned `new-1`.
- Explicit SQL reprepare and a new binary handle: 3/3 returned `new-1` with the new result type.
- Ordinary non-prepared text queries do not retain the old body.
- No panic, hang, connection corruption, or partial catalog DDL was observed.

## Evidence

The standalone Go reproducer pins physical connections through `database/sql`, exercises real SQL-level and binary prepared protocols, changes both body and return type, and asserts missing-function and reprepare controls. The tested server reported `git_version() = 8be242b25`.

## Code analysis

`pkg/sql/plan/base_binder.go` resolves a SQL UDF and expands its body directly into a normal expression or subquery during binding. `TxnCompilerContext.ResolveUdf` reads `body`, `language`, `rettype`, database, `modified_time`, and SQL mode from `mo_catalog.mo_user_defined_function`, but no function identity/version is recorded in the resulting query plan.

Prepared execution in `pkg/frontend/computation_wrapper.go` rebuilds from table/index/View catalog dependencies in `PreparePlan.Schemas` plus selected session-state generations. The inlined UDF has no corresponding schema/catalog dependency, so DROP/recreate cannot set `change=true`; the old expression, return type, column definitions, and cached compile remain reusable. This is confirmed by same-session DDL reproducing identically, while explicit reprepare immediately sees the new definition.

## Regression coverage

- Add a focused planner/frontend UT proving SQL UDF identity/version is included in prepared dependencies and changing it rebuilds result metadata.
- Add a motr multi-connection scenario covering SQL-level and binary protocols, DROP, body-only replacement, incompatible return-type replacement, missing-function rejection, same-session/cross-session DDL, repeated execution, and recovery without connection replacement.

## Related

- #25979 covered prepared View dependencies; UDFs are expanded through a different binder/catalog path.
- #26882 covers ordinary COM_QUERY View plan-cache invalidation, not prepared SQL UDFs.

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.