matrixorigin / matrixorigin/matrixone

[Compatibility]: implicit FLOAT and DOUBLE assignment uses a different tie-rounding rule

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

### MatrixOne version

Latest `main` at `a9d3b8b857c44f1249e682c91d4d62a914eb7d20`. MySQL control: 8.3.0.

### What is wrong

Assigning stored `FLOAT`/`DOUBLE` values exactly halfway between integers to an integer column uses a different rounding rule from both MySQL and MatrixOne's own explicit `CAST(... AS SIGNED)` path.

```sql
CREATE TABLE src(id INT PRIMARY KEY, f64 DOUBLE, f32 FLOAT, d DECIMAL(8,2));
INSERT INTO src VALUES
(1,-4.5,-4.5,-4.5),(2,-3.5,-3.5,-3.5),(3,-2.5,-2.5,-2.5),
(4,-1.5,-1.5,-1.5),(5,-0.5,-0.5,-0.5),(6,0.5,0.5,0.5),
(7,1.5,1.5,1.5),(8,2.5,2.5,2.5),(9,3.5,3.5,3.5),(10,4.5,4.5,4.5);

CREATE TABLE dst(id INT PRIMARY KEY, a BIGINT, b BIGINT, c BIGINT);
INSERT INTO dst SELECT id,f64,f32,d FROM src;
SELECT * FROM dst ORDER BY id;
```

For `DOUBLE`/`FLOAT`, MatrixOne stores every tie away from zero:

```text
-4.5 -3.5 -2.5 -1.5 -0.5 0.5 1.5 2.5 3.5 4.5
-5 -4 -3 -2 -1 1 2 3 4 5
```

MySQL 8.3 stores nearest-even values, which also matches `CAST(f64 AS SIGNED)` in both engines:

```text
-4 -4 -2 -2 0 0 2 2 4 4
```

The same MatrixOne/MySQL difference occurs when the source `DOUBLE`/`FLOAT` value reaches the integer column through:

- `INSERT ... SELECT`;
- `UPDATE` with correlated scalar subqueries;
- `INSERT ... SELECT ... ON DUPLICATE KEY UPDATE`;
- client parameterized inserts that emit approximate numeric literals.

All paths were repeated in three independent databases per engine. Non-tie controls (`±0.49`, `±0.51`, `±4.49`, `±4.51`) agree.

### Controls

- `SELECT CAST(f64 AS SIGNED), CAST(f32 AS SIGNED)` agrees with MySQL for every tested value.
- Stored generated columns using explicit `CAST(f64 AS SIGNED)` and `CAST(f32 AS SIGNED)` agree with MySQL.
- `DECIMAL(8,2)` assignments agree with MySQL and use exact-number half-away rounding.
- The discrepancy is therefore limited to implicit assignment of approximate numeric types, not generic integer storage or explicit conversion.

### Root cause

MatrixOne has separate conversion paths:

- explicit `FLOAT`/`DOUBLE` to signed integer uses [`math.RoundToEven`](https://github.com/matrixorigin/matrixone/blob/a9d3b8b857c44f1249e682c91d4d62a914eb7d20/pkg/sql/plan/function/func_cast.go#L355-L378);
- the generic conversion used by DML assignment calls [`math.Round`](https://github.com/matrixorigin/matrixone/blob/a9d3b8b857c44f1249e682c91d4d62a914eb7d20/pkg/sql/plan/function/func_cast.go#L3461-L3484), which rounds ties away from zero.

### Expected behavior

Implicit assignment from stored approximate numeric values should use the same MySQL-compatible tie conversion as the explicit cast path, so equivalent INSERT, UPDATE, ODKU, and generated-expression workflows do not persist different integers.

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.