Fred Jonsson <fred@pyth.net> writes:
> As I was playing around with `row_number()`s for cursor-based pagination, I
> came across some ordering behavior that I didn't expect.
> In particular, when I order in a way where multiple rows compete for the
> same position in the result set (i.e., rows that are equivalent in terms of
> the requested order), it appears that the `row_number()` column may be out
> of order.
If you remove the outer ORDER BY then you'll get results that make sense.
The problem is that the outer ORDER BY re-sorts the rows after the window
function has been applied, and it is not required to do anything
deterministic with rows having equal sort keys. IIRC, Postgres will
typically use a quicksort for small numbers of rows, and that doesn't
promise anything about the order in which equal keys are emitted.
> Curiously, if I set the row_number column to be `OVER (ORDER BY number
> ASC)`, the column is returned in order.
Yeah --- the planner is smart enough to not sort by the same condition
twice. But "number DESC" and "number ASC" are not the same sort
condition.
In short, if you want predictable results you need a unique sort key.
I think "ORDER BY number ASC, row_number" (or maybe row_number DESC
is what you want) is the most convenient solution for this example.
regards, tom lane