Clickhouse: Distributed table join ON fails

[alex-zaitsev]

CREATE TABLE foo_local ON CLUSTER '{cluster}'
(
`bar` UInt64
)
ENGINE = MergeTree()
ORDER BY tuple();

CREATE TABLE foo_distributed AS foo_local
ENGINE = Distributed('{cluster}', default, foo_local)

CREATE TEMPORARY TABLE _tmp_baz
(
`qux` UInt64
)

SELECT * FROM foo_distributed JOIN _tmp_baz ON (foo_distributed.bar = _tmp_baz.qux)

Received exception from server (version 20.3.10):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'foo_distributed.bar' while processing query: 'SELECT bar, qux FROM default.foo_local ALL INNER JOIN (SELECT * FROM _tmp_baz) AS _tmp_baz ON foo_distributed.bar = qux', required columns: 'bar' 'qux' 'foo_distributed.bar', source columns: 'bar', joined columns: 'qux'. 

I can not reproduce it with cluster('test_cluster_two_shards'), but probably there is a more concise example, e.g.:

SELECT * FROM foo_distributed JOIN system.one ON (foo_distributed.bar = one.dummy);

Received exception from server (version 20.3.10):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'foo_distributed.bar' while processing query: 'SELECT bar, dummy FROM default.foo_local ALL INNER JOIN system.one ON foo_distributed.bar = dummy', required columns: 'bar' 'dummy' 'foo_distributed.bar', source columns: 'bar', joined columns: 'dummy'. 

[alex-zaitsev]

It seem to work on 20.4.4.18, so it is probably already fixed, but please look anyway @4ertus2

[filimonov]

@4ertus2 it is a regression (happened somewhere between 19.11 and 19.13), and since 20.3 is lts we should try to backport / adopt the fix. :

[4ertus2]

I've backported #9972 into 20.3
It should be fixed there now.