Collections in outer joins with WHERE that checks for emptiness get filtered before join

[duailibe]

Problem

Given this query:

q.from({ user: usersCollection })
  .leftJoin(({ special: specialUsersCollection}) => eq(users.id, special.id))
  .where(({ special }) => isUndefined(special))

The result is all rows from usersCollection. What happens here is the isUndefined is pushed to a subquery on specialUsersCollection, which evaluates as false to all rows, so the resulting query is simply the original usersCollection

Investigation

Tried debugging and it seems the problem is in

db/packages/db/src/query/optimizer.ts

Lines 615 to 625 in 6ef3e60

	// Optimize JOIN clauses and track what was optimized
	const optimizedJoins = query.join
	? query.join.map((joinClause) => ({
	...joinClause,
	from: optimizeFromWithTracking(
	joinClause.from,
	groupedClauses.singleSource,
	actuallyOptimized
	),
	}))
	: undefined

There's a special case that the join is an outer join + the where clause is filtering on the "emptiness" of the joined collection.

Funny enough there are tests that assert this doesn't happen

db/packages/db/tests/query/optimizer.test.ts

Lines 1424 to 1427 in 6ef3e60

	describe(`JOIN semantics preservation`, () => {
	test(`should preserve WHERE clause semantics when pushing down to LEFT JOIN`, () => {
	// This test reproduces the bug where pushing WHERE clauses into LEFT JOIN subqueries
	// changes the semantics by filtering out null values that should remain

But that's not what I see in practice.

Reproduction

I added this test to packages/db/tests/query/join.test.ts:

test('left join without other', () => {
  const usersCollection = createUsersCollection()
  const specialUsersCollection = createCollection(
    mockSyncCollectionOptions({
      id: `special-users`,
      getKey: (user) => user.id,
      initialData: [{ id: 1, special: true }],
    })
  )

  const joinQuery = createLiveQueryCollection({
    startSync: true,
    query: (q) =>
      q
        .from({ user: usersCollection })
        .leftJoin(
          { special: specialUsersCollection },
          ({ user, special }) => eq(user.id, special.id),
        )
        .where(({ special }) => isUndefined(special)),
  })

  for (const row of joinQuery.toArray) {
    expect(row.special).toBeUndefined()
  }

  expect(joinQuery.size).toBe(3)
});

[samwillis]

Ah, thanks @duailibe, thats an interesting edge case.

I think the solution is to only push down predicates that match a property of a joined collection, rather than the namespace of the joined collection itself. So isUndefined(special.id) can be pushed down, but isUndefined(special) cannot.

Should be a fairly easy change.