matrixorigin / matrixorigin/matrixone

[Compatibility]: high-range DECIMAL VAR_POP errors instead of returning DOUBLE

Open
#28,131 0 comments 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

## Summary

`VAR_POP`, `VAR_SAMP`, and `VARIANCE` on a large-range `DECIMAL` column fail because MatrixOne declares a DECIMAL return type and attempts to convert the floating variance back into `DECIMAL(38,12)`. MySQL specifies `DOUBLE` for numeric variance and standard-deviation aggregates. The same input succeeds after an explicit `DOUBLE` cast.

This is distinct from the already closed small-variance accuracy issue: the present case deterministically raises an error instead of returning any variance.

## Environment

- MatrixOne official `main`: `99ed717b769e261b842c2f17345bd65865fc1470`
- Clean local single-CN build and isolated data directory
- Reproduced identically in 3/3 runs

## Reproduction

```sql
CREATE TABLE d (id INT PRIMARY KEY, v DECIMAL(30,10) NOT NULL);
INSERT INTO d VALUES
(1, 9007199254740993.1234567890),
(2, -12.5000000000),
(3, 0.0000000001);

SELECT SUM(v), AVG(v) FROM d;
-- 9007199254740980.6234567891 | 3002399751580326.874485596367

SELECT VAR_POP(v) FROM d;
```

Actual result:

```text
ERROR 20301 (HY000): invalid input: Can't convert Float64 To Decimal128: ... (38,12)
```

`VAR_SAMP(v)`, `VARIANCE(v)`, and `VAR_POP(v) OVER ()` fail the same way. `STDDEV_POP`/`STDDEV_SAMP` happen to return because the square root is small enough for the DECIMAL result range.

## Scope control

```sql
SELECT VAR_POP(CAST(v AS DOUBLE)) FROM d;
-- returns an approximate variance

SELECT VAR_POP(CAST(1.1 AS DECIMAL(10,4))),
STDDEV_POP(CAST(1.1 AS DECIMAL(10,4)));
-- small DECIMAL values return normally
```

The failure therefore depends on the DECIMAL return conversion range, not on input parsing, aggregation state construction, or the mathematical variance computation itself.

## Expected behavior

For numeric inputs, MySQL documents `VAR_POP`, `VAR_SAMP`, `VARIANCE`, and standard-deviation functions as returning `DOUBLE`. MatrixOne should not reject a valid high-range DECIMAL variance merely because it routes the result through a fixed-scale DECIMAL type; it should return an approximate `DOUBLE` result consistent with that contract.

## Preliminary code observation

`VarStdDevReturnType` selects `AvgReturnType` for DECIMAL input, while the aggregate state and result calculation are float64-based. The final conversion then calls `Decimal128FromFloat64(..., 38, scale)`, which fails when the variance exceeds the integer range left by the scale. This is a diagnosis hypothesis, not a proposed fix.

## Suggested regression coverage

Cover DECIMAL input with variance above `DECIMAL(38,12)` integer range for `VAR_POP`, `VAR_SAMP`, `VARIANCE`, and window `VAR_POP`; assert an approximate `DOUBLE` result. Include small DECIMAL and explicit `DOUBLE` controls.

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.