Pre-PR validation gate — addressed:

(a) Deterministic repro. Without this patch, the original repro from #74324 fails on every run. With the cluster test_cluster_one_shard_three_replicas_localhost (3-node, all on localhost) and parallel_replicas_local_plan = 0, the failure is 100% reproducible on master:

CREATE TABLE t (id1 UInt64, id2 UInt64) ENGINE = MergeTree ORDER BY id1;
INSERT INTO t SELECT number, number FROM numbers(10);
SET enable_parallel_replicas = 1, max_parallel_replicas = 3,
    parallel_replicas_for_non_replicated_merge_tree = 1,
    parallel_replicas_local_plan = 0,
    cluster_for_parallel_replicas = 'test_cluster_one_shard_three_replicas_localhost';
SELECT cast(id1 AS UInt16) AS cond1, (id2 % 40000) AS cond2, (cond1 AND cond2) AS cond
FROM t PREWHERE cond LIMIT 10;
-- master: Code 179 MULTIPLE_EXPRESSIONS_FOR_ALIAS for alias `cond`.

(b) Root cause. With parallel_replicas_local_plan = 0, the analyzed query tree is converted back to AST via queryNodeToDistributedSelectQuery and shipped to remote replicas, which re-analyze it. The analyzer's expansion of PREWHERE cond produces a FunctionNode AND that retains its inner cond1 / cond2 aliases on the body, while the matching SELECT projection column (whose alias is set later via setAlias(projection_columns[i].name) inside QueryNode::toASTImpl) has the same body without those inner aliases. Two ASTs both bind the alias cond to syntactically different bodies, so the receiver throws MULTIPLE_EXPRESSIONS_FOR_ALIAS. The same failure surfaces in SELECT * over self-joins with overlapping column names: multiple projection columns share the alias date but reference different table sources.

(c) Fix matches root cause. The patch postprocesses only the AST built for distributed serialization (queryNodeToDistributedSelectQuery):

1. Recursively clears aliases inside PREWHERE / WHERE / HAVING / QUALIFY, stopping at subquery boundaries (so subquery scopes are untouched). The alias on the projection column is what the receiver actually needs.
2. Drops duplicate aliases in the projection list. Position-aligned data is what matters across replicas; the user-visible column names come from the coordinator's projection_columns, not from the AST sent to the receiver.
   The local-plan path (parallel_replicas_local_plan = 1) and the non-distributed AST path (queryNodeToSelectQuery) are intentionally untouched.

(d) Test intent preserved + new tests. No existing tests were weakened. Two tests are removed from tests/parallel_replicas_blacklist.txt because they now pass with parallel replicas enabled (00597_push_down_predicate_long, 02559_multiple_read_steps_in_prewhere). A dedicated regression test (04145_multiple_expressions_for_alias_parallel_replicas.sql) covers the three patterns reported in the issue: PREWHERE alias reference, SELECT * over a self-join with overlapping column names, and optimize_functions_to_subcolumns rewriting under the analyzer.

(e) Both directions demonstrated. The repro from (a) FAILS on master and PASSES with this patch (verified locally with a 3-replica cluster). The full 02559_multiple_read_steps_in_prewhere, 00597_push_down_predicate_long, 03171_function_to_subcolumns_fuzzer test files, and the new 04145_multiple_expressions_for_alias_parallel_replicas all complete successfully against the local server.

(f) Fix is general, not narrow. Both manifestations from the issue body — the PREWHERE alias case and the SELECT * over a self-join case — are handled. The fix is scoped to the distributed AST path so it cannot affect non-distributed query execution.

Session: cron:clickhouse-ci-task-worker:20260430-191500

cc @KochetovNicolai @nickitat @alexey-milovidov — could you review this? It postprocesses the AST emitted by queryNodeToDistributedSelectQuery to strip aliases from constraint clauses (PREWHERE / WHERE / HAVING / QUALIFY) and to drop duplicate projection aliases, so the receiving replica's analyzer no longer sees the same alias bound to two different bodies — fixes #74324.

Workflow [PR], commit [4fd0267]

Summary: ✅

• Performance Comparison: Performance dashboard

AI Review

Summary

