feat: Support planning subqueries with OuterReferenceColumn belongs to non-adjacent outer relations by mkleen · Pull Request #19930 · apache/datafusion

mkleen · 2026-01-21T17:07:20Z

Which issue does this PR close?

Cleaned-up version of #18806 with:

Removed outer_queries_schema from PlannerContext
Planning logic only (optimizer modifications removed)
sql logic tests moved to sql_integration.rs
Closes Supporting planning (binding) Nested correlated subquery error with a depth exceeding 1 #19816

Rationale for this change

See #18806

What changes are included in this PR?

See #18806

Are these changes tested?

Yes

Are there any user-facing changes?

outer_queries_schema is removed from PlannerContext.

mkleen · 2026-01-21T17:18:11Z

datafusion/sql/tests/sql_integration.rs

+    Projection: l.a, l.b, l.c, l.d, l.e
      SubqueryAlias: l
-        Projection: lineitem.l_item_id AS a, lineitem.l_description AS b, lineitem.price AS c
+        Projection: lineitem.l_orderkey AS a, lineitem.l_item_id AS b, lineitem.l_description AS c, lineitem.l_extendedprice AS d, lineitem.price AS e


This is because the lineitem table is extended now.

mkleen · 2026-01-21T17:18:50Z

datafusion/sql/tests/sql_integration.rs

+        "o1.o_custkey".to_string(),
+        "o1.o_orderstatus".to_string(),
        "o1.customer_id".to_string(),
+        "o1.o_totalprice".to_string(),


This is because the order table is extended now.

mkleen · 2026-01-21T17:19:40Z

