Saved query-backed joins are lost, causing incorrect filtered totals #507

Problem

A saved query-backed model accepts and persists a declared join, but querying that model drops the relationship during planning. A joined business-segment filter can then succeed with an incorrect total instead of filtering the related rows.

With the existing five-order fixture, customers.tier = 'gold' returns 150 instead of 120, including the silver customer's 30. Declaring the relationship only at query-extension level over an otherwise identical model returns the correct 120. This is reproducible with SQLite and DuckDB.

Reproduction

From the repository root after the documented development setup (poetry install -E all --with dev), run:

SLAYER_USAGE_REPORTING=off poetry run python - <<'PY'
import asyncio
from tests._dev1902_fixtures import _seed_sqlite, renamed_graph
from tests._engine_helpers import seeded_exec_engine
from tests.test_dev1902_join_key_spelling import _customer_totals

async def main():
    async with seeded_exec_engine(
        dialect='sqlite', seed=_seed_sqlite, models=renamed_graph()
    ) as (engine, _):
        model = await engine.save_model(_customer_totals())
        stored = await engine.storage.get_model('customer_totals', data_source='test')
        assert stored is not None and len(stored.joins) == 1
        query = {
            'source_model': 'customer_totals',
            'measures': [{'formula': 'sum(amt)', 'name': 'total'}],
            'filters': ["customers.tier = 'gold'"],
        }
        actual = await engine.execute(query=query)
        print('stored join:', actual.data)
        print(actual.sql)
        assert actual.data == [{'customer_totals.total': 120.0}]

asyncio.run(main())
PY

The fixture has three customers: gold customers 1 and 3 have order totals 30 and 90; silver customer 2 has 30. To reproduce on DuckDB, import _seed_duckdb, select dialect='duckdb' and use that seed.

Expected and actual results

Expected: the saved relationship correlates the filter to each customer, returning 120 (the same result as an equivalent extension-only relationship).

Actual: the stored-join query successfully returns 150 with a semi_join_pushed advisory. Generated SQL checks only whether any gold customer exists:

WHERE EXISTS (
  SELECT 1 FROM customers AS customers
  WHERE customers.tier = 'gold'
)

The predicate does not correlate customer_totals.customer_id with customers.customer_pk. Grouping the joined customer-credit aggregate by customer also returns the global credit total 600 for every customer instead of 100, 200 and 300.

Context and likely boundary

• Confirmed on current main at 56006b688929847c9cc097a45c1cabbe88716703 (package version 1.4.0); the original audit also reproduced on 1.3.0-era main and both SQLite/DuckDB, twice each.
• slayer/engine/bundle_builder.py::_split_query_backed clears stored joins. The stage-planning/materialization helpers subsequently reconstruct a flat relation without those relationships.
• DEV-1902: join keys name columns logically; physical spelling applied once #426 already records outbound traversal as the known DEV-1918 gap and leaves TestQueryBackedJoinKeys.test_join_on_backing_output_column_executes as a strict xfail. This issue highlights the successful wrong-result case, rather than claiming the older traversal gap is new.
• DEV-1966: query-local stage names; splice query-backed models into the consuming statement #441 added query-backed splicing; Combine models and stages that have no join path: aggregates across a product hop #496 intentionally permits genuinely unrelated datasets. Neither preserves this declared relationship through the consuming relation.

A focused correction should preserve the saved relation's own joins through root and joined/sibling consumption, while keeping ordinary query-stage schemas flat and avoiding inheritance of the backing source's joins. Existing cycle, private-stage-name and extension behavior should remain intact.

No persisted format change or new dependency appears necessary. I intend to follow with a focused fix and SQLite/DuckDB regression tests.