matrixorigin / matrixorigin/matrixone
[Compatibility]: TIMESTAMPDIFF overcounts calendar periods at time-of-day boundaries
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## 现象
MatrixOne `main`(`ac660ae56ecd37bab3dd8c61193617a323377029`)的 `TIMESTAMPDIFF()` 在 `MONTH`、`QUARTER` 和 `YEAR` 单位下没有用完整的日期时间判断已完成的日历周期,导致同日但尚差 1 微秒的区间多算一个月,并导致小于一个月的负区间返回 `-1 MONTH`。
```sql
SELECT
TIMESTAMPDIFF(MONTH,
'2024-01-31 10:00:00.000001',
'2024-03-31 10:00:00.000000') AS months,
TIMESTAMPDIFF(QUARTER,
'2024-01-31 10:00:00.000001',
'2024-03-31 10:00:00.000000') AS quarters;
SELECT TIMESTAMPDIFF(MONTH,
'2000-01-01 00:00:00.000000',
'1999-12-31 23:59:59.999999');
SELECT
TIMESTAMPDIFF(MONTH,
'2000-02-29 12:00:00.123456',
'2024-02-29 09:59:59.999999') AS months,
TIMESTAMPDIFF(YEAR,
'2000-02-29 12:00:00.123456',
'2024-02-29 09:59:59.999999') AS years;
```
结果对比:
```text
MatrixOne MySQL 8.0.46
少 1 微秒的两个月区间 (MONTH,QUARTER) (2,0) (1,0)
反向 1 微秒 (MONTH) -1 0
同月日但时刻尚未到 (MONTH,YEAR) (288,24) (287,23)
```
精确满两个月、精确满 24 年和多 1 微秒的控制组在两端一致;`MICROSECOND/SECOND/MINUTE/HOUR/DAY/WEEK` 的同批差分也一致。问题限定在按日历月计算并派生 `QUARTER/YEAR` 的路径。
## 期望
`TIMESTAMPDIFF(MONTH)` 应只计算完整经过的日历月,并按朝零方向截断负结果;比较月日边界时必须包含时分秒和微秒。`QUARTER`、`YEAR` 应基于正确的完整月数计算。
## 代码定位
`pkg/container/types/datetime.go` 的 `Datetime.ConvertToMonth` 只使用 `Day()` 计算 `dayDiff`,忽略两端的时间部分;当 `dayDiff == 0` 时直接返回名义月份差。负方向则无条件在 `dayDiff < 0` 时减 1,产生远离零的结果。`DateTimeDiffWithUnit` 的 `QUARTER` 和 `YEAR` 直接对该错误月数除以 3/12。
## 复现信息
- MatrixOne main:`ac660ae56ecd37bab3dd8c61193617a323377029`
- MySQL:8.0.46
- 边界与控制组重复执行 3 次,结果一致
Contributor guide
Assessment
This issue has not been assessed yet.