matrixorigin / matrixorigin/matrixone

[Compatibility]: CONVERT_TZ returns VARCHAR and discards fractional seconds

Open
#28,564 0 comments 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`)的 `CONVERT_TZ()` 将 `DATETIME(6)` 转换为无小数的字符串,而 MySQL 8.0.46 返回保持原精度的 `DATETIME(6)`:

```sql
CREATE TABLE src(id INT PRIMARY KEY, dt DATETIME(6));
INSERT INTO src VALUES
(1, '2024-01-01 00:00:00.123456'),
(2, '2024-01-01 00:00:00.654321');

SELECT id, CONVERT_TZ(dt, '+00:00', '+05:30') AS shifted
FROM src ORDER BY id;
```

MatrixOne:

```text
1 2024-01-01 05:30:00
2 2024-01-01 05:30:00
```

MySQL 8.0.46:

```text
1 2024-01-01 05:30:00.123456
2 2024-01-01 05:30:00.654321
```

返回类型也不同:

```sql
CREATE VIEW v AS
SELECT CONVERT_TZ(dt, '+00:00', '+05:30') AS shifted FROM src;

SELECT DATA_TYPE, COLUMN_TYPE, DATETIME_PRECISION, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'v';
```

- MatrixOne:`varchar / VARCHAR(65535) / NULL / 65535`
- MySQL 8.0.46:`datetime / datetime(6) / 6 / NULL`

该差异会改变查询语义:上述两行在 MatrixOne 中按 `CONVERT_TZ(...)` 分组后合并为一组 `count(*) = 2`,按该表达式倒序仍返回 `1, 2`;MySQL 保留两个时间值,分为两组并按 `2, 1` 排序。CTAS、生成列和 PreparedStatement 路径也会丢失微秒。

## 期望

`CONVERT_TZ()` 应返回 `DATETIME`,并保留输入的 fractional-seconds precision;视图、CTAS、生成列、分组、排序和 PreparedStatement 不应因为时区转换丢失微秒或改变结果集合。

## 代码定位

- `pkg/sql/plan/function/list_builtIn.go` 将返回类型固定为 `VARCHAR`。
- `pkg/sql/plan/function/func_binary.go` 的 `ConvertTz` 使用 `time.DateTime` 格式化后写入字符串;该布局不包含小数秒。

## 复现信息

- MatrixOne main:`ac660ae56ecd37bab3dd8c61193617a323377029`
- MySQL:8.0.46
- `time_zone = '+00:00'`
- 完整场景重复执行 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.