Repository navigation
Common subexpression elimination masks hides aggregations in physical explain output #19684
Description
Describe the bug
When the common subexpression elimination deduplicates aggregations it can generate aliases for the common expression of the form __common_expr_<n>. In the logical plan explain output this gets output as <original expr> as __common_expr_<n>. In the physical plan explain output though only __common_expr_<n> is printed. The actual expression corresponding to this alias is no longer visible. This makes the explain output hard to interpret.
To Reproduce
Here's an example logic plan constructed using the data frame API. The problematic line is
AggregateExec: mode=Partial, gby=[idx@1 as idx], aggr=[__common_expr_1]
Logical plan
============
Projection: idx, agg, ord
Aggregate: groupBy=[[idx]], aggr=[[sum(column1) AS agg, sum(column1) AS ord]]
Projection: column1, column2, CASE WHEN column2 <= Int64(0) THEN Int64(0) WHEN column2 <= Int64(200) THEN Int64(1) WHEN column2 <= Int64(314) THEN Int64(3) ELSE Int64(4) END AS idx
Values: (Int64(1), Int64(100)), (Int64(2), Int64(200)), (Int64(3), Int64(314))
Optimized logical plan
======================
Projection: idx, __common_expr_1 AS agg, __common_expr_1 AS ord
Aggregate: groupBy=[[idx]], aggr=[[sum(column1) AS __common_expr_1]]
Projection: column1, CASE WHEN column2 <= Int64(0) THEN Int64(0) WHEN column2 <= Int64(200) THEN Int64(1) WHEN column2 <= Int64(314) THEN Int64(3) ELSE Int64(4) END AS idx
Values: (Int64(1), Int64(100)), (Int64(2), Int64(200)), (Int64(3), Int64(314))
Physical plan
=============
ProjectionExec: expr=[idx@0 as idx, __common_expr_1@1 as agg, __common_expr_1@1 as ord]
AggregateExec: mode=FinalPartitioned, gby=[idx@0 as idx], aggr=[__common_expr_1]
RepartitionExec: partitioning=Hash([idx@0], 10), input_partitions=1
AggregateExec: mode=Partial, gby=[idx@1 as idx], aggr=[__common_expr_1]
ProjectionExec: expr=[column1@0 as column1, CASE WHEN column2@1 <= 0 THEN 0 WHEN column2@1 <= 200 THEN 1 WHEN column2@1 <= 314 THEN 3 ELSE 4 END as idx]
DataSourceExec: partitions=1, partition_sizes=[1]
Expected behavior
Rather than
AggregateExec: mode=Partial, gby=[idx@1 as idx], aggr=[__common_expr_1]
the explain output should show
AggregateExec: mode=Partial, gby=[idx@1 as idx], aggr=[sum(column1@0) as __common_expr_1]
similarly to how the group by expression are printed.
Additional context
No response
Activity
sahilshingate01 commentedon Jan 8, 2026
I can fix this DataFusion bug where physical plan explain output hides the actual expressions behind
__common_expr_<n>aliases. The solution is to modify theAggregateExecdisplay formatter to show both the alias and its underlying expression (likesum(column1) as __common_expr_1), similar to how group by expressions are displayed. I can create a PR for this - it's a small change to the explain output formatting logic that will make query plans much more readable.pepijnve commentedon Jan 8, 2026
I had logged this as a distinct issue, but I think this might just be a specific case of #19685. That’s already been taken. Might be best to coordinate efforts.
sahilshingate01 commentedon Jan 8, 2026
Thanks for the pointer! I see this is likely covered by #19685 I'll coordinate with the person working on that issue to ensure this specific case with _common_expr aliases in AggregateExec explain output gets addressed. Happy to help test or contribute if needed
kosiew commentedon Sep 26, 2026
Current main contains #21739 which fixes #19685
Re-running the DataFrame example above on current main now shows
AggregateExec: ... aggr=[sum(?table?.column1) as __common_expr_1]in both the Partial and FinalPartitioned stages.
It's also covered by the EXPLAIN SELECT SUM(2+1), SUM(3) case in aggregates_simplify.slt.