Skip to content

Commit 6edc624

Browse files
committed
fix: plan ORDER BY on a source column hidden by a select alias
- Closes apache#26462. `SELECT lower(e.email) AS email FROM employees e ORDER BY e.email` failed to plan: Schema error: Schema contains qualified field name e.email and unqualified field name email which would be ambiguous The sort key is not in the select list, so the planner carries `e.email` into the projection to sort on it. That projection then holds the qualified field `e.email` and the unqualified field `email` in one schema, which `DFSchema::check_names` rejects, even though the carried column is internal and is projected away above the sort. PostgreSQL plans the same query and sorts on the input column. `LogicalPlanBuilder::sort` (`datafusion/expr/src/logical_plan/builder.rs`) now carries a missing sort column under an alias when the projection it is added to already outputs an unqualified field with the same name. The alias is the column's flat name (`e.email`), so the carried column cannot collide with the projection's other fields, and the sorts are rebound to it. The projection above the sort still strips the extra column, so the output schema of the query is unchanged. `datafusion/sqllogictest/test_files/order.slt` gets the query from the issue and its `ORDER BY email` counterpart, which pins the two resolutions apart: `ORDER BY e.email` sorts on the input column (`b@x`, `a@y`), and `ORDER BY email` sorts on the output column (`a@y`, `b@x`), as in PostgreSQL. A unit test in `datafusion/expr/src/logical_plan/builder.rs` covers the same path through `LogicalPlanBuilder` and checks the resulting plan. Validation, on 61c3472: - `cargo test -p datafusion-expr` passed: 285 unit tests, 63 documentation tests. - `cargo test -p datafusion-sql` passed: 95, 604 and 12 tests. - `cargo test -p datafusion-optimizer` passed: 941, 29 and 5 tests. - `cargo test -p datafusion --lib` passed: 468 tests. - `cargo test --profile ci -p datafusion-sqllogictest --test sqllogictests` passed: 534 of 534 files. - `cargo clippy -p datafusion-expr --all-targets --all-features -- -D warnings` and `cargo fmt --all -- --check` passed. The `order.slt` case was also run against the unpatched code, where it fails with the schema error quoted above. None beyond the query now planning. The result and the schema of the query are the ones PostgreSQL returns; plans that already planned are unchanged.
1 parent cd571e5 commit 6edc624

2 files changed

Lines changed: 92 additions & 2 deletions

File tree

‎datafusion/expr/src/logical_plan/builder.rs‎

