ClickHouse / ClickHouse/ClickHouse
The remote leg of a Distributed query re-serializes a lenient toDecimal64 constant into a strict CAST that throws: local and distributed results diverge
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe what's wrong**
A query whose `WHERE` clause contains a constant expression producing an out-of-precision `Decimal` value — e.g. `toDecimal64(1000000000000000000, 0)`, a 19-digit value in `Decimal(18, 0)` — executes fine against a local table, but the same query through a `Distributed` table throws `Code: 69. DB::Exception: Too many digits (19 > 18) in decimal value` from the remote leg. The distributed layer re-serializes the folded constant as `_CAST('1000000000000000000', 'Decimal(18, 0)')`, and that string-literal form validates digits strictly while the original integer-argument `toDecimal64` call does not (the long-standing leniency described in #14124). So the rewritten query the initiator ships to the shards is not executable, although the user's original query is — the same statement returns a result locally and an exception through `Distributed`.
**Does it reproduce on the most recent release?**
Reproduced on master `26.8.1.1307` (official build), 20/20 through the `Distributed` table (exception) and 20/20 on the local table (correct result).
**How to reproduce**
```sql
CREATE TABLE t (a Decimal(18, 0)) ENGINE = MergeTree ORDER BY a;
INSERT INTO t SELECT toDecimal64(number * 100000000000, 0) FROM numbers(100);
CREATE TABLE dist_t AS t ENGINE = Distributed(test_shard_localhost, currentDatabase(), 't');
-- local: returns 0
SELECT count() FROM t WHERE intDiv(a, toDecimal64(1000000000000000000, 0)) NOT IN (0, 1);
-- distributed: Code: 69. DB::Exception: Too many digits (19 > 18) in decimal value:
-- In scope SELECT count() FROM ... WHERE intDiv(a, _CAST('1000000000000000000', 'Decimal(18, 0)')) NOT IN (0, 1)
SELECT count() FROM dist_t WHERE intDiv(a, toDecimal64(1000000000000000000, 0)) NOT IN (0, 1)
SETTINGS prefer_localhost_replica = 0;
```
`prefer_localhost_replica = 0` is needed only because the repro cluster is a localhost loopback; on a cluster with genuinely remote shards the remote leg always takes the serialization path and the exception fires at default settings. Simpler predicates hit the same thing: `WHERE a != toDecimal64(1000000000000000000, 0)` and `WHERE a < toDecimal64(1000000000000000000, 0)` both error 69 through `Distributed` and succeed locally.
The constant itself evaluates leniently on the initiator:
```sql
SELECT toDecimal64(1000000000000000000, 0); -- returns 1000000000000000000
SELECT CAST('1000000000000000000', 'Decimal(18, 0)'); -- Code: 69, Too many digits (19 > 18)
```
**Expected behavior**
The same query should either succeed on both paths or fail on both. Whichever way the `toDecimal64` integer-form leniency is resolved, the SQL the distributed layer generates for the remote legs should be executable whenever the original query is — e.g. by serializing the folded constant in a form that round-trips (keeping the original function call, or a value form that does not re-validate into a stricter type).
Related: https://github.com/ClickHouse/ClickHouse/issues/14124
Found by an automatic optimizer-testing framework (differential testing of optimizer settings, query plans, and equivalent rewrites).
Contributor guide
Assessment
This issue has not been assessed yet.