Skip to content

unparser: a Limit that is a join input escapes onto the enclosing query without a projection above, and its derived table has no alias with one #14375

Closed

Description

@grokspice

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::Limit arm derives when select.already_projected(), and otherwise asks row_limit_needs_own_scope, which answers select.has_selection(). That test was written for a Filter above a Limit in one scope (WHERE runs before LIMIT), not for a join input, whose LIMIT has no faithful home in the enclosing query at all. A join input that is a Limit should be derived unconditionally, the way a scan's fetch already is (derive_join_side).
  • The derived table is built with derive_with_dialect_alias(DERIVED_LIMIT_ALIAS, …), which attaches an alias only when the dialect requires_derived_table_alias(); and even with one, the alias would be derived_limit, while the references above still say c. derive_join_side solves 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

  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#234 (base spiceai-54): a Limit that 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.id for the issue's bare plan, where origin/spiceai-54 emits FROM 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 its SELECT 1 FROM b LIMIT n count scope.

    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 / #14373 when their fork PRs merge).

  3. grokspice commented on Oct 3, 2026

    @grokspice
    ContributorAuthor

    A sibling of this issue that spiceai/datafusion#234 does not cover: a Sort carrying its own fetch as a join input. #234 handles a Limit node in that position; a Sort { fetch } there still puts its LIMIT on the enclosing query, so it bounds the join's output instead of that input.

    Run on #234's head, c519f7e70, with a probe in datafusion/sql/tests/cases/plan_to_sql.rs:

    let right = table_scan(Some("u"), &int32_schema(&["a"]), Some(vec![0]))?
        .sort_with_limit(vec![col("u.a").sort(true, true)], Some(2))?
        .build()?;
    let plan = table_scan(Some("t"), &int32_schema(&["a"]), Some(vec![0]))?
        .join_on(right, JoinType::Inner, vec![col("t.a").eq(col("u.a"))])?
        .build()?;
    $ cargo test -q -p datafusion-sql --test sql_integration -- probe_fetched_sort_join_input --nocapture
    F1-on-234: Ok("SELECT t.a, u.a FROM t INNER JOIN u ON (t.a = u.a) ORDER BY u.a ASC NULLS FIRST LIMIT 2")
    F2-on-234: Err(NotImplemented("Unparsing a row limit on a join input that is not a single table scan is not supported without a projection above the join"))
    

    F1 is the fetched sort. The plan joins t with the two rows of u that have the smallest a, but the SQL keeps two rows of the join. F2 is the same plan written as Sort then Limit, which #234 refuses. The SQL above was not run against an engine; the wrong rows follow from where its LIMIT sits. This issue stays open for the Sort { fetch } form, since #234 is refs.

  4. added a commit that references this issue on Oct 8, 2026
  5. 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