@@ -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+
444471fn 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]
471506async 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`].
0 commit comments