Lines changed: 70 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ use crate::expr::{Alias, PlannedReplaceSelectItem, Sort as SortExpr};
2828
use crate::expr_rewriter::{
2929
ColumnNormalizer, coerce_plan_expr_for_schema, normalize_col,
3030
normalize_col_with_schemas_and_ambiguity_check, normalize_cols, normalize_sorts,
31-
rewrite_sort_cols_by_aggs,
31+
replace_col, rewrite_sort_cols_by_aggs,
3232
};
3333
use crate::logical_plan::{
3434
Aggregate, Analyze, AsOfJoin, AsOfMatch, Distinct, DistinctOn, EmptyRelation,
@@ -823,16 +823,23 @@ impl LogicalPlanBuilder {
823823
/// (which will appear as a (1, 2), (1, 2) if a and b are projected
824824
///
825825
/// See <https://github.com/apache/datafusion/issues/5065> for more details
826+
///
827+
/// A projection cannot hold both the qualified field `t.c` and an
828+
/// unqualified field `c`. When it already outputs `c`, e.g. for
829+
/// `SELECT lower(t.c) AS c FROM t ORDER BY t.c`, the sort column is added
830+
/// under an alias instead: the alias is recorded in `aliases` so the caller
831+
/// can rebind the sorts to it, and the projection above the sort drops it.
826832
fn add_missing_columns(
827833
curr_plan: LogicalPlan,
828834
missing_cols: &IndexSet<Column>,
829835
is_distinct: bool,
836+
aliases: &mut HashMap<Column, Column>,
830837
) -> Result<LogicalPlan> {
831838
match curr_plan {
832839
LogicalPlan::Projection(Projection {
833840
input,
834841
mut expr,
835-
schema: _,
842+
schema,
836843
}) if missing_cols.iter().all(|c| input.schema().has_column(c)) => {
837844
let mut missing_exprs = missing_cols
838845
.iter()
@@ -846,6 +853,26 @@ impl LogicalPlanBuilder {
846853
if is_distinct {
847854
Self::ambiguous_distinct_check(&missing_exprs, missing_cols, &expr)?;
848855
}
856+
857+
for e in missing_exprs.iter_mut() {
858+
let Expr::Column(column) = &*e else {
859+
continue;
860+
};
861+
let column = column.clone();
862+
if !schema
863+
.iter()
864+
.any(|(q, f)| q.is_none() && f.name() == column.name())
865+
{
866+
continue;
867+
}
868+
let mut name = column.flat_name();
869+
while schema.has_column_with_unqualified_name(&name) {
870+
name.push('_');
871+
}
872+
aliases.insert(column.clone(), Column::new_unqualified(name.clone()));
873+
*e = Expr::Column(column).alias(name);
874+
}
875+
849876
expr.extend(missing_exprs);
850877
project(Arc::unwrap_or_clone(input), expr)
851878
}
@@ -860,6 +887,7 @@ impl LogicalPlanBuilder {
860887
(*input_plan).clone(),
861888
missing_cols,
862889
is_distinct,
890+
aliases,
863891
)
864892
})
865893
.collect::<Result<Vec<_>>>()?;
@@ -963,12 +991,28 @@ impl LogicalPlanBuilder {
963991
let new_expr = schema.columns().into_iter().map(Expr::Column).collect();
964992

965993
let is_distinct = false;
994+
let mut aliases = HashMap::new();
966995
let plan = Self::add_missing_columns(
967996
Arc::unwrap_or_clone(self.plan),
968997
&missing_cols,
969998
is_distinct,
999+
&mut aliases,
9701000
)?;
9711001

1002+
// Rebind the sorts to the columns that were carried under an alias
1003+
// rather than under their own name.
1004+
let replace: HashMap<&Column, &Column> = aliases.iter().collect();
1005+
let sorts = sorts
1006+
.into_iter()
1007+
.map(|sort| {
1008+
Ok(SortExpr::new(
1009+
replace_col(sort.expr, &replace)?,
1010+
sort.asc,
1011+
sort.nulls_first,
1012+
))
1013+
})
1014+
.collect::<Result<Vec<_>>>()?;
1015+
9721016
let sort_plan = LogicalPlan::Sort(Sort {
9731017
expr: normalize_sorts(sorts, &plan)?,
9741018
input: Arc::new(plan),
@@ -2638,6 +2682,30 @@ mod tests {
26382682
Ok(())
26392683
}
26402684

2685+
#[test]
2686+
fn plan_builder_sort_missing_column_colliding_with_alias() -> Result<()> {
2687+
// The carried sort key cannot be added to the projection as
2688+
// `employee_csv.salary`: a schema cannot hold both that qualified
2689+
// field and the unqualified field `salary` the projection outputs.
2690+
let plan = table_scan(Some("employee_csv"), &employee_schema(), None)?
2691+
.project(vec![(col("salary") + lit(1.0)).alias("salary")])?
2692+
.sort(vec![expr::Sort::new(
2693+
col("employee_csv.salary"),
2694+
true,
2695+
false,
2696+
)])?
2697+
.build()?;
2698+
2699+
assert_snapshot!(plan, @r#"
2700+
Projection: salary
2701+
Sort: employee_csv.salary ASC NULLS LAST
2702+
Projection: employee_csv.salary + Float64(1) AS salary, employee_csv.salary AS employee_csv.salary
2703+
TableScan: employee_csv
2704+
"#);
2705+
2706+
Ok(())
2707+
}
2708+
26412709
#[test]
26422710
fn plan_builder_union() -> Result<()> {
26432711
let plan =

‎datafusion/sqllogictest/test_files/order.slt‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2466,3 +2466,25 @@ DROP TABLE probe_constant_u;
24662466

24672467
statement ok
24682468
DROP TABLE probe_constant_v;
2469+
2470+
# Regression for #26462: a select item aliased to the name of a source column
2471+
# hid that source column from ORDER BY.
2472+
statement ok
2473+
CREATE TABLE employees (id INT, email VARCHAR) AS VALUES (1, 'B@x'), (2, 'a@y');
2474+
2475+
# `e.email` is qualified, so it is the input column: b@x sorts before a@y.
2476+
query T
2477+
SELECT lower(e.email) AS email FROM employees e ORDER BY e.email;
2478+
----
2479+
b@x
2480+
a@y
2481+
2482+
# The bare name is the output column, as in PostgreSQL.
2483+
query T
2484+
SELECT lower(e.email) AS email FROM employees e ORDER BY email;
2485+
----
2486+
a@y
2487+
b@x
2488+
2489+
statement ok
2490+
DROP TABLE employees;

0 commit comments

Comments
 (0)