matrixorigin / matrixorigin/matrixone

[Bug]: prepared UPDATE cannot recompute DATE_ADD or DATE_SUB generated columns

Open
#28,240 0 comments 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

### 问题描述

当生成列使用 `DATE_ADD` 或 `DATE_SUB`,prepared `UPDATE` 修改其依赖列时,MatrixOne 会把已经规范化为三个参数的内部表达式重新送入只接受两个 SQL 参数的绑定逻辑,执行阶段因此返回 `date_add/date_sub function need two args, bad value 3`。相同 UPDATE 直接执行可以正确重算生成列,MySQL prepared UPDATE 也可以执行。

该问题同时影响 STORED 和 VIRTUAL 生成列。它不影响 prepared INSERT、ODKU、REPLACE、只修改无关列的 prepared UPDATE;使用 `TIMESTAMPADD`、`ADDTIME` 或普通算术表达式的生成列也不受影响。

### 环境

- MatrixOne: latest `main`, commit `e7bb0572235ec4bac81eb098eb0ad8900f0065ab`
- MySQL: 8.3.0(对照)

### 最小复现

```sql
create database gc_ps_repro;
use gc_ps_repro;

create table t (
id int primary key,
dt datetime(6),
g datetime(6) generated always as
(date_add(dt, interval 1 microsecond)) stored
);

insert into t(id, dt) values (1, '2024-01-01 01:02:03.123456');

-- 直接 UPDATE 能正确重算 g
update t set dt='2025-02-03 04:05:06.222222' where id=1;
select id, dt, g, microsecond(g) from t;

prepare s from 'update t set dt=? where id=?';
set @dt='2026-03-04 05:06:07.333333', @id=1;
execute s using @dt, @id;
```

### MatrixOne 行为

```text
ERROR 20203: invalid argument date_add/date_sub function need two args, bad value 3
```

prepared UPDATE 没有生效,行仍保留直接 UPDATE 后的值。

### 期望行为

prepared UPDATE 应与直接 UPDATE 一致:更新 `dt`,并把 `g` 重算为 `2026-03-04 05:06:07.333334`。MySQL 8.3.0 可得到该结果。

### 补充边界

以下组合得到相同问题:

```sql
g datetime(6) generated always as
(date_sub(dt, interval 1 microsecond)) stored

g datetime(6) generated always as
(date_add(dt, interval 1 microsecond)) virtual
```

以下相邻路径可正常执行,有助于定位为 `DATE_ADD/DATE_SUB` 生成表达式在普通 prepared UPDATE 重绑定时的问题:

```sql
-- prepared INSERT
prepare i from 'insert into t(id,dt) values(?,?)';

-- generated expression controls
timestampadd(microsecond, 1, dt)
addtime(dt, '00:00:00.000001')
```

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.