Hybrid table fails with subquery referencing itself (UNKNOWN_TABLE error) #1208
Description
Using docker://altinity/clickhouse-server:25.8.9.20496.altinityantalya
Merge tree table:
SHOW CREATE TABLE merge_tree_table_b9faf88a_d5d3_11f0_b816_e0c26496f172
Query id: e816cc40-68c2-4ca1-99a4-44bd49cb3bab
┌─statement──────────────────────────────────────────────────────────────────┐
1. │ CREATE TABLE default.merge_tree_table_b9faf88a_d5d3_11f0_b816_e0c26496f172↴│
│↳( ↴│
│↳ `boolean_col` Nullable(Bool), ↴│
│↳ `long_col` Nullable(Int64), ↴│
│↳ `double_col` Nullable(Float64), ↴│
│↳ `string_col` String, ↴│
│↳ `timestamp_col` Nullable(DateTime64(6)), ↴│
│↳ `date_col` Nullable(Date), ↴│
│↳ `time_col` Nullable(Int64), ↴│
│↳ `timestamptz_col` Nullable(DateTime64(6, 'UTC')), ↴│
│↳ `integer_col` Nullable(Int32), ↴│
│↳ `float_col` Nullable(Float32), ↴│
│↳ `decimal_col` Nullable(Decimal(10, 2)) ↴│
│↳) ↴│
│↳ENGINE = MergeTree ↴│
│↳PARTITION BY string_col ↴│
│↳ORDER BY tuple() ↴│
│↳SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────┘
1 row in set. Elapsed: 0.001 sec. SELECT *
FROM default.merge_tree_table_b9faf88a_d5d3_11f0_b816_e0c26496f172
Query id: 75f74985-fddd-4c8d-b902-97f61e0682cd
┌─boolean_col─┬─long_col─┬─────────double_col─┬─string_col─┬──────────────timestamp_col─┬───date_col─┬────time_col─┬────────────timestamptz_col─┬─integer_col─┬─float_col─┬─decimal_col─┐
1. │ true │ 9044 │ 2931.782814070929 │ William │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 3733 │ 7730.6836 │ 456.78 │
2. │ true │ 1654 │ 3801.2622503916614 │ Oliver │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 3432 │ 6701.752 │ 456.78 │
3. │ true │ 8751 │ 7291.267979503492 │ Frank │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 5313 │ 8428.52 │ 456.78 │
4. │ true │ 1519 │ 3799.273006373374 │ Louis │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 8785 │ 1248.2616 │ 456.78 │
5. │ false │ 3611 │ 4492.090462838536 │ Isaac │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 4552 │ 1554.795 │ 456.78 │
└─────────────┴──────────┴────────────────────┴────────────┴────────────────────────────┴────────────┴─────────────┴────────────────────────────┴─────────────┴───────────┴─────────────┘
5 rows in set. Elapsed: 0.003 sec. Hybrid table:
SHOW CREATE TABLE hybrid_table
Query id: ec84afda-eadb-47ff-9e25-290ce81ddeb6
┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE TABLE default.hybrid_table ↴│
│↳( ↴│
│↳ `boolean_col` Nullable(Bool), ↴│
│↳ `long_col` Nullable(Int64), ↴│
│↳ `double_col` Nullable(Float64), ↴│
│↳ `string_col` String, ↴│
│↳ `timestamp_col` Nullable(DateTime64(6)), ↴│
│↳ `date_col` Nullable(Date), ↴│
│↳ `time_col` Nullable(Int64), ↴│
│↳ `timestamptz_col` Nullable(DateTime64(6, 'UTC')), ↴│
│↳ `integer_col` Nullable(Int32), ↴│
│↳ `float_col` Nullable(Float32), ↴│
│↳ `decimal_col` Nullable(Decimal(10, 2)) ↴│
│↳) ↴│
│↳ENGINE = Hybrid(remote('localhost', 'default', 'merge_tree_table_b9faf88a_d5d3_11f0_b816_e0c26496f172'), date_col <= '2024-01-01', icebergCluster('replicated_cluster', 'http://minio:9000/warehouse/data_hybrid', 'admin', 'password'), date_col > '2024-01-01') │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 row in set. Elapsed: 0.001 sec. SELECT *
FROM default.hybrid_table
Query id: d078fc05-ccb1-4d74-bf3b-fdac80523d32
┌─boolean_col─┬─long_col─┬─────────double_col─┬─string_col─┬──────────────timestamp_col─┬───date_col─┬────time_col─┬────────────timestamptz_col─┬─integer_col─┬─float_col─┬─decimal_col─┐
1. │ true │ 1519 │ 3799.273006373374 │ Louis │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 8785 │ 1248.2616 │ 456.78 │
2. │ true │ 8751 │ 7291.267979503492 │ Frank │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 5313 │ 8428.52 │ 456.78 │
3. │ false │ 3611 │ 4492.090462838536 │ Isaac │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 4552 │ 1554.795 │ 456.78 │
4. │ true │ 9044 │ 2931.782814070929 │ William │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 3733 │ 7730.6836 │ 456.78 │
5. │ true │ 1654 │ 3801.2622503916614 │ Oliver │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 3432 │ 6701.752 │ 456.78 │
└─────────────┴──────────┴────────────────────┴────────────┴────────────────────────────┴────────────┴─────────────┴────────────────────────────┴─────────────┴───────────┴─────────────┘
5 rows in set. Elapsed: 0.020 sec. Iceberg table:
SHOW CREATE TABLE database_b4bcf6c0_d5d3_11f0_b919_e0c26496f172.`namespace_b4bd0364_d5d3_11f0_9b00_e0c26496f172.table_b4bd039e_d5d3_11f0_8208_e0c26496f172`
Query id: 7824a4c8-bc6e-4c29-9f71-d73cd288cc21
┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE TABLE database_b4bcf6c0_d5d3_11f0_b919_e0c26496f172.`namespace_b4bd0364_d5d3_11f0_9b00_e0c26496f172.table_b4bd039e_d5d3_11f0_8208_e0c26496f172`↴│
│↳( ↴│
│↳ `boolean_col` Nullable(Bool), ↴│
│↳ `long_col` Nullable(Int64), ↴│
│↳ `double_col` Nullable(Float64), ↴│
│↳ `string_col` String, ↴│
│↳ `timestamp_col` Nullable(DateTime64(6)), ↴│
│↳ `date_col` Nullable(Date), ↴│
│↳ `time_col` Nullable(Int64), ↴│
│↳ `timestamptz_col` Nullable(DateTime64(6, 'UTC')), ↴│
│↳ `integer_col` Nullable(Int32), ↴│
│↳ `float_col` Nullable(Float32), ↴│
│↳ `decimal_col` Nullable(Decimal(10, 2)) ↴│
│↳) ↴│
│↳ENGINE = Iceberg('http://minio:9000/warehouse/data_hybrid/', 'admin', '[HIDDEN]') │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 row in set. Elapsed: 0.010 sec.SELECT *
FROM database_b4bcf6c0_d5d3_11f0_b919_e0c26496f172.`namespace_b4bd0364_d5d3_11f0_9b00_e0c26496f172.table_b4bd039e_d5d3_11f0_8208_e0c26496f172`
Query id: 5e439aa7-6975-4f4a-b2ca-cede83996cdc
┌─boolean_col─┬─long_col─┬─────────double_col─┬─string_col─┬──────────────timestamp_col─┬───date_col─┬────time_col─┬────────────timestamptz_col─┬─integer_col─┬─float_col─┬─decimal_col─┐
1. │ false │ 3611 │ 4492.090462838536 │ Isaac │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 4552 │ 1554.795 │ 456.78 │
2. │ true │ 1519 │ 3799.273006373374 │ Louis │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 8785 │ 1248.2616 │ 456.78 │
3. │ true │ 9044 │ 2931.782814070929 │ William │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 3733 │ 7730.6836 │ 456.78 │
4. │ true │ 1654 │ 3801.2622503916614 │ Oliver │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 3432 │ 6701.752 │ 456.78 │
5. │ true │ 8751 │ 7291.267979503492 │ Frank │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 5313 │ 8428.52 │ 456.78 │
└─────────────┴──────────┴────────────────────┴────────────┴────────────────────────────┴────────────┴─────────────┴────────────────────────────┴─────────────┴───────────┴─────────────┘
5 rows in set. Elapsed: 0.018 sec.This query returns result with exception:
SELECT *
FROM default.hybrid_table
WHERE string_col IN (
SELECT DISTINCT string_col
FROM default.hybrid_table
WHERE long_col > 1500
)Query id: c7318d08-2b31-43cc-8dc2-a3b151532c2a
┌─boolean_col─┬─long_col─┬─────────double_col─┬─string_col─┬──────────────timestamp_col─┬───date_col─┬────time_col─┬────────────timestamptz_col─┬─integer_col─┬─float_col─┬─decimal_col─┐
1. │ false │ 3611 │ 4492.090462838536 │ Isaac │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 4552 │ 1554.795 │ 456.78 │
2. │ true │ 9044 │ 2931.782814070929 │ William │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 3733 │ 7730.6836 │ 456.78 │
3. │ true │ 1519 │ 3799.273006373374 │ Louis │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 8785 │ 1248.2616 │ 456.78 │
4. │ true │ 8751 │ 7291.267979503492 │ Frank │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 5313 │ 8428.52 │ 456.78 │
5. │ true │ 1654 │ 3801.2622503916614 │ Oliver │ 2024-01-01 06:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │ 3432 │ 6701.752 │ 456.78 │
└─────────────┴──────────┴────────────────────┴────────────┴────────────────────────────┴────────────┴─────────────┴────────────────────────────┴─────────────┴───────────┴─────────────┘
5 rows in set. Elapsed: 0.038 sec.
Received exception from server (version 25.8.9):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse3:9000. DB::Exception: Unknown table expression identifier 'default.hybrid_table' in scope (SELECT DISTINCT __table1.string_col AS string_col FROM default.hybrid_table AS __table1 WHERE __table1.long_col > 1500). (UNKNOWN_TABLE)