matrixorigin / matrixorigin/matrixone
[Compatibility]: TIMEDIFF subtracts mixed datetime and time strings as calendar values
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Problem
`TIMEDIFF()` in MatrixOne calculates a large TIME value when one string operand is a datetime and the other is a time. MySQL requires both operands to have the same temporal kind and returns `NULL` for a datetime/time mixture.
Tested on MatrixOne `main` commit `f0c31cd4b830be32442cf329e0a3fb08aa9c16c3` and MySQL 8.3.0. Each MatrixOne reproduction was repeated three times.
## Minimal reproduction
```sql
SELECT TIMEDIFF('2024-01-02 12:00:00','01:00:00');
SELECT TIMEDIFF('12:00:00','2024-01-01 01:00:00');
```
MatrixOne returns:
```text
-23533:00:00.000000
23579:00:00.000000
```
MySQL returns `NULL` for both expressions without a warning.
## Row, prepared, and persistence coverage
The same values are produced when the operands come from mixed `VARCHAR` rows and when they are supplied through SQL `PREPARE`/`EXECUTE`.
A view preserves the invalid large values as TIME results. CTAS introduces an additional corruption step because the values exceed the persistable MySQL TIME range: MatrixOne emits range warnings and stores clamped values around `-838:59:59` / `838:59:59`, while MySQL stores `NULL`.
## Controls
- Two time strings return the same signed, microsecond-preserving difference in both systems.
- Two full datetime strings return the same difference in both systems.
- Invalid strings and NULL operands produce NULL in both systems.
- MatrixOne remains healthy after the vector, prepared, view, and CTAS cases.
## Code analysis
`TimeDiffString` in `pkg/sql/plan/function/func_binary.go` parses each string independently. If datetime parsing fails, it parses the value as TIME and immediately calls `Time.ToDatetime`, which attaches `Today(time.UTC)`.
The function then subtracts the two DATETIME values without retaining or comparing their original temporal kinds. A datetime/time mixture therefore becomes a real calendar-date subtraction instead of an incompatible pair.
## Expected behavior
`TIMEDIFF()` should only subtract operands of the same temporal kind. When string inputs resolve to a datetime/time mixture, it should return `NULL` consistently for literal, column, prepared, view, and CTAS paths.
Contributor guide
Assessment
This issue has not been assessed yet.