Skip to content

leftJoin + where isUndefined on right field filters out relation but not the row #685

@john-rtr

Description

@john-rtr
  • I've validated the bug against the latest version of DB packages

Describe the bug
When using a left join, applying a where predicate on a right-side field with isUndefined(right?.payload) removes the right side only (sets right to undefined) but still returns the left rows. The filter does not exclude the entire joined row.

To Reproduce
Steps to reproduce the behavior:

  1. Create two collections:
it('left join with isUndefined filtering on right-side field', () => {
  const leftSchema = z.object({ id: z.string(), rightId: z.string().nullable() })
  const rightSchema = z.object({ id: z.string(), payload: z.string().nullish() })

  const left = createCollection(
    localOnlyCollectionOptions({
      id: 'left',
      getKey: (item: { id: string }) => item.id,
      schema: leftSchema,
      initialData: [
        { id: 'l1', rightId: 'r1' },
        { id: 'l2', rightId: 'r2' },
        { id: 'l3', rightId: 'r3' },
        { id: 'l4', rightId: null }
      ]
    })
  )

  const right = createCollection(
    localOnlyCollectionOptions({
      id: 'right',
      getKey: (item: { id: string }) => item.id,
      schema: rightSchema,
      initialData: [
        { id: 'r1', payload: 'ok' },
        { id: 'r2', payload: null },
        { id: 'r3', payload: undefined }
      ]
    })
  )

  const lq = createLiveQueryCollection({
    startSync: true,
    query: (q) =>
      q
        .from({ l: left })
        .leftJoin({ r: right }, ({ l, r }) => eq(l.rightId, r.id))
        .where(({ r }) => isUndefined(r?.payload))
        .select(({ l, r }) => ({ leftId: l.id, right: r }))
  })

  const data = lq.toArray

  expect(data.sort((a, b) => a.leftId.localeCompare(b.leftId))).toEqual([
    { leftId: 'l3', right: { id: 'r3', payload: undefined } },
    { leftId: 'l4', right: undefined }
  ])
})

Current results:

[
  { leftId: 'l1', right: undefined },
  { leftId: 'l2', right: undefined },
  { leftId: 'l3', right: { id: 'r3', payload: undefined } },
  { leftId: 'l4', right: undefined }
]

Expected behavior
The where(({ r }) => isUndefined(r?.payload)) predicate should filter rows at the joined row level. Given the data above, the expected result should only include rows where the right side is either missing (no match in left join) or where the right payload is actually undefined:

[
  { leftId: 'l3', right: { id: 'r3', payload: undefined } },
  { leftId: 'l4', right: undefined }
]

Rows l1 (payload 'ok') and l2 (payload null) should not be returned at all.

Desktop (please complete the following information):

  • OS: macOS 15.0
  • Runtime: Vitest / Node (unit test)
  • Browser: N/A

Additional context

A workaround that behaves as expected is to explicitly include the case where the right side is missing:

.where(({ r }) => or(isUndefined(r), isUndefined(r?.payload)))

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions