Merge branch '18915-pagination-with-priority-sort-repeats-results' into 'master'

Fix pagination on sorts with lots of ties

## What does this MR do?

Fixes #18915. As we only order by the sorted column, we don't have any tie-breaker. Some orderings, like priority and weight, have lots of ties, so you can see duplicate results as you page through. (Timestamp columns are less susceptible to this.)

## Are there points in the code the reviewer needs to double check?

I just picked `id DESC`, this could as easily be `id ASC`.

## Why was this MR needed?

Postgres and MySQL don't guarantee that pagination with `LIMIT` and
`OFFSET` will work as expected if the ordering isn't unique. From the Postgres docs:

> When using `LIMIT`, it is important to use an `ORDER BY` clause that
> constrains the result rows into a unique order. Otherwise you will get
> an unpredictable subset of the query's rows

Before:

    [1] pry(main)> issues = 1.upto(Issue.count).map { |i| Issue.sort('priority').page(i).per(1).map(&:id) }.flatten
    [2] pry(main)> issues.count
    => 81
    [3] pry(main)> issues.uniq.count
    => 42

After:

    [1] pry(main)> issues = 1.upto(Issue.count).map { |i| Issue.sort('priority').page(i).per(1).map(&:id) }.flatten
    [2] pry(main)> issues.count
    => 81
    [3] pry(main)> issues.uniq.count
    => 81

See merge request !4878

0 files changed, 0 insertions, 0 deletions