ClickHouse / ClickHouse/ClickHouse
Parallel-replicas `INSERT SELECT` route forwards a materialized CTE as the initiator's `_materialized_cte_*` temporary table
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
Related: https://github.com/ClickHouse/ClickHouse/issues/112642
Related: https://github.com/ClickHouse/ClickHouse/pull/118853
### Describe the bug
With `enable_materialized_cte = 1`, `parallel_distributed_insert_select = 2` and parallel replicas, an `INSERT ... SELECT` whose `SELECT` uses a materialized CTE inside an `IN` subquery takes the parallel-replicas insert route. The route forwards `toAST()` of the analyzed query tree, in which the materialized CTE has already been resolved to the initiator's temporary table, so the replicas receive
```sql
INSERT INTO default.dst SELECT __table8.x IN (SELECT toUInt64(__table1.r = __table4.r) FROM _materialized_cte_c_18279169332899438788 AS __table1, _materialized_cte_c_18279169332899438788 AS __table4) AS same FROM default.src AS __table8 WHERE ...
```
and fail with `Unknown table expression identifier '_materialized_cte_c_18279169332899438788'` (code 60) after the local replica has inserted its rows. This is the `INSERT SELECT` counterpart of #112642, which describes the same mechanism for a plain `SELECT` with parallel replicas. Reproduced on the master CI binary `26.9.1.1447` (commit `cc102704b1b`, 2026-09-14). The same happens when the CTE is hidden in a SQL user-defined function, in `SELECT * APPLY ` or in `SELECT * APPLY (lambda)`, because the analyzer expands those before the query is forwarded.
### How to reproduce
```sql
CREATE TABLE src (x UInt64) ENGINE = MergeTree ORDER BY x;
INSERT INTO src SELECT number FROM numbers(5);
CREATE TABLE dst (same UInt8) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/dst', 'r1') ORDER BY tuple();
SET enable_analyzer = 1, enable_materialized_cte = 1, parallel_distributed_insert_select = 2,
enable_parallel_replicas = 1, max_parallel_replicas = 3, cluster_for_parallel_replicas = 'test_cluster_one_shard_three_replicas_localhost',
parallel_replicas_for_non_replicated_merge_tree = 1, automatic_parallel_replicas_mode = 0,
parallel_replicas_local_plan = 1, parallel_replicas_insert_select_local_pipeline = 1, parallel_replicas_prefer_local_replica = 1;
-- fails on the remote replicas, partial local insert
INSERT INTO dst SELECT x IN (WITH c AS MATERIALIZED (SELECT rand64() AS r FROM numbers(1)) SELECT toUInt64(a.r = b.r) FROM c AS a, c AS b) AS same FROM src WHERE x = 1;
-- same through a SQL UDF, an APPLY transformer, or an APPLY lambda
CREATE FUNCTION g AS (x) -> x IN (WITH c AS MATERIALIZED (SELECT rand64() AS r FROM numbers(1)) SELECT toUInt64(a.r = b.r) FROM c AS a, c AS b);
INSERT INTO dst SELECT g(x) AS same FROM src WHERE x = 1;
INSERT INTO dst SELECT * APPLY g FROM src WHERE x = 1;
INSERT INTO dst SELECT * APPLY (y -> y IN (WITH c AS MATERIALIZED (SELECT rand64() AS r FROM numbers(1)) SELECT toUInt64(a.r = b.r) FROM c AS a, c AS b)) FROM src WHERE x = 1;
```
The general path (`parallel_distributed_insert_select = 0`) inserts the correct row `1` for every variant, with and without parallel replicas for the `SELECT`. A materialized CTE inside a *scalar* subquery does not fail on the route: the analyzer folds it to a constant on the initiator before forwarding.
### Expected behavior
The forwarded query must be executable on the replicas: either the CTE definition travels with the query, or the route declines queries whose forwarded fragment would reference a materialized CTE.
### Root cause
`ClusterProxy::executeInsertSelectWithParallelReplicas` builds `InterpreterSelectQueryAnalyzer` on the `SELECT`, takes `getQueryTree()` and serializes it with `toAST()`. A materialized CTE referenced more than once is resolved to a `TableNode` over the initiator-local `StorageMemory` named `_materialized_cte__`, and that name is what `toAST()` emits.
Contributor guide
Assessment
This issue has not been assessed yet.