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`)

Open
#119,497 0 comments 0 reactions 1 assignee Claimed by @Ergus View on GitHub
bug comp-decimal comp-regular-function
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

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.