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)
- 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
Assessment
This issue has not been assessed yet.