Issue with search and a limited number on a page
I have a data processor with a activity search output and a csv output. The data processor searches for all activities on the case. And the default sorting is on the ID of the case.
I have a couple of cases and each case has multiple activities.
When I execute the search and I set to records per page on 1. I discover that on some pages results might randomly be different.
Also I discovered that the CSV export has a similar problem basicly it includes some records twice and other records are missing.
Diving into this this is caused by the fact that we have an
ORDER BY and
LIMIT n,n in the query.
Here is a movie showing the mysql problem:
The solution is to add a second order by caluse. In the above example an order by on the activity id will fix the issue.