matrixorigin / matrixorigin/matrixone

[Compatibility]: INSERT VALUES rejects exact fractional numbers and rounds approximate ties asymmetrically

Open
#28,469 1 comment 0 reactions 1 assignee Claimed by @ck89119 View on GitHub
area/compatibility kind/bug severity/s0
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

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.