matrixorigin / matrixorigin/matrixone
[Bug]: DOUBLE to scaled DECIMAL128 cast silently loses exact integer precision
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
Casting an exactly representable integral `DOUBLE` to a scaled `DECIMAL128` introduces a fractional error at large magnitudes. The same source converted to scale 0-5, direct integer/string input, and `DECIMAL256` remains exact.
This can silently change persisted values and downstream aggregate results.
## Environment
- MatrixOne: latest official `main`, commit `07fdd4ae0f80f287b93fc4b525fd296d5617abc7`
- Deployment: local standalone launch (isolated Log/TN/CN ports and data directory)
## Reproduction
```sql
select cast(cast(1000000000001 as double) as decimal(38,0)) s0,
cast(cast(1000000000001 as double) as decimal(38,3)) s3,
cast(cast(1000000000001 as double) as decimal(38,6)) s6;
select cast(cast(-1000000000001 as double) as decimal(38,6)) negative_,
cast(1000000000001 as decimal(38,6)) integer_direct,
cast('1000000000001' as decimal(38,6)) string_direct,
cast(cast(1000000000001 as double) as decimal(65,6)) decimal256_control;
create table t(d double, x decimal(38,6));
insert into t(d,x) values
(cast(1000000000001 as double), cast(1000000000001 as double)),
(cast(-1000000000001 as double), cast(-1000000000001 as double));
select cast(d as char), cast(x as char) from t order by d;
```
## Actual behavior
```text
DECIMAL(38,0): 1000000000001
DECIMAL(38,3): 1000000000001.000
DECIMAL(38,6): 1000000000000.999936
negative DECIMAL(38,6): -1000000000000.999936
direct integer/string: 1000000000001.000000
DECIMAL(65,6): 1000000000001.000000
```
Inserting from a `DOUBLE` expression persists the erroneous `DECIMAL(38,6)` value. The behavior reproduced identically in 3/3 fresh runs.
## Expected behavior
`1000000000001` is exactly representable as IEEE-754 binary64. Converting it to a decimal type with six fractional digits should therefore produce exactly `1000000000001.000000`, as the scale 0-5 and DECIMAL256 controls do.
## Impact
The error is not presentation-only. A 400,000-row fixture built from these casts stored `999999999999` as `999999999999.000064` and `1000000000001` as `1000000000000.999936`; consequently `VAR_POP` returned `1.999948801638` instead of `2` even though the variance implementation was correct for the stored inputs.
## Code-path analysis
`pkg/container/types.Decimal128FromFloat64` scales the binary64 value with floating-point multiplication before converting the scaled value to the integer decimal payload. For this case, multiplying by `10^6` reaches approximately `10^18`, where binary64 cannot retain unit precision, and the converted integer payload loses 64 scale units. `Decimal256FromFloat64` instead formats and parses the float value and does not show the defect.
## Suggested regression coverage
- exactly representable integers near scale-dependent binary64 precision boundaries;
- positive/negative values and scales 0 through the target width;
- explicit CAST, INSERT/UPDATE assignment, prepared parameters, CTAS, and generated expressions;
- consistency between DECIMAL64/128/256 conversions and round-trip formatting.
## Duplicate search
Open and closed issues were searched for float-to-decimal cast precision, `Decimal128FromFloat64`, scale loss, and related Decimal coercion reports. Existing reports concern FIELD/comparison/prepared-expression coercion or high-range variance, not this explicit conversion defect.
Contributor guide
Assessment
This issue has not been assessed yet.