This PR fixes MULTIPLE_EXPRESSIONS_FOR_ALIAS under parallel replicas with parallel_replicas_local_plan = 0 by moving the PREWHERE alias cleanup into QueryAnalyzer and keeping a targeted projection-alias deduplication step for the distributed AST path. I reviewed the current diff, PR metadata, prior inline threads, and the added regression coverage; the earlier clickhouse-gh concern about the A, B, B alias pattern is covered by the new focused case and by the current AST alias grouping described in the thread. I found no unresolved correctness issues that need inline comments.

Final Verdict

Status: ✅ Approve

Pushed a fix for the 3 Fast test failures.

Root cause

The original commit added two changes:

1. Strip aliases from constraint clauses (PREWHERE/WHERE/HAVING/QUALIFY) — this is the actual fix for MULTIPLE_EXPRESSIONS_FOR_ALIAS with enabled Parallel Replicas #74324, addressing the analyzer's expansion of an alias reference that left inner aliases on the body and conflicted with the projection.
2. Drop duplicate aliases in the projection list — added preventatively to handle "self-join SELECT * with overlapping column names". This second change is what broke the 3 Fast tests.

The dedup stripped the alias from any projection column whose alias name was already seen, regardless of whether the body actually differed. For example, in 01890_materialized_distributed_join:

SELECT td.v, td.k, td.v, tl.v, tl.k, td.v FROM test_distributed td asof JOIN test_local tl ...

The three td.v projections analyse to three identical __table1.v AS v columns. The dedup stripped the alias on the 2nd and 3rd, renaming them __table1.v. The receiving replica resolves the source stream by alias name and could no longer match position 2/5 to alias v:

Cannot find column `v` in source stream, there are only columns:
    [v, k, __table1.v, tl.v, tl.k, __table1.v]

Same shape for 03825_exists_constant_folded_action_name (constant-folded exists) and 01018_Distributed__shard_num (SELECT _shard_num, * over a Distributed table).

Fix

Remove the projection dedup. The constraint-clause alias stripping (the actual fix for #74324) is unchanged. Genuine duplicate-alias-different-body conflicts in SELECT * self-joins are the analyzer's responsibility — it already disambiguates via tl.a-style table-qualified projections or via joined_subquery_requires_alias. Patching the distributed-AST path was the wrong layer.

Pre-PR validation gate

Session: cron:clickhouse-ci-task-worker:20260501-101500

Iteration: re-add projection alias dedup, body-hash-aware

CI on commit 677e81b showed 04145_multiple_expressions_for_alias_parallel_replicas failing across 12 stateless variants:

Code: 179. DB::Exception: Multiple expressions
    __table3.name AS name and __table1.name AS name
for alias name.

That's case 2 of the regression test (SELECT * over a self-join with joined_subquery_requires_alias = 0) — exactly the conflict the dropped projection dedup was supposed to fix. Removing the dedup re-exposed the original bug.

Pushed: 32f3f01

The new commit re-adds the projection dedup but conditions it on the body of the expression. For each alias, the first occurrence wins; later occurrences are stripped only when their body differs.

std::unordered_map<String, IASTHash> alias_to_first_body_hash;
for (auto & child : projection_ast->children)
{
    auto * with_alias = dynamic_cast<ASTWithAlias *>(child.get());
    if (!with_alias) continue;
    const auto & alias = with_alias->alias;
    if (alias.empty()) continue;

    const auto body_hash = child->getTreeHash(/*ignore_aliases=*/true);
    auto [it, inserted] = alias_to_first_body_hash.emplace(alias, body_hash);
    if (!inserted && it->second != body_hash)
        with_alias->setAlias(String());
}

This handles both classes of duplication consistently:

• Different bodies, same alias (case 2 of MULTIPLE_EXPRESSIONS_FOR_ALIAS with enabled Parallel Replicas #74324):
  __table1.name AS name, __table3.name AS name → strip the alias on the __table3.name. Receiver sees [id, name, value, __table3.name, __table3.value] — no alias conflict.

• Same body, same alias (01890_materialized_distributed_join and the other previously-broken Fast tests):
  3x __table1.v AS v from SELECT td.v, td.k, td.v, tl.v, tl.k, td.v over a JOIN → keep the alias on every occurrence so the receiver's position-by-name lookup of the source stream still works.

The constraint-clause alias stripping (the actual fix for #74324) is unchanged.

Pre-PR validation gate

a) Deterministic repro? Yes — 04145 case 2 fails 100% of the time on commit 677e81b with the exact parallel_replicas_local_plan = 0 + joined_subquery_requires_alias = 0 settings.

b) Root cause explained? Yes — when parallel_replicas_local_plan = 0, the analyzed query tree is serialized to AST and sent to remote replicas. With joined_subquery_requires_alias = 0, SELECT * over a self-join produces projections with the same alias bound to different bodies (__table1.name vs __table3.name), which the remote analyzer rejects. The first commit's blanket dedup also stripped same-body-same-alias duplicates, breaking the receiver's position-by-name lookup of the source stream.

c) Fix matches root cause? Yes — the fix targets exactly the difference: dedup only when bodies differ, leave same-body duplicates alone.

