bug: nested selected link shapes miss recursive SQLite joins

[dodok8]

Summary

Selecting nested link shapes deeper than one link can produce SQL that references a nested alias without adding the corresponding JOIN.

The issue appears in the SQLite query planning layer for selected result shapes. Root-level selected links get planned as joins, but selected links inside an already selected child shape are not recursively converted into selected-link joins.

Reproduction

Using a schema with a path such as:

type Article {
  required link space: Space
}

type Space {
  required link owner: User
}

type User {
  required email: str
}

A query like this should require joins for both Article.space and Space.owner:

select Article {
  space: {
    owner: {
      email
    }
  }
}

Expected join shape:

article -> space -> user(owner)

Observed planner behavior during example verification: generated SELECT values can reference the nested alias, for example "owner"."email", while the generated FROM/JOIN section only includes the root-level selected link join. That makes the SQL invalid or semantically incomplete for deeper selected shapes.

Likely cause

engine/sqlite-query-plan/src/lib.rs currently builds selected-link joins from only the root shape fields:

let mut joins: Vec<SQLiteJoin> = ir
    .shape()
    .fields()
    .into_iter()
    .filter(|field| field.child_shape().is_some())
    .map(SQLiteJoin::selected_single_link)
    .collect();

Meanwhile plan_shape_values recursively descends into child shapes and can emit selected values for nested child aliases. The selected values and join list can therefore get out of sync.

Expected behavior

• Selected single-link shapes should recursively produce SQLite joins for every selected nested link level.
• The join source alias for a nested selected link should be the parent selected link alias, not always root.
• Join kind should continue to respect required vs optional cardinality, including optional ancestors.
• Result-shaping metadata and selected value aliases should remain consistent with the generated joins.

Scope

• engine/sqlite-query-plan: recursively plan selected single-link joins from result shapes.
• engine/sqlite-query-sqlgen: should not need semantic changes if the structured plan is correct.
• Tests should cover a selected shape at least two links deep, including the generated join aliases.
• A cross-crate query pipeline test should compile or execute a query with a two-level nested selected shape.

Boundaries and non-goals

• This issue does not add multi-link result shaping.
• This issue does not add follow-up fetch plans.
• This issue should not move selected-shape semantics into SQL generation.
• Parser and resolver support for nested shapes already exists; the bug is in SQLite planning/lowering.

Acceptance criteria

• A query shaped like Article { space: { owner: { email } } } produces SQL with joins for both space and owner.
• The nested selected link join uses the parent alias as its source alias.
• Required nested links use INNER JOIN; optional nested links preserve parent rows with LEFT JOIN where needed.
• Existing one-level selected link queries continue to render the same or equivalent SQL.
• cargo test --workspace passes.

Checks

• I searched existing issues for the same bug.
• I checked the relevant planner implementation while documenting the issue.