fix(optimizer): error on correlated subqueries with ambiguous duplicate columns across relations by Awesome06 · Pull Request #27244 · apache/datafusion

Awesome06 · 2026-11-08T17:22:13Z

Which issue does this PR close?

Rationale for this change

When a correlated subquery joins multiple inner relations that expose columns with identical unqualified names (e.g., one.k and two.k), DataFusion's SubqueryAlias naming contract keeps the first field with a given name (k) and disambiguates subsequent ones (k:1, k:2).

Previously, subquery decorrelation (for EXISTS, NOT EXISTS, IN, scalar subqueries, LATERAL joins, and inner derived tables) requalified inner column references by unqualified column name into <alias>.<col>. This collapsed references from distinct inner tables into the same aliased column (e.g. __correlated_sq_N.k or d.k), producing contradictory join filters (k = outer.k AND k = outer.k + 1) that silently returned wrong results (empty rows) without an error.

Per DataFusion's maintainer doctrine ("Fail-Fast / Error Over Wrong Results"), rather than introducing speculative alias heuristics, decorrelation must fail fast with an explicit planning error when a correlated column is not the primary 0-indexed column retained under the alias.

What changes are included in this PR?

datafusion/optimizer/src/decorrelate.rs:
- Added check_alias_keeps_column_name(schema: &DFSchema, col: &Column) -> Result<()>: Validates whether col is the first field with its unqualified name in schema. If not, raises:
  Unsupported correlated subquery: correlated column {col} has the same name as an earlier column of the subquery output
- Added check_alias_keeps_column_name call inside build_join_filter to guard top-level subquery aliasing.
- Added check_alias_keeps_column_name(alias.input.schema(), col)?; to the LogicalPlan::SubqueryAlias arm of PullUpCorrelatedExpr, guarding intermediate derived tables (FROM (SELECT ... FROM one, two) d) inside subqueries.
- Updated test_build_join_filter unit tests to verify that unambiguous first-column correlations succeed while subsequent ambiguous correlations fail.
datafusion/optimizer/src/decorrelate_lateral_join.rs:
- Added check_alias_keeps_column_name check inside requalify_filter, guarding lateral join filter requalification against ambiguous duplicate column collision.
datafusion/optimizer/src/decorrelate_predicate_subquery.rs & datafusion/optimizer/src/scalar_subquery_to_join.rs:
- Passed new_plan.schema() and decorrelated_subquery.schema() into build_join_filter.
datafusion/sqllogictest/test_files/subquery.slt:
- Added regression test suite covering:
  - EXISTS, NOT EXISTS, IN, scalar subqueries, and LATERAL joins referencing ambiguous second columns (two.k), asserting the exact Unsupported correlated subquery error.
  - Derived tables inside subqueries with ambiguous duplicate columns (EXISTS and NOT EXISTS), asserting the exact error.
  - Positive controls for single-relation correlations, first-column correlations, and single-relation derived tables, asserting exact expected output.

Are these changes tested?

Yes:

cargo test -p datafusion-optimizer --lib (975 unit tests passed)
cargo test -p datafusion-sqllogictest --test sqllogictests -- subquery (100% passed)
cargo clippy -p datafusion-optimizer --all-targets -- -D warnings (0 warnings)
cargo fmt --all -- --check (clean)

Are there any user-facing changes?

Queries with correlated subqueries that reference ambiguous duplicate columns across multiple inner relations (or within derived tables inside subqueries) now fail fast with an explicit DataFusionError::Plan instead of silently returning incorrect results. Unambiguous references (referencing only the first relation) continue to succeed.