datafusion/sql/tests/sql_integration.rs

    assert_snapshot!(
        err.strip_backtrace(),
-        @"Error during planning: Source table contains 3 columns but only 2 names given as column alias"
+        @"Error during planning: Source table contains 5 columns but only 2 names given as column alias"


This is because the lineitem table is extended now.

mkleen · 2026-01-21T17:20:16Z

datafusion/sql/tests/common/mod.rs

            ])),
            "orders" => Ok(Schema::new(vec![
                Field::new("order_id", DataType::UInt32, false),
+                Field::new("o_orderkey", DataType::UInt32, false),


These extensions were necessary to get the tests to work.

## Which issue does this PR close? While working on #19930 , I noticed a trailing whitespace in the CROSS JOIN logical plan output. This whitespace is inconsistent with the rest of the logical plan formatting. ## Rationale for this change This change removes the unnecessary trailing whitespace in the logical plan output of a CROSS JOIN. ## What changes are included in this PR? ## Are these changes tested? Yes. ## Are there any user-facing changes? No.

mkleen · 2026-01-26T08:49:55Z

@alamb @duongcongtoai Could you review this when you have a moment so we can get it merged?

duongcongtoai · 2026-01-28T03:15:47Z

nice, i'll take a look once i have time

duongcongtoai · 2026-02-05T01:27:50Z

datafusion/sql/tests/sql_integration.rs

 fn table_with_column_alias() {
-    let sql = "SELECT a, b, c
-                   FROM lineitem l (a, b, c)";
+    let sql = "SELECT a, b, c, d, e


would be nice to have a sqllogictest for this PR

ah i see you replaced them with integration test instead

I agree we should have some sqllogictest coverage to make sure everything is hooked up right

I added two sqllogictests back which pass without the optimizer changes, the other two remain in sql_integration.rs because they depend on the optimizer change.

duongcongtoai · 2026-02-05T01:34:51Z

datafusion/expr/src/logical_plan/invariants.rs

                        // TODO revisit this validation logic
                        plan_err!(
-                            "Correlated scalar subquery in the GROUP BY clause must also be in the aggregate expressions"
+                            "Correlated scalar subquery in the GROUP BY clause must \


nit: this change is unrelated (it's in my original PR)

This is from fmt.

mkleen · 2026-02-05T08:00:03Z

@duongcongtoai Thank you for your feedback!

alamb

Thank you @mkleen and @duongcongtoai

I think this looks pretty good to me -- I have some small suggestions for API improvements, but the basic idea is 🏆

It would also be nice to restore one or two slt tests to make sure this code is connected end to end

alamb · 2026-02-05T22:55:09Z

datafusion/sql/tests/sql_integration.rs

 fn table_with_column_alias() {
-    let sql = "SELECT a, b, c
-                   FROM lineitem l (a, b, c)";
+    let sql = "SELECT a, b, c, d, e


I agree we should have some sqllogictest coverage to make sure everything is hooked up right

alamb · 2026-02-05T22:55:40Z

datafusion/sql/src/planner.rs

-    /// The query schema of the outer query plan, used to resolve the columns in subquery
-    outer_query_schema: Option<DFSchemaRef>,
+
+    /// The queries schemas of outer query relations, used to resolve the outer referenced


datafusion/sql/src/planner.rs

datafusion/sql/src/expr/identifier.rs

mkleen · 2026-02-06T07:34:19Z

@alamb Thank you for the review. I will add your feedback soon!

alamb · 2026-02-06T13:16:11Z

Thanks @mkleen -- sorry for the delay in reviewing this

mkleen · 2026-02-07T19:36:43Z

@alamb @duongcongtoai Could you please do one more review?

This is the interesting change 818926d

github-actions bot added sql SQL Planner logical-expr Logical plan and expressions sqllogictest SQL Logic Tests (.slt) and removed sqllogictest SQL Logic Tests (.slt) labels Jan 21, 2026

mkleen changed the title ~~Plann recursive subquery cleanup~~ feat: Support planning subqueries with OuterReferenceColumn belongs to non-adjacent outer relations Cleanup Jan 21, 2026

mkleen commented Jan 21, 2026

View reviewed changes

mkleen mentioned this pull request Jan 22, 2026

Fix trailing whitespace in CROSS JOIN logical plan formatting #19936

Merged

mkleen changed the title ~~feat: Support planning subqueries with OuterReferenceColumn belongs to non-adjacent outer relations Cleanup~~ feat: Support planning subqueries with OuterReferenceColumn belongs to non-adjacent outer relations Jan 23, 2026

mkleen force-pushed the plann-recursive-subquery-cleanup branch from 99902bc to 3b1fa64 Compare January 23, 2026 08:51

mkleen force-pushed the plann-recursive-subquery-cleanup branch from af87772 to cd81a28 Compare January 26, 2026 08:52

alamb mentioned this pull request Feb 2, 2026

Andrew Lamb Weekly-ish Open Source plan - 2026-02-01 #20108

Open

10 tasks

duongcongtoai reviewed Feb 5, 2026

View reviewed changes

duongcongtoai approved these changes Feb 5, 2026

View reviewed changes

alamb approved these changes Feb 5, 2026

View reviewed changes

mkleen force-pushed the plann-recursive-subquery-cleanup branch from 8899f47 to 5328de1 Compare February 7, 2026 13:11

github-actions bot added the sqllogictest SQL Logic Tests (.slt) label Feb 7, 2026

duongcongtoai added 4 commits February 7, 2026 14:40

fix: allow OuterRefColumn for non-adjacent outer relation

5dec3b9

fix: accidentally pushdown filter with subquery

af21609

chore: clippy

3247d31

chore: rm debug details

3fbb9ba

duongcongtoai and others added 19 commits February 7, 2026 14:40

fix: breaking changes

c6d570c

fix: lateral join losing its outer ref columns

c4cd3f7

test: more test case for other decorrelation

3d924af

doc: better comments

df34dc7

fix: test

68b423f

Remove outer_query_schema from planner

a83e4d2

Add new tests

73e9387

fixup! Add new tests

6144277

fixup! fixup! Add new tests

193be03

Fix fmt

4dbb62d

Remove test from joins.slt

0e77c16

Remove whitespace in CROSS JOIN output

75a6ebb

Return references in planner

ed7b5d9

Add sql logic tests back

cc9ee37

Simplify loop logic

818926d

Fix fmt

8a228bc

Remove redundant tests for sql integrations

20583c5

fixup! Remove redundant tests for sql integrations

510838a

fixup! fixup! Remove redundant tests for sql integrations

42170bf

mkleen force-pushed the plann-recursive-subquery-cleanup branch from 9ba50c7 to 42170bf Compare February 7, 2026 13:40

mkleen added 3 commits February 7, 2026 14:55

fixup! fixup! fixup! Remove redundant tests for sql integrations

885c337

Fix fmt

d094f13

Fix clippy

14aac86

mkleen requested review from alamb and duongcongtoai February 7, 2026 19:35

Conversation

mkleen commented Jan 21, 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!

mkleen Jan 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

mkleen Jan 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

mkleen commented Jan 26, 2026

Uh oh!

duongcongtoai commented Jan 28, 2026

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

mkleen Feb 7, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

mkleen commented Feb 5, 2026

Uh oh!

alamb left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

mkleen commented Feb 6, 2026

Uh oh!

alamb commented Feb 6, 2026

Uh oh!

mkleen commented Feb 7, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

mkleen commented Jan 21, 2026 •

edited

Loading

mkleen Jan 21, 2026 •

edited

Loading

mkleen Jan 21, 2026 •

edited

Loading

mkleen Feb 7, 2026 •

edited

Loading

mkleen commented Feb 7, 2026 •

edited

Loading