Skip to content

Commit 31ee8ec

Browse files
committed
fix: preserve aggregate scope when unparsing
The unparser can fold a Projection into its Aggregate and then keep walking into the Aggregate input. When that input is another Aggregate or an unnamed derived Projection, the generated SQL moved references above the SELECT block that defined them. Optimizer aliases such as `group_alias_0` and base-table qualifiers such as `c.signup_date` were no longer in scope. Stop crossing those scope boundaries blindly. Render nested aggregate inputs as derived relations, and when an aggregate reads from a derived Projection, rewrite only input-schema columns to the derived output names before rendering SELECT and GROUP BY expressions. CLOSES apache#23317 Signed-off-by: Jiawei Zhao <[email protected]>
1 parent 0fcaef3 commit 31ee8ec

2 files changed

Lines changed: 228 additions & 9 deletions

File tree

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

Lines changed: 115 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,9 @@ use arrow::array::RecordBatch;
4343
use arrow::datatypes::{DataType, Field, Schema};
4444
use datafusion::common::Result;
4545
use datafusion::datasource::empty::EmptyTable;
46+
use datafusion::optimizer::{
47+
OptimizerRule, single_distinct_to_groupby::SingleDistinctToGroupBy,
48+
};
4649
use datafusion::prelude::{ParquetReadOptions, SessionContext};
4750
use datafusion_catalog::memory::MemorySchemaProvider;
4851
use datafusion_catalog::{CatalogProvider, MemoryCatalogProvider, SchemaProvider};
@@ -399,6 +402,118 @@ async fn optimized_duckdb_unparse_qualifies_nested_passthrough_column() -> Resul
399402
Ok(())
400403
}
401404

