Skip to content

Commit abec311

Browse files
authored
refactor(unparser): centralize aggregate-scope rendering in the SQL unparser (apache#23789)
## Which issue does this PR close? - Closes apache#23668. ## Rationale for this change - When printing a GROUP BY query, the unparser sometimes wraps the aggregate's input in an inner subquery (`... FROM (SELECT ...)`). - Table aliases like `cs` only exist inside that subquery. Any clause outside it (SELECT, GROUP BY, HAVING, QUALIFY, ORDER BY) must use the subquery's output columns, not the aliases, so the unparser drops the alias. - Each clause does that dropping on its own, so it is easy to miss one. - ORDER BY on an aggregate that is not in the SELECT list was missed: it printed `ORDER BY round(sum("cs"."total_revenue"), 2)`, while the SELECT list in the same query correctly printed `sum("total_revenue")`. - DataFusion reads that SQL back fine, but stricter databases reject it because `cs` is out of scope there. ## What changes are included in this PR? I moved the rule into one helper, `UnparserAggScope`, that checks once per aggregate whether the input is an inner subquery and then prepares expressions for each clause. SELECT, GROUP BY, HAVING, QUALIFY, and both ORDER BY paths now go through it, which fixes the ORDER BY case. The window-over-aggregate path is left as-is with a comment: it is only reachable from hand-built plans, since a window in SQL always sits inside a SELECT that already drops the alias. Only ORDER BY output changes; every already-correct case stays the same. ## Are these changes tested? Yes. New tests in `datafusion/core/tests/sql/unparser.rs` cover the inner-subquery shape for a window sorting by an aggregate, ORDER BY on an unselected aggregate (the fixed case), and a top-level ORDER BY (the second sort path). Existing unparser tests and the TPC-H/Clickbench roundtrips still pass. ## Are there any user-facing changes? ORDER BY over an aggregate is now printed without an out-of-scope table qualifier, so the generated SQL is valid for stricter databases. No API changes.
1 parent 9e3c71f commit abec311

2 files changed

Lines changed: 231 additions & 55 deletions

File tree

‎datafusion/core/tests/sql/unparser.rs‎

Lines changed: 135 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -468,6 +468,59 @@ QUALIFY
468468
rn = 1 AND count(DISTINCT cs.customer_id) > 0
469469
"#;
470470

471+
// https://github.com/apache/datafusion/issues/23668
472+
//
473+
// Extends the #23317 aggregate-scope fix to the window and ORDER BY clauses.
474+
// Reuses issue_23317_context() (same derived-projection shape).
475+
476+
// Window sorting by an aggregate, over a derived-projection input. Already
477+
// correct today; this locks the OVER clause against keeping the out-of-scope
478+
// `cs` qualifier across the refactor.
479+
const ISSUE_23668_WINDOW_QUERY: &str = r#"
480+
SELECT
481+
date_part('year', c.signup_date) AS signup_year,
482+
count(DISTINCT cs.customer_id) AS customers,
483+
row_number() OVER (ORDER BY count(DISTINCT cs.customer_id) DESC) AS rn
484+
FROM
485+
"warehouse"."main"."sales" cs
486+
JOIN "warehouse"."main"."customers" c USING (customer_id)
487+
GROUP BY
488+
1
489+
"#;
490+
491+
// ORDER BY an aggregate that is NOT selected, so it can't use a select alias
492+
// and is unprojected through the Aggregate. It must be normalized like the
493+
// SELECT list, not keep the out-of-scope `cs` qualifier.
494+
const ISSUE_23668_ORDER_BY_QUERY: &str = r#"
495+
SELECT
496+
date_part('year', c.signup_date) AS signup_year,
497+
count(DISTINCT cs.customer_id) AS customers
498+
FROM
499+
"warehouse"."main"."sales" cs
500+
JOIN "warehouse"."main"."customers" c USING (customer_id)
501+
GROUP BY
502+
1
503+
ORDER BY
504+
round(sum(cs.total_revenue), 2) DESC
505+
"#;
506+
507+
// ORDER BY a selected aggregate keeps a top-level Sort (the direct `Sort` arm,
508+
// vs the projection-absorbed one above). It resolves to the select alias, so
509+
// this covers routing only -- the normalization in that arm isn't reachable
510+
// from SQL (an unselected aggregate takes the absorbed path above instead).
511+
const ISSUE_23668_TOP_LEVEL_SORT_QUERY: &str = r#"
512+
SELECT
513+
date_part('year', c.signup_date) AS signup_year,
514+
count(DISTINCT cs.customer_id) AS customers
515+
FROM
516+
"warehouse"."main"."sales" cs
517+
JOIN "warehouse"."main"."customers" c USING (customer_id)
518+
GROUP BY
519+
1
520+
ORDER BY
521+
customers DESC
522+
"#;
523+
471524
fn issue_23317_context() -> Result<SessionContext> {
472525
let ctx = SessionContext::new();
473526

@@ -612,6 +665,88 @@ async fn optimized_duckdb_unparse_qualify_unqualifies_agg_input() -> Result<()>
612665
Ok(())
613666
}
614667

668+
#[tokio::test]
669+
async fn optimized_duckdb_unparse_window_over_agg_unqualifies_input() -> Result<()> {
670+
let ctx = issue_23317_context()?;
671+
assert!(ctx.remove_optimizer_rule(SingleDistinctToGroupBy::new().name()));
672+
673+
let plan = ctx
674+
.sql(ISSUE_23668_WINDOW_QUERY)
675+
.await?
676+
.into_optimized_plan()?;
677+
let dialect = DuckDBDialect::new();
678+
let unparser = Unparser::new(&dialect);
679+
let sql = unparser.plan_to_sql(&plan)?.to_string();
680+
681+
assert_issue_23317_unparsed_sql_plans(&ctx, &sql).await?;
682+
683+
assert!(
684+
sql.contains(r#"OVER (ORDER BY count(DISTINCT "customer_id")"#),
685+
"window ORDER BY aggregate should resolve against the derived projection output: {sql}",
686+
);
687+
assert!(
688+
!sql.contains(r#"count(DISTINCT "cs"."customer_id")"#),
689+
"window OVER clause must not reference out-of-scope alias cs: {sql}",
690+
);
691+
692+
Ok(())
693+
}
694+
695+
#[tokio::test]
696+
async fn optimized_duckdb_unparse_order_by_unqualifies_agg_input() -> Result<()> {
697+
let ctx = issue_23317_context()?;
698+
assert!(ctx.remove_optimizer_rule(SingleDistinctToGroupBy::new().name()));
699+
700+
let plan = ctx
701+
.sql(ISSUE_23668_ORDER_BY_QUERY)
702+
.await?
703+
.into_optimized_plan()?;
704+
let dialect = DuckDBDialect::new();
705+
let unparser = Unparser::new(&dialect);
706+
let sql = unparser.plan_to_sql(&plan)?.to_string();
707+
708+
assert_issue_23317_unparsed_sql_plans(&ctx, &sql).await?;
709+
710+
assert!(
711+
sql.contains(r#"ORDER BY round(sum("total_revenue"), 2)"#),
712+
"ORDER BY aggregate should resolve against the derived projection output: {sql}",
713+
);
714+
assert!(
715+
!sql.contains(r#"sum("cs"."total_revenue")"#),
716+
"ORDER BY must not reference out-of-scope alias cs: {sql}",
717+
);
718+
719+
Ok(())
720+
}
721+
722+
#[tokio::test]
723+
async fn optimized_duckdb_unparse_top_level_sort_over_agg_uses_select_alias() -> Result<()>
724+
{
725+
let ctx = issue_23317_context()?;
726+
assert!(ctx.remove_optimizer_rule(SingleDistinctToGroupBy::new().name()));
727+
728+
let plan = ctx
729+
.sql(ISSUE_23668_TOP_LEVEL_SORT_QUERY)
730+
.await?
731+
.into_optimized_plan()?;
732+
let dialect = DuckDBDialect::new();
733+
let unparser = Unparser::new(&dialect);
734+
let sql = unparser.plan_to_sql(&plan)?.to_string();
735+
736+
assert_issue_23317_unparsed_sql_plans(&ctx, &sql).await?;
737+
738+
assert!(
739+
sql.contains(r#"ORDER BY "customers""#),
740+
"top-level ORDER BY should resolve to the select alias: {sql}",
741+
);
742+
assert!(
743+
!sql.contains(r#""cs"."customer_id") AS "customers""#),
744+
"aggregate output must not reference out-of-scope alias cs: {sql}",
745+
);
746+
747+
Ok(())
748+
}
749+
615750
/// The outcome of running a single roundtrip test.
616751
///
617752
/// A successful test produces [`TestCaseResult::Success`].

‎datafusion/sql/src/unparser/plan.rs‎

Lines changed: 96 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,69 @@ pub fn plan_to_sql(plan: &LogicalPlan) -> Result<ast::Statement> {
101101
unparser.plan_to_sql(plan)
102102
}
103103

104+
/// Aggregate-expression scope for one rendered SELECT block.
105+
///
106+
/// When an aggregate's input is itself emitted as a derived subquery (a
107+
/// projection sits between the aggregate and its relation), the input columns
108+
/// are only reachable by that derived table's output names. Base-table
109+
/// qualifiers like `t.col` name a relation that is out of scope above the
110+
/// boundary, so emitting them produces SQL a strict engine rejects.
111+
///
112+
/// Every clause that renders an aggregate expression (SELECT / GROUP BY /
113+
/// HAVING / QUALIFY / ORDER BY) has to apply the same rule. Detect the
114+
/// boundary once here and reuse it, so the clauses can't drift apart (which is
115+
/// how earlier fixes left some clauses correct and others not).
116+
struct UnparserAggScope<'a> {
117+
agg: &'a Aggregate,
118+
/// `agg.input` renders as a derived projection, so out-of-scope qualifiers
119+
/// must be stripped from expressions in this scope.
120+
input_is_derived_projection: bool,
121+
}
122+
123+
impl<'a> UnparserAggScope<'a> {
124+
fn new(agg: &'a Aggregate) -> Self {
125+
Self {
126+
agg,
127+
input_is_derived_projection: Unparser::contains_projection_before_relation(
128+
agg.input.as_ref(),
129+
),
130+
}
131+
}
132+
133+
/// Prepare a projected column or predicate that still references the
134+
/// aggregate by its output columns: unproject it back onto the aggregate
135+
/// (and `windows`) expressions, then normalize it for this scope.
136+
fn prepare(&self, expr: Expr, windows: Option<&[&Window]>) -> Result<Expr> {
137+
self.normalize(unproject_agg_exprs(expr, self.agg, windows)?)
138+
}
139+
140+
/// Normalize an expression that is already in aggregate form (group / aggr
141+
/// exprs, or an unprojected sort expr): strip the qualifiers that fall out
142+
/// of scope once the input is a derived projection. No-op otherwise.
143+
fn normalize(&self, expr: Expr) -> Result<Expr> {
144+
if self.input_is_derived_projection {
145+
Unparser::strip_column_qualifiers_for_schema(
146+
expr,
147+
self.agg.input.schema().as_ref(),
148+
)
149+
} else {
150+
Ok(expr)
151+
}
152+
}
153+
154+
/// Unproject a sort expression onto this aggregate, then normalize it so
155+
/// ORDER BY uses the same scope as the other clauses.
156+
fn prepare_sort_expr(
157+
&self,
158+
sort_expr: SortExpr,
159+
input: &LogicalPlan,
160+
) -> Result<SortExpr> {
161+
let mut sort_expr = unproject_sort_expr(sort_expr, Some(self.agg), input)?;
162+
sort_expr.expr = self.normalize(sort_expr.expr)?;
163+
Ok(sort_expr)
164+
}
165+
}
166+
104167
impl Unparser<'_> {
105168
pub fn plan_to_sql(&self, plan: &LogicalPlan) -> Result<ast::Statement> {
106169
let mut plan = normalize_union_schema(plan)?;
@@ -312,17 +375,12 @@ impl Unparser<'_> {
312375
match (agg, window) {
313376
(Some(agg), window) => {
314377
let window_option = window.as_deref();
315-
let agg_input_has_derived_projection =
316-
Self::contains_projection_before_relation(agg.input.as_ref());
378+
let unparser_agg_scope = UnparserAggScope::new(agg);
317379
let items = exprs
318380
.into_iter()
319381
.map(|proj_expr| {
320-
let unproj = unproject_agg_exprs(proj_expr, agg, window_option)?;
321-
let unproj = Self::normalize_agg_input_columns(
322-
unproj,
323-
agg,
324-
agg_input_has_derived_projection,
325-
)?;
382+
let unproj =
383+
unparser_agg_scope.prepare(proj_expr, window_option)?;
326384
self.select_item_to_sql(&unproj)
327385
})
328386
.collect::<Result<Vec<_>>>()?;
@@ -333,12 +391,7 @@ impl Unparser<'_> {
333391
.iter()
334392
.cloned()
335393
.map(|expr| {
336-
let expr = Self::normalize_agg_input_columns(
337-
expr,
338-
agg,
339-
agg_input_has_derived_projection,
340-
)?;
341-
self.expr_to_sql(&expr)
394+
self.expr_to_sql(&unparser_agg_scope.normalize(expr)?)
342395
})
343396
.collect::<Result<Vec<_>>>()?,
344397
vec![],
@@ -379,18 +432,6 @@ impl Unparser<'_> {
379432
}
380433
}
381434

382-
fn normalize_agg_input_columns(
383-
expr: Expr,
384-
agg: &Aggregate,
385-
input_has_derived_projection: bool,
386-
) -> Result<Expr> {
387-
if input_has_derived_projection {
388-
Self::strip_column_qualifiers_for_schema(expr, agg.input.schema().as_ref())
389-
} else {
390-
Ok(expr)
391-
}
392-
}
393-
394435
fn contains_projection_before_relation(plan: &LogicalPlan) -> bool {
395436
match plan {
396437
LogicalPlan::Projection(_) => true,
@@ -429,6 +470,19 @@ impl Unparser<'_> {
429470
}
430471
}
431472

473+
/// Unproject a sort expression; normalize it when the sort is above an
474+
/// aggregate, otherwise just unproject (no scope to normalize against).
475+
fn unproject_sort_expr_in_scope(
476+
sort_expr: SortExpr,
477+
agg: Option<&Aggregate>,
478+
input: &LogicalPlan,
479+
) -> Result<SortExpr> {
480+
match agg {
481+
Some(agg) => UnparserAggScope::new(agg).prepare_sort_expr(sort_expr, input),
482+
None => unproject_sort_expr(sort_expr, None, input),
483+
}
484+
}
485+
432486
fn derive(
433487
&self,
434488
plan: &LogicalPlan,
@@ -592,6 +646,9 @@ impl Unparser<'_> {
592646
window_expr
593647
.iter()
594648
.map(|expr| {
649+
// No normalization: this agg branch is only reachable from a
650+
// hand-built plan. SQL wraps windows in a projection, which
651+
// reconstruct_select_statement handles (and normalizes).
595652
let expr = if let Some(agg) = agg {
596653
unproject_agg_exprs(expr.clone(), agg, None)?
597654
} else {
@@ -977,7 +1034,7 @@ impl Unparser<'_> {
9771034
sort.expr
9781035
.iter()
9791036
.map(|sort_expr| {
980-
unproject_sort_expr(
1037+
Self::unproject_sort_expr_in_scope(
9811038
sort_expr.clone(),
9821039
agg,
9831040
sort.input.as_ref(),
@@ -1028,23 +1085,14 @@ impl Unparser<'_> {
10281085
let mut unprojected =
10291086
unproject_window_exprs(filter.predicate.clone(), window)?;
10301087
if let Some(agg) = agg {
1031-
unprojected = unproject_agg_exprs(unprojected, agg, None)?;
1032-
unprojected = Self::normalize_agg_input_columns(
1033-
unprojected,
1034-
agg,
1035-
Self::contains_projection_before_relation(agg.input.as_ref()),
1036-
)?;
1088+
unprojected =
1089+
UnparserAggScope::new(agg).prepare(unprojected, None)?;
10371090
}
10381091
let filter_expr = self.expr_to_sql(&unprojected)?;
10391092
select.qualify(Some(filter_expr));
10401093
} else if let Some(agg) = agg {
1041-
let unprojected =
1042-
unproject_agg_exprs(filter.predicate.clone(), agg, None)?;
1043-
let unprojected = Self::normalize_agg_input_columns(
1044-
unprojected,
1045-
agg,
1046-
Self::contains_projection_before_relation(agg.input.as_ref()),
1047-
)?;
1094+
let unprojected = UnparserAggScope::new(agg)
1095+
.prepare(filter.predicate.clone(), None)?;
10481096
let filter_expr = self.expr_to_sql(&unprojected)?;
10491097
select.having(Some(filter_expr));
10501098
} else {
@@ -1130,7 +1178,11 @@ impl Unparser<'_> {
11301178
.expr
11311179
.iter()
11321180
.map(|sort_expr| {
1133-
unproject_sort_expr(sort_expr.clone(), agg, sort.input.as_ref())
1181+
Self::unproject_sort_expr_in_scope(
1182+
sort_expr.clone(),
1183+
agg,
1184+
sort.input.as_ref(),
1185+
)
11341186
})
11351187
.collect::<Result<Vec<_>>>()?;
11361188

@@ -1146,8 +1198,7 @@ impl Unparser<'_> {
11461198
LogicalPlan::Aggregate(agg) => {
11471199
// Aggregation can be already handled in the projection case
11481200
if !select.already_projected() {
1149-
let agg_input_has_derived_projection =
1150-
Self::contains_projection_before_relation(agg.input.as_ref());
1201+
let unparser_agg_scope = UnparserAggScope::new(agg);
11511202
// The query returns aggregate and group expressions. If that weren't the case,
11521203
// the aggregate would have been placed inside a projection, making the check above^ false
11531204
let exprs: Vec<_> = agg
@@ -1156,12 +1207,7 @@ impl Unparser<'_> {
11561207
.chain(agg.group_expr.iter())
11571208
.cloned()
11581209
.map(|expr| {
1159-
let expr = Self::normalize_agg_input_columns(
1160-
expr,
1161-
agg,
1162-
agg_input_has_derived_projection,
1163-
)?;
1164-
self.select_item_to_sql(&expr)
1210+
self.select_item_to_sql(&unparser_agg_scope.normalize(expr)?)
11651211
})
11661212
.collect::<Result<Vec<_>>>()?;
11671213
select.projection(exprs);
@@ -1171,12 +1217,7 @@ impl Unparser<'_> {
11711217
.iter()
11721218
.cloned()
11731219
.map(|expr| {
1174-
let expr = Self::normalize_agg_input_columns(
1175-
expr,
1176-
agg,
1177-
agg_input_has_derived_projection,
1178-
)?;
1179-
self.expr_to_sql(&expr)
1220+
self.expr_to_sql(&unparser_agg_scope.normalize(expr)?)
11801221
})
11811222
.collect::<Result<Vec<_>>>()?,
11821223
vec![],

0 commit comments

Comments
 (0)