Repository navigation
Description
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
grokspice commentedon Sep 25, 2026
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 outerFROM, and the dangling-qualifier check learns the sources inside it. Reproduced with the issue's plan as a regression test: onorigin/spiceai-54it emitsFROM 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/spiceaiPR that moves thedatafusionpin past that commit and adds thedocs/dev/fork_patches.mdrow (together with #12593 / #13493 when their fork PRs merge).fix(unparser): keep a join that is another join's right input on that…
chore: pin the fork at spiceai/datafusion#249 and guard the DataFusio…
chore: pin the fork at spiceai/datafusion#249 and guard the DataFusio…