ClickHouse / ClickHouse/ClickHouse
Filter above INTERSECT/EXCEPT is not pushed into the input branches (UNION is)
- 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
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