Skip to content

Commit 4abdeef

Browse files
committed
fix: preserve computed names in derived SQL projections
Signed-off-by: 1fanwang <[email protected]>
1 parent 925d7f8 commit 4abdeef

2 files changed

Lines changed: 59 additions & 11 deletions

File tree

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

Lines changed: 37 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -593,10 +593,37 @@ impl Unparser<'_> {
593593
alias: Option<ast::TableAlias>,
594594
lateral: bool,
595595
) -> Result<()> {
596+
let preserve_names = matches!(plan, LogicalPlan::Projection(_))
597+
&& alias.as_ref().is_some_and(|alias| alias.columns.is_empty());
596598
let mut derived_builder = DerivedRelationBuilder::default();
597599
derived_builder.lateral(lateral).alias(alias).subquery({
598600
let inner_statement = self.plan_to_sql(plan)?;
599-
if let ast::Statement::Query(inner_query) = inner_statement {
601+
if let ast::Statement::Query(mut inner_query) = inner_statement {
602+
if preserve_names
603+
&& let SetExpr::Select(select) = inner_query.body.as_mut()
604+
&& select.projection.len() == plan.schema().fields().len()
605+
{
606+
for (item, field) in
607+
select.projection.iter_mut().zip(plan.schema().fields())
608+
{
609+
if let ast::SelectItem::UnnamedExpr(expr) = item {
610+
let alias = self.column_alias_to_sql(field.name())?;
611+
let preserves_name = match expr {
612+
ast::Expr::Identifier(name) => name.value == alias.value,
613+
ast::Expr::CompoundIdentifier(names) => names
614+
.last()
615+
.is_some_and(|name| name.value == alias.value),
616+
_ => false,
617+
};
618+
if !preserves_name {
619+
*item = ast::SelectItem::ExprWithAlias {
620+
expr: expr.clone(),
621+
alias,
622+
};
623+
}
624+
}
625+
}
626+
}
600627
inner_query
601628
} else {
602629
return internal_err!(
@@ -2689,18 +2716,9 @@ impl Unparser<'_> {
26892716
Expr::Alias(Alias { expr, name, .. }) => {
26902717
let inner = self.expr_to_sql(expr)?;
26912718

2692-
// Determine the alias name to use
2693-
let col_name = if let Some(rewritten_name) =
2694-
self.dialect.col_alias_overrides(name)?
2695-
{
2696-
rewritten_name.to_string()
2697-
} else {
2698-
name.to_string()
2699-
};
2700-
27012719
Ok(ast::SelectItem::ExprWithAlias {
27022720
expr: inner,
2703-
alias: self.new_ident_quoted_if_needs(col_name),
2721+
alias: self.column_alias_to_sql(name)?,
27042722
})
27052723
}
27062724
_ => {
@@ -2711,6 +2729,14 @@ impl Unparser<'_> {
27112729
}
27122730
}
27132731

2732+
fn column_alias_to_sql(&self, name: &str) -> Result<Ident> {
2733+
let name = self
2734+
.dialect
2735+
.col_alias_overrides(name)?
2736+
.unwrap_or_else(|| name.to_string());
2737+
Ok(self.new_ident_quoted_if_needs(name))
2738+
}
2739+
27142740
fn sorts_to_sql(&self, sort_exprs: &[SortExpr]) -> Result<OrderByKind> {
27152741
Ok(OrderByKind::Expressions(
27162742
sort_exprs

‎datafusion/sql/tests/cases/plan_to_sql.rs‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -392,6 +392,28 @@ fn roundtrip_statement_with_dialect_4() -> Result<(), DataFusionError> {
392392
Ok(())
393393
}
394394

395+
#[test]
396+
fn unparse_preserves_derived_aggregate_output_name() -> Result<()> {
397+
let schema = Schema::new(vec![Field::new("j1_id", DataType::Int32, false)]);
398+
let aggregate = sum(col("j1.j1_id"));
399+
let output = Expr::Column(Column::from_name(aggregate.schema_name().to_string()));
400+
let plan = table_scan(Some("j1"), &schema, None)?
401+
.aggregate(Vec::<Expr>::new(), vec![aggregate])?
402+
.project(vec![output.clone().alias("visible"), output.clone()])?
403+
.project(vec![output])?
404+
.build()?;
405+
406+
let sql = Unparser::new(&UnparserPostgreSqlDialect {})
407+
.plan_to_sql(&plan)?
408+
.to_string();
409+
println!("UNPARSED_SQL={sql}");
410+
assert_snapshot!(
411+
sql,
412+
@r#"SELECT "sum(j1.j1_id)" FROM (SELECT sum("j1"."j1_id") AS "visible", sum("j1"."j1_id") AS "sum(j1.j1_id)" FROM "j1") AS "derived_projection""#
413+
);
414+
Ok(())
415+
}
416+
395417
#[test]
396418
fn roundtrip_rebases_derived_projection_references() -> Result<(), DataFusionError> {
397419
roundtrip_statement_with_dialect_helper!(

0 commit comments

Comments
 (0)