"Table default.xxx doesn't exist.." Exception occurs when both distributed and local tables are involved

[dgzdot]

clickhouse server version 20.8.3.18

reproduce step:

local table:
SHOW CREATE TABLE test_query_local;
CREATE TABLE dgz.test_query_local
(
id UInt64,
repo String
)
ENGINE = ReplicatedMergeTree('/clickhouse/dgz/tables/{shard}/test_query_local', '{replica}')
ORDER BY id
SETTINGS index_granularity = 8192;

distributed table:
SHOW CREATE TABLE test_query_all;
CREATE TABLE dgz.test_query_all
(
id UInt64,
repo String
)
ENGINE = Distributed('xxx', 'dgz', 'test_query_local', rand());

execute sql:
use dgz;
SELECT uniq(id) FROM test_query_all WHERE repo = '100' AND id IN (SELECT id FROM test_query_local WHERE repo = '200');

then exception occurs:
Received exception from server (version 20.8.3):
Code: 60. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: Received from xxxx:9000. DB::Exception: Table default.test_query_local doesn't exist..

Why this sql try to search table in default database in remote clickhouse instance.

[den-crane]

CH uses default database in this case by design.

#12489
#15898
#10471

you need to specify database: dgz.test_query_local

SELECT uniq(id) FROM test_query_all WHERE repo = '100' AND id IN (SELECT id FROM dgz.test_query_local WHERE repo = '200');

[dgzdot]

CH uses default database in this case by design.

#12489
#15898
#10471

you need to specify database: dgz.test_query_local

SELECT uniq(id) FROM test_query_all WHERE repo = '100' AND id IN (SELECT id FROM dgz.test_query_local WHERE repo = '200');

What are the reasons for this design? In general, SQL is always executed in the current database, such a design violates common sense.

[den-crane]

What are the reasons for this design?

Basically: it is what it is

#12489 (comment)