matrixorigin / matrixorigin/matrixone
[Compatibility]: DATETIME(p) exact-second text output omits declared fractional width
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Bug Report
### MatrixOne Version
- Branch: latest `main`
- Commit: `0c3a04f390adaf6281fd592a49778ea5b1155e67`
- `git_version()`: `0c3a04f390`
- MySQL oracle: `8.0.45`
### Problem
For a `DATETIME(p)` expression whose microsecond value is exactly zero, MatrixOne's MySQL text-protocol result omits the fractional field even though `p > 0`.
This makes the direct result inconsistent with:
- MySQL, which emits exactly the declared precision (`.0`, `.000`, or `.000000`);
- MatrixOne's own `TIMESTAMP(p)` and `TIME(p)` output;
- `CAST(datetime_column AS CHAR)` in MatrixOne, which also emits the declared precision;
- the column metadata, which still reports the correct `DATETIME_PRECISION`.
Non-zero fractional values are formatted correctly. The stored value and declared scale are not lost; the problem is specific to direct `DATETIME` text-protocol formatting when `MicroSec() == 0`.
### Reproduce Steps
```sql
DROP DATABASE IF EXISTS dt_scale_repro;
CREATE DATABASE dt_scale_repro;
USE dt_scale_repro;
CREATE TABLE t (
id INT PRIMARY KEY,
d1 DATETIME(1),
d3 DATETIME(3),
d6 DATETIME(6),
ts3 TIMESTAMP(3),
tm3 TIME(3)
);
INSERT INTO t VALUES
(1,
'2026-09-16 00:00:00',
'2026-09-16 00:00:00',
'2026-09-16 00:00:00',
'2026-09-16 00:00:00',
'12:34:56');
SELECT d1, d3, d6, ts3, tm3 FROM t;
SELECT CAST(d1 AS CHAR), CAST(d3 AS CHAR), CAST(d6 AS CHAR)
FROM t;
SELECT CAST('2026-09-16 00:00:00' AS DATETIME(3));
```
### MatrixOne Result
```text
d1 d3 d6 ts3 tm3
2026-09-16 00:00:00 2026-09-16 00:00:00 2026-09-16 00:00:00 2026-09-16 00:00:00.000 12:34:56.000
CAST(d1 AS CHAR) CAST(d3 AS CHAR) CAST(d6 AS CHAR)
2026-09-16 00:00:00.0 2026-09-16 00:00:00.000 2026-09-16 00:00:00.000000
CAST('2026-09-16 00:00:00' AS DATETIME(3))
2026-09-16 00:00:00
```
### MySQL 8.0.45 Result
```text
d1 d3 d6 ts3 tm3
2026-09-16 00:00:00.0 2026-09-16 00:00:00.000 2026-09-16 00:00:00.000000 2026-09-16 00:00:00.000 12:34:56.000
CAST(d1 AS CHAR) CAST(d3 AS CHAR) CAST(d6 AS CHAR)
2026-09-16 00:00:00.0 2026-09-16 00:00:00.000 2026-09-16 00:00:00.000000
CAST('2026-09-16 00:00:00' AS DATETIME(3))
2026-09-16 00:00:00.000
```
### Code Location
`pkg/frontend/output.go` contains a special case in `ColumnSlices.GetDatetime` that calls `dt.String2(0)` whenever the declared scale is positive but `dt.MicroSec() == 0`. The adjacent comment identifies this as MySQL behavior, but MySQL 8.0.45 preserves the declared fractional width in the text-protocol result.
### Reproducibility and Safety
- Reproduced 3/3 times on the commit above.
- No server error or restart occurred.
- The temporary database was dropped after every run.
- Full SQL and raw MatrixOne/MySQL outputs: https://gist.github.com/Ariznawlll/02b328a78076bbb2a59330f4531f7372
### Related Issue Check
Closed issue #25305 concerned a non-zero `%f` value returned by `STR_TO_DATE()` losing fractional precision. It was fixed by #25391. This issue is a different remaining branch: an exact-zero fractional value with a positive declared `DATETIME` scale is shortened only in direct protocol output.
Contributor guide
Assessment
This issue has not been assessed yet.