matrixorigin / matrixorigin/matrixone
[Compatibility]: INSERT VALUES rejects exact fractional numbers and rounds approximate ties asymmetrically
- 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
The `INSERT ... VALUES` path cannot assign exact fractional numeric literals to integer columns, even though the equivalent UPDATE and column-driven INSERT conversions work. Approximate literals are accepted but use asymmetric/inconsistent tie rounding.
```sql
CREATE TABLE t(id INT PRIMARY KEY, v BIGINT);
INSERT INTO t VALUES
(1,2.5), (2,-2.5), (3,3.5), (4,-3.5);
```
MatrixOne aborts atomically with:
```text
invalid argument cast to int, bad value 2.5
```
MySQL 8.3 stores `3,-3,4,-4`.
Using approximate literals exposes a second inconsistency in the same VALUES path:
```sql
INSERT INTO t VALUES
(1,2.5E0), (2,-2.5E0), (3,3.5E0), (4,-3.5E0);
```
```text
2.5E0 -2.5E0 3.5E0 -3.5E0
MatrixOne 2 -3 3 -4
MySQL 8.3 2 -2 4 -4
```
The exact-literal error also occurs before the duplicate-key update can run:
```sql
INSERT INTO t VALUES (1,2.5),(2,-2.5)
ON DUPLICATE KEY UPDATE v=VALUES(v);
```
It also occurs with SQL `PREPARE` when `@v` is `2.5`, `CAST(2.5 AS DOUBLE)`, or `CAST(2.5 AS DECIMAL(5,1))`:
```sql
PREPARE p FROM 'INSERT INTO t VALUES (?,?)';
SET @id=1, @v=2.5;
EXECUTE p USING @id,@v;
```
All variants were reproduced in three independent databases per engine. Failed multi-row INSERT and ODKU statements leave the target rows unchanged, and the connection remains usable.
### Controls
- `UPDATE t SET v=CASE ... 2.5 ...` stores `3/-3` in both engines.
- `INSERT INTO dst SELECT id,d FROM src` with a `DECIMAL(5,1)` source column stores `3/-3/4/-4` in both engines.
- `CAST(2.5 AS SIGNED)`, `CAST(-2.5 AS SIGNED)`, and their approximate-literal equivalents agree in both engines.
- Integer values through the same SQL prepared statement succeed in MatrixOne.
### Code-path evidence
The returned error text is emitted by the string-to-integer executor at [`strToSignedWithProc`](https://github.com/matrixorigin/matrixone/blob/a9d3b8b857c44f1249e682c91d4d62a914eb7d20/pkg/sql/plan/function/func_cast.go#L6485-L6554). `EXPLAIN VERBOSE` shows the VALUES input staged as `_valuescan.column_0` without the explicit typed conversion visible on the `INSERT ... SELECT` control. This is consistent with the VALUES/prepared path feeding the fractional numeric value through the strict string parser rather than the DECIMAL/FLOAT assignment executor.
### Expected behavior
`INSERT ... VALUES`, ODKU, and SQL prepared execution should apply the same numeric-to-integer assignment rules as equivalent typed expressions and MySQL, instead of rejecting exact fractional values or rounding approximate ties asymmetrically.
Contributor guide
Assessment
This issue has not been assessed yet.