ClickHouse中CTE结合ReplacingMergeTree与FINAL的行为疑问

核心原因 #

这并非CTE的预期行为，而是ClickHouse在多副本场景下，CTE中FINAL的执行逻辑与直接查询存在差异导致的：

• 直接执行SELECT count(*) FROM table FINAL时，ClickHouse会在单个副本节点上完成FINAL的强制合并与去重，再返回结果，因此数值稳定。
• 而使用普通CTE时，查询优化器可能不会将FINAL下推到数据源副本，而是先从多个副本拉取未合并的原始数据到查询节点，再在查询节点执行去重。由于ReplicatedReplacingMergeTree的副本间合并操作是异步的，不同副本可能存在未清理的旧版本数据，跨副本拉取时会将这些冗余数据聚合到一起，加上ClickHouse默认的副本负载均衡策略会随机选择副本，导致每次查询拉取的数据量不一致，最终结果波动。

验证方式 #

可以通过查看查询执行计划，对比两种查询的FINAL执行位置：

-- 查看CTE查询的计划
EXPLAIN WITH unique_members AS (
  SELECT id, name, version FROM db17611525.member FINAL
)
SELECT count(*) FROM unique_members;

-- 查看直接查询的计划
EXPLAIN SELECT count(*) FROM db17611525.member FINAL;

对比后会发现，直接查询的FINAL逻辑在数据源副本节点执行，而普通CTE的FINAL可能延迟到查询节点执行。

解决办法 #

1. 使用物化CTE强制FINAL下推 #

给CTE添加MATERIALIZED关键字，强制CTE的查询逻辑在数据源副本节点执行，确保FINAL先完成去重再汇总数据：

WITH unique_members AS MATERIALIZED (
  SELECT id, name, version FROM db17611525.member FINAL
)
SELECT count(*) FROM unique_members;

2. 替换为子查询 #

子查询的形式会让优化器更倾向于将FINAL下推到数据源节点：

SELECT count(*) FROM (
  SELECT id, name, version FROM db17611525.member FINAL
) AS unique_members;

3. 固定查询的副本来源 #

通过设置查询参数，强制每次查询都从同一个副本获取数据，避免跨副本拉取未合并数据：

WITH unique_members AS (
  SELECT id, name, version FROM db17611525.member FINAL
)
SELECT count(*) FROM unique_members
SETTINGS prefer_localhost_replica = 1;

4. 手动触发合并（临时方案） #

如果副本间合并状态差异过大，可以手动触发表的合并操作（注意：此操作资源消耗高，仅适合测试或紧急场景）：

OPTIMIZE TABLE db17611525.member FINAL;

补充验证说明 #

你之前的验证查询（GROUP BY id HAVING cnt>1无结果）仅证明单个副本上FINAL去重后无重复id，但跨副本拉取时，不同副本的同id旧版本数据会被聚合，此时查询节点执行FINAL时虽然会保留最大version的行，但如果某次查询拉取了多个副本的旧版本数据，就会导致计数临时偏高。

内容的提问来源于stack exchange，提问作者Tirtharaj Sengupta