[EPIC] Leaf expression pushdown and filter pushdown: bugs, decisions and tests

@adriangb

Summary

This EPIC tracks bugs, design decisions and tests for the logical rules that move expressions and filters towards the scan:

ExtractLeafExpressions + PushDownLeafProjections (extract_leaf_expressions.rs, datafusion.optimizer.enable_leaf_expression_pushdown, default true)
PushDownFilter (push_down_filter.rs)
Their interaction with CommonSubexprEliminate and OptimizeProjections

Refreshed 2026-10-27 on main at 985bbbf. The previous body is kept in the edit history.

Goal

#	Requirement	Priority
G1	A rule never changes query results.	Must
G2	A rule never adds an evaluation of a volatile expression.	Must
G3	Cheap leaf expressions (`s['a']`, `file_row_index()`) and filters reach the scan.	Should
G4	A rule does not duplicate an expensive expression.	Should
G5	The optimizer reaches a fixed point. No two rules undo each other.	Should
G6	The data source decides what it can absorb. A third-party source or UDF can take part.	Should

Where we are

Area	State on `main`
Same-name / rename / swap wrong results (#25414, #25446, #26421)	Fixed. 21 SQL variants match `enable_leaf_expression_pushdown = false`. Differential fuzz: 0 failures in 66k seeds.
Rule precedence leaf vs filter (#14540)	Fixed by #25455 and #26085.
`KeepInPlace` expression duplicated (#25329, WHERE form)	Open. Also never converges (4-rule cycle).
`PushDownFilter` vs `CommonSubexprEliminate`	Never converges. Every pass changes the plan; `__common_expr_10` at `max_passes = 10`. No issue yet.
Same `get_field` extracted two times (#25447)	Open. Correct results, double work. Also causes #26391.
Name-based resolution	14 sites in the leaf rules, 7 in `PushDownFilter`.
Alias-prefix protocol	6 read sites of `__datafusion_extracted`. A user alias with that prefix blocks filter pushdown (plan quality only). Typed marker: #26769.
Per-site duplication guards	14 sites with different rules. #25456 replaces the optimizer ones with one policy.

Root causes

Columns are identified by name. This caused most of the wrong results. Patches fixed the known shapes; the structural fix is Resolve columns by schema index, not by name, in ExtractLeafExpressions and PushDownLeafProjections #25448.
KeepInPlace means two things. It is the default for every unclassified expression (a + 1, CAST, every UDF that does not override placement()), so no rule can use it as a "do not duplicate" signal (bert-beyondloops on CommonSubexprEliminate + leaf-expression pushdown duplicate a UDF call wrapped in get_field #25329, pepijnve on fix: one inlining policy for the rules that inline a projection column #25456).
Rules undo each other in a fixed-point loop. CSE hoists, PushDownFilter inlines, CSE hoists again with a new alias.
The function decides placement, not the source. Only 3 UDFs override placement(). feat[functions]: use placement to pushdown bit/octet/''_length #25025 was closed because a cheap function on one source is not cheap on another.

Comparison with DuckDB

Problem	DuckDB	Proposal for DataFusion
Column identity	`ColumnBinding{table_index, column_index}`. No lookup by name after binding.	#25448, then a shared binding-replacer utility.
Rule interaction	~50 passes in a fixed order, each runs one time. No plan-level fixed point.	CSE after pushdown, not in the loop with it (step 3).
CSE scope	`PROJECTION` and `AGGREGATE` only, after filter pushdown. Never touches a filter.	Same.
Filter through projection	Inlines. Stays above if a referenced definition is volatile or the filter can throw.	Same, plus a cost limit (#25456).
Expression properties	Orthogonal: `FunctionStability`, `FunctionErrors::CAN_THROW_RUNTIME_ERROR`. Cost is a separate heuristic.	Split `placement()` into: volatility, `is_fallible` (joseph-isaacs, #25450), duplication cost.
Struct field into scan	In place, in `RemoveUnusedColumns`: `ColumnIndex` with a nested path. No projection, no alias. Scan opts in with `supports_pushdown_extract`.	Long-term option. Removes the two leaf rules.
Other expressions into scan	Last pass. Scan accepts per expression (`projection_expression_pushdown`). Pushed only if every use of the column is the same expression.	#25450: source decides.

Decisions

Decision	Answer	Status
`Filter` vs pure extraction projection	`PushDownFilter` yields.	#25455 merged.
Leaf projection that cannot move	`PushDownLeafProjections` yields.	#26085 merged.
Recovery projection needed	Whenever an expression is not a pass-through column, or the names differ.	#25445 merged.
What `ExpressionPlacement` means	Only "move this towards the scan?". It is not a duplication signal.	Proposed. Answers bert-beyondloops (#25329) and pepijnve (#25456).
Inlining a definition into a consumer	One policy (`ProjectionInliner`). Volatile: never. Expensive: at most one site. Column, literal, cheap: always.	#25456, draft.
CSE and `PushDownFilter`	Do not push a filter through a CSE projection when the predicate uses the definition more than one time. Long term: CSE runs after pushdown, on projections and aggregates only.	Prototype converges in 2 passes, no worse plans (see "Experiments").
Who decides placement	The source, per expression. The function marks only what is safe to move (`!volatile && !fallible`).	#25450. joseph-isaacs is ready to build it.

Plan

Step	Work	Items	Owner
0	Reply to owed reviews and questions.	Submit pending review on #25388; answer pepijnve on #25456; reply to joseph-isaacs on #25450; agree #25457 ownership with buraksenn; review #26769; get a reviewer for #26281; answer jayzhan211 on #25458 and #25476.	@adriangb
1	Fix the wrong results.	#26267, #26619, #25268	open PRs / reporters
2	One inlining policy. Define `ExpressionPlacement` as placement only.	#25456, #25329	@adriangb, bert-beyondloops
3	Convergence.	New issue: `PushDownFilter` vs CSE. Then CSE out of the fixed-point loop.	open
4	Turn the "no new volatile evaluations" invariant on in CI.	#25458. Needs #25476 and #25561 first (see "Related").	@adriangb
5	Remove the string protocols.	#26769 (#25449), #25448, #25447, #26144	mattsu2020, buraksenn, open
6	Source-driven placement. Orthogonal expression properties.	#25450, #25036	joseph-isaacs

Steps 1 to 4 are independent of steps 5 and 6.

Open bugs

Issue	Effect	Fix
#26267	Wrong results: volatile subquery column evaluated again in the Parquet scan with `pushdown_filters = true`	Tiancheng-Xu
#26619	`push_down_filter` substitutes the wrong expression when `col` and `CAST(col)` share a schema name	Not started
#25268	Pushed conjunct dropped per file at run time with `pushdown_filters = true`	zhuqi-lucas
#25329	`KeepInPlace` UDF evaluated two times in `WHERE`; plan never converges	#25456 (partial), step 3
#25447	Same `get_field` extracted two times	buraksenn
#26391	SanityCheckPlan error, caused by #25447	#26397
#26144	Unqualified extracted column in an `Aggregate` name breaks a proto round trip	Not started
(no issue)	`PushDownFilter` vs CSE never converge	To file
(no issue)	User alias `__datafusion_extracted_1` blocks filter pushdown	#26769

Fixed since 2026-09-18: #25414, #25446, #14540, #15046, #26421, #26183.

Related: not caused by pushdown

These bugs are in expression rewrites. They reproduce with no projection and no pushdown rule. They are tracked here because the invariant in #25458 found them, and it cannot be on in CI until they are fixed.

Issue	MRE	Where the operand is copied	Fix
#25457	`SELECT count(*) FROM v WHERE random() BETWEEN 0.4 AND 0.6`	`SimplifyExpressions` (`expr_simplifier.rs`) and the physical planner (`planner.rs`)	#25476
#25477	`SELECT coalesce(nullif(floor(random()*2), 0), -1) FROM v`	`coalesce::simplify` rewrites to `CASE WHEN a IS NOT NULL THEN a ELSE b END`	#25561 (draft, conflicts)

Rule: copy an operand only when it is a column or a literal. Otherwise keep one node. DuckDB expands BETWEEN only for a column reference and keeps COALESCE as one lazy n-ary node.

Pushdown can make this shape: PushDownFilter inlines r from SELECT random() AS r into the filter, then the simplifier copies it. The copy is the bug.

Tests

Item	Status
Differential fuzz (leaf on/off, Parquet `pushdown_filters` on/off)	#25453 merged. Runs only in the merge-queue `extended_tests` job.
Mutation-testing gaps	#25451; part fixed in #25452.
End-of-run "no new volatile evaluations" invariant	#25458. Switched off. 0 findings on `main` + #25476.

Experiments (2026-10-27, local)

Branch: main + #25476 + #25456 + #26281 + #25458, invariant on.

Check	Result
Merge conflicts between the PRs	None. Each PR has small conflicts with `main` only.
`cargo test -p datafusion-optimizer`	Pass
sqllogictest	542 / 542 files
Invariant findings	0
Differential fuzz	0 failures, 66k seeds
CSE convergence prototype (below)	Converges in 2 passes; only alias numbers change in 3 slt files

Semantic conflict: #26085 keeps file_row_index() two times in the logical plan where the #25456 test expects one extraction column. The physical plan is the same.

CSE convergence prototype, on top of #25456:

 fn rewrite_projection(
+    // A CSE projection records that its definitions are worth computing one time.
+    // Keep a cheap definition there when the predicates use it more than one time,
+    // or CSE hoists it again with a new alias and the rules never converge.
+    let max_cost = if is_cse_projection(&projection) {
+        DuplicationCost::Free
+    } else {
+        DuplicationCost::Cheap
+    };
-    let pinned = inliner.pinned_with_unknown_consumers(&predicates, no_other_consumers, DuplicationCost::Cheap);
+    let pinned = inliner.pinned_with_unknown_consumers(&predicates, no_other_consumers, max_cost);

Trade-offs (need a benchmark, not a heuristic)

Filter-first vs extraction-first on a source that cannot absorb the projection.
datafusion.execution.parquet.pushdown_filters default: Enable parquet filter pushdown (filter_pushdown) by default #3463, [EPIC] Fix performance regressions when enabling parquet filter pushdown (late materialization) #20324, Parquet Pushdown Filters: Way to keep the I/O pattern the same when pushdown filters are enabled #24393.
Which functions are cheap at which source: Enable placement on more scalar_fns to allow lowering and pushdown to TableScans #25036, Let the data source veto ExpressionPlacement::MoveTowardsLeafNodes per function #25450 (feat[functions]: use placement to pushdown bit/octet/''_length #25025 closed).
Nested pruning in the Parquet row filter: Extend Parquet nested schema pruning to filter pushdown #24120 (3 questions open), Push down IS NULL / IS NOT NULL on struct columns into Parquet scan #21795.

I've assigned #25447 and #25457 to myself and plan to work on them soon. But if you want to work on either of them, or if they're not up for grabs, I can drop both.

Optimizer convergence

Two rule fights stop plans from reaching a fixed point before max_passes. PushDownFilter is one side of each fight. I checked both on main at 95bb0a0.

1. `PushDownFilter` and `PushDownLeafProjections` (#14540, #25455)

On main, push_down_filter, push_down_leaf_projections and optimize_projections change the #14540 plan in every pass. The loop stops after 3 passes only because it sees a plan that it saw before. With max_passes = 10 it also stops after 3 passes. Thus the cost is one extra pass. With #25455, the last pass changes nothing.

2. `PushDownFilter` and `CommonSubexprEliminate` never converge

This is the fight in the original title of #14540. #25455 does not fix it. I propose a new issue for it and a row in the "Open bugs" table.

Reproduction (the query is already in floor_preimage.slt):

CREATE TABLE test_data (id INT, int_val INT) AS VALUES (1, 100);
SET datafusion.optimizer.max_passes = 10;
EXPLAIN VERBOSE SELECT * FROM test_data WHERE floor(int_val) = 100;

In each pass:

common_sub_expression_eliminate moves CAST(int_val AS Float64) into a projection below the Filter, as __common_expr_N.
push_down_filter pushes the Filter through that projection and puts the cast back into the predicate.
common_sub_expression_eliminate moves it out again, as __common_expr_N+1.

The alias changes in every pass, so the loop never sees a repeated plan. It runs all 10 passes, and the final plan contains __common_expr_10. The expected plans in floor_preimage.slt and eliminate_outer_join.slt contain __common_expr_3 for the same reason.

rewrite_projection already refuses to push through a MoveTowardsLeafNodes expression, because ExtractLeafExpressions undoes that push. It has no equivalent guard for a CSE projection.

I tried the simple fix: rewrite_projection does not push through a __common_expr projection. The plans then converge, but 3 expected plans get worse. A CSE projection stays in the final plan when the simplified predicate uses the expression only once. Thus this needs a design decision. Some options:

PushDownFilter does not push through a CSE projection, and a later rule removes a CSE projection whose expression is used only once.
CSE does not extract an expression from a Filter predicate if it extracted that expression before in the same optimizer run.
CSE runs only after the other rules reach a fixed point.
In all cases, a typed marker for a CSE projection (Replace the __datafusion_extracted / __common_expr alias prefix protocol with a typed marker #25449) replaces the alias-prefix check.

fix: drop the redundant Limit above a Sort in the same PushDownLimit visit #25672: PushDownLimit removes a redundant Limit one pass late, so every ORDER BY ... LIMIT n query runs one extra pass.
Avoid rerunning logical optimizer rules on unchanged plans #22411 / Avoid rerunning no-op logical optimizer rules #22412: skip a rule that returned Transformed::no when no rule changed the plan since then. A pass in which a fight changes the plan cannot skip rules. Thus fixing the two fights above also makes this change more effective.
perf: reuse projection schema in OptimizeProjections instead of recomputing it #24281: reuse the projection schema in OptimizeProjections. optimize_projections runs again in every pass of both fights, so this makes each extra pass cheaper.

Thanks, this is super helpful!

test: close five mutation-testing gaps in leaf expression pushdown gu…

fix: keep the recovery projection when leaf pushdown would drop a com…

test: differential fuzz for leaf expression pushdown and parquet filt…

fix: decide the precedence between PushDownFilter and PushDownLeafPro…

test: close five mutation-testing gaps in leaf expression pushdown gu…

fix: keep the recovery projection when leaf pushdown would drop a com…

test: differential fuzz for leaf expression pushdown and parquet filt…

fix: decide the precedence between PushDownFilter and PushDownLeafPro…

fix: resolve pass-through columns against the input when merging an e…

fix: keep renamed columns intact in leaf projection pushdown (apache#…

fix: `PushDownLeafProjections` yields when the extraction cannot move (…

I have done some exploration with a coding agent to deal with this issue more fundamentally rather than playing whackamole: #26842. The core of this is adding ids to columns:

/// A stable identity for a column of a logical plan
pub struct ColumnId(NonZeroU64);

pub struct Column {
    pub relation: Option<TableReference>,
    pub name: String,
    pub spans: Spans,
    pub id: Option<ColumnId>, // new: the field this column refers to
}

pub struct DFSchema {
    inner: SchemaRef,
    field_qualifiers: Vec<Option<TableReference>>,
    field_ids: Vec<Option<ColumnId>>, // new: lookups use the id first
    functional_dependencies: FunctionalDependencies,
}

pub struct Alias {
    pub expr: Box<Expr>,
    pub relation: Option<TableReference>,
    pub name: String,
    pub metadata: Option<FieldMetadata>,
    pub id: ColumnId,     // new: the column this alias defines
    pub kind: AliasKind,  // new: Explicit, Implicit or Extraction
}

Every computed output of a projection, aggregate or window gets an implicit alias (not displayed), so its id is part of the expression and survives rewrites. For example, SELECT a FROM t GROUP BY a, CAST(a AS VARCHAR) has an aggregate with two outputs named t.a, the column and the cast. By name they are confused (#26619). With ids they are t.a#9 and t.a#10.

At the core of it, using names to refer to columns causes a whole raft of bugs. A column and a cast of it (t.a and CAST(t.a AS ...), which is also named t.a), two scans of the same table, two references to a CTE, or a column and an extracted leaf expression can all end up with the same name. When a rule moves or rebuilds a node and looks columns up by name, it finds the wrong one, or none. Things like __datafusion_extracted_N (plus the reserved prefix, and skipping past user aliases that use it) feel like a bit of a smell if we have to do this just to keep names unique.

This draft PR passes all tests, and fixes a plethora of issues. These are 12 open issues with one mechanism, including wrong results and bugs that have been open since 2022:

Issue	Opened	Symptom on `main`
#26692	2026-10	wrong results: decorrelation loses correlated columns when a subquery has two equal scans
#26621	2026-10	`No field named t.a`, or a panic (`GROUP BY CAST(a AS VARCHAR), a` with CSE)
#26573	2026-10	`No field named x.a` (`= ANY (subquery)` in a join over an aliased table)
#25837	2026-09	schema error in a LATERAL subquery over a derived table alias
#26622	2026-10	internal schema error for `x > ALL (subquery)` in the SELECT list
#26033	2026-10	`No field named __correlated_sq_1.y` (correlated `IN` with a cast)
#26221	2026-10	`duplicate unqualified field name` for `sum(x) AS a, sum(CAST(x ...)) AS b` (the unaliased form is still rejected)
#25447	2026-09	leaf extraction computes the same expression twice
#3353	2022-09	`approx_distinct(c9)` and `approx_distinct(CAST(c9 ...))` in one query fail
#4478	2022-12	`SELECT a, CAST(a AS INT)` fails to project
#6543	2023-06	two columns with the same alias are rejected
#13558	2024-11	`SELECT CAST(c AS TEXT) ... ORDER BY c` fails with a duplicate expression error

Most of these are each patched in their own open PR, one rule at a time (#26702, #26596, #26044, #26699, #26638, #26558, #26376, #26350). Here they all fall out of the same change. It also covers the class of #26619, which #26783 just fixed in PushDownFilter. And because names no longer need to be unique, __datafusion_extracted_N, __common_expr_N, their alias generators and the reserved prefix all go away.

In the draft, every column of a logical plan gets a ColumnId, the way Spark (ExprId), DuckDB (ColumnBinding) and Trino (symbols) do it:

The id is created where the column is defined (a scan, an alias, a union) and stored with the definition, so it survives rewrites. No pass assigns or repairs ids afterwards.
References get the id when their names are resolved, and lookups use the id first.
CSE and leaf extraction name the columns they define after their expressions and reference them by id. Extraction projections are marked by an alias kind instead of a name prefix.
A validator checks after every rule that references are consistent with the columns they read. Across the whole sqllogictest suite, no reference resolves to a different column by id than by name.

It is a draft to discuss the direction, not something to merge as is. The open points are in the PR description:

planning cost isn't measured yet;
some code still builds references by name and relies on a name fallback;
plans decoded from protobuf or Substrait, and extension nodes, don't carry ids;
EXPLAIN output changes.

The targeted fixes can land first. If this is a direction people want, the PR description has a staged plan for landing it in smaller pieces.

Navigation Menu

Repository navigation

[EPIC] Leaf expression pushdown and filter pushdown: bugs, decisions and tests #25459

Summary

Goal

Where we are

Root causes

Comparison with DuckDB

Decisions

Plan

Open bugs

Related: not caused by pushdown

Tests

Experiments (2026-10-27, local)

Trade-offs (need a benchmark, not a heuristic)

Optimizer convergence

1. `PushDownFilter` and `PushDownLeafProjections` (#14540, #25455)

2. `PushDownFilter` and `CommonSubexprEliminate` never converge

Related

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[EPIC] Leaf expression pushdown and filter pushdown: bugs, decisions and tests #25459

Description

Summary

Goal

Where we are

Root causes

Comparison with DuckDB

Decisions

Plan

Open bugs

Related: not caused by pushdown

Tests

Experiments (2026-10-27, local)

Trade-offs (need a benchmark, not a heuristic)

Activity

buraksenn commented on Sep 18, 2026

adriangb commented on Sep 24, 2026

Optimizer convergence

1. PushDownFilter and PushDownLeafProjections (#14540, #25455)

2. PushDownFilter and CommonSubexprEliminate never converge

Related

shehabgamin commented on Sep 26, 2026

cetra3 commented on Oct 28, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

1. `PushDownFilter` and `PushDownLeafProjections` (#14540, #25455)

2. `PushDownFilter` and `CommonSubexprEliminate` never converge