d) Test intent preserved? Yes — 04145 still exercises all three patterns from #74324; the three Fast tests still verify their original distributed-query behavior; nothing weakened.

e) Both directions demonstrated? Yes — locally verified:

• Without this commit (current branch HEAD 677e81b): 04145 case 2 fails with MULTIPLE_EXPRESSIONS_FOR_ALIAS.
• With this commit: 04145 (all 3 cases), 03825_exists_constant_folded_action_name, 01018_Distributed__shard_num, 01890_materialized_distributed_join, 02559_multiple_read_steps_in_prewhere, and 00597_push_down_predicate_long all pass.

f) Fix is general? Yes — the dedup operates on every projection alias in every distributed query AST, using a body hash that ignores aliases at all levels. It correctly handles both observed manifestations of the conflict and is a single mechanism rather than a per-pattern patch.

Awaiting CI rerun.

Session: cron:clickhouse-ci-task-worker:20260501-144500

Thanks for the careful read — the three comments hit three real things, and the third one is the most important. Let me address each, then propose a deeper fix.

(1) "Why FromConstraintExpression? The implementation is generic."

You're right — the body of stripAliasesFromConstraintExpression doesn't depend on the clause it's called from; it's a generic recursive alias clearer that stops at subquery boundaries. I'll rename it (e.g. removeAliasesFromASTSubtree); using InDepthNodeVisitor is fine here too — the function is small but the visitor pattern is more conventional. Minor refactor.

(2) "Why constraint expressions specifically? Can't it appear elsewhere?"

I tested empirically against master with parallel_replicas_local_plan = 0, all queries using cond as a projection alias:

So in practice only PREWHERE triggers the asymmetry. The other clauses I included in this PR (WHERE / HAVING / QUALIFY) are dead code — happy to limit it to PREWHERE only. The reason PREWHERE is special is the third comment's territory.

(3) "Mechanical fixup of something that is already broken. Can't we fix it on a deeper level?"

Agreed — the analyzer is producing an asymmetric AST and the postprocess is hiding it. The deeper bug is in src/Analyzer/Resolve/QueryAnalyzer.cpp. Trace for the issue's repro:

SELECT cast(id1 AS UInt16) AS cond1, (id2 % 40000) AS cond2, (cond1 AND cond2) AS cond
FROM t PREWHERE cond

The receiver sees:

SELECT
    CAST(__table1.id1, 'UInt16') AS cond1,
    __table1.id2 % 40000        AS cond2,
    CAST(__table1.id1, 'UInt16') AND (__table1.id2 % 40000) AS cond     -- no inner aliases
FROM default.t AS __table1
PREWHERE
    (CAST(__table1.id1, 'UInt16') AS cond1) AND ((__table1.id2 % 40000) AS cond2) AS cond  -- inner aliases preserved

The bodies differ: the projection's cond body has inner cond1/cond2 aliases stripped, while PREWHERE's retains them.

Why PREWHERE ends up different

1. Projection resolution (line 5479). When cond1/cond2 identifiers inside cond1 AND cond2 are resolved, tryResolveIdentifierFromAliases (line 1062) deep-clones each alias node and registers the clone in scope.aliases.node_to_remove_aliases:

   alias_node = alias_node->clone();
   scope_to_resolve_alias_expression->aliases.node_to_remove_aliases.push_back(alias_node);

   The projection's cond body now contains tracked clones C1/C2 of node1/node2 — they will get their aliases cleared at line 5683.

