Skip to content

Commit 91ca248

Browse files
committed
fix: preserve output names without derived table aliases
Signed-off-by: 1fanwang <[email protected]>
1 parent 4abdeef commit 91ca248

3 files changed

Lines changed: 84 additions & 34 deletions

File tree

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

Lines changed: 39 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -677,6 +677,7 @@ pub struct DerivedRelationBuilder {
677677
lateral: Option<bool>,
678678
subquery: Option<Box<ast::Query>>,
679679
alias: Option<ast::TableAlias>,
680+
projection_names: Vec<ast::Ident>,
680681
}
681682

682683
impl DerivedRelationBuilder {
@@ -692,18 +693,49 @@ impl DerivedRelationBuilder {
692693
self.alias = value;
693694
self
694695
}
696+
pub(super) fn projection_names(&mut self, value: Vec<ast::Ident>) -> &mut Self {
697+
self.projection_names = value;
698+
self
699+
}
695700
fn build(&self) -> Result<ast::TableFactor, BuilderError> {
701+
let mut subquery = match self.subquery {
702+
Some(ref value) => value.clone(),
703+
None => {
704+
return Err(Into::into(UninitializedFieldError::from("subquery")));
705+
}
706+
};
707+
if self
708+
.alias
709+
.as_ref()
710+
.is_none_or(|alias| alias.columns.is_empty())
711+
&& let ast::SetExpr::Select(select) = subquery.body.as_mut()
712+
&& select.projection.len() == self.projection_names.len()
713+
{
714+
for (item, alias) in select.projection.iter_mut().zip(&self.projection_names)
715+
{
716+
if let ast::SelectItem::UnnamedExpr(expr) = item {
717+
let preserves_name = match expr {
718+
ast::Expr::Identifier(name) => name.value == alias.value,
719+
ast::Expr::CompoundIdentifier(names) => {
720+
names.last().is_some_and(|name| name.value == alias.value)
721+
}
722+
_ => false,
723+
};
724+
if !preserves_name {
725+
*item = ast::SelectItem::ExprWithAlias {
726+
expr: expr.clone(),
727+
alias: alias.clone(),
728+
};
729+
}
730+
}
731+
}
732+
}
696733
Ok(ast::TableFactor::Derived {
697734
lateral: match self.lateral {
698735
Some(ref value) => *value,
699736
None => return Err(Into::into(UninitializedFieldError::from("lateral"))),
700737
},
701-
subquery: match self.subquery {
702-
Some(ref value) => value.clone(),
703-
None => {
704-
return Err(Into::into(UninitializedFieldError::from("subquery")));
705-
}
706-
},
738+
subquery,
707739
alias: self.alias.clone(),
708740
sample: None,
709741
})
@@ -713,6 +745,7 @@ impl DerivedRelationBuilder {
713745
lateral: Default::default(),
714746
subquery: Default::default(),
715747
alias: Default::default(),
748+
projection_names: Default::default(),
716749
}
717750
}
718751
}

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

Lines changed: 11 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -594,36 +594,20 @@ impl Unparser<'_> {
594594
lateral: bool,
595595
) -> Result<()> {
596596
let preserve_names = matches!(plan, LogicalPlan::Projection(_))
597-
&& alias.as_ref().is_some_and(|alias| alias.columns.is_empty());
597+
&& alias.as_ref().is_none_or(|alias| alias.columns.is_empty());
598598
let mut derived_builder = DerivedRelationBuilder::default();
599+
if preserve_names {
600+
derived_builder.projection_names(
601+
plan.schema()
602+
.fields()
603+
.iter()
604+
.map(|field| self.column_alias_to_sql(field.name()))
605+
.collect::<Result<Vec<_>>>()?,
606+
);
607+
}
599608
derived_builder.lateral(lateral).alias(alias).subquery({
600609
let inner_statement = self.plan_to_sql(plan)?;
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-
}
610+
if let ast::Statement::Query(inner_query) = inner_statement {
627611
inner_query
628612
} else {
629613
return internal_err!(

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

Lines changed: 34 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@
1717

1818
use arrow::datatypes::{DataType, Field, Schema};
1919

20+
use datafusion_common::tree_node::{Transformed, TransformedResult};
2021
use datafusion_common::{
2122
Column, DFSchema, DFSchemaRef, DataFusionError, Result, TableReference,
2223
assert_contains,
@@ -111,6 +112,25 @@ fn roundtrip_expr(table: TableReference, sql: &str) -> Result<String> {
111112
Ok(ast.to_string())
112113
}
113114

115+
fn remove_column_self_aliases(plan: LogicalPlan) -> Result<LogicalPlan> {
116+
plan.transform_up_with_subqueries(|plan| {
117+
plan.map_expressions(|expr| {
118+
if let Expr::Alias(alias) = &expr
119+
&& alias.relation.is_none()
120+
&& alias.metadata.is_none()
121+
&& let Expr::Column(column) = alias.expr.as_ref()
122+
&& column.relation.is_none()
123+
&& column.name == alias.name
124+
{
125+
Ok(Transformed::yes(*alias.expr.clone()))
126+
} else {
127+
Ok(Transformed::no(expr))
128+
}
129+
})
130+
})
131+
.data()
132+
}
133+
114134
#[test]
115135
fn roundtrip_statement() -> Result<()> {
116136
let tests: Vec<&str> = vec![
@@ -254,7 +274,11 @@ fn roundtrip_statement() -> Result<()> {
254274
.sql_statement_to_plan(roundtrip_statement.clone())
255275
.unwrap();
256276

257-
assert_eq!(plan, plan_roundtrip);
277+
// Explicit output names can add unqualified self-aliases without changing the plan's meaning.
278+
assert_eq!(
279+
remove_column_self_aliases(plan)?,
280+
remove_column_self_aliases(plan_roundtrip)?,
281+
);
258282
}
259283

260284
Ok(())
@@ -411,6 +435,15 @@ fn unparse_preserves_derived_aggregate_output_name() -> Result<()> {
411435
sql,
412436
@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""#
413437
);
438+
439+
let sql = Unparser::new(&BigQueryDialect {})
440+
.plan_to_sql(&plan)?
441+
.to_string();
442+
println!("BIGQUERY_SQL={sql}");
443+
assert_snapshot!(
444+
sql,
445+
@r#"SELECT `sum_40j1_46j1_id_41` FROM (SELECT sum(`j1`.`j1_id`) AS `visible`, sum(`j1`.`j1_id`) AS `sum_40j1_46j1_id_41` FROM `j1`)"#
446+
);
414447
Ok(())
415448
}
416449

0 commit comments

Comments
 (0)