Fix pagination on sorts with lots of ties
What does this MR do?
Fixes #18915 (closed). 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 anORDER 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