supabase / supabase-flutter / 37468012283

92%

push

github

fix(supabase): sort null values in an ordered stream the way the database does (#1918)

## Summary

An ordered `stream()` stops sorting correctly as soon as one row holds
`null` in the order column.

```dart
supabase.from('todos').stream(primaryKey: ['id']).order('priority');

// fetched:  [{id: 1, priority: 1}, {id: 2, priority: null}]
// insert:   {id: 3, priority: 0}
// emitted:  [{id: 1, priority: 1}, {id: 2, priority: null}, {id: 3, priority: 0}]
```

`_sortData` compares two values only when both are numbers or both are
strings and answers `0` for every other pair, so `null` counts as equal
to every value. That is not an ordering (`0 == null`, `null == 1`, but
`0 < 1`), and `List.sort` needs one. Two things follow:

- A row arriving over realtime stops at the first `null` it meets
instead of moving to its place, as in the example above. With `limit()`
the wrong rows are then cut off.
- On a longer list the rows that hold no `null` come out in the wrong
order as well. With 100 sorted rows followed by three `null` rows, one
insert leaves `37` ahead of `36`.

The fix treats `null` as larger than any value, which is how PostgreSQL
orders it by default: last on an ascending sort, first on a descending
one. Since #1912 `order()` sends no null placement, so this is also the
order the fetch itself returns, and a row keeps its position between the
fetch, a realtime change and a refetch after a reconnect.

| Order | Before | After |
| -- | -- | -- |
| ascending | a `null` row stays where it arrived, later rows pile up
behind it | `null` rows last |
| descending | same | `null` rows first |

Only the comparison of a `null` with another value changes. Numbers and
strings compare as before, and so does every pair `_sortData` did not
order before, for example two booleans.

## Test

`packages/supabase/test/stream_order_test.dart`, on the same mocks as
`stream_insert_test.dart`:

- `an ascending order keeps null values last`. On `main`: `at location
[0]['id'] ... (continued)

4 of 4 new or added lines in 1 file covered. (100.0%)

9311 of 10178 relevant lines covered (91.48%)

5.18 hits per line