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

[adriangb]

Summary

This EPIC tracks the open bugs, the design decisions and the test work for the two logical optimizer features that move expressions and filters towards the table scan:

• ExtractLeafExpressions and PushDownLeafProjections (datafusion.optimizer.enable_leaf_expression_pushdown, default true, in datafusion/optimizer/src/extract_leaf_expressions.rs)
• PushDownFilter (datafusion/optimizer/src/push_down_filter.rs)

The leaf rules have received ten bug fixes since they landed in February 2026 (#20117). As far as the linked issues show, all ten were reported by users or found by an ad-hoc fuzzer, not by an existing test. Two mechanisms explain most of them:

1. Columns are resolved by name string (flat_name() or the bare name) when a projection is moved through another node. A computed column with the same name as a table column is then confused with that column. This gave Wrong results: leaf expression pushdown removes a computed column that has the same name as its input column #25414 (wrong results), fix: resolve pass-through columns against the input when merging an extraction projection #25412 (planning error), push_down_leaf_projections fails for self joins in some scenarios #24241 and optimize_projections fails with "No field named ..." when join keys contain get_field (ExtractLeafExpressions) #22895.
2. A column definition is inlined into a consumer at several sites, and each site has its own guard against duplicating a volatile or expensive expression. Each guard was added after a bug report: fix: do not duplicate volatile expressions when extracting leaf expressions #24720, fix: evaluate struct-returning UDFs once across repeated field accesses #23691, perf: don't re-inline CSE'd expensive expressions in projection pushdown #23459, fix: do not push filters on volatile group keys below Aggregate #25416. CommonSubexprEliminate + leaf-expression pushdown duplicate a UDF call wrapped in get_field #25329 is the shape that none of them covers.

The report behind this EPIC, with SQL reproductions verified on main at 4e90755, is summarized in the sections below.

Strategy

Work in this order. Each step is independent of the ones after it.

1. Fix the wrong-results bugs in the default configuration first. Add every reproduction to sqllogictest with a result assertion, not only an EXPLAIN.
2. Replace per-site guards with one inlining policy, so the next duplication bug cannot appear at a site that has no guard.
3. Lock down the decisions that are ambiguous today, so plans are intentional and deterministic (section "Decisions" below). A decision that is wrong but documented is better than a plan that depends on rule order.
4. Add tests that find the next bug before a user does: a differential fuzz (leaf pushdown on vs off, Parquet filter pushdown on vs off), an optimizer invariant that forbids new evaluations of volatile and KeepInPlace expressions, and the test gaps that mutation testing found.
5. Then, and only then, the large refactor: resolve columns by schema index instead of name, and replace the alias-prefix protocol with a typed marker.

Items that depend on data or cost that the planner does not know (which function is cheap for which source, filter-first vs projection-first on a non-Parquet source, pushdown_filters by default) are listed as trade-offs. They are not bugs. They get a decision and a benchmark, not a heuristic.

Decisions

These are the ambiguous behaviours that this EPIC locks down. Each one gets a documented answer in the code, a test that fails if the answer changes, and no configuration option.

Decision	Today	Decided behaviour	Where it is enforced
Order of a Filter and a pure extraction projection that it does not reference (#14540)	Depends on rule order in the list. PushDownFilter and PushDownLeafProjections undo each other. One optimizer pass is wasted (analysis by kakiuwang-ui on the issue, who also asked which rule should yield).	PushDownFilter yields. A pure extraction projection stays below a filter that does not reference its aliases. Measured on Parquet: the opposite order (leaf rule yields) loses the struct leaf in DataSourceExec at the default pushdown_filters = false, because the physical ProjectionPushdown cannot move the projection through a FilterExec whose predicate needs a column the projection does not produce. The filter loses nothing: PushDownFilter runs before ExtractLeafExpressions, so the predicate is already recorded in TableScan::filters before any extraction projection exists. On a source that cannot absorb the projection the filter node still sits above the projection, so get_field runs before the filter there. That is the order main produces today, so it is not a regression, but it is not the better order for that source. kakiuwang-ui's measurement on the issue (filter first is better for a memory table) is correct; the decision goes the other way because of the Parquet default.	#25455 (docs in both rules; max_passes pinned in slt so a change in the order fails a test)
Inlining a column definition into a consumer	Five guards at five sites, each with its own rule.	Never inline a volatile definition. Never inline a KeepInPlace definition into more than one evaluation site. Column and MoveTowardsLeafNodes definitions may always be inlined. A literal is classed cheap, so OptimizeProjections keeps its existing rule for it (#8296).	#25456 (crate-private ProjectionInliner, four leaf-rule sites plus PushDownFilter::rewrite_projection and the OptimizeProjections merge routed through it; would_duplicate_volatile and merge_would_duplicate_kept_expr deleted)
When PushDownLeafProjections needs a recovery projection	Compares the set of unqualified field names, so a computed column with the same name as an input column is dropped.	A recovery projection is required whenever a recovery expression is not a pass-through column, or when the set of unqualified output names differs. The check does not compare qualifiers or types. A type change is caught because it implies a computed value.	#25445
Pushing a predicate through a projection that computes a KeepInPlace expression referenced once	Pushed. The expression is evaluated in the filter and again in the projection.	Keep pushing it. The predicate can reach the scan (row-group pruning, partial_filters), which the planner cannot value without a cost model. pepijnve raised this cost himself on his draft #25388: its snapshots show CAST(ts ...) range filters stop reaching the scan. The draft also widens Expr::placement to operators and casts, which is independent of this decision and worth keeping.	Documented in the inlining policy; test in push_down_filter.rs.
Which functions are MoveTowardsLeafNodes	A global constant per UDF.	Unchanged for now. A source-level veto is a follow-up (see design issues). array_length(arr, dim) with a non-literal dim stays KeepInPlace.	Not implemented yet. quwin found the array_length gap in the review of #25025; that PR does not carry the change.

Open bugs

Issue	Effect	Fix
#25414	Wrong results, default config, five SQL shapes	#25445
#25412	Planning error, UNION ALL with an empty branch over a CTE	PR open
#25329	KeepInPlace UDF evaluated two times in a WHERE	#25456 (crate-private ProjectionInliner, four leaf-rule sites plus PushDownFilter::rewrite_projection and the OptimizeProjections merge routed through it; would_duplicate_volatile and merge_would_duplicate_kept_expr deleted) (narrow rule). The issue is assigned to bert-beyondloops, whose #25330 was the first fix. pepijnve's draft #25388 also closes it with a broader rule and has an open design question that needs an answer. The guard that #25456 deletes was written by tohuya6 in #23691 and reviewed by kosiew; it is subsumed, not wrong, and its two regression tests stay.
#14540	Two rules undo each other, one wasted pass	#25455 (docs in both rules; max_passes pinned in slt so a change in the order fails a test)
#15046 (helgikrs)	Subquery outer references invisible to column_refs, plan fails to execute past an extension node. A PushDownFilter extension-node bug, related but not a leaf-pushdown bug.	#25294 by kakiuwang-ui fixes the consumer and deliberately does not widen column_refs, which the issue title asks for; the author asked which of the two a maintainer prefers.
#25268	A conjunct marked pushed at plan time is dropped per file at run time with pushdown_filters = true	The reporter, zhuqi-lucas, plans a fix PR. His proposal 1 (make the silent debug! fallback an error or a post-scan filter) is the direction this EPIC prefers.
#25446	Planning error when a sub-query projection renames or swaps columns and a struct field is read above	#25478. Not fixed by #25412 (verified on its head). The fix also carries the needs_recovery hunk of #25445, byte-identical, because without it the shape returns swapped columns instead of an error.
#25447	The same get_field is extracted two times into one projection	Not started.
#25457	Wrong results: BETWEEN on a volatile operand evaluates it two times, in the logical expansion and again in the physical planner. Found by the invariant in #25458.	#25476 (BetweenExpr evaluates the operand one time; non-volatile BETWEEN keeps the two-comparison form so pruning and interval analysis are unchanged)
#25477	Wrong results: COALESCE on a volatile operand evaluates it two times; the query fails with an Arrow non-nullable error when the last argument is a literal. Same mechanism as #25457 through the coalesce to CASE rewrite. Found while fixing #25457.	Not started.

Fixed recently, listed so the pattern is visible: #25415 (#25416), #24678 (#24720), #23655 (#23691), #24241, #22955, #22895, #22615, #20430.

Tests

Item	Status
Differential fuzz: leaf pushdown on vs off, Parquet pushdown_filters on vs off	#25453 (reproduces #25414 in 526 of 3000 seeds, 50 of them silent wrong results, and the #25412 error in 404 of 3000; both shapes gated by a const until the fixes land). Note: fuzz_cases only runs in the merge-queue extended_tests job, not in PR CI.
Optimizer invariant: no rule may add an evaluation site of a volatile or KeepInPlace expression	#25458 (evaluation_sites.rs, a per-rule check, +12% to +15% CPU on the sqllogictest run when on, shipped off behind two const switches). An independent review found one reported finding that is not a duplication in the final plan (abs(c1) BETWEEN ...: simplify_expressions duplicates, CSE re-hoists in the same pass) and a key weakness (sites are keyed by printed text, so a rule that duplicates and re-qualifies a column escapes). Decision: the invariant is checked once, at the end of the optimizer run, against the input plan. Rules may cooperate; intermediate plans are not checked. Only the volatile half is an invariant (same results); the KeepInPlace count is a cost and stays a diagnostic. Sites are keyed structurally, not by printed text. Reworked on the PR (2026-09-18): the end-of-run check reports exactly one query on main, random() BETWEEN ... (#25457, fixed by #25476), and its overhead is not measurable (full sqllogictest wall clock inside noise, against +12% CPU for the per-rule version). file_row_index() passes end-to-end on main: CSE hoists it and OptimizeProjections puts it back, so the final plan has the input plan's count. The KeepInPlace count is deleted, not kept as a diagnostic. With the switches on it reports 14 queries in 7 files, including #25329 and #25457.
Test gaps found by mutation testing of the two files	#25451 (220 mutants, 139 caught, 25 missed, 9 real gaps); tests for 3 functions in #25452
Result assertions (not only EXPLAIN) for every reproduction in this EPIC	Part of each fix PR

Design issues (larger refactors)

Item	Issue
Resolve columns by schema index, not by name, in the leaf rules	#25448
Replace the __datafusion_extracted / __common_expr alias-prefix protocol with a typed marker	#25449 (see also #25330 by bert-beyondloops, a sixth prefix sniff)
Let the data source veto MoveTowardsLeafNodes per function	#25450

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

• Filter-first vs extraction-first on sources that cannot absorb the projection (see Decisions).
• 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 scalar functions are cheap enough to move to the scan: Enable placement on more scalar_fns to allow lowering and pushdown to TableScans #25036, feat[functions]: use placement to pushdown bit/octet/''_length #25025.
• Nested pruning and IS NULL on whole structs in the Parquet row filter: Extend Parquet nested schema pruning to filter pushdown #24120 (blinding-pixels took it, reports the headline example no longer reproduces after fix: apply struct field filters when the file schema needs adaptation #24125, and has three open questions), Push down IS NULL / IS NOT NULL on struct columns into Parquet scan #21795.

Findings from building the integration branch

A local branch leaf-pushdown-integration holds all of the PRs above merged on top of main (#25412, #25445, #25456, #25455, #25453, #25452, #25458). Facts that matter for review order:

• fix: one inlining policy for the rules that inline a projection column #25456 deletes merge_would_duplicate_kept_expr, so the unit test that test: close five mutation-testing gaps in leaf expression pushdown guards #25452 adds for that function must be dropped when both land. The other two tests in that PR apply unchanged.
• fix: decide the precedence between PushDownFilter and PushDownLeafProjections #25455 changes is_pure_extraction_projection to take &[Expr] and shares it with PushDownFilter. fix: one inlining policy for the rules that inline a projection column #25456 changes the signature of try_push_input. The two merge with a small manual resolution in push_extraction_pairs.
• With fix: one inlining policy for the rules that inline a projection column #25456 merged, one snapshot in fix: resolve pass-through columns against the input when merging an extraction projection #25412 changes: the extracted expression is spelled leaf_udf(test.a, ...) instead of leaf_udf(a, ...). That is the qualified spelling the input schema holds and it removes the bare-vs-qualified gap that Leaf extraction computes the same expression two times in one projection #25447 describes for that path.
• Correction: an earlier version of this EPIC claimed that [profile.ci.package."*"] turns debug assertions off inside datafusion-optimizer. That is wrong. The override applies to non-member dependencies only, and debug_assertions is on for workspace members under --profile ci (verified with cargo build -v and a cfg!(debug_assertions) probe). The existing check_invariants call does run in the sqllogictest suite. No issue is needed.
• datafusion/core/tests/fuzz_cases runs only in the merge-queue extended_tests job. A fuzz test there does not run on pull requests.

Integration branch results (2026-09-17)

Branch leaf-pushdown-integration = main at 3a647e4 plus the seven PRs above, with the fuzz switches for #25414 and #25412 turned on and the swap shape (#25446, no fix yet) kept off.

Check	Result
cargo fmt --all -- --check	clean
cargo clippy --profile ci -p datafusion-optimizer --all-targets -- -D warnings	clean
cargo clippy --profile ci -p datafusion --tests --features avro,extended_tests,parquet_encryption -- -D warnings	clean
cargo test --profile ci -p datafusion-optimizer	905 + 26 + 5 passed, 0 failed
cargo test --profile ci -p datafusion-sqllogictest --test sqllogictests	520 of 520 files, 0 failures
Differential fuzz, short (250 memory, 125 Parquet, 50 schema-evolution seeds)	0 failures, 0 skips
Differential fuzz, extended (5000 memory + 1000 Parquet seeds)	0 failures, 0 skips
Workspace --lib --tests --bins with the CI feature set (68 suites)	1786 passed, 0 failed after the swap shape was gated
Full sqllogictest with the invariant switches on	7 files flagged, listed below. Not every row is a duplication in the final plan, see the invariant row above.

With every fuzz shape on, the only remaining failures are the swap shape with the ambiguity error (24 of 250 seeds, 555 of 5000), which is #25446.

Round 2 (2026-09-18)

The branch now holds nine PRs: the seven above plus #25476 and #25478, with the reworked #25458 and the updated #25456. The end-of-run volatile invariant is enforced with no exemptions, and every differential shape is enabled, including the swap shape.

Check	Result
cargo fmt, clippy (optimizer, core tests)	clean
optimizer, physical-expr, proto crate tests (10 suites)	2999 passed, 0 failed
full sqllogictest, invariant enforced	520 of 520 files, 0 failures
differential test, short (all shapes)	0 failures
differential test, extended, 6000 seeds (all shapes)	0 failures
workspace --lib --tests --bins, CI feature set (68 suites)	11951 passed, 0 failed

The table below is from round 1 and is kept for the record; the per-rule findings it lists are no longer checked, by decision.

Duplications the invariant still reports on the integration branch (the arrow_field case of #25329 no longer appears, so #25456 closes it):

Rule	File	Expression duplicated
simplify_expressions	expr.slt, select.slt, array/array_has.slt	BETWEEN and COALESCE expansion: random() (#25457), abs(c1), array_has(...)
push_down_filter	expr.slt (4 queries)	struct(t1.time, t1.load1, t1.load2, t1.host)
extract_leaf_expressions	order.slt (5 queries)	named_struct(...) referenced from an ORDER BY field access
push_down_leaf_projections	struct.slt (2 queries)	named_struct(...) referenced from a field access above
replace_distinct_aggregate	distinct_on.slt (2 queries)	ascii(c1), chr(...), upper(c1)
optimize_projections	file_row_index.slt	file_row_index() (logical plan only; the physical planner collapses it)

The named_struct and struct rows are the next work item for the inlining policy: the definition is KeepInPlace by the placement API, and the rules still evaluate it at two sites. Whether named_struct should be classed as cheap instead is a placement decision (#25450), and until it is made the rules must not duplicate it.

Independent review (2026-09-18)

An independent review of this EPIC and its PRs found the errors corrected above and left these asks open:

• feat: add a "no new evaluations" optimizer invariant, switched off #25458: rework as described in the tests table (end-of-run only, no per-rule mode); do not merge in its current form. The two volatile findings that survive an end-of-run check get fixes, not exemptions: BETWEEN evaluates its operand two times, so a volatile operand returns wrong rows #25457, and file_row_index() re-inlined by OptimizeProjections, which on the integration branch is a classification-order bug in fix: one inlining policy for the rules that inline a projection column #25456 (DuplicationCost::of checked placement before volatility; fixed on the PR on 2026-09-18, with an EXPLAIN in file_row_index.slt showing the CSE column kept; the physical plan is identical either way).
• test: differential fuzz for leaf expression pushdown and parquet filter pushdown #25453: the generic generator only emits pass-through spellings (c, q.c, q.c AS c, c AS c), so renames and computed columns come only from one hardcoded arm. Add rename and computed arms, assert non-empty results, and move the short run out from behind extended_tests so it runs on pull requests. It is a fixed-seed differential regression suite, not a fuzzer that accumulates coverage.
• fix: one inlining policy for the rules that inline a projection column #25456: credit tohuya6 and kosiew for the guard it subsumes, state the new behaviour for definitions that contain a subquery (they are now never pushed through), rename pinned_for_known_consumers, and argue rather than assert that OptimizeProjections plans do not change.
• fix: decide the precedence between PushDownFilter and PushDownLeafProjections #25455: state the residual risk (a filter created after PushDownFilter ran in a pass stays above a pure extraction projection until the next pass) and add a test with a TableProvider that answers Exact.
• Replies owed on the issues: kakiuwang-ui on push_down_filter and common_sub_expression_eliminate fight between them #14540 and pepijnve on Avoid duplicating keep-in-place expressions #25388.

What the review could not break: 15 differential queries over renames, swaps, same-name joins, unions, nested subqueries and correlated subqueries are identical with leaf pushdown on and off; 14 plan shapes probing for a lost TableScan::filters after #25455 all reached DataSourceExec; the three shapes #25456 promises to keep pushing still reach the scan.

Not filed yet

• EXPLAIN prints a nested alias inside a CAST (CAST(character_length(x) AS length(x) AS Int64)). Pre-existing, display only, seen while working on fix: decide the precedence between PushDownFilter and PushDownLeafProjections #25455.
• optimize_projections re-inlines a CSE column for file_row_index() in the logical plan; the physical planner collapses it again, so there is no wrong result. Seen with the invariant in feat: add a "no new evaluations" optimizer invariant, switched off #25458.

[buraksenn]

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.