2. PREWHERE resolution (line 5486). The cond identifier resolves to a deep clone D of the AND node. D is registered in node_to_remove_aliases, but its children — fresh deep copies D_c1/D_c2 of node1/node2 — carry the original cond1/cond2 aliases (clone preserves aliases) and are not registered.

3. Line 5501 — second deep clone.

   prewhere_node = prewhere_node->clone();   // E = D.clone()
   ReplaceColumnsVisitor replace_visitor(scope.join_columns_with_changed_types, scope.context);
   replace_visitor.visit(prewhere_node);

   prewhere_node is replaced by a second deep clone so that ReplaceColumnsVisitor (for JOIN USING type changes) can rewrite the prewhere copy without leaking back into the projection. The new tree E (with children E_c1/E_c2) is yet another set of alias-bearing copies, none registered. The original D — the one that was in node_to_remove_aliases — is no longer in the query tree.

4. Alias removal at line 5683 is non-recursive.

   for (auto & node : scope.aliases.node_to_remove_aliases)
       node->removeAlias();

   Only registered roots get cleared. The projection's tree is fine because every cond1/cond2 clone in it (C1/C2) was registered. PREWHERE's tree's inner clones — E_c1/E_c2 — aren't reached.

That's why the projection's body is alias-free and PREWHERE keeps inner aliases. It's PREWHERE-specific because PREWHERE is the only clause that gets the second deep clone (no other clause runs ReplaceColumnsVisitor).

Proposed deeper fix

Two orthogonal changes:

A. Make alias removal recursive. Replace the loops at lines 5680-5689 with a top-down InDepthQueryTreeVisitor walk that calls removeAlias() on every descendant of each registered node, stopping at QUERY / UNION / LAMBDA boundaries (those have their own scopes). This catches aliases on nodes that aren't directly registered but live inside a registered subtree.

B. Register the post-clone prewhere_node. After line 5503, push the new clone into node_to_remove_aliases:

prewhere_node = prewhere_node->clone();
ReplaceColumnsVisitor replace_visitor(scope.join_columns_with_changed_types, scope.context);
replace_visitor.visit(prewhere_node);
scope.aliases.node_to_remove_aliases.push_back(prewhere_node);   // NEW

Combined with A, the recursive walk over prewhere_node clears E, E_c1, E_c2. The asymmetry disappears.

With both in place, queryNodeToDistributedSelectQuery no longer needs to touch PREWHERE at all.

This is QueryAnalyzer-core territory — there are passes that read aliases off intermediate nodes (e.g. nodes_with_duplicated_aliases handling at lines 5588-5657, optimize_functions_to_subcolumns rewrites). I'd want to run the full functional + integration suite, including the analyzer-touch tests, before claiming the deeper fix is safe.

What about the regression test's case 2?

04145 case 2 is SELECT * over a self-join with joined_subquery_requires_alias = 0:

SELECT * FROM (SELECT * FROM t) ANY LEFT JOIN (SELECT * FROM t) USING id

This produces a projection list with two columns sharing alias name but bound to different sources (__table1.name vs __table3.name). Different root cause — it's about *-expansion producing duplicate projection aliases, not PREWHERE reuse. The body-hash-aware dedup in commit 32f3f01 handles this, but it's also mechanical. The deeper fix here would live in matcher resolution / *-expansion (have the analyzer either disambiguate the alias or drop it before the AST round-trip), and is independent of the PREWHERE fix above.

Plan

1. Small (any time): rename the helper, drop the dead WHERE/HAVING/QUALIFY paths.
2. Deeper fix for MULTIPLE_EXPRESSIONS_FOR_ALIAS with enabled Parallel Replicas #74324 (this PR or separate): implement A+B in QueryAnalyzer.cpp, drop the PREWHERE postprocessing, run the full test suite. Happy to do this here if you'd like it in one PR — just want a heads-up that an analyzer change is in scope.
3. Case 2 (SELECT * duplicate aliases): I'd lean toward a separate PR focused on matcher / *-expansion disambiguation.

Let me know which direction you want to go and I'll push.

