matrixorigin / matrixorigin/matrixone

[Compatibility]: TIME_TRUNCATE_FRACTIONAL is accepted but temporal conversions still round

Open
#28,600 1 comment 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

## 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

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.