…te columns across relations Fail fast with an explicit error when a correlated subquery references the outer query from multiple relations that share identical column names, preventing silent wrong results caused by column collision during decorrelation. - Motivation & Intent: When a correlated subquery joins multiple inner relations that expose columns with identical unqualified names (e.g., `one.k` and `two.k`), `SubqueryAlias` disambiguates them in the schema as `k` and `k:1`. However, decorrelation rewrote join filters by bare column name to `<alias>.<col>`, collapsing both references into `__correlated_sq_N.k`. This caused conditions intended for one relation to be evaluated against the other, silently producing incorrect results. Per maintainer guidelines (apache#5492, apache#26672), rather than introducing fragile heuristic alias rewrites, DataFusion must fail fast with a plan error. - Key Changes: - `datafusion/optimizer/src/decorrelate.rs`: Added `subquery_schema: &DFSchema` parameter to `build_join_filter`. Checks whether any correlated column has duplicate fields in `subquery_schema.fields()`. Returns an explicit `plan_err!` if duplicates exist. Added duplicate field error test in `test_build_join_filter`. - `datafusion/optimizer/src/decorrelate_predicate_subquery.rs`: Passed `new_plan.schema()` into `build_join_filter`. - `datafusion/optimizer/src/scalar_subquery_to_join.rs`: Passed `decorrelated_subquery.schema()` into `build_join_filter`. - `datafusion/sqllogictest/test_files/subquery.slt`: Added regression test suite covering `EXISTS`, `NOT EXISTS`, `IN`, and scalar subqueries asserting `Unsupported correlated subquery` error, along with a positive control query ensuring single-relation correlation continues to succeed. - Verification Evidence: - `cargo test -p datafusion-sqllogictest --test sqllogictests -- subquery` (100% passed) - `cargo test -p datafusion-optimizer` (975 unit tests + 29 integration tests + 5 doctests passed) - `cargo fmt --all -- --check` (clean) - `cargo clippy -p datafusion-optimizer -p datafusion-sqllogictest --all-targets -- -D warnings` (0 warnings) Closes apache#26234

Awesome06 · 2026-11-12T05:34:58Z

@alamb could you review this bug fix

jayzhan211

@Awesome06 thanks for the PR. In decorrelate_lateral_join.rs around line 402, requalify_filter does the same name-based mapping, so LATERAL still returns wrong rows with this PR. Expected (1, 1), (2, 0), actual (1, 0), (2, 0):

SELECT t_outer.k, s.c FROM t26234 t_outer, LATERAL (
    SELECT count(*) AS c FROM t26234 one, t26234 two
    WHERE one.k = t_outer.k AND two.k = t_outer.k + 1
) s;

Could you call the same check there, so every place that maps correlated columns by name uses it, and add this query as a statement error test?

             if let Expr::Column(col) = &expr
                 && inner_schema.has_column(col)
             {
+                check_alias_keeps_column_name(inner_schema, col)?;
                 let new_col = Column::new(Some(alias.clone()), col.name.clone());

…ecorrelation

Awesome06 · 2026-11-12T13:58:42Z

Thanks for the catch and the helpful pointers, @jayzhan211! Pushed an update with your suggestions:

Replaced the duplicate counter with check_alias_keeps_column_name so queries where the correlated column is the first occurrence continue to work, while later ones fail with the new message naming the column.
Added the same check to requalify_filter in decorrelate_lateral_join.rs so LATERAL joins fail fast instead of returning wrong rows.
Added the LATERAL query error test, updated the 5 existing error queries, added passing tests for the non-ambiguous cases, and updated the unit test in decorrelate.rs.

jayzhan211

@Awesome06 thanks for the update. A derived table inside the subquery still returns wrong rows with no error. This query returns no rows; the expected result is 1 (the NOT EXISTS form returns 1, 2; expected 2):

SELECT t_outer.k FROM t26234 t_outer WHERE EXISTS (
    SELECT 1 FROM (
        SELECT * FROM t26234 one, t26234 two WHERE one.k = t_outer.k AND two.k = t_outer.k + 1
    ) d
);

In decorrelate.rs around line 711, the SubqueryAlias arm of PullUpCorrelatedExpr maps one.k and two.k to d.k by name, which is the third place that does this. Could you call the same check there?

                 for col in local_correlated_cols.iter() {
+                    check_alias_keeps_column_name(alias.input.schema(), col)?;
                     new_correlated_cols
                         .insert(Column::new(Some(alias.alias.clone()), col.name.clone()));

Tests to add before DROP TABLE t26234: the query above and its NOT EXISTS form as errors, and a one-relation derived table that keeps working

# Same-named columns of a derived table inside the subquery
statement error Unsupported correlated subquery: correlated column two.k has the same name as an earlier column of the subquery output
SELECT t_outer.k FROM t26234 t_outer WHERE EXISTS (
    SELECT 1 FROM (
        SELECT * FROM t26234 one, t26234 two WHERE one.k = t_outer.k AND two.k = t_outer.k + 1
    ) d
);

statement error Unsupported correlated subquery: correlated column two.k has the same name as an earlier column of the subquery output
SELECT t_outer.k FROM t26234 t_outer WHERE NOT EXISTS (
    SELECT 1 FROM (
        SELECT * FROM t26234 one, t26234 two WHERE one.k = t_outer.k AND two.k = t_outer.k + 1
    ) d
);

# A derived table with one relation still works
query I
SELECT t_outer.k FROM t26234 t_outer WHERE EXISTS (
    SELECT 1 FROM (SELECT * FROM t26234 one WHERE one.k = t_outer.k + 1) d
) ORDER BY t_outer.k;
----
1

With this applied, the full sqllogictest suite and cargo test -p datafusion-optimizer --lib pass, and cargo clippy -p datafusion-optimizer --all-targets -- -D warnings is clean. Without the new line, the two error tests return rows.

Optional cleanup: the PR description still quotes the old error text (inner relation column '{col}' has ambiguous duplicate references across relations) and doesn't mention the LATERAL check. Could you update it to the current message (correlated column two.k has the same name as an earlier column of the subquery output) and add decorrelate_lateral_join.rs?

…rrelated subqueries Fail fast with an explicit error when a correlated subquery contains a derived table referencing multiple inner relations with identical column names, preventing silent wrong results caused by duplicate column collapse into the derived table alias. Closes apache#26234

Awesome06 · 2026-11-14T08:52:12Z

Thanks for catching this, @jayzhan211!

Updated in cc97b61de:

Added check_alias_keeps_column_name to the SubqueryAlias arm of PullUpCorrelatedExpr in decorrelate.rs.
Added the negative error tests and single-relation positive control to subquery.slt.
Updated the PR description with the current error message and added decorrelate_lateral_join.rs.

… correlated subqueries Use unique_field_aliases in check_alias_keeps_column_name to mirror SubqueryAlias renaming logic and prevent collisions with synthetically aliased columns.

github-actions Bot added optimizer Optimizer rules sqllogictest SQL Logic Tests (.slt) labels Nov 8, 2026

Merge branch 'apache:main' into fix-26234-decorrelate-ambiguous-cols

565c00f

jayzhan211 requested changes Nov 12, 2026

View reviewed changes

Comment thread datafusion/optimizer/src/decorrelate.rs Resolved

fix(optimizer): check column position under alias and guard lateral d…

8f7690a

…ecorrelation

jayzhan211 requested changes Nov 14, 2026

View reviewed changes

jayzhan211 approved these changes Nov 14, 2026

View reviewed changes

Comment thread datafusion/optimizer/src/decorrelate.rs Resolved

fix(optimizer): use unique_field_aliases to detect renamed columns in…

d10cd93

… correlated subqueries Use unique_field_aliases in check_alias_keeps_column_name to mirror SubqueryAlias renaming logic and prevent collisions with synthetically aliased columns.

Navigation Menu

Repository navigation

fix(optimizer): error on correlated subqueries with ambiguous duplicate columns across relations - #27244

fix(optimizer): error on correlated subqueries with ambiguous duplicate columns across relations#27244
Awesome06 wants to merge 5 commits into
apache:mainfrom
Awesome06:fix-26234-decorrelate-ambiguous-cols

Awesome06 commented Nov 8, 2026 •

edited

Loading

Uh oh!

Awesome06 commented Nov 12, 2026

Uh oh!

jayzhan211 left a comment

Uh oh!

Uh oh!

Awesome06 commented Nov 12, 2026

Uh oh!

jayzhan211 left a comment

Uh oh!

Awesome06 commented Nov 14, 2026

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

Awesome06 commented Nov 8, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Which issue does this PR close?

Rationale for this change

What changes are included in this PR?

Are these changes tested?

Are there any user-facing changes?

Uh oh!

Awesome06 commented Nov 12, 2026

Uh oh!

jayzhan211 left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Awesome06 commented Nov 12, 2026

Uh oh!

jayzhan211 left a comment

Choose a reason for hiding this comment

Uh oh!

Awesome06 commented Nov 14, 2026

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Awesome06 commented Nov 8, 2026 •

edited

Loading