[I] SQL Unparser loses subquery structure when SubqueryAlias directly wraps an Aggregate (PR to follow) [datafusion]

["yonatan-sevenai (via GitHub)" <gi...@apache.org>]

yonatan-sevenai opened a new issue, #21098:
URL: https://github.com/apache/datafusion/issues/21098

   ### Describe the bug
   
   When the SQL unparser encounters a SubqueryAlias node whose direct child is an Aggregate (without an intermediate Projection), it flattens the subquery into a simple table alias, losing the aggregate entirely.
   
   # Root cause
   
   The SubqueryAlias handler in select_to_sql_recursively (datafusion/sql/src/unparser/plan.rs) calls
   subquery_alias_inner_query_and_columns (which only unwraps Projection children) and unparse_table_scan_pushdown (which only
   handles TableScan/SubqueryAlias/Projection). When both return nothing useful for an Aggregate child, the code recurses directly
   into the Aggregate, merging its GROUP BY into the outer SELECT instead of emitting a derived subquery.
   
   
   # Additional context
   
   This also affects other plan types that build their own SELECT clauses (Window, Sort, Limit, Union) when directly wrapped by
   SubqueryAlias.
   
   
   ### To Reproduce
   
   Manually construct a logical plan with a join where the right side is SubqueryAlias > Aggregate:
   
   ```sql
   SELECT j1.j1_string FROM j1 JOIN (SELECT max(j2_id) AS max_id FROM j2) AS b ON j1.j1_id = b.max_id
   ```
   
   This bug doesn't manifest when the SQL is parsed from a string, as the parser inserts a Projection between the SubqueryAlias and Aggregate, so the roundtrip works. The bug only manifests with manually constructed plans where SubqueryAlias directly wraps Aggregate.
   
   
   ```rust
   let right_scan = table_scan(Some("j2"), &j2_schema, None)?.build()?;
   let right_agg = LogicalPlanBuilder::from(right_scan)
       .aggregate(vec![] as Vec<Expr>, vec![max(col("j2.j2_id")).alias("max_id")])?
       .build()?;
   let right_subquery = subquery_alias(right_agg, "b")?;
   
   let left_scan = table_scan(Some("j1"), &j1_schema, None)?.build()?;
   let plan = LogicalPlanBuilder::from(left_scan)
       .join(right_subquery, JoinType::Inner,
           (vec![Column::from_qualified_name("j1.j1_id")],
            vec![Column::from_qualified_name("b.max_id")]),
           None)?
       .project(vec![col("j1.j1_string")])?
       .build()?;
   
   let sql = Unparser::default().plan_to_sql(&plan)?.to_string();
   ```
   ## Current behavior
   
   ```sql
   SELECT j1.j1_string FROM j1 INNER JOIN j2 AS b ON j1.j1_id = b.max_id
   ``` 
   The aggregate subquery is completely dropped — (SELECT max(j2_id) AS max_id FROM j2) AS b becomes just j2 AS b.
   
   ### Expected behavior
   
   ```sql
   SELECT j1.j1_string FROM j1 INNER JOIN (SELECT max(j2.j2_id) AS max_id FROM j2) AS b ON j1.j1_id = b.max_id
   ```
   
   ### Additional context
   
   _No response_


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] SQL Unparser loses subquery structure when SubqueryAlias directly wraps an Aggregate (PR to follow) [datafusion]

["kosiew (via GitHub)" <gi...@apache.org>]

kosiew closed issue #21098: SQL Unparser loses subquery structure when SubqueryAlias directly wraps an Aggregate (PR to follow)
URL: https://github.com/apache/datafusion/issues/21098


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org