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

Open
#114,657 0 comments 0 reactions 1 assignee Claimed by @azat View on GitHub
comp-decimal comp-distributed comp-query-execution minor potential bug
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

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.