matrixorigin / matrixorigin/matrixone

[Compatibility]: EXTRACT returns formatted strings instead of numeric values

Open
#28,572 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`)把 `EXTRACT(... FROM DATETIME/TIMESTAMP/TIME)` 的结果定义为 `VARCHAR(65535)`;复合 unit 还返回带分隔符的日期时间文本。MySQL 返回数值类型和无分隔符数值。

```sql
CREATE TABLE src(id INT PRIMARY KEY, dt DATETIME(6));
INSERT INTO src VALUES
(1, '2024-02-29 23:58:57.000002'),
(2, '2024-02-29 23:58:57.000010'),
(3, '2024-02-29 23:58:57.000100');

SELECT EXTRACT(YEAR FROM dt),
EXTRACT(DAY_SECOND FROM dt),
EXTRACT(SECOND_MICROSECOND FROM dt),
EXTRACT(MICROSECOND FROM dt)
FROM src WHERE id=1;
```

```text
YEAR DAY_SECOND SECOND_MICROSECOND MICROSECOND
MatrixOne '2024' '29 23:58:57' '57.000002' '2'
MySQL 8.0.46 2024 29235857 57000002 2
```

该返回形态会改变后续查询:

- `ORDER BY EXTRACT(MICROSECOND FROM dt)` 在 MatrixOne 按字符串得到 id `2,3,1`(`10,100,2`),MySQL 按数值得到 `1,2,3`。
- `EXTRACT(DAY_SECOND FROM dt)+0` 以及与数值的谓词比较在 MatrixOne 报 `invalid argument cast to int, bad value 29 23:58:57`;MySQL 可正常执行。
- 视图中 `YEAR`、`DAY_SECOND`、`MICROSECOND` 三列在 MatrixOne 都是 `VARCHAR(65535)`;MySQL 分别是 `INT`、`BIGINT`、`INT`。

## 期望

`EXTRACT` 应返回与 unit 相适配的数值类型;复合 unit 应返回 MySQL 定义的拼接数值,不能包含空格、冒号或小数点。排序、算术、谓词和视图元数据应保持数值语义。

## 代码定位

- `pkg/sql/plan/function/list_builtIn.go` 将 DATETIME、TIME、VARCHAR、TIMESTAMP 四个重载的返回类型固定为 `VARCHAR`,只有 DATE 重载返回 `UINT32`。
- `pkg/sql/plan/function/func_binary.go` 的 `extractFromDatetime` 调用 `DaySecondStr`、`SecondMicrosecondStr` 等展示字符串方法,并由 `ExtractFromDatetime` 直接写入字符串结果向量。

## 复现信息

- 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.