matrixorigin / matrixorigin/matrixone
[Compatibility]: high-range DECIMAL VAR_POP errors instead of returning DOUBLE
- 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
Assessment
This issue has not been assessed yet.