ClickHouse / ClickHouse/ClickHouse

Filter above INTERSECT/EXCEPT is not pushed into the input branches (UNION is)

Open
#110,113 2 comments 0 reactions 0 assignees View on GitHub
comp-query-optimizer fuzz performance
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Describe the bug

A filter above a UNION is pushed into every branch, so each input prunes with its own primary key. The same filter above INTERSECT or EXCEPT — in both ALL and DISTINCT variants — stays above the IntersectOrExcept step, so neither branch sees it and both inputs are read in full.

Pushing a deterministic predicate into all branches of a set operation is equivalence-preserving. Set operations match entire rows, and the predicate is a function of the row value:

INTERSECT ALL: output multiplicity per value is min(left(x), right(x)); values failing the predicate drop to 0 on both sides, exactly matching the outer filter, and passing values keep their multiplicities.
EXCEPT ALL: output is max(0, left(x) − right(x)); failing values leave the left side at 0, and filtering the right side only removes rows that could only have cancelled already-removed left rows.
The DISTINCT variants follow by the same argument on sets.
Unlike the related window/LIMIT BY cases, there is no key-subset restriction — the entire deterministic filter can be pushed, exactly as for UNION ALL.

EXCEPT ALL, INTERSECT DISTINCT, and EXCEPT DISTINCT behave identically: same results for both filter placements, no branch pruning for the outer placement.

For contrast, the same outer filter over UNION ALL/DISTINCT of the same two tables is pushed into both branches and prunes both primary keys.

### Expected behavior

The filter is cloned into every branch of INTERSECT/EXCEPT (both ALL and DISTINCT), as already happens for UNION ALL/DISTINCT, letting each input prune with its primary key, partition key, and skip indexes.

### Additional context

src/Processors/QueryPlan/Optimizations/filterPushDown.cpp has a UnionStep case that swaps the set operation below the filter and clones the filter into every branch, but no IntersectOrExceptStep case, so the pass stops there. The two steps share the properties the Union transformation relies on — the header is unchanged and branches are positionally aligned — so the same transformation applies: adopt the filter's output header for the branch inputs, clone the filter expression into each branch, remove the original. The pass's existing global guard already rejects stateful/non-deterministic filters.

### How to reproduce

Run Fiddle: https://fiddle.clickhouse.com/4d1403c5-2a56-41dd-9401-4a84f1c52b24 It only contains the ALL case. The fix PR should also test the DISTINCT case.

### Error message and/or stacktrace

_No response_

Contributor guide

Open the contributing guide

Research direction

Start in src/Processors/QueryPlan/Optimizations/filterPushDown.cpp by reading the existing UnionStep handling, then reproduce the ALL case with the linked Fiddle. Add coverage for INTERSECT and EXCEPT in both ALL and DISTINCT variants, and verify that deterministic outer filters reach every branch and enable input pruning without changing results.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
70/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.