ClickHouse / ClickHouse/ClickHouse
`transform` returns wrong results for unmatched `Decimal` values when the result scale is larger: the fractional part is not rescaled (`1.5` -> `1.005`)
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe what's wrong**
TL;DR: In the three-argument form of `transform`, when the input is a `Decimal` and the result supertype is a `Decimal` with a larger scale, unmatched values pass through with the fractional part NOT rescaled — it is reinterpreted at the output scale, so `1.5` silently becomes `1.005` (or `1.00005` at scale 5). Matched values and the four-argument form with an explicit default are correct. Silent wrong results, no error.
**Does it reproduce on the most recent release?**
Yes — reproduced on current master (26.9.1.1) and on 26.4.1.1. The defective code is present in releases at least back to v24.3.
**How to reproduce**
```sql
SELECT x, transform(x, [toDecimal32('9.9', 1)], [toDecimal64('100.001', 3)]) AS t
FROM values('x Decimal32(1)', ('1.5'), ('9.9'), ('-2.7'), ('0.1'));
```
Returns:
```
1.5 1.005
9.9 100.001
-2.7 -2.007
0.1 0.001
```
The matched row (`9.9`) is correct; every unmatched row is wrong. With a scale-5 target the same input returns `1.00005`. `clickhouse local` reproduces it directly.
**Expected behavior**
Unmatched values are returned unchanged, rescaled to the result type — exactly what `CAST(x AS Decimal64(3))` returns: `1.5`, `-2.7`, `0.1`.
**Additional context**
Root cause in `src/Functions/transform.cpp`, `executeNumToNumHelper` (master lines 476–481): the pass-through branch splits the input value at the input scale — `getWholePart(def[i], def_scale)` / `getFractionalPart(def[i], def_scale)` — but then reassembles it with `decimalFromComponents(whole, fract, out_scale)`, which interprets `fract` as having `out_scale` digits (`DecimalFunctions.h`: result = `whole * 10^scale ± fract % 10^scale`). The fractional part must be multiplied by `10^(out_scale - def_scale)` before reassembly. The `CASE`/`caseWithExpression` route is unaffected because there the default column is cast to the result type before reaching this helper.
Contributor guide
Assessment
This issue has not been assessed yet.