matrixorigin / matrixorigin/matrixone

[Bug]: CTAS persists fractional TIME values in an unscaled TIME column

Open
#28,127 0 comments 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

## Summary

CTAS can persist fractional seconds in a column declared as unscaled `TIME`. This violates the declared FSP contract: direct `INSERT ... SELECT` into the same `TIME`/`TIME(0)` target truncates the fraction, while CTAS stores it.

## Environment

- MatrixOne official `main`: `99ed717b769e261b842c2f17345bd65865fc1470`
- Clean local single-CN build and isolated data directory
- Reproduced identically in 3/3 runs

## Reproduction

```sql
CREATE TABLE src (id INT PRIMARY KEY, dt DATETIME(6) NOT NULL);
INSERT INTO src VALUES (1, '2024-01-01 07:08:09.123456');

CREATE TABLE ctas_time AS
SELECT TIME(dt) AS v FROM src;
SHOW CREATE TABLE ctas_time;
SELECT v FROM ctas_time;
```

Actual result:

```text
CREATE TABLE `ctas_time` (`v` time NOT NULL)
07:08:09.123456
```

## Scope control

The normal target-insert path respects the same FSP contract:

```sql
CREATE TABLE t0 (v TIME NOT NULL);
CREATE TABLE t6 (v TIME(6) NOT NULL);
INSERT INTO t0 SELECT TIME(dt) FROM src;
INSERT INTO t6 SELECT TIME(dt) FROM src;
SELECT v FROM t0; -- 07:08:09
SELECT v FROM t6; -- 07:08:09.123456
```

Likewise, direct literal `INSERT` into `TIME`/`TIME(0)` rounds to whole seconds. The unexpected behavior is the CTAS path retaining precision that its own synthesized schema does not declare.

## Expected behavior

CTAS must either declare `TIME(6)` for this expression or coerce the inserted value to the declared unscaled `TIME` precision. Schema metadata and persisted value must agree.

## Preliminary code observation

The `TIME()` evaluator returns a value carrying source fractional precision at runtime, while CTAS derives an unscaled `TIME` target schema from static metadata. Its generated insert appears not to apply the same target-scale coercion as normal `INSERT ... SELECT`. This is a diagnosis hypothesis, not a proposed fix.

## Suggested regression coverage

Compare CTAS and `INSERT ... SELECT` into `TIME`, `TIME(0)`, and `TIME(6)` for a `TIME(DATETIME(6))` expression; assert both column metadata and stored values.

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.