Skip to content

Commit b56a339

Browse files
committed
fix(sql): normalize aggregate filter inputs
HAVING and QUALIFY predicates can reference aggregate inputs after unprojection. Normalize those inputs when the aggregate reads from a derived projection so unparsed SQL does not leak inner aliases. Refs #23317
1 parent d7544ad commit b56a339

2 files changed

Lines changed: 107 additions & 0 deletions

File tree

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

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -441,6 +441,33 @@ FROM
441441
cohort
442442
"#;
443443

444+
const ISSUE_23317_HAVING_QUERY: &str = r#"
445+
SELECT
446+
date_part('year', c.signup_date) AS signup_year,
447+
count(DISTINCT cs.customer_id) AS customers
448+
FROM
449+
"warehouse"."main"."sales" cs
450+
JOIN "warehouse"."main"."customers" c USING (customer_id)
451+
GROUP BY
452+
1
453+
HAVING
454+
count(DISTINCT cs.customer_id) > 0
455+
"#;
456+
457+
const ISSUE_23317_QUALIFY_QUERY: &str = r#"
458+
SELECT
459+
date_part('year', c.signup_date) AS signup_year,
460+
count(DISTINCT cs.customer_id) AS customers,
461+
row_number() OVER (ORDER BY date_part('year', c.signup_date)) AS rn
462+
FROM
463+
"warehouse"."main"."sales" cs
464+
JOIN "warehouse"."main"."customers" c USING (customer_id)
465+
GROUP BY
466+
1
467+
QUALIFY
468+
rn = 1 AND count(DISTINCT cs.customer_id) > 0
469+
"#;
470+
444471
fn issue_23317_context() -> Result<SessionContext> {
445472
let ctx = SessionContext::new();
446473

@@ -467,6 +494,14 @@ fn issue_23317_context() -> Result<SessionContext> {
467494
Ok(ctx)
468495
}
469496

497+
async fn assert_issue_23317_unparsed_sql_plans(
498+
ctx: &SessionContext,
499+
sql: &str,
500+
) -> Result<()> {
501+
ctx.sql(sql).await?.into_optimized_plan()?;
502+
Ok(())
503+
}
504+
470505
#[tokio::test]
471506
async fn optimized_duckdb_unparse_preserves_nested_aggregate_scope() -> Result<()> {
472507
let ctx = issue_23317_context()?;
@@ -475,6 +510,8 @@ async fn optimized_duckdb_unparse_preserves_nested_aggregate_scope() -> Result<(
475510
let unparser = Unparser::new(&dialect);
476511
let sql = unparser.plan_to_sql(&plan)?.to_string();
477512

513+
assert_issue_23317_unparsed_sql_plans(&ctx, &sql).await?;
514+
478515
assert!(
479516
sql.contains(concat!(
480517
r#"FROM (SELECT sum("total_revenue") AS "alias2", "#,
@@ -501,6 +538,8 @@ async fn optimized_duckdb_unparse_unqualifies_aggregate_input_projection() -> Re
501538
let unparser = Unparser::new(&dialect);
502539
let sql = unparser.plan_to_sql(&plan)?.to_string();
503540

541+
assert_issue_23317_unparsed_sql_plans(&ctx, &sql).await?;
542+
504543
assert!(
505544
sql.contains(
506545
r#"SELECT date_part('year', "signup_date") AS "signup_year", count(DISTINCT "customer_id") AS "customers", round(sum("total_revenue"), 2) AS "revenue" FROM ("#
@@ -515,6 +554,64 @@ async fn optimized_duckdb_unparse_unqualifies_aggregate_input_projection() -> Re
515554
Ok(())
516555
}
517556

557+
#[tokio::test]
558+
async fn optimized_duckdb_unparse_having_unqualifies_agg_input() -> Result<()> {
559+
let ctx = issue_23317_context()?;
560+
assert!(ctx.remove_optimizer_rule(SingleDistinctToGroupBy::new().name()));
561+
562+
let plan = ctx
563+
.sql(ISSUE_23317_HAVING_QUERY)
564+
.await?
565+
.into_optimized_plan()?;
566+
let dialect = DuckDBDialect::new();
567+
let unparser = Unparser::new(&dialect);
568+
let sql = unparser.plan_to_sql(&plan)?.to_string();
569+
570+
assert_issue_23317_unparsed_sql_plans(&ctx, &sql).await?;
571+
572+
assert!(
573+
sql.contains(r#"HAVING (count(DISTINCT "customer_id") > 0)"#),
574+
"HAVING aggregate should resolve against the derived projection output: {sql}",
575+
);
576+
assert!(
577+
!sql.contains(r#"count(DISTINCT "cs"."customer_id")"#),
578+
"HAVING must not reference out-of-scope alias cs: {sql}",
579+
);
580+
581+
Ok(())
582+
}
583+
584+
#[tokio::test]
585+
async fn optimized_duckdb_unparse_qualify_unqualifies_agg_input() -> Result<()> {
586+
let ctx = issue_23317_context()?;
587+
assert!(ctx.remove_optimizer_rule(SingleDistinctToGroupBy::new().name()));
588+
589+
let plan = ctx
590+
.sql(ISSUE_23317_QUALIFY_QUERY)
591+
.await?
592+
.into_optimized_plan()?;
593+
let dialect = DuckDBDialect::new();
594+
let unparser = Unparser::new(&dialect);
595+
let sql = unparser.plan_to_sql(&plan)?.to_string();
596+
597+
assert_issue_23317_unparsed_sql_plans(&ctx, &sql).await?;
598+
599+
assert!(
600+
sql.contains("QUALIFY"),
601+
"expected QUALIFY clause in unparsed SQL: {sql}",
602+
);
603+
assert!(
604+
sql.contains(r#"count(DISTINCT "customer_id") > 0"#),
605+
"QUALIFY aggregate should resolve against the derived projection output: {sql}",
606+
);
607+
assert!(
608+
!sql.contains(r#"count(DISTINCT "cs"."customer_id")"#),
609+
"QUALIFY must not reference out-of-scope alias cs: {sql}",
610+
);
611+
612+
Ok(())
613+
}
614+
518615
/// The outcome of running a single roundtrip test.
519616
///
520617
/// A successful test produces [`TestCaseResult::Success`].

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1029,12 +1029,22 @@ impl Unparser<'_> {
10291029
unproject_window_exprs(filter.predicate.clone(), window)?;
10301030
if let Some(agg) = agg {
10311031
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+
)?;
10321037
}
10331038
let filter_expr = self.expr_to_sql(&unprojected)?;
10341039
select.qualify(Some(filter_expr));
10351040
} else if let Some(agg) = agg {
10361041
let unprojected =
10371042
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+
)?;
10381048
let filter_expr = self.expr_to_sql(&unprojected)?;
10391049
select.having(Some(filter_expr));
10401050
} else {

0 commit comments

Comments
 (0)