matrixorigin / matrixorigin/matrixone
[Compatibility]: FROM_DAYS maps pre-year-1 day numbers to NULL instead of zero date
- 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
Assessment
This issue has not been assessed yet.