matrixorigin / matrixorigin/matrixone

[Compatibility]: temporal formatting functions reject row-dependent format expressions

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

Description

## 现象

MatrixOne `main`(`ac660ae56ecd37bab3dd8c61193617a323377029`)要求 `DATE_FORMAT()`、`TIME_FORMAT()` 和双参数 `FROM_UNIXTIME()` 的格式参数必须是常量,因此不能按行使用格式列或依赖当前行的表达式。MySQL 8.0.46 支持这些用法。

```sql
CREATE TABLE src(
id INT PRIMARY KEY,
dt DATETIME(6),
tm TIME(6),
epoch DECIMAL(20,6),
fmt VARCHAR(64)
);

INSERT INTO src VALUES
(1,'2024-01-02 03:04:05.123456','12:34:56.123456',
1704164645.123456,'%Y-%m-%d %H:%i:%s.%f'),
(2,'2025-06-07 08:09:10.654321','23:45:01.654321',
1749283750.654321,'%H:%i:%s.%f');

SELECT id, DATE_FORMAT(dt, fmt) FROM src ORDER BY id;
SELECT id, TIME_FORMAT(tm, fmt) FROM src ORDER BY id;
SELECT id, FROM_UNIXTIME(epoch, fmt) FROM src ORDER BY id;
SELECT id, DATE_FORMAT(dt, CASE WHEN id=1 THEN '%Y' ELSE '%m' END)
FROM src ORDER BY id;
```

MatrixOne 对以上语句分别返回同类错误:

```text
invalid argument date format format, bad value not constant
invalid argument time format format, bad value not constant
invalid argument from_unixtime format, bad value not constant
```

MySQL 根据每行的 `fmt` 返回对应字符串;例如第一条返回:

```text
1 2024-01-02 03:04:05.123456
2 08:09:10.654321
```

常量格式和 PreparedStatement 中单个格式参数在两端均可执行,问题限定在格式值随行变化的场景。

## 期望

上述三个函数应逐行读取格式参数,使格式列、`CASE`、`IF`、`CONCAT` 等行相关表达式具有与 MySQL 一致的语义。

## 代码定位

`pkg/sql/plan/function/func_binary.go` 的 `DateFormat`、`TimeFormat` 及四个 `FromUnixTime*Format` 实现均在入口处拒绝 `!ivecs[1].IsConst()`,并只读取格式向量的第 0 个值。

## 复现信息

- MatrixOne main:`ac660ae56ecd37bab3dd8c61193617a323377029`
- MySQL:8.0.46
- 完整场景重复执行 3 次,结果一致

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.