matrixorigin / matrixorigin/matrixone

[Compatibility]: FROM_DAYS maps pre-year-1 day numbers to NULL instead of zero date

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

## Problem

`FROM_DAYS()` maps every day number below the first representable non-zero date (`366`) to SQL `NULL` in MatrixOne. MySQL 8.3 returns the DATE sentinel `0000-00-00` for this range.

MatrixOne already models MySQL zero dates explicitly (`types.ZeroDate`) and supports them through parsing, storage, and the wire protocol, so this is a function implementation gap rather than a general decision to reject zero dates.

Tested on MatrixOne `main` commit `f0c31cd4b830be32442cf329e0a3fb08aa9c16c3` and MySQL 8.3.0. The MatrixOne result was reproduced three times under each SQL mode below.

## Reproduction

```sql
SET SESSION sql_mode='';

SELECT FROM_DAYS(0), FROM_DAYS(1), FROM_DAYS(365), FROM_DAYS(366);
```

MatrixOne:

```text
NULL | NULL | NULL | 0001-01-01
```

MySQL:

```text
0000-00-00 | 0000-00-00 | 0000-00-00 | 0001-01-01
```

Negative day numbers also return `0000-00-00` in MySQL, while MatrixOne returns `NULL`.

## SQL-mode behavior

The scalar-expression difference is unchanged under:

- empty `sql_mode`
- `NO_ZERO_DATE`
- `STRICT_TRANS_TABLES`
- `STRICT_TRANS_TABLES,NO_ZERO_DATE`

MySQL's `NO_ZERO_DATE` checks apply when a generated zero date is written into a table, not to the scalar result of `FROM_DAYS()` itself. MatrixOne currently returns `NULL` before that distinction can be made.

## Row and persistence coverage

```sql
CREATE TABLE src(n BIGINT);
INSERT INTO src VALUES (-1), (0), (1), (365), (366), (730485), (3652424), (3652425);

SELECT n, FROM_DAYS(n), TO_DAYS(FROM_DAYS(n)) FROM src ORDER BY n;
CREATE TABLE copied AS SELECT n, FROM_DAYS(n) AS d FROM src;
```

The row-wise MatrixOne result stores `NULL` for `n <= 365` in CTAS. With non-strict mode MySQL stores `0000-00-00`. The behavior is also reproduced with a bound/prepared parameter.

## Controls

- `FROM_DAYS(366)` returns `0001-01-01` in both systems.
- `FROM_DAYS(730485)` returns `2000-01-01` in both systems.
- `FROM_DAYS(3652424)` returns `9999-12-31` in both systems.
- Values above the supported upper date range return `NULL` in both systems.
- `TO_DAYS(FROM_DAYS(n)) = n` for representable dates in both systems.
- MatrixOne remains healthy after all boundary queries.

## Code analysis

`pkg/sql/plan/function/func_builtin.go`, `builtInFromDays`, computes an interval relative to `types.DatetimeEpoch` (`0001-01-01`). For day numbers below 366, `Datetime.AddInterval` rejects the underflow and the function appends `types.Date(0)` with the null flag set.

The underflow branch does not distinguish the MySQL zero-date range from a true upper overflow, and it does not return the existing `types.ZeroDate` sentinel.

## Expected behavior

`FROM_DAYS(n)` should return the DATE sentinel `0000-00-00` for day numbers that MySQL maps to the pre-year-1 range, reserving SQL `NULL` for actual out-of-range overflow. Subsequent assignment/CTAS should then apply the active SQL-mode rules independently.

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.