Skip to content

unparser: a join that is the right input of a join is linearised into the outer FROM in the wrong order #14373

Closed

Description

@grokspice

What happens

The unparser linearises a join that is the right input of another join into the enclosing FROM in the wrong order. The nested join's own right side is appended to the outer FROM before the relation it is joined to, so its ON names a relation that is not yet in scope, and the join order the SQL states is not the plan's.

Reproduced on spiceai-54 (11624fb82), cargo test -p datafusion-sql:

let a = table_scan(Some("a"), &schema, Some(vec![0]))?.build()?;
let b = table_scan_with_filters(Some("b"), &schema, Some(vec![0]), vec![col("b.id").eq(lit("x"))])?.build()?;
let c = table_scan(Some("c"), &schema, Some(vec![0]))?.build()?;
let inner = LogicalPlanBuilder::from(b).join(c, JoinType::Inner, (vec!["b.id"], vec!["c.id"]), None)?.build()?;
let plan = LogicalPlanBuilder::from(a).join(inner, join_type, (vec!["a.id"], vec!["b.id"]), None)?.build()?;
eprintln!("{}", plan_to_sql(&plan)?);
join_type emitted SQL
Full SELECT a.id, b.id, c.id FROM a INNER JOIN c ON b.id = c.id FULL JOIN b ON a.id = b.id WHERE (b.id = 'x')
Inner SELECT a.id, b.id, c.id FROM a INNER JOIN c ON b.id = c.id INNER JOIN b ON a.id = b.id WHERE (b.id = 'x')

The plan is a ⋈ (b ⋈ c). The SQL says (a ⋈ c ON b.id = c.id) ⋈ b, with b unbound at the point its column is referenced. PostgreSQL and DuckDB reject the statement (missing FROM-clause entry for table "b"); an engine that resolves the reference lazily runs a different join tree.

Where

datafusion/sql/src/unparser/plan.rs, the LogicalPlan::Join arm of select_to_sql_recursively_inner. The nested join is walked with the enclosing join's right_relation as its relation, so its left side lands there correctly, but its right side is attached by

let mut from = select.pop_from().unwrap();
from.push_join(ast_join);
select.push_from(from);

which pops the shared SelectBuilder's last FROM entry — the enclosing join's left relation — rather than the relation the nested join was handed. The enclosing join then pushes its own JOIN b after it.

Reach

The SQL planner wraps a parenthesised join (FROM a JOIN (b JOIN c ON …) ON …) in a SubqueryAlias only when the user aliases it, so an unaliased nested join, and any right-deep join tree an optimizer rewrite produces, reaches this arm. Left-deep trees, which is what the planner produces for a flat FROM a JOIN b JOIN c, are unaffected.

Not the same defect as #12593

Found while fixing #12593 (a FULL JOIN input's filters leaking into WHERE); that fix scopes the filters correctly for a left-nested input and does not touch this ordering. The right-nested shape is wrong on both branches and for every join type, so it is filed on its own rather than folded in.

Expected

SELECT a.id, b.id, c.id FROM a FULL JOIN (SELECT … FROM b … INNER JOIN c ON b.id = c.id) AS … ON a.id = b.id — or, where the dialect can bind it, FROM a FULL JOIN (b INNER JOIN c ON b.id = c.id) ON a.id = b.id. Either way the nested join stays on the right of the outer join, with b in scope where it is referenced.

Activity

  1. self-assigned this
    on Sep 25, 2026
  2. grokspice commented on Sep 25, 2026

    @grokspice
    ContributorAuthor

    Fix is up on the fork as spiceai/datafusion#233 (base spiceai-54): a join that is another join's right input is now emitted as a parenthesised joined table on that join's right — FROM a INNER JOIN (b INNER JOIN c ON b.id = c.id) ON a.id = b.id — instead of being linearised into the outer FROM, and the dangling-qualifier check learns the sources inside it. Reproduced with the issue's plan as a regression test: on origin/spiceai-54 it emits FROM a INNER JOIN c ON b.id = c.id INNER JOIN b ON a.id = b.id; on the branch the shape above. The FULL JOIN variant with a filtered nested scan depends on spiceai/datafusion#231's scoping and is left to that PR.

    This issue closes with the spiceai/spiceai PR that moves the datafusion pin past that commit and adds the docs/dev/fork_patches.md row (together with #12593 / #13493 when their fork PRs merge).

  3. added a commit that references this issue on Oct 8, 2026
  4. added a commit that references this issue on Oct 8, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions