matrixorigin / matrixorigin/matrixone

[Compatibility]: DATETIME(p) exact-second text output omits declared fractional width

Open
#28,921 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

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

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.