JOINs on the local tables do not work with swarm queries

[alex-zaitsev]

When 'SETTINGS object_storage_cluster='my-swarm'' is used in a query with join, swarm throws an error that table does not exist. GLOBAL JOIN does not work as well. We need to use the GLOBAL join behaviour by default, when requestor server runs a subquery to calculate the right table. This temporary table is passed to each remote server, and queries are run on them using the temporary data that was transmitted

[ianton-ru]

The same when join used with cluster functions

:) select * from icebergS3('http://minio:9000/warehouse/data/', 'minio', 'minio123') join q on symbol=q.symbol

   ┌───────────────────datetime─┬─symbol─┬────bid─┬────ask─┬─q.symbol─┬─somevalue─┐
1. │ 2019-08-09 08:35:00.000000 │ AAPL   │ 198.23 │ 195.45 │ AAPL     │ Foo       │
2. │ 2019-08-09 08:35:00.000000 │ AAPL   │ 198.25 │  198.5 │ AAPL     │ Foo       │
3. │ 2019-08-09 08:35:00.000000 │ AAPL   │ 198.23 │ 195.45 │ AAPL     │ Foo       │
4. │ 2019-08-09 08:35:00.000000 │ AAPL   │ 198.25 │  198.5 │ AAPL     │ Foo       │
5. │ 2019-08-07 08:35:00.000000 │ AAPL   │ 195.23 │ 195.28 │ AAPL     │ Foo       │
6. │ 2019-08-07 08:35:00.000000 │ AAPL   │ 195.22 │ 195.28 │ AAPL     │ Foo       │
   └────────────────────────────┴────────┴────────┴────────┴──────────┴───────────┘

:) select * from icebergS3Cluster('ch-cluster', 'http://minio:9000/warehouse/data/', 'minio', 'minio123') join q on symbol=q.symbol

Received exception from server (version 25.3.3):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse2:9000. DB::Exception: Unknown table expression identifier 'default.q' in scope SELECT __table1.datetime AS datetime, __table1.symbol AS symbol, __table1.bid AS bid, __table1.ask AS ask, __table2.symbol AS `q.symbol`, __table2.somevalue AS somevalue FROM icebergS3Cluster('ch-cluster', 'http://minio:9000/warehouse/data/', 'minio', '[HIDDEN]', 'Parquet', '`datetime` Nullable(DateTime64(6)), `symbol` Nullable(String), `bid` Nullable(Float64), `ask` Nullable(Float64)') AS __table1 ALL INNER JOIN default.q AS __table2 ON __table1.symbol = __table2.symbol. (UNKNOWN_TABLE)

[ianton-ru]

EXPLAIN
SELECT
    c2,
    s2
FROM q
INNER JOIN s3Cluster('ch-cluster', 'http://minio1:9001/root/data/database/*', 'minio', 'minio123', 'CSV') AS s3 ON s3.c2 = q.c2

Query id: cbec5386-6a39-4734-b906-8a6786075a39

   ┌─explain─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + Projection))                                                                                           │
2. │   Expression                                                                                                                        │
3. │     Join                                                                                                                            │
4. │       Expression                                                                                                                    │
5. │         Expression (Change column names to column identifiers)                                                                      │
6. │           ReadFromMergeTree (default.q)                                                                                             │
7. │       Expression ((Change column names to column identifiers + (Project names + Change remote column names to local column names))) │
8. │         ReadFromCluster                                                                                                             │
   └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

but

EXPLAIN
SELECT
    c2,
    s2
FROM s3Cluster('ch-cluster', 'http://minio1:9001/root/data/database/*', 'minio', 'minio123', 'CSV') AS s3
INNER JOIN q ON s3.c2 = q.c2

Query id: f95a2d0b-058d-4ded-a648-5b45333e4980

   ┌─explain────────────────────┐
1. │ Expression (Project names) │
2. │   ReadFromCluster          │
   └────────────────────────────┘

meanwhile

EXPLAIN
SELECT
    c2,
    s2
FROM s3('http://minio1:9001/root/data/database/*', 'minio', 'minio123', 'CSV') AS s3
INNER JOIN q ON s3.c2 = q.c2

Query id: 10774bf0-f20c-447d-84a3-ff5e68e025c0

   ┌─explain────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + Projection))                      │
2. │   Expression                                                   │
3. │     Join                                                       │
4. │       Expression (Change column names to column identifiers)   │
5. │         S3(_table_function.s3)Source                           │
6. │       Expression                                               │
7. │         Expression (Change column names to column identifiers) │
8. │           ReadFromMergeTree (default.q)                        │
   └────────────────────────────────────────────────────────────────┘

[ianton-ru]

In DB::buildJoinTreeQueryPlan when called buildQueryPlanForTableExpression use something like left_select_query_info instead of select_query_info?
SELECT a,b,c FROM s3Cluster(...) JOIN x => SELECT a,c FROM s3Cluster(...) somehow?