405+
// https://github.com/apache/datafusion/issues/23317
406+
//
407+
// `SingleDistinctToGroupBy` can produce two stacked Aggregates where the inner
408+
// Aggregate defines intermediate fields such as `group_alias_0`, `alias1`, and
409+
// `alias2`. The unparser must preserve that inner Aggregate as a derived table
410+
// before the outer Aggregate references those fields.
411+
//
412+
// Without `SingleDistinctToGroupBy`, the Aggregate still sits over an unnamed
413+
// derived Projection. In that SQL scope, base table aliases `cs` and `c` are no
414+
// longer visible, so aggregate expressions must refer to the derived table's
415+
// output columns unqualified.
416+
const ISSUE_23317_QUERY: &str = r#"
417+
WITH cohort AS (
418+
SELECT
419+
signup_year,
420+
sum(customers) AS customers,
421+
sum(revenue) AS revenue
422+
FROM
423+
(
424+
SELECT
425+
date_part('year', c.signup_date) AS signup_year,
426+
count(DISTINCT cs.customer_id) AS customers,
427+
round(sum(cs.total_revenue), 2) AS revenue
428+
FROM
429+
"warehouse"."main"."sales" cs
430+
JOIN "warehouse"."main"."customers" c USING (customer_id)
431+
GROUP BY
432+
1
433+
)
434+
GROUP BY
435+
signup_year
436+
)
437+
SELECT
438+
*
439+
FROM
440+
cohort
441+
"#;
442+
443+
fn issue_23317_context() -> Result<SessionContext> {
444+
let ctx = SessionContext::new();
445+
446+
let schema_provider = Arc::new(MemorySchemaProvider::new());
447+
schema_provider.register_table(
448+
"customers".to_string(),
449+
Arc::new(EmptyTable::new(Arc::new(Schema::new(vec![
450+
Field::new("customer_id", DataType::Int32, false),
451+
Field::new("signup_date", DataType::Date32, true),
452+
])))),
453+
)?;
454+
schema_provider.register_table(
455+
"sales".to_string(),
456+
Arc::new(EmptyTable::new(Arc::new(Schema::new(vec![
457+
Field::new("customer_id", DataType::Int32, false),
458+
Field::new("total_revenue", DataType::Decimal128(12, 2), true),
459+
])))),
460+
)?;
461+
462+
let catalog = Arc::new(MemoryCatalogProvider::new());
463+
catalog.register_schema("main", schema_provider)?;
464+
ctx.register_catalog("warehouse", catalog);
465+
466+
Ok(ctx)
467+
}
468+
469+
#[tokio::test]
470+
async fn optimized_duckdb_unparse_preserves_nested_aggregate_scope() -> Result<()> {
471+
let ctx = issue_23317_context()?;
472+
let plan = ctx.sql(ISSUE_23317_QUERY).await?.into_optimized_plan()?;
473+
let dialect = DuckDBDialect::new();
474+
let unparser = Unparser::new(&dialect);
475+
let sql = unparser.plan_to_sql(&plan)?.to_string();
476+
477+
assert!(
478+
sql.contains(concat!(
479+
r#"FROM (SELECT sum("total_revenue") AS "alias2", "#,
480+
r#"date_part('year', "signup_date") AS "group_alias_0", "#,
481+
r#""customer_id" AS "alias1" "#
482+
)),
483+
"inner aggregate should define the aliases before the outer aggregate uses them: {sql}",
484+
);
485+
assert!(
486+
!sql.contains(r#"date_part('year', "c"."signup_date") AS "group_alias_0""#),
487+
"inner aggregate must not reference out-of-scope alias c: {sql}",
488+
);
489+
490+
Ok(())
491+
}
492+
493+
#[tokio::test]
494+
async fn optimized_duckdb_unparse_unqualifies_aggregate_input_projection() -> Result<()> {
495+
let ctx = issue_23317_context()?;
496+
assert!(ctx.remove_optimizer_rule(SingleDistinctToGroupBy::new().name()));
497+
498+
let plan = ctx.sql(ISSUE_23317_QUERY).await?.into_optimized_plan()?;
499+
let dialect = DuckDBDialect::new();
500+
let unparser = Unparser::new(&dialect);
501+
let sql = unparser.plan_to_sql(&plan)?.to_string();
502+
503+
assert!(
504+
sql.contains(
505+
r#"SELECT date_part('year', "signup_date") AS "signup_year", count(DISTINCT "customer_id") AS "customers", round(sum("total_revenue"), 2) AS "revenue" FROM ("#
506+
),
507+
"aggregate expressions should resolve against the derived projection output: {sql}",
508+
);
509+
assert!(
510+
!sql.contains(r#"date_part('year', "c"."signup_date") AS "signup_year""#),
511+
"derived aggregate must not reference out-of-scope alias c: {sql}",
512+
);
513+
514+
Ok(())
515+
}
516+
402517
/// The outcome of running a single roundtrip test.
403518
///
404519
/// A successful test produces [`TestCaseResult::Success`].

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

Lines changed: 113 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -312,10 +312,17 @@ impl Unparser<'_> {
312312
match (agg, window) {
313313
(Some(agg), window) => {
314314
let window_option = window.as_deref();
315+
let agg_input_has_derived_projection =
316+
Self::contains_projection_before_relation(agg.input.as_ref());
315317
let items = exprs
316318
.into_iter()
317319
.map(|proj_expr| {
318320
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+
)?;
319326
self.select_item_to_sql(&unproj)
320327
})
321328
.collect::<Result<Vec<_>>>()?;
@@ -324,7 +331,15 @@ impl Unparser<'_> {
324331
select.group_by(ast::GroupByExpr::Expressions(
325332
agg.group_expr
326333
.iter()
327-
.map(|expr| self.expr_to_sql(expr))
334+
.cloned()
335+
.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)
342+
})
328343
.collect::<Result<Vec<_>>>()?,
329344
vec![],
330345
));
@@ -364,6 +379,56 @@ impl Unparser<'_> {
364379
}
365380
}
366381

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+
394+
fn contains_projection_before_relation(plan: &LogicalPlan) -> bool {
395+
match plan {
396+
LogicalPlan::Projection(_) => true,
397+
LogicalPlan::TableScan(_)
398+
| LogicalPlan::Subquery(_)
399+
| LogicalPlan::SubqueryAlias(_)
400+
| LogicalPlan::Join(_)
401+
| LogicalPlan::EmptyRelation(_)
402+
| LogicalPlan::Values(_) => false,
403+
_ => {
404+
let inputs = plan.inputs();
405+
matches!(
406+
inputs.as_slice(),
407+
[input] if Self::contains_projection_before_relation(input)
408+
)
409+
}
410+
}
411+
}
412+
413+
fn contains_aggregate_before_relation(plan: &LogicalPlan) -> bool {
414+
match plan {
415+
LogicalPlan::Aggregate(_) => true,
416+
LogicalPlan::TableScan(_)
417+
| LogicalPlan::Subquery(_)
418+
| LogicalPlan::SubqueryAlias(_)
419+
| LogicalPlan::Join(_)
420+
| LogicalPlan::EmptyRelation(_)
421+
| LogicalPlan::Values(_) => false,
422+
_ => {
423+
let inputs = plan.inputs();
424+
matches!(
425+
inputs.as_slice(),
426+
[input] if Self::contains_aggregate_before_relation(input)
427+
)
428+
}
429+
}
430+
}
431+
367432
fn derive(
368433
&self,
369434
plan: &LogicalPlan,
@@ -1071,23 +1136,49 @@ impl Unparser<'_> {
10711136
LogicalPlan::Aggregate(agg) => {
10721137
// Aggregation can be already handled in the projection case
10731138
if !select.already_projected() {
1139+
let agg_input_has_derived_projection =
1140+
Self::contains_projection_before_relation(agg.input.as_ref());
10741141
// The query returns aggregate and group expressions. If that weren't the case,
10751142
// the aggregate would have been placed inside a projection, making the check above^ false
10761143
let exprs: Vec<_> = agg
10771144
.aggr_expr
10781145
.iter()
10791146
.chain(agg.group_expr.iter())
1080-
.map(|expr| self.select_item_to_sql(expr))
1147+
.cloned()
1148+
.map(|expr| {
1149+
let expr = Self::normalize_agg_input_columns(
1150+
expr,
1151+
agg,
1152+
agg_input_has_derived_projection,
1153+
)?;
1154+
self.select_item_to_sql(&expr)
1155+
})
10811156
.collect::<Result<Vec<_>>>()?;
10821157
select.projection(exprs);
10831158

10841159
select.group_by(ast::GroupByExpr::Expressions(
10851160
agg.group_expr
10861161
.iter()
1087-
.map(|expr| self.expr_to_sql(expr))
1162+
.cloned()
1163+
.map(|expr| {
1164+
let expr = Self::normalize_agg_input_columns(
1165+
expr,
1166+
agg,
1167+
agg_input_has_derived_projection,
1168+
)?;
1169+
self.expr_to_sql(&expr)
1170+
})
10881171
.collect::<Result<Vec<_>>>()?,
10891172
vec![],
10901173
));
1174+
} else if Self::contains_aggregate_before_relation(agg.input.as_ref()) {
1175+
return self.derive_with_dialect_alias(
1176+
"derived_aggregate",
1177+
agg.input.as_ref(),
1178+
relation,
1179+
false,
1180+
vec![],
1181+
);
10911182
}
10921183

10931184
self.select_to_sql_recursively(
@@ -1992,11 +2083,10 @@ impl Unparser<'_> {
19922083
Ok(Some(relation))
19932084
}
19942085

1995-
/// Strip the table qualifier from every column in a pushdown pass-through
1996-
/// projection expression, so it resolves against the unnamed derived table
1997-
/// rendered for the inner pushdown projection rather than a deeper table
1998-
/// alias that is out of scope at this nesting level.
1999-
fn strip_pushdown_column_qualifiers(expr: Expr) -> Result<Expr> {
2086+
/// Strip the table qualifier from every column in an expression that must
2087+
/// resolve against an unnamed derived table's output columns rather than a
2088+
/// deeper table alias that is out of scope at this nesting level.
2089+
fn strip_column_qualifiers(expr: Expr) -> Result<Expr> {
20002090
expr.transform(|e| match e {
20012091
Expr::Column(mut column) => {
20022092
column.relation = None;
@@ -2007,6 +2097,20 @@ impl Unparser<'_> {
20072097
.data()
20082098
}
20092099

2100+
fn strip_column_qualifiers_for_schema(expr: Expr, schema: &DFSchema) -> Result<Expr> {
2101+
expr.transform(|e| match e {
2102+
Expr::Column(mut column)
2103+
if column.relation.is_some()
2104+
&& schema.index_of_column(&column).is_ok() =>
2105+
{
2106+
column.relation = None;
2107+
Ok(Transformed::yes(Expr::Column(column)))
2108+
}
2109+
other => Ok(Transformed::no(other)),
2110+
})
2111+
.data()
2112+
}
2113+
20102114
/// Try to unparse a table scan with pushdown operations into a new subquery plan.
20112115
/// If the table scan is without any pushdown operations, return None.
20122116
fn unparse_table_scan_pushdown(
@@ -2155,7 +2259,7 @@ impl Unparser<'_> {
21552259
.expr
21562260
.iter()
21572261
.cloned()
2158-
.map(Self::strip_pushdown_column_qualifiers)
2262+
.map(Self::strip_column_qualifiers)
21592263
.collect::<Result<Vec<_>>>()?;
21602264
return Ok(Some(LogicalPlan::Projection(Projection::try_new(
21612265
exprs,

0 commit comments

Comments
 (0)