ClickHouse / ClickHouse/ClickHouse

FULL JOIN USING over a Distributed left table: qualified column t1.a returns the coalesced USING value for right-only rows (or exception 8 at pure defaults)

Open
#112,909 1 comment 0 reactions 1 assignee Claimed by @KochetovNicolai View on GitHub
bug clickgap-analyzed comp-distributed comp-joins culprit-pr-pinned
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

`FULL JOIN ... USING` where the **left** table is read through `Distributed`: selecting the qualified join column `t1.a` is wrong for right-only rows — and at pure defaults the same query throws an exception.

**How to reproduce** (26.8.1.561, any single-shard cluster whose replica is the server itself, e.g. `test_shard_localhost` from the standard test configs):

```sql
CREATE TABLE t1 (a UInt16, b UInt16) ENGINE = MergeTree ORDER BY tuple();
CREATE TABLE t2 (a Int16, b Nullable(Int64)) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t1 SELECT number + 1, number FROM numbers(10);
INSERT INTO t2 SELECT number - 4, number FROM numbers(10);
CREATE TABLE dist_t1 AS t1 ENGINE = Distributed(test_shard_localhost, currentDatabase(), t1);
CREATE TABLE dist_t2 AS t2 ENGINE = Distributed(test_shard_localhost, currentDatabase(), t2);

-- LOCAL, correct: right-only rows show t1.a = 0 (the UInt16 default)
SELECT a, t1.a, t2.a FROM t1 FULL JOIN t2 USING (a) ORDER BY (t1.a, t2.a);

-- DISTRIBUTED, wrong values: right-only rows show t1.a = -4..-1
-- (the COALESCED USING value leaks into the qualified left column)
SELECT a, t1.a, t2.a FROM dist_t1 AS t1 FULL JOIN dist_t2 AS t2 USING (a)
ORDER BY (t1.a, t2.a) SETTINGS prefer_localhost_replica = 0;

-- DISTRIBUTED, pure defaults (prefer_localhost_replica = 1): exception
-- Code: 8. DB::Exception: Cannot find column `a` in source stream,
-- there are only columns: [a, __table1.a, t2.a]. (THERE_IS_NO_COLUMN)
SELECT a, t1.a, t2.a FROM dist_t1 AS t1 FULL JOIN dist_t2 AS t2 USING (a)
ORDER BY (t1.a, t2.a);
```

Deterministic 20/20 for both manifestations. Characterization:

- Wrapping ONLY the left table in `Distributed` is sufficient (right side local: same wrong values / exception).
- `RIGHT JOIN` is correct; the defect is FULL-specific (left-only default rows vs right-only coalesced rows).
- The bare `a` (USING projection) is correct in all variants — only the QUALIFIED `t1.a` is corrupted/lost.
- `prefer_localhost_replica` selects which manifestation appears (1, the default → exception 8; 0 → silent wrong values); everything else is at defaults.

Related: https://github.com/ClickHouse/ClickHouse/issues/66739

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.