matrixorigin / matrixorigin/matrixone
[Compatibility]: TIME_TRUNCATE_FRACTIONAL is accepted but temporal conversions still round
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Environment
- MatrixOne: `main` at `fc621e3616d229c7a29d0c80e73ed8eb1997459c`
- MySQL reference: 8.0.46
- Session time zone: `+00:00`
## Problem
MatrixOne accepts `TIME_TRUNCATE_FRACTIONAL` in `sql_mode`, but temporal values are still rounded when converted to a lower fractional-seconds precision. The mode therefore has no effect on `TIME`, `DATETIME`, or `TIMESTAMP` values.
MySQL documents this mode as selecting truncation instead of rounding when temporal values are converted to lower precision: [`TIME_TRUNCATE_FRACTIONAL`](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_time_truncate_fractional).
## Minimal reproduction
```sql
SET SESSION time_zone = '+00:00';
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,TIME_TRUNCATE_FRACTIONAL';
CREATE TABLE t(
id INT PRIMARY KEY,
dt DATETIME(3),
ts TIMESTAMP(3),
tm TIME(3)
);
INSERT INTO t VALUES(
1,
'2024-01-02 03:04:05.123556',
'2024-01-02 03:04:05.123556',
'12:34:56.123556'
);
SELECT CAST(dt AS CHAR), CAST(ts AS CHAR), CAST(tm AS CHAR) FROM t;
```
MatrixOne returns rounded values:
```text
2024-01-02 03:04:05.124
2024-01-02 03:04:05.124
12:34:56.124
```
MySQL 8.0.46 returns truncated values:
```text
2024-01-02 03:04:05.123
2024-01-02 03:04:05.123
12:34:56.123
```
Without `TIME_TRUNCATE_FRACTIONAL`, both systems round the same input to `.124`.
## Boundary behavior
With the mode enabled, MatrixOne also rounds `23:59:59.999500` across the day boundary and rounds `12:59:59.999500` across the hour boundary:
```text
DATETIME/TIMESTAMP: 2024-01-03 00:00:00.000
TIME: 13:00:00.000
```
MySQL truncates them to `2024-01-02 23:59:59.999` and `12:59:59.999`. Negative `TIME` values show the same difference: MatrixOne converts `-12:34:56.123556` to `-12:34:56.124`, while MySQL converts it to `-12:34:56.123`.
## Affected execution paths
The same mode-insensitive rounding occurs in MatrixOne for:
- direct `INSERT` and `UPDATE`;
- non-key assignments in `INSERT ... ON DUPLICATE KEY UPDATE`;
- `INSERT ... SELECT` from FSP 6 columns into FSP 3 columns;
- SQL `PREPARE ... EXECUTE ... USING` parameters;
- explicit `CAST(... AS DATETIME(3))` and `CAST(... AS TIME(3))`;
- CTAS over those casts;
- `ALTER TABLE ... MODIFY` from FSP 6 columns to FSP 3 columns.
A prepared statement created once and executed first with ordinary rounding mode and then with `TIME_TRUNCATE_FRACTIONAL` returns `.124` both times in MatrixOne. MySQL returns `.124` for the first execution and `.123` for the second, so the mode is evaluated for the active execution rather than being permanently captured by preparation.
## Code analysis
`pkg/frontend/variables.go` lists `TIME_TRUNCATE_FRACTIONAL` as a valid `sql_mode` token, but there is no other reference to this token in the repository. `pkg/container/types/timestamp.go:getMsec` unconditionally examines the first discarded digit and increments the retained fractional part when it is 5-9. `TIME`, `DATETIME`, `TIMESTAMP`, and relevant casts use this helper without a session-mode input, so truncation cannot currently be selected.
The behavior above was reproduced three times with fresh databases and connections. All listed paths include an ordinary rounding-mode control, where MatrixOne and MySQL agree.
Contributor guide
Assessment
This issue has not been assessed yet.