matrixorigin / matrixorigin/matrixone
[Compatibility]: CONVERT_TZ returns VARCHAR and discards fractional seconds
- 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
Assessment
This issue has not been assessed yet.