matrixorigin / matrixorigin/matrixone
[Compatibility]: ADDTIME and SUBTIME keep TIMESTAMP type instead of returning DATETIME
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## 现象
当 `ADDTIME()` 或 `SUBTIME()` 的第一个参数是 `TIMESTAMP` 列时,MatrixOne 将函数结果解析为 `TIMESTAMP`;MySQL 8.0.46 将结果解析为 `DATETIME`。该类型差异会进入 view 和 CTAS 元数据,并使已经物化的 CTAS 值随后受到会话时区转换。
```sql
CREATE DATABASE addsub_ts_repro;
USE addsub_ts_repro;
SET time_zone = '+00:00';
CREATE TABLE src(
id INT PRIMARY KEY,
ts TIMESTAMP(6),
dt DATETIME(6),
tm TIME(6)
);
INSERT INTO src VALUES
(1, '2024-01-01 01:02:03.123456',
'2024-01-01 01:02:03.123456',
'01:02:03.123456');
CREATE TABLE copied AS
SELECT
ADDTIME(ts, '00:00:01.000001') AS add_ts,
SUBTIME(ts, '00:00:01.000001') AS sub_ts,
ADDTIME(dt, '00:00:01.000001') AS add_dt,
ADDTIME(tm, '00:00:01.000001') AS add_tm
FROM src;
SELECT column_name, data_type, datetime_precision
FROM information_schema.columns
WHERE table_schema = 'addsub_ts_repro' AND table_name = 'copied'
ORDER BY ordinal_position;
SELECT CAST(add_ts AS CHAR), CAST(sub_ts AS CHAR),
CAST(add_dt AS CHAR), CAST(add_tm AS CHAR)
FROM copied;
SET time_zone = '+08:00';
SELECT CAST(add_ts AS CHAR), CAST(sub_ts AS CHAR),
CAST(add_dt AS CHAR), CAST(add_tm AS CHAR)
FROM copied;
```
MatrixOne 的 CTAS 元数据为:
```text
add_ts timestamp 6
sub_ts timestamp 6
add_dt datetime 6
add_tm time 6
```
MySQL 8.0.46 的 CTAS 元数据为:
```text
add_ts datetime 6
sub_ts datetime 6
add_dt datetime 6
add_tm time 6
```
在 `+00:00` 创建 CTAS 后切换到 `+08:00`,MatrixOne 中两个 `*_ts` 值变化 8 小时:
```text
-- +00:00
2024-01-01 01:02:04.123457 2024-01-01 01:02:02.123455
-- +08:00
2024-01-01 09:02:04.123457 2024-01-01 09:02:02.123455
```
MySQL 的 CTAS 列为 `DATETIME(6)`,切换会话时区后保持 `01:02:04.123457` / `01:02:02.123455`。`DATETIME` 和 `TIME` 输入列作为相邻控制,两端类型和值一致。
## 影响
- view/CTAS 客户端读取到不同的列类型;
- 基于 `ADDTIME(TIMESTAMP, ...)` 或 `SUBTIME(TIMESTAMP, ...)` 物化的数据,在 MatrixOne 中成为绝对时间并随读取会话时区变化,而 MySQL 将其保存为不做时区转换的 `DATETIME`;
- 下游 schema inference、ETL 和跨时区查询会得到不同结果。
## 代码定位
- `pkg/sql/plan/function/list_builtIn.go` 中 ADDTIME/SUBTIME 的 `TIMESTAMP` overload(4/5)将返回类型声明为 `T_timestamp`。
- `pkg/sql/plan/function/func_binary.go` 的 `addTimeToTimestamp` / `subTimeFromTimestamp` 将结果重新转换为 `types.Timestamp`。
- 相邻的 DATETIME 和 TIME overload 与 MySQL 一致,问题仅出现在 TIMESTAMP overload。
## 验证范围
- MatrixOne: latest `main` `ac660ae56ecd37bab3dd8c61193617a323377029`
- MySQL oracle: 8.0.46
- ADDTIME / SUBTIME
- 标量、view、CTAS、server-side prepared statement
- `TIMESTAMP(6)`、`DATETIME(6)`、`TIME(6)` 类型控制
- `+00:00` 创建并在 `+08:00` 读取
- 3 个全新数据库重复,结果一致
MySQL 的时间函数文档:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_addtime
Contributor guide
Assessment
This issue has not been assessed yet.