Skip to content

Fix pagination on sorts with lots of ties

Sean McGivern requested to merge 18915-pagination-with-priority-sort-repeats-results into master

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 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

Merge request reports