Pagination problems when sorting on non-unique column

[Nicklas Nordborg]

So far, this has been observed on PostgreSQL only...

It was discovered when sorting a list of RNA items by the QiacubeDate annotation. Since there are typically 12 samples with the same value, it may happen that a list page breaks so that 8 of 12 are listed on the first page and 4 of 12 on the next page. The problem was that some of the 4 on the second page are the same as the 8 on the first page.

This means that if we go page by page and think that we are going to see all items we are wrong and will actually see some items twice and some not at all. This can be very confusing. I guess it happens because the database will not sub-sort matches having the same value and repeating the same query may return different results depending on some unknown factor.

The workaround is to CTRL/SHIFT/ALT-click another column (for example, the name or ID column) to make the sort order predictable.

It would be good if BASE could automatically add an extra column for sorting to make the ordering is always predictable. For example, using the ID is a good candidate.

[Nicklas Nordborg]

(In [6887]) Fixes #1943: Pagination problems when sorting on non-unique column

Adding an extra order by on the 'id' property unless it has already been explicitely included.