ClickHouse / ClickHouse/ClickHouse

Parallel-replicas `INSERT SELECT` route forwards a materialized CTE as the initiator's `_materialized_cte_*` temporary table

Open
#120,022 0 comments 0 reactions 0 assignees View on GitHub
bug common table expressions comp-parallel-replicas
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.