orderBy with nulls on an optional field regression.

[thruflo]

I have a component that's ordering with nulls first on an optional inserted_at field:

const { data: threads } = useLiveQuery(
  (query) =>
    query
      .from({ thread: threadCollection })
      .innerJoin(
        { membership: membershipCollection },
        ({ thread, membership }) => eq(thread.id, membership.thread_id)
      )
      .orderBy(({ thread }) => thread.inserted_at, {
        direction: 'desc',
        nulls: 'first',
      })
      .select(({ thread }) => ({
        id: thread.id,
        name: thread.name,
      }))
      .where(({ membership }) => eq(membership.user_id, currentUserId)),
  [currentUserId]
)

console.log('threads', threads)

This was working fine at this commit of this now merged PR 5f070a0 (I was developing against this as a local workspace dependency). By "worked" I mean I could insert a new thread with a membership locally, have the optimistic state without the inserted_at shown first and then when the write synced back, it updated the row with the correct inserted_at and still sorted first. No flickering, no issues.

Since upgrading to version 0.1.3, the behaviour has regressed. Specifically, I insert locally and it's fine but then when the write syncs back and the optimistic state is removed, my collection seems to lose the previous thread (which was synced data) to just leave the newly inserted thread in the results:

If I refresh the page, I get the correct synced data. If I switch the field to a non-optional field like thread.name it works fine (i.e.: .orderBy(({ thread }) => thread.name, { direction: 'desc', nulls: 'first' }) works fine.

I wonder if it's possibly related to either:

• this subsequent commit in the same PR d13378f
• this subsequent PR Modify topK and orderBy to track by key instead of by object reference #405

[thruflo]

Ok, weirdly, this two step workaround works:

const { collection: threadResults } = useLiveQuery(
  (query) => (
    query
      .from({ thread: threadCollection })
      .innerJoin(
        { membership: membershipCollection },
        ({ thread, membership }) => eq(thread.id, membership.thread_id)
      )
      .select(({ thread }) => ({
        id: thread.id,
        name: thread.name,
        inserted_at: thread.inserted_at
      }))
      .where(({ membership }) => eq(membership.user_id, currentUserId))
  ),
  [currentUserId]
)

const { data: threads } = useLiveQuery(
  (query) => (
    query
      .from({ result: threadResults })
      .orderBy(({ result }) => result.inserted_at, {
        direction: 'desc',
        nulls: 'first',
      })
      .select(({ result }) => ({
        id: result.id,
        name: result.name,
      }))
  ),
  [threadResults]
)

I.e.: materialise a collection selecting inserted_at: thread.inserted_at and its fine to sort on the potentially undefined inserted_at value.

(Sharing as an indication of what's going on, not as a workable solution).

[kevin-dp]

@thruflo Could you try 2 things to see if any of those make the problem go away:

1. run it without auto indexing
2. run it without inner join

@samwillis and I tried reproducing this bug but we were unable to.