Repository navigation
Description
What happens
A Limit that is a join input is only given a scope of its own when the enclosing SELECT already carries a WHERE or a projection. Without either, the input's LIMIT lands on the enclosing query, bounding the join's output instead of one input's contribution to it. And when it is derived, the derived table gets no alias on the default dialect, so the join's ON and the projection reference a relation that is no longer in scope.
Reproduced on spiceai-54 (11624fb82), cargo test -p datafusion-sql:
let b = table_scan(Some("b"), &schema, Some(vec![0]))?.build()?;
let c = table_scan(Some("c"), &schema, Some(vec![0]))?.build()?;
let limited_c = LogicalPlanBuilder::from(c).limit(0, Some(1))?.build()?;
let bare = LogicalPlanBuilder::from(b).join(limited_c, JoinType::Full, (vec!["b.id"], vec!["c.id"]), None)?.build()?;
let projected = LogicalPlanBuilder::from(bare.clone()).project(vec![col("b.id"), col("c.id")])?.build()?;| plan | emitted SQL |
|---|---|
bare — no projection above the join |
SELECT b.id, c.id FROM b FULL JOIN c ON b.id = c.id LIMIT 1 |
projected |
SELECT b.id, id FROM b FULL JOIN (SELECT c.id FROM c LIMIT 1) ON b.id = c.id |
The first bounds the whole FULL JOIN to one row where the plan bounds only c's side: with b = {1, 2} and c = {1} the plan returns two rows and the SQL returns one. The second scopes the limit but names the derived table nothing, so c.id in ON and the bare id in the select list do not bind to it (PostgreSQL: subquery in FROM must have an alias; engines that accept an unaliased derived table still cannot resolve c.id).
Where
datafusion/sql/src/unparser/plan.rs:
- The
LogicalPlan::Limitarm derives whenselect.already_projected(), and otherwise asksrow_limit_needs_own_scope, which answersselect.has_selection(). That test was written for aFilterabove aLimitin one scope (WHEREruns beforeLIMIT), not for a join input, whoseLIMIThas no faithful home in the enclosing query at all. A join input that is aLimitshould be derived unconditionally, the way a scan'sfetchalready is (derive_join_side). - The derived table is built with
derive_with_dialect_alias(DERIVED_LIMIT_ALIAS, …), which attaches an alias only when the dialectrequires_derived_table_alias(); and even with one, the alias would bederived_limit, while the references above still sayc.derive_join_sidesolves both for a scan by aliasing the derived table with the scan's own name; the same is needed here.
Reach
An optimized plan normally has a Projection at the top, so the un-projected shape is reachable from hand-built or rewritten plans rather than from user SQL; the unaliased derived table is reachable from any plan with a Limit join input on a dialect that does not require derived-table aliases.
Provenance
Raised by an adversarial review (codex) of the fix for #12593 (spiceai/datafusion, branch fix/12593-full-join-complex-input-scope) while it was checking whether scoping a nested scan filter (which removes a WHERE from the enclosing SELECT) could let a sibling Limit input escape. It can, and the reason is that the WHERE was never the right signal: the shape above has no filter anywhere and escapes on spiceai-54 unchanged.
Activity
grokspice commentedon Sep 25, 2026
Fix is up on the fork as spiceai/datafusion#234 (base
spiceai-54): aLimitthat is a join input is derived unconditionally, under the scan's own name, with the scan's columns as that side's select items —SELECT b.id, c.id FROM b FULL JOIN (SELECT c.id FROM c LIMIT 1) AS c ON b.id = c.idfor the issue's bare plan, whereorigin/spiceai-54emitsFROM b FULL JOIN c ON b.id = c.id LIMIT 1. A limit over anything but one scan keeps the dialect-alias derived table when a projection is above the join and is refused otherwise; an empty-projection limited scan keeps itsSELECT 1 FROM b LIMIT ncount scope.This issue closes with the
spiceai/spiceaiPR that moves thedatafusionpin past that commit and adds thedocs/dev/fork_patches.mdrow (together with #12593 / #13493 / #14373 when their fork PRs merge).grokspice commentedon Oct 3, 2026
A sibling of this issue that spiceai/datafusion#234 does not cover: a
Sortcarrying its ownfetchas a join input. #234 handles aLimitnode in that position; aSort { fetch }there still puts itsLIMITon the enclosing query, so it bounds the join's output instead of that input.Run on #234's head,
c519f7e70, with a probe indatafusion/sql/tests/cases/plan_to_sql.rs:F1is the fetched sort. The plan joinstwith the two rows ofuthat have the smallesta, but the SQL keeps two rows of the join.F2is the same plan written asSortthenLimit, which #234 refuses. The SQL above was not run against an engine; the wrong rows follow from where itsLIMITsits. This issue stays open for theSort { fetch }form, since #234 isrefs.fix(unparser): derive a Limit that is a join input under the scan's o…
chore: pin the fork at spiceai/datafusion#249 and guard the DataFusio…
chore: pin the fork at spiceai/datafusion#249 and guard the DataFusio…