matrixorigin / matrixorigin/matrixone

[Compatibility]: ADDDATE and SUBDATE reject prepared value and interval parameters

Open
#28,135 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

## 现象

在 MatrixOne main `99ed717b769e261b842c2f17345bd65865fc1470`,`ADDDATE()` 和 `SUBDATE()` 的 prepared statement 参数无法执行:

```sql
PREPARE p FROM 'SELECT ADDDATE(?, INTERVAL 1 DAY)';
SET @d = '2024-02-29';
EXECUTE p USING @d;

PREPARE p FROM 'SELECT ADDDATE(?, INTERVAL ? DAY)';
SET @d = '2024-02-29'; SET @n = 1;
EXECUTE p USING @d, @n;

PREPARE p FROM 'SELECT SUBDATE(?, INTERVAL 1 DAY)';
SET @d = '2024-02-29';
EXECUTE p USING @d;
```

三种形式在 prepare/execute 阶段均返回 `invalid argument date_add/date_sub function need two args, bad value 3`。相同值改为字面量可执行;`WEEK(?, ?)`、`YEARWEEK(?, ?)`、`CONVERT_TZ(?, ?, ?)` 的 prepared 参数可执行。

## 期望

`ADDDATE()`/`SUBDATE()` 是日期加减的公开语法,prepared statement 中的值参数和 interval 参数应按字面量调用得到同样的日期结果。当前绑定阶段把内部三参数表达式重新按公开两参数校验,导致合法 SQL 无法执行。

## 影响

使用 MySQL 兼容 prepared API 的应用无法参数化 `ADDDATE`/`SUBDATE`,只能改写为字面量或 `TIMESTAMPADD`。

## 复现信息

- MatrixOne main: `99ed717b769e261b842c2f17345bd65865fc1470`
- 重复执行 3 次,结果一致
- 相关:#28119(`DATE_ADD`/`DATE_SUB` 的同类参数化问题)

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.