matrixorigin / matrixorigin/matrixone
[Compatibility]: TIME functions accept and return values beyond the ±838:59:59 endpoint
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## 现象
MatrixOne 的多个 `TIME` 结果函数可以直接返回超出 MySQL `TIME` 合法范围的值。MySQL 的完整范围是 `-838:59:59.000000` 到 `838:59:59.000000`;相同输入会被截到端点并产生 `1292` warning。
```sql
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SELECT CAST(SEC_TO_TIME(3020399.000001) AS CHAR);
SELECT CAST(
ADDTIME(CAST('838:59:58.999999' AS TIME(6)), '00:00:00.000002')
AS CHAR
);
SELECT CAST(
SUBTIME(CAST('-838:59:58.999999' AS TIME(6)), '00:00:00.000002')
AS CHAR
);
SELECT CAST(
TIMEDIFF(
CAST('9999-12-31 23:59:59.999999' AS DATETIME(6)),
CAST('0001-01-01 00:00:00.000000' AS DATETIME(6))
) AS CHAR
);
```
MatrixOne 返回:
```text
838:59:59.000001
838:59:59.000001
-838:59:59.000001
87649415:59:59.999999
```
上述语句均没有 warning。MySQL 8.0.46 返回:
```text
838:59:59.000000
838:59:59.000000
-838:59:59.000000
838:59:59.000000
```
并为越界结果产生 `Warning 1292: Truncated incorrect time value`。
## 影响
同一表达式在 `SELECT`、view 和 prepared statement 中能够返回非法 `TIME`,但写入 `TIME(6)` 或通过 CTAS 物化时又被 MatrixOne 的列范围检查拒绝。例如:
```sql
CREATE TABLE src(id INT PRIMARY KEY, t TIME(6));
INSERT INTO src VALUES (1, '838:59:58.999999');
SELECT CAST(ADDTIME(t, '00:00:00.000002') AS CHAR) FROM src;
-- 838:59:59.000001
CREATE TABLE copied AS
SELECT ADDTIME(t, '00:00:00.000002') AS t FROM src;
-- ERROR 1690: data out of range: data type time, value '838:59:59.000001'
```
这使查询结果、view/prepared 路径与物化路径使用了不同的值域契约。
## 代码定位
- `pkg/sql/plan/function/func_binary.go` 的 `addTimeToTime` 和 `subTimeFromTime` 使用 `types.ValidTime`;该检查允许 MatrixOne 内部的约 25.6 亿小时范围,而不是 MySQL `TIME` 范围。
- 同文件 `timeDiff` 也使用 `types.ValidTime`,溢出时会截到 `types.MaxHourInTime`。
- `secToTimeFromFloat64` 使用 `types.IsMySQLTimeFunctionResult`;`pkg/container/types/time.go` 将函数端点定义为 `838:59:59.999999`,与 MySQL 文档和 MySQL 8.0.46 的实际结果不一致。
- 另一方面,TIME 列赋值已经使用 `MySQLTimeMax`,因此会拒绝上述函数生成的非法值。
## 验证范围
- MatrixOne: latest `main` `ac660ae56ecd37bab3dd8c61193617a323377029`
- MySQL oracle: 8.0.46
- `sql_mode=''` 与 `STRICT_TRANS_TABLES` 结果一致
- 标量、表列、view、server-side prepared statement、CTAS/`INSERT ... SELECT`
- 正负边界、精确端点、端点外 1 微秒、普通范围和超大 `DATETIME` 差值
- 3 个全新数据库重复,现象一致
MySQL 文档说明 `TIME` 的完整范围为 `-838:59:59.000000` 到 `838:59:59.000000`,且 `TIMEDIFF()` 的结果受 `TIME` 范围限制:
- https://dev.mysql.com/doc/refman/8.0/en/time.html
- https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timediff
Contributor guide
Assessment
This issue has not been assessed yet.