matrixorigin / matrixorigin/matrixone

[Bug]: strict DECIMAL DML paths accept values outside the declared column range

Open
#28,114 1 comment 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

## Description

Several DML assignment paths can write a value outside a `DECIMAL(p,s)` column's declared range even when `STRICT_TRANS_TABLES` is enabled. For `DECIMAL(5,2)`, `1000.00` is stored and remains reachable through a secondary-index lookup, although the maximum valid value is `999.99`.

## Environment

- Branch: `main`
- Commit: `99ed717b769e261b842c2f17345bd65865fc1470`
- Deployment: isolated local single-CN / single-TN / single-LOG instance

## Steps to reproduce

```sql
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
CREATE TABLE t (
id INT PRIMARY KEY,
amount DECIMAL(5,2) NOT NULL,
KEY amount_idx(amount)
);
INSERT INTO t VALUES (1, 1.00), (2, 2.00);

-- Control: correctly rejected as out of range.
INSERT INTO t VALUES (3, 1000.00);
SELECT id, amount FROM t ORDER BY id;

-- Incorrectly succeeds.
UPDATE t SET amount = 1000.00 WHERE id = 2;
SELECT id, amount FROM t ORDER BY id;

EXPLAIN SELECT id FROM t WHERE amount = 1000.00;
SELECT id, amount FROM t WHERE amount = 1000.00;
```

## Actual behavior

- The control `INSERT` returns `ERROR 20301`: `1000.00` is beyond `Decimal64(5,2)` and leaves only rows `(1, 1.00)` and `(2, 2.00)`.
- The strict `UPDATE` returns success and stores `(2, 1000.00)`.
- `EXPLAIN` uses `amount_idx`, and `WHERE amount = 1000.00` returns that invalid row.
- The same invalid value is accepted by direct and `CASE` UPDATE, scalar-subquery UPDATE, `UPDATE ... JOIN`, `ON DUPLICATE KEY UPDATE`, and `INSERT ... SELECT`.
- The value remains present after an explicit transaction commits.

## Expected behavior

Under `STRICT_TRANS_TABLES`, an out-of-range `UPDATE` assignment must be rejected, and all affected rows must retain their prior values. A `DECIMAL(5,2)` column must never persist a value greater than `999.99`.

## Stability and controls

- Minimal reproducer: 3/3 identical runs on the official `main` commit above.
- Passing control: out-of-range single-row `INSERT` is rejected and leaves the table unchanged.
- Atomicity/data check: the update path writes the invalid value; index lookup reads it back. The server remains queryable after the operation.
- Rejection controls: arithmetic UPDATE and real server binary PreparedStatements using `UPDATE t SET amount = ? WHERE id = ?` reject the same value and preserve the prior row 3/3. These controls show the defect depends on the DML expression/binding path, rather than the DECIMAL range definition itself.
- Related but not duplicate: #27340 concerns non-strict/`IGNORE` assignments being rejected instead of boundary-adjusted. Its strict-mode expectation is also that an out-of-range `UPDATE` is rejected.

## Code analysis

Hypothesis: numeric UPDATE binding can arrive at `forceCastExpr2WithProcess` with a type considered equal to the target. Its equal-type fast path skips an assignment cast unless `needsSameTypeAssignmentCast` is true; that helper currently covers TINYTEXT and TIME but not numeric targets. The UPDATE planner also seeds numeric target context before applying `forceProjectedAssignmentCastExpr`. The insert path still rejects this value, so the two DML write paths are not enforcing the same DECIMAL range invariant.

Relevant code:

- `pkg/sql/plan/bind_update.go`
- `pkg/sql/plan/build_constraint_util.go`

## Regression coverage

Add a BVT SQL regression after the fix covering strict direct, `CASE`, scalar-subquery, joined, `ON DUPLICATE KEY UPDATE`, and `INSERT ... SELECT` assignments to `DECIMAL(5,2)`, with arithmetic and parameterized-update rejection controls, rejected-operation atomicity, and indexed readback.

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.