Skip to content

Commit 2424dae

Browse files
authored
fix: preserve computed names in derived SQL projections (apache#25521)
## Which issue does this PR close? Related to apache#21066. ## Rationale for this change An outer query can reference a computed result by its logical output name while the generated inner query leaves that result unnamed. The database cannot resolve the outer reference. For example, BigQuery output refers to `sum_40j1_46j1_id_41` without defining it in the derived table. ## What changes are included in this PR? Derived projections now emit matching output names whether or not the table has an alias. The unparser waits until the final table alias is known, so a table-column alias list still supplies the names. Explicit expression aliases and plain column references keep their existing SQL. ## What is the testing strategy for this PR? The regression exercises PostgreSQL and BigQuery output through the unparser. Existing column-alias and UNNEST snapshots remain unchanged. Roundtrip tests still compare the whole logical plan after removing only unqualified, metadata-free column self-aliases. ### Testing Done SQLite 3.51.0 executes the emitted BigQuery SQL without rewriting it. This uses local SQLite, not the Google BigQuery service. The pre-fix query below was captured from apache@4abdeef; the fixed query comes directly from the regression run. | # | Scenario | Command | Result | |---|---|---|---| | 1 | Recorded pre-fix SQL | `python3 - "$log"` below | `no such column: sum_40j1_46j1_id_41` | | 2 | Freshly emitted fixed SQL | `python3 - "$log"` below | `[(3,)]` | Run from this checkout: ```bash set -euo pipefail log=$(mktemp) CARGO_BUILD_JOBS=2 cargo test --locked --profile ci \ -p datafusion -p datafusion-sql --features datafusion-sql/recursive_protection \ --test sql_integration unparse_preserves_derived_aggregate_output_name \ -- --nocapture > "$log" 2>&1 cat "$log" python3 - "$log" <<'PY' import sqlite3 import sys from pathlib import Path before = ( "SELECT `sum_40j1_46j1_id_41` FROM " "(SELECT sum(`j1`.`j1_id`) AS `visible`, sum(`j1`.`j1_id`) FROM `j1`)" ) emitted: list[str] = [ line.removeprefix("BIGQUERY_SQL=") for line in Path(sys.argv[1]).read_text().splitlines() if line.startswith("BIGQUERY_SQL=") ] assert len(emitted) == 1, emitted print(f"SQLite version: {sqlite3.sqlite_version}") with sqlite3.connect(database=":memory:") as connection: connection.executescript( "CREATE TABLE j1 (j1_id INTEGER); INSERT INTO j1 VALUES (1), (2);" ) print(f"before SQL: {before}") try: connection.execute(before).fetchall() except sqlite3.OperationalError as error: assert str(error) == "no such column: sum_40j1_46j1_id_41", error print(f"before error: {error}") else: raise AssertionError("The recorded pre-fix query unexpectedly succeeded.") print(f"after SQL: {emitted[0]}") rows = connection.execute(emitted[0]).fetchall() print(f"after rows: {rows}") assert rows == [(3,)], rows PY ``` <details> <summary>Raw logs</summary> ```text SQLite version: 3.51.0 before SQL: SELECT `sum_40j1_46j1_id_41` FROM (SELECT sum(`j1`.`j1_id`) AS `visible`, sum(`j1`.`j1_id`) FROM `j1`) before error: no such column: sum_40j1_46j1_id_41 after SQL: 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`) after rows: [(3,)] ``` </details> ## Are there any user-facing changes? Generated derived projections expose the computed column names used by outer queries, including when the dialect does not require a table alias. SQL input validation and public APIs are unchanged. --------- Signed-off-by: 1fanwang <[email protected]>
1 parent 4fe342d commit 2424dae

3 files changed

Lines changed: 115 additions & 17 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: 20 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -593,7 +593,18 @@ 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_none_or(|alias| alias.columns.is_empty());
596598
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+
}
597608
derived_builder.lateral(lateral).alias(alias).subquery({
598609
let inner_statement = self.plan_to_sql(plan)?;
599610
if let ast::Statement::Query(inner_query) = inner_statement {
@@ -2693,18 +2704,9 @@ impl Unparser<'_> {
26932704
Expr::Alias(Alias { expr, name, .. }) => {
26942705
let inner = self.expr_to_sql(expr)?;
26952706

2696-
// Determine the alias name to use
2697-
let col_name = if let Some(rewritten_name) =
2698-
self.dialect.col_alias_overrides(name)?
2699-
{
2700-
rewritten_name.to_string()
2701-
} else {
2702-
name.to_string()
2703-
};
2704-
27052707
Ok(ast::SelectItem::ExprWithAlias {
27062708
expr: inner,
2707-
alias: self.new_ident_quoted_if_needs(col_name),
2709+
alias: self.column_alias_to_sql(name)?,
27082710
})
27092711
}
27102712
_ => {
@@ -2715,6 +2717,14 @@ impl Unparser<'_> {
27152717
}
27162718
}
27172719

2720+
fn column_alias_to_sql(&self, name: &str) -> Result<Ident> {
2721+
let name = self
2722+
.dialect
2723+
.col_alias_overrides(name)?
2724+
.unwrap_or_else(|| name.to_string());
2725+
Ok(self.new_ident_quoted_if_needs(name))
2726+
}
2727+
27182728
fn sorts_to_sql(&self, sort_exprs: &[SortExpr]) -> Result<OrderByKind> {
27192729
Ok(OrderByKind::Expressions(
27202730
sort_exprs

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

Lines changed: 56 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(())
@@ -392,6 +416,37 @@ fn roundtrip_statement_with_dialect_4() -> Result<(), DataFusionError> {
392416
Ok(())
393417
}
394418

419+
#[test]
420+
fn unparse_preserves_derived_aggregate_output_name() -> Result<()> {
421+
let schema = Schema::new(vec![Field::new("j1_id", DataType::Int32, false)]);
422+
let aggregate = sum(col("j1.j1_id"));
423+
let output = Expr::Column(Column::from_name(aggregate.schema_name().to_string()));
424+
let plan = table_scan(Some("j1"), &schema, None)?
425+
.aggregate(Vec::<Expr>::new(), vec![aggregate])?
426+
.project(vec![output.clone().alias("visible"), output.clone()])?
427+
.project(vec![output])?
428+
.build()?;
429+
430+
let sql = Unparser::new(&UnparserPostgreSqlDialect {})
431+
.plan_to_sql(&plan)?
432+
.to_string();
433+
println!("UNPARSED_SQL={sql}");
434+
assert_snapshot!(
435+
sql,
436+
@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""#
437+
);
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+
);
447+
Ok(())
448+
}
449+
395450
#[test]
396451
fn roundtrip_rebases_derived_projection_references() -> Result<(), DataFusionError> {
397452
roundtrip_statement_with_dialect_helper!(

0 commit comments

Comments
 (0)