When doing a query, I got some strange results when sorting the records.
The records have an 'order' column, but currently they are all set to 0.
When I limit my resultset by 15, everything is as expected. Only when I set the limit to 16, the recordorder goes haywire...
I know I can fix it with an extra order value, but I want to know why this is happening...
There is no issue. You are simple learning that
order by in MySQL (and SQL in general) is not stable. What this means is that keys with a tied value can occur in an arbitrary order. So, two different runs of the same query can produce different results.
Why are SQL sorts not stable? The reason is simple: SQL tables and result sets represent unordered sets. There is no available information to create a stable sort. It is meaningless on unordered sets.
The fix? That's easy. Just add the keys that you want:
order by `order`, product_id