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

[jheffernan-il]

• 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.

join	source	autoIndex	result
inner	collection	off / eager	✅ ["p2"]
inner	subquery	off / eager	❌ ["p1","p2"]
left	collection	off / eager	✅ ["p2"]
left	subquery	off / eager	✅ ["p2"]

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.

[84dnnvbdvp-debug]

I think the current source has a concrete optimizer/cache-identity seam that explains this whole matrix.

For the inner-join + subquery case, predicate pushdown moves the single-source outer where(author.name === 'Bob') into the existing joined QueryRef. applyOptimizations() then marks author as optimized and, because this is not an outer join, removes that predicate from the outer query.

But during compilation, mapNestedQueries() maps that optimized joined subquery back to the original joined subquery for caching, and processJoinSource() does:

const originalQuery = queryMapping.get(from.query) || from.query
const subQueryResult = onCompileSubquery(originalQuery, ...)

So the recursive compile receives the original user subquery, not the optimized subquery that now contains the pushed outer predicate. That leaves the original deletedAt filter intact while dropping the pushed name === 'Bob' filter.

This also lines up with the neighboring controls: a bare collection gets an optimizer-created QueryRef that is not mapped back to an original user QueryRef, while a LEFT join keeps the joined-side predicate out of this pushdown path because that alias is nullable/residual.

A regression I’d add is specifically that optimizer-added predicates on an existing joined QueryRef survive the original-query/cache identity path into recursive compilation. The repair does not necessarily need to abandon original-query identity for caching; the important invariant is that using the original as a cache key must not substitute away the optimized IR being compiled.