Outer `where` on an inner-joined subquery source is ignored (matches every row)

The bug is in the query builder's join handling when the source is a subquery. Start by examining the join logic in the query builder files, likely in packages/db-core/src/query/query.ts or similar. The repro script shows the exact failing case: inner join with a subquery source. Look at how the where clause is applied to joined aliases and compare the handling for collection vs subquery sources. Run the repro script to confirm the fix works for all eight combinations.

• I've validated the bug against the latest version of DB packages (@tanstack/db@0.9.2, currently latest)

Describe the bug

When a live query inner-joins a subquery (a new Query().from(...).where(...) used as the join source), a where on the joined alias in the outer query is ignored. The outer query returns every joined row, as if the where weren't there.

• The subquery's own where is still applied. Only the outer where on the joined alias is lost.
• Every neighbouring shape works:
  • joining the bare collection instead of a subquery
  • a left join to the same subquery
  • a where on the primary alias
• The result is the same with autoIndex: 'off' and with autoIndex: 'eager' + BasicIndex.

To Reproduce

npm i @tanstack/db@0.9.2, then node repro.mjs:

// repro.mjs
import {
  BasicIndex,
  createCollection,
  createLiveQueryCollection,
  eq,
  isNull,
  localOnlyCollectionOptions,
  Query,
} from '@tanstack/db'

function collections(autoIndex) {
  const index = autoIndex === 'eager' ? { autoIndex, defaultIndexType: BasicIndex } : { autoIndex }
  const posts = createCollection(
    localOnlyCollectionOptions({
      getKey: (r) => r.id,
      ...index,
      initialData: [
        { id: 'p1', authorId: 'a1' },
        { id: 'p2', authorId: 'a2' },
      ],
    }),
  )
  const authors = createCollection(
    localOnlyCollectionOptions({
      getKey: (r) => r.id,
      ...index,
      initialData: [
        { id: 'a1', name: 'Alice', deletedAt: null },
        { id: 'a2', name: 'Bob', deletedAt: null },
      ],
    }),
  )
  return { posts, authors }
}

async function run(label, { autoIndex, type, source }) {
  const { posts, authors } = collections(autoIndex)
  const joined =
    source === 'subquery'
      ? new Query().from({ author: authors }).where(({ author }) => isNull(author.deletedAt))
      : authors
  const q = createLiveQueryCollection((q) =>
    q
      .from({ post: posts })
      .join({ author: joined }, ({ post, author }) => eq(author.id, post.authorId), type)
      // Outer where on the joined alias: only Bob's post should remain.
      .where(({ author }) => eq(author.name, 'Bob'))
      .select(({ post }) => ({ id: post.id })),
  )
  await q.preload()
  const ids = q.toArray.map((r) => r.id).sort()
  const ok = JSON.stringify(ids) === JSON.stringify(['p2'])
  console.log(`${ok ? 'ok  ' : 'FAIL'} ${label.padEnd(44)} -> ${JSON.stringify(ids)}`)
}

for (const autoIndex of ['off', 'eager']) {
  for (const type of ['inner', 'left']) {
    for (const source of ['collection', 'subquery']) {
      await run(`${type} join, ${source} source, autoIndex ${autoIndex}`, { autoIndex, type, source })
    }
  }
}

Output (the "Join requires an index" warnings for the autoIndex: 'off' runs are omitted):

ok   inner join, collection source, autoIndex off -> ["p2"]
FAIL inner join, subquery source, autoIndex off   -> ["p1","p2"]
ok   left join, collection source, autoIndex off  -> ["p2"]
ok   left join, subquery source, autoIndex off    -> ["p2"]
ok   inner join, collection source, autoIndex eager -> ["p2"]
FAIL inner join, subquery source, autoIndex eager -> ["p1","p2"]
ok   left join, collection source, autoIndex eager -> ["p2"]
ok   left join, subquery source, autoIndex eager  -> ["p2"]

The .innerJoin() shorthand behaves the same way. In the variant below Bob is soft-deleted, so the subquery excludes him. The outer where asks for Bob, so the result should be empty, but Alice's post comes back: the subquery's where was applied and the outer one was not.

const sub = new Query().from({ author: authors }).where(({ author }) => isNull(author.deletedAt))
q.from({ post: posts })
  .innerJoin({ author: sub }, ({ post, author }) => eq(author.id, post.authorId))
  .where(({ author }) => eq(author.name, 'Bob')) // Bob has deletedAt set
// expected [], got ["p1"]

Expected behavior

A where on an inner-joined alias narrows the result the same way whether the join source is a collection or a subquery. Here that means ["p2"], as the other seven combinations return.

Screenshots

N/A. The script output above shows it.

Desktop (please complete the following information):

• OS: macOS 27.0
• Runtime: Node.js v24.16.0 (no browser needed)
• Version: @tanstack/db@0.9.2

Additional context

We first saw this in a React app (@tanstack/react-db + query-db-collection): a table filter on a joined row "didn't apply until a page reload". The join to the joined table was a subquery because it filters out soft-deleted rows. After a reload the collections held only the server's already-filtered rows, which hid the bug.

Our workaround: for inner joins, join the bare collection and apply the subquery's conditions as an outer where. For an inner join that's equivalent, and it filters correctly.

The only other issues I found about subquery join sources don't match this shape. #1590 (left join onto a subquery that itself contains a join) and #1709 (join inside a correlated include) are both closed.