ClickHouse / ClickHouse/ClickHouse
Support join/global join && in/global involved with multiple s3cluster table function.
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
if a sql has `in` or `global in` and both `from` clause are s3cluster table function, such as
```[sql]
select count(*) from s3Cluster ('test_shard_lpcalhost','http://127.0.0.1:9876/buck2/f1/part1.csv', 'minioadmin', 'minioadmin', 'CSV', 'name String, value UInt32, polygon Array(Array(Tuple(Float64, Float64)))')
where name in (select name from s3Cluster ('test_shard_localhost','http://127.0.0.1:9876/buck1/part1.csv', 'minioadmin', 'minioadmin', 'CSV', 'name String, value UInt32, polygon Array(Array(Tuple(Float64, Float64)))'))
```
same with `join` and `global join`
```[sql]
select count(*) from s3Cluster('test_shard_localhost','http://127.0.0.1:9876/buck2/{f1,f2}/part1.csv', 'minioadmin', 'minioadmin', 'CSV', 'name String, value UInt32, polygon Array(Array(Tuple(Float64, Float64)))') t1
join s3Cluster('test_shard_localhost', 'http://127.0.0.1:9876/buck1/part1.csv', 'minioadmin', 'minioadmin', 'CSV', 'name String, value UInt32, polygon Array(Array(Tuple(Float64, Float64)))') t2 ON t1.name = t2.name
```
the result is 0.
the reason is, when table function converts to storage, it will check if it's a remote query by: https://github.com/ClickHouse/ClickHouse/blob/master/src/TableFunctions/TableFunctionS3Cluster.cpp#L107
If it is a remote query, it will get the S3 key from the client side. The client side assumes only one s3cluster table in the remote servers. When another s3Cluster gets keys, it will get empty set.
Unfortunately, the s3cluster in `in` sub-query and in `join` right table should not be treated as a remote query. It should execute as a normal query, instead. but we cannot check it easily.
Contributor guide
Assessment
This issue has not been assessed yet.