Beware pagination with offset

I ran into an issue this morning, which I cannot see anything online about it (maybe I have not found the right search terms yet) so posting here incase it helps someone in the future.

Imagine you have a mysql database with 30 or so events. The events each have a date field. The paginated eloquent query might look like;

$events = Event::where('course_id',$course->id)->orderBy('date', 'ASC')->paginate(10);

This all appears to work fine

Now suppose there are two events that have the same date, and these two records just happen to be records 10 and 11 in the total recordset. However, since they both have the same date, its up to the database to decide which record to show first when the order by is ambiguous.

So now we load page 1 and get records 1 through 10 (all ok)

Now we load page 2, and the pagination uses limit 10 and offset 10

We could reasonably expect to get records 11 through 20, but for some reason the database evaluates the records in a different order and instead outputs records 10,12,13.... 20

So, record 10 is output on page 2 (as well as page 1), and record 11 is never displayed.

The answer is quite simple and that is to make sure there is no ambiguity in the search order by adding an additional, unique, orderBy;

$events = Event::where('course_id',$course->id)->orderBy('date', 'ASC')->orderBy('id','ASC')->paginate(10);

If anyone has any links or has experienced this issue, please feel free to contribute.