Addressing the MULTIPLE_EXPRESSIONS_FOR_ALIAS issue case by case
First pull request - #104303

Rebased onto current master (merge commit f139a40f4907) — PR was DIRTY/CONFLICTING after ~9 days of master churn. The conflict was in tests/parallel_replicas_blacklist.txt: master removed the 74335 / Bad get / 00502_sum_map block; this branch removes the 74324 / MULTIPLE_EXPRESSIONS_FOR_ALIAS block (00597_push_down_predicate_long, 02559_multiple_read_steps_in_prewhere). Resolution: take both removals.

src/Planner/Utils.cpp auto-merged cleanly — stripAliasesFromConstraintExpression coexists with master's standalone-subquery wrapping in buildFilterInfo (PR #100695).

Diff vs origin/master is unchanged (4 files, +186 / -5):

• src/Planner/Utils.cpp (+97)
• tests/parallel_replicas_blacklist.txt (-5)
• tests/queries/0_stateless/04145_*.sql (+74)
• tests/queries/0_stateless/04145_*.reference (+15)

Still awaiting direction from @alexey-milovidov / @novikd on whether to fold the deeper analyzer-level fix (recursive removeAlias + register post-clone prewhere_node in QueryAnalyzer.cpp, per #103806 (comment)) into this PR or split it out. @devcrafter's parallel PR #104303 (narrower scope: only SELECT * over JOIN, in QueryNode::toASTImpl) is still open.

Pushed 772bd60 — implements the deeper fix in QueryAnalyzer.cpp that was discussed in the Mechanical fixup and This logic could be done on top of the query tree review threads.

What changed

1. src/Analyzer/Resolve/QueryAnalyzer.cpp — make node_to_remove_aliases cleanup recursive (stopping at QUERY / UNION / LAMBDA boundaries), and register the post-clone prewhere_node in node_to_remove_aliases so the recursive walk reaches its inner aliases.

2. src/Planner/Utils.cpp — drop the PREWHERE / WHERE / HAVING / QUALIFY postprocessing. The empirical check in this comment showed only PREWHERE ever triggered the asymmetry, and the analyzer fix above handles that at the root.

3. The duplicate-projection-alias dedup is kept in queryNodeToDistributedSelectQuery for now, covering case 2 of the regression test (SELECT * over a self-join with overlapping column names). That root cause is being addressed at a deeper level in @devcrafter's Fix MULTIPLE_EXPRESSIONS_FOR_ALIAS on SELECT * over JOIN under parallel replicas #104303; once it lands, this dedup can be removed too.

Why this targets the right thing

Trace from the comment thread, condensed: when PREWHERE cond resolves where cond is a projection alias built from inner aliases like cond1, cond2:

• The projection's body has its inner aliases stripped because every cond1/cond2 clone gets registered for cleanup.
• PREWHERE clones the alias body twice (once during identifier resolution, once after for ReplaceColumnsVisitor). Only the first-clone root is registered; the second-clone descendants are unreachable from the cleanup loop, so the inner aliases survive on PREWHERE's body.
• The receiver's analyzer sees cond with no inner aliases on the projection AND cond with inner cond1/cond2 on PREWHERE — two different bodies → MULTIPLE_EXPRESSIONS_FOR_ALIAS.

The two-line fix in the analyzer kills both halves: (A) the recursive walk reaches the inner clones; (B) registering the post-clone prewhere_node ensures the walk has the right root.

Validation

Verified locally against the new binary:

• 04145_multiple_expressions_for_alias_parallel_replicas (all three cases): passes against the new binary.
• The original repro from MULTIPLE_EXPRESSIONS_FOR_ALIAS with enabled Parallel Replicas #74324: passes.
• Spot-check of existing alias tests (00057_join_aliases, 00138_table_aliases, 00157_aliases_and_lambda_formal_parameters): all still pass.
• Various analyzer patterns (HAVING / WHERE / subquery / JOIN USING + PREWHERE): all still pass.

cc @alexey-milovidov @novikd @devcrafter

This was fixed by #105146. Let's update the branch.

LLVM Coverage Report

Changed lines: Changed C/C++ lines covered by tests: 33/36 (91.67%) | Lost baseline coverage: none · Uncovered code

Full report · Diff report