matrixorigin / matrixorigin/matrixone
[Compatibility]: MAKEDATE truncates exact DECIMAL arguments instead of rounding
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Environment
- MatrixOne: latest `main`, commit `f0c31cd4b830be32442cf329e0a3fb08aa9c16c3`
- MySQL comparison: 8.3.0
## Problem
`MAKEDATE` truncates exact `DECIMAL` year/day-of-year arguments to integers. MySQL rounds exact numeric arguments during integer conversion, so values with a fractional part of `.5` or greater select a different year or day.
This issue is separate from #28493, which covers the function's incorrect VARCHAR return domain.
## Reproduction
```sql
SELECT MAKEDATE(2024, CAST(1.4 AS DECIMAL(3,1)));
SELECT MAKEDATE(2024, CAST(1.5 AS DECIMAL(3,1)));
SELECT MAKEDATE(2024, CAST(1.9 AS DECIMAL(3,1)));
SELECT MAKEDATE(CAST(2024.5 AS DECIMAL(5,1)), 1);
```
MatrixOne returns:
```text
2024-01-01
2024-01-01
2024-01-01
2024-01-01
```
MySQL 8.3 returns:
```text
2024-01-01
2024-01-02
2024-01-02
2025-01-01
```
A year-end boundary also shows the one-day error:
```sql
SELECT MAKEDATE(2024, CAST(365.5 AS DECIMAL(4,1)));
-- MatrixOne: 2024-12-30
-- MySQL: 2024-12-31
```
## Coverage
The DECIMAL behavior was reproduced three times through:
- exact DECIMAL constants around `.4`, `.5`, and `.9` boundaries;
- DECIMAL year and day-of-year columns;
- SQL prepared statement parameters;
- CTAS, which persists the earlier date.
Integer and NULL controls behave normally. FLOAT/DOUBLE and character inputs are intentionally excluded from the compatibility claim because MySQL itself has path-dependent conversion behavior for those domains; this issue is limited to the stable exact-DECIMAL contract.
## Code analysis
`MAKEDATE` registers both parameters as `VARCHAR`, so DECIMAL inputs are converted to text. `MakeDateString` in `pkg/sql/plan/function/func_binary.go` then falls back from `ParseInt` to `ParseFloat` and converts with `int64(...)`:
```go
yearFloat, err := strconv.ParseFloat(yearStrStr, 64)
...
year = int64(yearFloat)
```
The same logic is used for the day argument. The direct float-to-int conversion truncates toward zero and also unnecessarily loses the exact DECIMAL domain before conversion.
Contributor guide
Assessment
This issue has not been assessed yet.