matrixorigin / matrixorigin/matrixone
[Compatibility]: unsigned subtraction ignores NO_UNSIGNED_SUBTRACTION mode
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Summary
MatrixOne ignores the `NO_UNSIGNED_SUBTRACTION` SQL-mode distinction for unsigned integer subtraction. `CAST(0 AS UNSIGNED) - 1` returns `-1` in every tested mode, whereas MySQL returns an out-of-range error by default and only returns `-1` when `NO_UNSIGNED_SUBTRACTION` is explicitly enabled.
## Environment
- MatrixOne official `main`: `99ed717b769e261b842c2f17345bd65865fc1470`
- Clean local single-CN build and isolated data directory
- Reproduced identically in 3/3 runs
## Reproduction
```sql
SET SESSION sql_mode = '';
SELECT @@sql_mode, CAST(0 AS UNSIGNED) - 1;
-- '' | -1
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SELECT @@sql_mode, CAST(0 AS UNSIGNED) - 1;
-- STRICT_TRANS_TABLES | -1
SET SESSION sql_mode = 'NO_UNSIGNED_SUBTRACTION';
SELECT @@sql_mode, CAST(0 AS UNSIGNED) - 1;
-- NO_UNSIGNED_SUBTRACTION | -1
```
## Expected behavior
MySQL's documented default is unsigned subtraction when either integer operand is unsigned. Therefore `CAST(0 AS UNSIGNED) - 1` must raise an unsigned `BIGINT` out-of-range error unless `NO_UNSIGNED_SUBTRACTION` is enabled; only with that mode should the signed result `-1` be returned.
## Scope control
Assigning the result back to a `BIGINT UNSIGNED` column raises an out-of-range error and leaves the row unchanged in both tested modes. The discrepancy is specifically expression evaluation/type selection: the read expression already behaves as if `NO_UNSIGNED_SUBTRACTION` were always on, while the session mode value does not change it.
## Suggested regression coverage
Test unsigned subtraction in empty mode, strict mode, and `NO_UNSIGNED_SUBTRACTION`, both as a scalar expression and in an update to an unsigned column. Assert the distinct MySQL results and SQLSTATEs.
Contributor guide
Assessment
This issue has not been assessed yet.