matrixorigin / matrixorigin/matrixone

[Compatibility]: unsigned subtraction ignores NO_UNSIGNED_SUBTRACTION mode

Open
#28,134 2 comments 0 reactions 1 assignee Claimed by @slyunnn View on GitHub
kind/bug phase/testing
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

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.