matrixorigin / matrixorigin/matrixone

[Compatibility]: TIMEDIFF subtracts mixed datetime and time strings as calendar values

Open
#28,501 0 comments 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
area/compatibility kind/bug needs-triage
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.