matrixorigin / matrixorigin/matrixone
[Compatibility]: implicit FLOAT and DOUBLE assignment uses a different tie-rounding rule
- 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
Assessment
This issue has not been assessed yet.