matrixorigin / matrixorigin/matrixone

[Compatibility]: MAKEDATE truncates exact DECIMAL arguments instead of rounding

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

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

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.