You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/10/14 19:56:40 UTC

(datafusion) branch gh-readonly-queue/main/pr-26150-0d79252f61bd8777990b2749c216ffd07079c30d created (now 6079c8b67b)

This is an automated email from the ASF dual-hosted git repository.

github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-26150-0d79252f61bd8777990b2749c216ffd07079c30d
in repository https://gitbox.apache.org/repos/asf/datafusion.git


      at 6079c8b67b fix: match functional dependencies by column position, not by name (#26150)

This branch includes the following new commits:

     new 6079c8b67b fix: match functional dependencies by column position, not by name (#26150)

The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails.  The revisions
listed as "add" were already present in the repository and have only
been added to this reference.



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


(datafusion) 01/01: fix: match functional dependencies by column position, not by name (#26150)

Posted by gi...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-26150-0d79252f61bd8777990b2749c216ffd07079c30d
in repository https://gitbox.apache.org/repos/asf/datafusion.git

commit 6079c8b67bbd88c22fc7a5cb153e130d4e9532bb
Author: Jay Zhan <ja...@gmail.com>
AuthorDate: Wed Oct 14 17:44:56 2026 +0000

    fix: match functional dependencies by column position, not by name (#26150)
    
    ## Which issue does this PR close?
    
    - None yet. The bug and its repro are below.
    
    ## Rationale for this change
    
    If `x` is a primary key, the optimizer treats `CAST(x AS ...)` as `x`.
    It then drops the other ORDER BY or GROUP BY keys that `x` determines,
    so the query returns wrong results:
    
    ```sql
    CREATE TABLE t (x DOUBLE, y VARCHAR, PRIMARY KEY (x))
      AS VALUES (1.1, 'b'), (1.2, 'a'), (2.1, 'c');
    
    SELECT x, y FROM t ORDER BY CAST(x AS INT), y;
    -- main:     1.1 b / 1.2 a / 2.1 c   (`y` is dropped from the sort)
    -- expected: 1.2 a / 1.1 b / 2.1 c
    
    SELECT CAST(x AS INT) k, count(*) n FROM t GROUP BY CAST(x AS INT), y;
    -- main:     (1, 2) / (2, 1)         (`y` is dropped from the grouping)
    -- expected: (1, 1) / (1, 1) / (2, 1)
    
    SELECT y, count(*) FROM t GROUP BY CAST(x AS INT);
    -- main:     accepted
    -- expected: Error: Column in SELECT must be in GROUP BY or an aggregate function
    ```
    
    `CAST(x AS INT)` is named `t.x`, because casts are left out of
    expression names. The functional dependency helpers matched GROUP BY and
    ORDER BY expressions to key columns by comparing names, so the cast
    matched the key `t.x`.
    
    The same happens with `TRY_CAST`, with a `UNIQUE NOT NULL` key, and with
    a key that comes from an inner GROUP BY. The ORDER BY case comes from
    the sort key pruning added in #21362 (54.0.0).
    
    ## What changes are included in this PR?
    
    - **Index-based helpers.** The four helpers in
    `functional_dependencies.rs` now take, for each GROUP BY or ORDER BY
    expression, the index of the input field it references (`Option<usize>`)
    instead of its name. A computed expression has no index, so it never
    matches a key. The new `datafusion_expr::utils::passthrough_field_index`
    returns the index for a column reference, aliased or not.
    - **One GROUP BY list.** The dependencies of an `Aggregate`'s output use
    the GROUP BY list that its schema is built from
    (`grouping_set_to_exprlist`). Before, they used a list de-duplicated by
    name, which merged `CAST(x AS INT)` and `x`.
    - **Projections resolve columns.** A projection now finds each input
    column with `DFSchema::index_of_column_by_name` instead of comparing
    `"qualifier.name"` strings. The unit test
    `projection_duplicate_flattened_name_uses_first_input_index` asserted
    the old string behaviour: a column named `"orders.id"` got the
    dependency of the different field `orders.id`. It is renamed and now
    asserts that each column keeps its own dependency.
    
    ## What is the testing strategy for this PR?
    
    - **New tests.** Section 6 of `functional_dependencies.slt` has one
    query for each user of the helpers:
      - ORDER BY pruning;
      - GROUP BY pruning;
      - `Aggregate` output dependencies;
      - GROUP BY expansion.
    
    On `main`, each of these returns a wrong result or accepts an invalid
    query. The same queries also run over `TRY_CAST` on a `UNIQUE NOT NULL`
    key and over a key from an inner GROUP BY. One more query pins that a
    widening cast (`INT` to `BIGINT`) of a key does not carry its
    dependencies.
    - **New unit tests.** `passthrough_field_index` resolves an ambiguous
    unqualified column to nothing. A key that is in the GROUP BY list two
    times (`id, id AS k`, builder API only) still determines the row.
    - **Existing tests** are unchanged, apart from the rewritten unit test.
    - **Planning time.** Planning is not slower. The `sql_planner` TPC-H and
    TPC-DS benchmarks, whose tables have primary keys, are about 5% faster,
    because the new code no longer renders expression names.
    
    <details><summary>Benchmark numbers (3 interleaved runs per
    side)</summary>
    
    | Benchmark | `main` | This PR |
    |---|---|---|
    | `physical_plan_tpch_all` | 17.3 / 17.8 / 18.0 ms | 16.1 / 17.0 / 17.2
    ms |
    | `physical_plan_tpcds_all` | 291 / 300 / 301 ms | 276 / 280 / 282 ms |
    
    </details>
    
    ## Are there any user-facing changes?
    
    - **Results.** The queries above return correct results.
    - **Rejected queries.** Only a column reference to a key carries its
    dependencies. A query that selects other columns while grouping by an
    expression over the key is now rejected, even when the expression keeps
    distinct values distinct, like `GROUP BY CAST(x AS BIGINT)` over an
    `INT` key. `main` accepts it and its result is correct. PostgreSQL and
    DuckDB reject it too. Group by the key column itself instead.
    - **API change.** Four `pub` functions in `datafusion_common` take
    `&[Option<usize>]` instead of `&[String]`:
      - `aggregate_functional_dependencies`
      - `get_target_functional_dependencies`
      - `get_required_group_by_exprs_indices`
      - `get_required_sort_exprs_indices`
    
      The upgrade guide has a migration note.
---
 datafusion/common/src/functional_dependencies.rs   |  97 +++++++++---------
 datafusion/expr/src/logical_plan/builder.rs        |  20 ++--
 datafusion/expr/src/logical_plan/plan.rs           |  84 +++++++++-------
 datafusion/expr/src/utils.rs                       |  44 ++++++++-
 .../optimizer/src/eliminate_duplicated_expr.rs     |   9 +-
 .../optimizer/src/optimize_projections/mod.rs      |   9 +-
 .../test_files/functional_dependencies.slt         | 108 +++++++++++++++++++++
 docs/source/library-user-guide/upgrading/56.0.0.md |  39 ++++++++
 8 files changed, 307 insertions(+), 103 deletions(-)

diff --git a/datafusion/common/src/functional_dependencies.rs b/datafusion/common/src/functional_dependencies.rs
index 8bcfdbe97c..1c23134e77 100644
--- a/datafusion/common/src/functional_dependencies.rs
+++ b/datafusion/common/src/functional_dependencies.rs
@@ -452,9 +452,15 @@ impl Deref for FunctionalDependencies {
 }
 
 /// Calculates functional dependencies for aggregate output, when there is a GROUP BY expression.
+///
+/// `group_by_input_indices[i]` is the index of the input field that GROUP BY
+/// expression `i` passes through unchanged (a column reference), or `None` if
+/// the expression computes a new value. Only a column reference can carry an
+/// input dependency; a computed expression such as `CAST(pk AS INT)` cannot,
+/// even though its output name may equal the column's name.
 pub fn aggregate_functional_dependencies(
     aggr_input_schema: &DFSchema,
-    group_by_expr_names: &[String],
+    group_by_input_indices: &[Option<usize>],
     aggr_schema: &DFSchema,
 ) -> FunctionalDependencies {
     let mut aggregate_func_dependencies = vec![];
@@ -466,10 +472,9 @@ pub fn aggregate_functional_dependencies(
     // The loop below only re-expresses input dependencies. Skip it when the
     // input has none. The GROUP BY-key dependency below always runs.
     if !func_dependencies.is_empty() {
-        let aggr_input_fields = aggr_input_schema.field_names();
         // Compute once: this does not change in the loop.
         let existing_target_indices =
-            get_target_functional_dependencies(aggr_input_schema, group_by_expr_names);
+            get_target_functional_dependencies(aggr_input_schema, group_by_input_indices);
         for FunctionalDependence {
             source_indices,
             nullable,
@@ -480,24 +485,24 @@ pub fn aggregate_functional_dependencies(
         {
             // Indices into the GROUP BY list for this determinant:
             let mut new_source_indices = vec![];
-            let mut new_source_field_names = vec![];
-            let source_field_names = source_indices
-                .iter()
-                .map(|&idx| &aggr_input_fields[idx])
-                .collect::<Vec<_>>();
-
-            for (idx, group_by_expr_name) in group_by_expr_names.iter().enumerate() {
-                // When one of the input determinant expressions matches with
-                // the GROUP BY expression, add the index of the GROUP BY
-                // expression as a new determinant key:
-                if source_field_names.contains(&group_by_expr_name) {
+            let mut new_source_input_indices = vec![];
+            for (idx, input_idx) in group_by_input_indices.iter().enumerate() {
+                // When one of the input determinant columns is a GROUP BY
+                // expression, add the index of the GROUP BY expression as a new
+                // determinant key:
+                if let Some(input_idx) = input_idx
+                    && source_indices.contains(input_idx)
+                    // A key column can be in the GROUP BY list more than one
+                    // time (`x, x AS k`). Count it one time.
+                    && !new_source_input_indices.contains(&Some(*input_idx))
+                {
                     new_source_indices.push(idx);
-                    new_source_field_names.push(group_by_expr_name.clone());
+                    new_source_input_indices.push(Some(*input_idx));
                 }
             }
             let new_target_indices = get_target_functional_dependencies(
                 aggr_input_schema,
-                &new_source_field_names,
+                &new_source_input_indices,
             );
             let mode = if existing_target_indices == new_target_indices
                 && new_target_indices.is_some()
@@ -513,7 +518,7 @@ pub fn aggregate_functional_dependencies(
                 // GROUP BY treats NULLs as equal: a determinant covering the
                 // complete grouping key gets at most one output row per NULL too.
                 let output_null_equality =
-                    if new_source_indices.len() == group_by_expr_names.len() {
+                    if new_source_indices.len() == group_by_input_indices.len() {
                         NullEquality::NullEqualsNull
                     } else {
                         *null_equality
@@ -533,8 +538,8 @@ pub fn aggregate_functional_dependencies(
 
     // When we have a GROUP BY key, we can guarantee uniqueness after
     // aggregation:
-    if !group_by_expr_names.is_empty() {
-        let count = group_by_expr_names.len();
+    if !group_by_input_indices.is_empty() {
+        let count = group_by_input_indices.len();
         let source_indices = (0..count).collect::<Vec<_>>();
         let nullable = source_indices
             .iter()
@@ -566,32 +571,30 @@ pub fn aggregate_functional_dependencies(
 
 /// Returns target indices, for the determinant keys that are inside
 /// group by expressions.
+///
+/// `group_by_input_indices` holds, for each GROUP BY expression, the index of
+/// the `schema` field it references, or `None` for a computed expression.
 pub fn get_target_functional_dependencies(
     schema: &DFSchema,
-    group_by_expr_names: &[String],
+    group_by_input_indices: &[Option<usize>],
 ) -> Option<Vec<usize>> {
     let dependencies = schema.functional_dependencies();
     if dependencies.is_empty() {
         return None;
     }
     let mut combined_target_indices = HashSet::new();
-    let field_names = schema.field_names();
     for FunctionalDependence {
         source_indices,
         target_indices,
         ..
     } in &dependencies.deps
     {
-        let source_key_names = source_indices
-            .iter()
-            .map(|id_key_idx| &field_names[*id_key_idx])
-            .collect::<Vec<_>>();
         // If the GROUP BY expression contains a determinant key, we can use
         // the associated fields after aggregation even if they are not part
         // of the GROUP BY expression.
-        if source_key_names
+        if source_indices
             .iter()
-            .all(|source_key_name| group_by_expr_names.contains(source_key_name))
+            .all(|source_idx| group_by_input_indices.contains(&Some(*source_idx)))
         {
             combined_target_indices.extend(target_indices.iter());
         }
@@ -605,19 +608,18 @@ pub fn get_target_functional_dependencies(
 
 /// Returns indices for the minimal subset of GROUP BY expressions that are
 /// functionally equivalent to the original set of GROUP BY expressions.
+///
+/// `group_by_input_indices` holds, for each GROUP BY expression, the index of
+/// the `schema` field it references, or `None` for a computed expression. If
+/// any GROUP BY expression is computed, returns `None`.
 pub fn get_required_group_by_exprs_indices(
     schema: &DFSchema,
-    group_by_expr_names: &[String],
+    group_by_input_indices: &[Option<usize>],
 ) -> Option<Vec<usize>> {
     let dependencies = schema.functional_dependencies();
-    let field_names = schema.field_names();
-    let mut groupby_expr_indices = group_by_expr_names
+    let mut groupby_expr_indices = group_by_input_indices
         .iter()
-        .map(|group_by_expr_name| {
-            field_names
-                .iter()
-                .position(|field_name| field_name == group_by_expr_name)
-        })
+        .copied()
         .collect::<Option<Vec<_>>>()?;
 
     groupby_expr_indices.sort_unstable();
@@ -642,33 +644,32 @@ pub fn get_required_group_by_exprs_indices(
     groupby_expr_indices
         .iter()
         .map(|idx| {
-            group_by_expr_names
+            group_by_input_indices
                 .iter()
-                .position(|name| &field_names[*idx] == name)
+                .position(|input_idx| *input_idx == Some(*idx))
         })
         .collect()
 }
 
 /// Returns indices for the minimal subset of ORDER BY expressions that are
 /// functionally equivalent to the original set of ORDER BY expressions.
+///
+/// `sort_input_indices` holds, for each ORDER BY expression, the index of the
+/// `schema` field it references, or `None` for a computed expression.
 pub fn get_required_sort_exprs_indices(
     schema: &DFSchema,
-    sort_expr_names: &[String],
+    sort_input_indices: &[Option<usize>],
 ) -> Vec<usize> {
     let dependencies = schema.functional_dependencies();
-    let field_names = schema.field_names();
 
-    let mut known_field_indices = HashSet::with_capacity(sort_expr_names.len());
-    let mut required_sort_expr_indices = Vec::with_capacity(sort_expr_names.len());
+    let mut known_field_indices = HashSet::with_capacity(sort_input_indices.len());
+    let mut required_sort_expr_indices = Vec::with_capacity(sort_input_indices.len());
 
-    for (sort_expr_idx, sort_expr_name) in sort_expr_names.iter().enumerate() {
-        // If the sort expression doesn't correspond to a known schema field
-        // (e.g. a computed expression), we can't reason about it via functional
+    for (sort_expr_idx, field_idx) in sort_input_indices.iter().enumerate() {
+        // If the sort expression doesn't reference a schema field (e.g. a
+        // computed expression), we can't reason about it via functional
         // dependencies, so conservatively keep it.
-        let Some(field_idx) = field_names
-            .iter()
-            .position(|field_name| field_name == sort_expr_name)
-        else {
+        let Some(field_idx) = *field_idx else {
             required_sort_expr_indices.push(sort_expr_idx);
             continue;
         };
diff --git a/datafusion/expr/src/logical_plan/builder.rs b/datafusion/expr/src/logical_plan/builder.rs
index 345180808e..aa81df2255 100644
--- a/datafusion/expr/src/logical_plan/builder.rs
+++ b/datafusion/expr/src/logical_plan/builder.rs
@@ -41,7 +41,7 @@ use crate::utils::{
     Columnizer, can_hash, check_all_columns_from_schema, compare_sort_expr,
     expand_qualified_wildcard, expand_wildcard, expr_to_columns,
     find_valid_equijoin_key_pair, group_window_expr_by_sort_keys,
-    split_conjunction_owned,
+    passthrough_field_index, split_conjunction_owned,
 };
 use crate::{
     BinaryExpr, DmlStatement, ExplainOption, Expr, ExprSchemable, Operator,
@@ -1988,22 +1988,20 @@ pub fn add_group_by_exprs_from_dependencies(
         return Ok(group_expr);
     }
 
-    // Names of the fields produced by the GROUP BY exprs for example, `GROUP BY
-    // c1 + 1` produces an output field named `"c1 + 1"`
-    let mut group_by_field_names = group_expr
+    // The input field that each GROUP BY expression passes through, or `None`
+    // for a computed expression such as `c1 + 1`
+    let mut group_by_input_indices = group_expr
         .iter()
-        .map(|e| e.schema_name().to_string())
+        .map(|e| passthrough_field_index(e, schema))
         .collect::<Vec<_>>();
 
     if let Some(target_indices) =
-        get_target_functional_dependencies(schema, &group_by_field_names)
+        get_target_functional_dependencies(schema, &group_by_input_indices)
     {
         for idx in target_indices {
-            let expr = Expr::Column(Column::from(schema.qualified_field(idx)));
-            let expr_name = expr.schema_name().to_string();
-            if !group_by_field_names.contains(&expr_name) {
-                group_by_field_names.push(expr_name);
-                group_expr.push(expr);
+            if !group_by_input_indices.contains(&Some(idx)) {
+                group_by_input_indices.push(Some(idx));
+                group_expr.push(Expr::Column(Column::from(schema.qualified_field(idx))));
             }
         }
     }
diff --git a/datafusion/expr/src/logical_plan/plan.rs b/datafusion/expr/src/logical_plan/plan.rs
index e0dd008945..0213f78283 100644
--- a/datafusion/expr/src/logical_plan/plan.rs
+++ b/datafusion/expr/src/logical_plan/plan.rs
@@ -44,7 +44,7 @@ use crate::utils::{
     check_aggregate_and_window_nesting, check_no_window_functions,
     enumerate_grouping_sets, expr_to_columns, exprlist_to_fields,
     find_out_reference_exprs, grouping_set_expr_count, grouping_set_to_exprlist,
-    merge_schema, split_conjunction,
+    merge_schema, passthrough_field_index, split_conjunction,
 };
 use crate::{
     BinaryExpr, CreateMemoryTable, CreateView, Execute, Expr, ExprSchemable, GroupingSet,
@@ -69,7 +69,6 @@ use datafusion_common::{
     aggregate_functional_dependencies, assert_eq_or_internal_err, assert_or_internal_err,
     internal_err, plan_datafusion_err, plan_err, validate_range_split_points,
 };
-use indexmap::IndexSet;
 use itertools::Itertools as _;
 
 // backwards compatibility
@@ -4386,15 +4385,14 @@ fn calc_func_dependencies_for_aggregate(
     //   that GROUP BY expression results will be unique.
     // - Otherwise, it may be possible to propagate functional dependencies.
     if !contains_grouping_set(group_expr) {
-        let group_by_expr_names = group_expr
-            .iter()
-            .map(|item| item.schema_name().to_string())
-            .collect::<IndexSet<_>>()
+        // One entry per GROUP BY output field, in the order of `aggr_schema`
+        let group_by_input_indices = grouping_set_to_exprlist(group_expr)?
             .into_iter()
+            .map(|item| passthrough_field_index(item, input.schema()))
             .collect::<Vec<_>>();
         let aggregate_func_dependencies = aggregate_functional_dependencies(
             input.schema(),
-            &group_by_expr_names,
+            &group_by_input_indices,
             aggr_schema,
         );
         Ok(aggregate_func_dependencies)
@@ -4420,19 +4418,9 @@ fn calc_func_dependencies_for_project(
         return Ok(FunctionalDependencies::empty());
     }
 
-    // Map each input field name to its first index so that projection
-    // expressions resolve with a hash lookup instead of a linear scan.
-    let input_fields = input.schema().field_names();
-    let mut input_index_by_name: HashMap<&str, usize> =
-        HashMap::with_capacity(input_fields.len());
-    for (index, name) in input_fields.iter().enumerate() {
-        input_index_by_name.entry(name.as_str()).or_insert(index);
-    }
-    let input_index = |name: &str| {
-        input_index_by_name
-            .get(name)
-            .copied()
-            .unwrap_or(COMPUTED_EXPR_INDEX)
+    let input_schema = input.schema();
+    let input_index = |expr: &Expr| {
+        passthrough_field_index(expr, input_schema).unwrap_or(COMPUTED_EXPR_INDEX)
     };
 
     // Map each projection output position to its input column index.
@@ -4453,16 +4441,14 @@ fn calc_func_dependencies_for_project(
                     wildcard_fields
                         .into_iter()
                         .map(|(qualifier, f)| {
-                            let flat_name = qualifier
-                                .map(|t| format!("{}.{}", t, f.name()))
-                                .unwrap_or_else(|| f.name().clone());
-                            input_index(&flat_name)
+                            input_schema
+                                .index_of_column_by_name(qualifier.as_ref(), f.name())
+                                .unwrap_or(COMPUTED_EXPR_INDEX)
                         })
                         .collect::<Vec<_>>(),
                 )
             }
-            Expr::Alias(alias) => Ok(vec![input_index(&format!("{}", alias.expr))]),
-            _ => Ok(vec![input_index(&format!("{expr}"))]),
+            _ => Ok(vec![input_index(expr)]),
         })
         .collect::<Result<Vec<_>>>()?
         .into_iter()
@@ -5565,16 +5551,37 @@ mod tests {
     }
 
     #[test]
-    fn projection_duplicate_flattened_name_uses_first_input_index() -> Result<()> {
+    fn aggregate_group_by_repeated_key_keeps_key_dependency() -> Result<()> {
+        let constraints =
+            Constraints::new_unverified(vec![Constraint::PrimaryKey(vec![0])]);
+        let source = Arc::new(
+            LogicalTableSource::new(Arc::new(employee_schema()))
+                .with_constraints(constraints),
+        );
+        // `id` is in the GROUP BY list two times: as itself and as `k`.
+        let plan = LogicalPlanBuilder::scan("employee_csv", source, None)?
+            .aggregate(
+                vec![col("id"), col("state"), col("id").alias("k")],
+                Vec::<Expr>::new(),
+            )?
+            .build()?;
+
+        // `id` alone still determines the row.
+        let deps = plan.schema().functional_dependencies();
+        assert_eq!(deps.len(), 1);
+        assert_eq!(deps[0].source_indices, vec![0]);
+        assert_eq!(deps[0].target_indices, vec![0, 1, 2]);
+
+        Ok(())
+    }
+
+    #[test]
+    fn projection_resolves_columns_not_flattened_names() -> Result<()> {
         // Build an input schema where a qualified field (`orders`.`id`) and an
-        // unqualified field that is literally named `"orders.id"` flatten to
-        // the exact same lookup key that `calc_func_dependencies_for_project`
-        // uses to resolve projection expressions against input fields. This is
-        // the only way two entries of `DFSchema::field_names()` can collide
-        // (`DFSchema::check_names` otherwise forbids duplicate names), and it
-        // pins that the hash-map based lookup resolves such a collision to the
-        // *first* matching index, exactly like the linear `position()` scan it
-        // replaces.
+        // unqualified field that is literally named `"orders.id"` (the name of
+        // e.g. `CAST(orders.id AS INT)`) have the same flattened name. The
+        // projection must resolve the column it references, not the first
+        // field with the same flattened name.
         let schema = DFSchema::new_with_metadata(
             vec![
                 (
@@ -5597,11 +5604,14 @@ mod tests {
             schema: Arc::new(schema),
         });
 
-        // References the *unqualified* second field, whose flattened name
-        // ("orders.id") collides with the first (qualified) field's.
+        // References the *unqualified* second field, which is not a key.
         let exprs = vec![Expr::Column(Column::new_unqualified("orders.id"))];
         let deps = calc_func_dependencies_for_project(&exprs, &input)?;
+        assert!(deps.is_empty());
 
+        // References the qualified first field, which is a key.
+        let exprs = vec![Expr::Column(Column::new(Some("orders"), "id"))];
+        let deps = calc_func_dependencies_for_project(&exprs, &input)?;
         assert_eq!(deps.len(), 1);
         assert_eq!(deps[0].source_indices, vec![0]);
 
diff --git a/datafusion/expr/src/utils.rs b/datafusion/expr/src/utils.rs
index 771571f489..777e835078 100644
--- a/datafusion/expr/src/utils.rs
+++ b/datafusion/expr/src/utils.rs
@@ -1021,6 +1021,26 @@ pub(crate) fn find_columns_referenced_by_expr(e: &Expr) -> Vec<Column> {
     exprs
 }
 
+/// Returns the index of the `schema` field that `expr` passes through
+/// unchanged, or `None` if `expr` computes a new value.
+///
+/// Only a column reference (possibly aliased) passes a field through. Use this
+/// instead of comparing [`Expr::schema_name`] with field names: different
+/// expressions can have the same name, e.g. `CAST(t.a AS INT)` is named `t.a`.
+///
+/// A column is resolved like any other column reference, so an ambiguous
+/// unqualified name (e.g. `id` over `a.id` and `b.id`) passes nothing through.
+pub fn passthrough_field_index(expr: &Expr, schema: &DFSchema) -> Option<usize> {
+    match expr {
+        Expr::Column(col) => {
+            let (_, field) = schema.qualified_field_from_column(col).ok()?;
+            schema.fields().iter().position(|f| std::ptr::eq(f, field))
+        }
+        Expr::Alias(alias) => passthrough_field_index(&alias.expr, schema),
+        _ => None,
+    }
+}
+
 /// Convert any `Expr` to an `Expr::Column`.
 pub fn expr_as_column_expr(expr: &Expr, plan: &LogicalPlan) -> Result<Expr> {
     match expr {
@@ -1563,9 +1583,31 @@ mod tests {
         expr_vec_fmt, grouping_set, lit, rollup,
         test::function_stub::{max_udaf, min_udaf, sum_udaf},
     };
-    use arrow::datatypes::{UnionFields, UnionMode};
+    use arrow::datatypes::{Metadata, UnionFields, UnionMode};
     use datafusion_expr_common::signature::Volatility;
 
+    #[test]
+    fn passthrough_field_index_resolves_like_a_column_reference() -> Result<()> {
+        let field = |name| Arc::new(Field::new(name, DataType::Int32, false));
+        let schema = DFSchema::new_with_metadata(
+            vec![
+                (Some("a".into()), field("id")),
+                (Some("b".into()), field("id")),
+                (Some("b".into()), field("v")),
+            ],
+            Metadata::default(),
+        )?;
+        let index = |e: Expr| passthrough_field_index(&e, &schema);
+
+        assert_eq!(index(col("b.id")), Some(1));
+        assert_eq!(index(col("v").alias("w")), Some(2));
+        // ambiguous: `a.id` or `b.id`
+        assert_eq!(index(col("id")), None);
+        // computed, even though it is named `b.id`
+        assert_eq!(index(crate::cast(col("b.id"), DataType::Int64)), None);
+        Ok(())
+    }
+
     #[test]
     fn test_group_window_expr_by_sort_keys_empty_case() -> Result<()> {
         let result = group_window_expr_by_sort_keys(vec![])?;
diff --git a/datafusion/optimizer/src/eliminate_duplicated_expr.rs b/datafusion/optimizer/src/eliminate_duplicated_expr.rs
index 97a53eef74..e65c6a79be 100644
--- a/datafusion/optimizer/src/eliminate_duplicated_expr.rs
+++ b/datafusion/optimizer/src/eliminate_duplicated_expr.rs
@@ -22,6 +22,7 @@ use crate::{OptimizerConfig, OptimizerRule};
 use datafusion_common::tree_node::Transformed;
 use datafusion_common::{Result, get_required_sort_exprs_indices, internal_err};
 use datafusion_expr::logical_plan::LogicalPlan;
+use datafusion_expr::utils::passthrough_field_index;
 use datafusion_expr::{Aggregate, Expr, Sort, SortExpr};
 use std::hash::{Hash, Hasher};
 
@@ -72,13 +73,15 @@ impl OptimizerRule for EliminateDuplicatedExpr {
                     .map(|wrapper| wrapper.0)
                     .collect();
 
-                let sort_expr_names = unique_exprs
+                let sort_input_indices = unique_exprs
                     .iter()
-                    .map(|sort_expr| sort_expr.expr.schema_name().to_string())
+                    .map(|sort_expr| {
+                        passthrough_field_index(&sort_expr.expr, sort.input.schema())
+                    })
                     .collect::<Vec<_>>();
                 let required_indices = get_required_sort_exprs_indices(
                     sort.input.schema().as_ref(),
-                    &sort_expr_names,
+                    &sort_input_indices,
                 );
 
                 let unique_exprs = if required_indices.len() < unique_exprs.len() {
diff --git a/datafusion/optimizer/src/optimize_projections/mod.rs b/datafusion/optimizer/src/optimize_projections/mod.rs
index c2550f59bb..c913a4ce96 100644
--- a/datafusion/optimizer/src/optimize_projections/mod.rs
+++ b/datafusion/optimizer/src/optimize_projections/mod.rs
@@ -28,6 +28,7 @@ use datafusion_common::{
     get_required_group_by_exprs_indices, internal_datafusion_err, internal_err,
 };
 use datafusion_expr::expr::Alias;
+use datafusion_expr::utils::passthrough_field_index;
 use datafusion_expr::{
     Aggregate, Distinct, EmptyRelation, Expr, Projection, TableScanBuilder, Unnest,
     Window, logical_plan::LogicalPlan,
@@ -154,16 +155,18 @@ fn optimize_projections(
             {
                 aggregate.group_expr
             } else {
-                let group_by_expr_existing = aggregate
+                let group_by_input_indices = aggregate
                     .group_expr
                     .iter()
-                    .map(|group_by_expr| group_by_expr.schema_name().to_string())
+                    .map(|group_by_expr| {
+                        passthrough_field_index(group_by_expr, aggregate.input.schema())
+                    })
                     .collect::<Vec<_>>();
 
                 if let Some(simplest_groupby_indices) =
                     get_required_group_by_exprs_indices(
                         aggregate.input.schema(),
-                        &group_by_expr_existing,
+                        &group_by_input_indices,
                     )
                 {
                     // Some of the fields in the GROUP BY may be required by
diff --git a/datafusion/sqllogictest/test_files/functional_dependencies.slt b/datafusion/sqllogictest/test_files/functional_dependencies.slt
index af5d2e5077..a407b4cd6d 100644
--- a/datafusion/sqllogictest/test_files/functional_dependencies.slt
+++ b/datafusion/sqllogictest/test_files/functional_dependencies.slt
@@ -358,6 +358,114 @@ drop table t_null;
 statement ok
 drop table t_probe;
 
+
+# 6. Only a column reference carries a dependency. `CAST(x AS INT)` is named
+# `t_cast.x`, like the PRIMARY KEY column, but it does not determine `y`:
+# 1.1 and 1.2 both cast to 1.
+statement ok
+CREATE TABLE t_cast (x DOUBLE, y VARCHAR, PRIMARY KEY (x)) AS VALUES (1.1, 'b'), (1.2, 'a'), (2.1, 'c');
+
+# 6.1 ORDER BY: `y` is the tie-breaker within `CAST(x AS INT) = 1` and is kept.
+query RT
+SELECT x, y FROM t_cast ORDER BY CAST(x AS INT), y;
+----
+1.2 a
+1.1 b
+2.1 c
+
+query TT
+EXPLAIN SELECT x, y FROM t_cast ORDER BY CAST(x AS INT), y;
+----
+logical_plan
+01)Sort: CAST(t_cast.x AS Int32) ASC NULLS LAST, t_cast.y ASC NULLS LAST
+02)--TableScan: t_cast projection=[x, y]
+
+# 6.2 GROUP BY: `y` is not selected but still splits the groups.
+query II rowsort
+SELECT CAST(x AS INT) k, count(*) n FROM t_cast GROUP BY CAST(x AS INT), y;
+----
+1 1
+1 1
+2 1
+
+# 6.3 GROUP BY output: the dependency `k -> y` does not exist, so `y` still
+# orders the rows within `k = 1`.
+query IT
+SELECT CAST(x AS INT) k, y FROM t_cast GROUP BY CAST(x AS INT), y ORDER BY k, y;
+----
+1 a
+1 b
+2 c
+
+# 6.4 `y` is not determined by the GROUP BY expression, so it can't be selected.
+query error DataFusion error: Error during planning: Column in SELECT must be in GROUP BY or an aggregate function
+SELECT y, count(*) FROM t_cast GROUP BY CAST(x AS INT);
+
+statement ok
+drop table t_cast;
+
+# 6.5 Only the key column itself carries the dependency, even when the cast
+# keeps distinct values distinct, like INT to BIGINT. PostgreSQL and DuckDB
+# reject this too.
+statement ok
+CREATE TABLE t_wide (x INT, y VARCHAR, PRIMARY KEY (x)) AS VALUES (1, 'b'), (2, 'a');
+
+query error DataFusion error: Error during planning: Column in SELECT must be in GROUP BY or an aggregate function
+SELECT y, count(*) FROM t_wide GROUP BY CAST(x AS BIGINT);
+
+statement ok
+drop table t_wide;
+
+# 6.6 The same with `TRY_CAST` over a `UNIQUE NOT NULL` key: 'bad-a' and
+# 'bad-b' both cast to NULL.
+statement ok
+CREATE TABLE t_try_cast (x VARCHAR NOT NULL UNIQUE, y VARCHAR) AS VALUES ('bad-a', 'b'), ('bad-b', 'a'), ('1', 'c');
+
+query TT
+SELECT x, y FROM t_try_cast ORDER BY TRY_CAST(x AS INT), y;
+----
+1 c
+bad-b a
+bad-a b
+
+query IT rowsort
+SELECT TRY_CAST(x AS INT) k, y FROM t_try_cast GROUP BY TRY_CAST(x AS INT), y;
+----
+1 c
+NULL a
+NULL b
+
+query error DataFusion error: Error during planning: Column in SELECT must be in GROUP BY or an aggregate function
+SELECT y, count(*) FROM t_try_cast GROUP BY TRY_CAST(x AS INT);
+
+statement ok
+drop table t_try_cast;
+
+# 6.7 A key that comes from an inner GROUP BY: `x` is unique in the subquery,
+# but `CAST(x AS INT)` is not.
+statement ok
+CREATE TABLE t_inner (x DOUBLE, y VARCHAR) AS VALUES (1.1, 'b'), (1.2, 'a'), (2.1, 'c');
+
+query RT
+SELECT x, y FROM (SELECT x, max(y) AS y FROM t_inner GROUP BY x) ORDER BY CAST(x AS INT), y;
+----
+1.2 a
+1.1 b
+2.1 c
+
+query II rowsort
+SELECT CAST(x AS INT) k, count(*) n FROM (SELECT x, max(y) AS y FROM t_inner GROUP BY x) GROUP BY CAST(x AS INT), y;
+----
+1 1
+1 1
+2 1
+
+query error DataFusion error: Error during planning: Column in SELECT must be in GROUP BY or an aggregate function
+SELECT y, count(*) FROM (SELECT x, max(y) AS y FROM t_inner GROUP BY x) GROUP BY CAST(x AS INT);
+
+statement ok
+drop table t_inner;
+
 ##########
 ## Cleanup
 ##########
diff --git a/docs/source/library-user-guide/upgrading/56.0.0.md b/docs/source/library-user-guide/upgrading/56.0.0.md
index cd60aa365c..b344eff617 100644
--- a/docs/source/library-user-guide/upgrading/56.0.0.md
+++ b/docs/source/library-user-guide/upgrading/56.0.0.md
@@ -940,6 +940,45 @@ let dep = FunctionalDependence::new(source_indices, target_indices, nullable)
     .with_null_equality(NullEquality::NullEqualsNull);
 ```
 
+### Functional dependency helpers take field indices instead of names
+
+`aggregate_functional_dependencies`, `get_target_functional_dependencies`,
+`get_required_group_by_exprs_indices` and `get_required_sort_exprs_indices` in
+`datafusion_common` now take, for each GROUP BY or ORDER BY expression, the
+index of the input field it references (`Option<usize>`) instead of its name.
+Matching by name gave incorrect results, because different expressions can
+have the same name: `CAST(t.pk AS INT)` is named `t.pk`, but it does not
+determine the columns that `t.pk` does.
+
+Queries are affected too: only a column reference to the key carries its
+dependencies, so a query that selects other columns while grouping by an
+expression over the key is now rejected, as in PostgreSQL and DuckDB. Group by
+the key column itself instead:
+
+```sql
+-- `id` is the INT PRIMARY KEY of `t`
+SELECT name FROM t GROUP BY CAST(id AS BIGINT); -- now an error
+SELECT name FROM t GROUP BY id;                 -- ok
+```
+
+**Migration guide:**
+
+Pass `None` for an expression that computes a new value. For a column
+reference, `datafusion_expr::utils::passthrough_field_index` returns the index:
+
+```rust,ignore
+// Before
+let names: Vec<String> = exprs.iter().map(|e| e.schema_name().to_string()).collect();
+let target = get_target_functional_dependencies(schema, &names);
+
+// After
+let indices: Vec<Option<usize>> = exprs
+    .iter()
+    .map(|e| passthrough_field_index(e, schema))
+    .collect();
+let target = get_target_functional_dependencies(schema, &indices);
+```
+
 ### `FileSource::exact_filter` controls scan equivalence properties
 
 `FileScanConfig` no longer derives equivalence properties (constant columns


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