xudong963 opened a new issue, #26349:
URL: https://github.com/apache/datafusion/issues/26349
### Is your feature request related to a problem or challenge?
An inner join whose other input is provably one row of literals can remain a
`NestedLoopJoinExec`, even though substituting those literals into the join
predicate would turn it into a filter and projection.
Besides the join overhead, this loses useful ordering information:
`NestedLoopJoinExec` declares that it preserves neither input's order. For an
input already ordered by grouping keys, this can prevent downstream aggregation
from using that ordering for early emission and state release. A
filter/projection can preserve the relevant ordering.
#### Reproducer
Executed with the official `datafusion-cli 52.3.0`:
```sql
SET datafusion.execution.target_partitions = 1;
CREATE TABLE events AS
SELECT * FROM (VALUES ('a', 10), ('a', 30), ('b', 20), ('b', NULL))
AS v(account_id, event_time);
EXPLAIN FORMAT INDENT
SELECT e.account_id, e.event_time, b.cutoff
FROM (SELECT 25 AS cutoff) b
JOIN events e ON e.event_time < b.cutoff;
```
Actual physical plan:
```text
ProjectionExec: expr=[account_id@1 as account_id, event_time@2 as
event_time, cutoff@0 as cutoff]
NestedLoopJoinExec: join_type=Inner, filter=event_time@1 < cutoff@0
ProjectionExec: expr=[25 as cutoff]
PlaceholderRowExec
DataSourceExec: partitions=1, partition_sizes=[1]
```
The equivalent query:
```sql
EXPLAIN FORMAT INDENT
SELECT account_id, event_time, 25 AS cutoff
FROM events
WHERE event_time < 25;
```
produces:
```text
ProjectionExec: expr=[account_id@0 as account_id, event_time@1 as
event_time, 25 as cutoff]
FilterExec: event_time@1 < 25
DataSourceExec: partitions=1, partition_sizes=[1]
```
Executing both queries returns the same rows: `('a', 10, 25)` and `('b', 20,
25)`. This small example demonstrates the plan rewrite opportunity; it does not
measure the ordering or memory benefit.
### Describe the solution you'd like
Rewrite an inner join with a provably single constant row into:
1. A filter on the other input, with the constant-side column references
replaced by literals.
2. A projection that restores the original output columns, including any
constant-side columns still needed.
An initial physical optimization could recognize `ProjectionExec` of
literals over `PlaceholderRowExec` on either side of `NestedLoopJoinExec`.
Please also cover constants introduced during physical optimization.
`AggregateStatistics` can replace a scalar aggregate with exactly this constant
projection/placeholder shape. A logical-only rewrite would miss that
opportunity, so the physical rewrite should run after that folding and before
distribution/ordering enforcement.
Correctness considerations:
- Prove there is exactly one row overall, including partitioning; an
estimated row count of one is insufficient.
- Preserve SQL NULL behavior in the substituted predicate, output
types/nullability, column positions, and embedded join projections.
- Start with literal expressions; do not change evaluation counts for
volatile expressions or evaluate arbitrary subplans during optimization.
- Keep the initial scope to inner joins; outer/semi/anti joins need separate
reasoning.
Suggested regression coverage includes the constant input on either side,
NULL literals, predicates that evaluate to false/NULL, retained/pruned constant
columns, and a constant row produced by `AggregateStatistics`. An ordered,
multi-partition input should also verify that valid ordering reaches downstream
aggregation.
### Describe alternatives you've considered
- Manually rewrite SQL to use a literal filter. This works for an explicitly
known literal, but does not cover a bound that becomes constant only after
statistics-based folding.
- Teach `NestedLoopJoinExec` to preserve order for a proven single-row
input. This could help ordering, but still retains the join machinery and does
not expose the literal predicate as a regular filter.
### Additional context
I also inspected upstream `main` at
`e732212cabd030d500efc35049448b3714017c7b` (2026-10-14). The specific rewrite
appears to be absent there as well; the executed reproducer above is on 52.3.0,
not a build of that commit.
Relevant upstream code:
- [AggregateStatistics creates the constant projection/placeholder
subtree](https://github.com/apache/datafusion/blob/e732212cabd030d500efc35049448b3714017c7b/datafusion/physical-optimizer/src/aggregate_statistics.rs).
- [EliminateJoin handles other join
simplifications](https://github.com/apache/datafusion/blob/e732212cabd030d500efc35049448b3714017c7b/datafusion/optimizer/src/eliminate_join.rs).
- [NestedLoopJoinExec declares neither input order
preserved](https://github.com/apache/datafusion/blob/e732212cabd030d500efc35049448b3714017c7b/datafusion/physical-plan/src/joins/nested_loop_join.rs#L453-L456).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
