Does mysql "order by" guarantees to give the same order for the same query every time if the sort key is equal? I'm planning to write a query that sorts the result by a value s and then paginate the result. Let's say I have ten items that match the query and all the items have the same s. In the first query, I sort the value by s and get the first five items. Then in the next query, I sort the value by s and get the sixth to tenth items. Is it possible that the items that appear on my first query will appear again in my second query, and that some items will not show up in either query?
When the field being sorted on is not unique, then the order of the results are non-deterministic, meaning there is no guarantee on their exact ordering. Specifically in your example case where the first 10 results all share the same exact value for the s field could yield indeterminate results for the first time you run the query vs the second time. It is possible you see the same result in both cases or not at all as you guessed. You would need to add another field to the ORDER BY clause that uniqifies the sort in order to always guarantee deterministic results.
For a little proof of case and further reading, here is a DBA.StackExchange question and answer where the user was experiencing unpredictable ordering of results because the field they were sorting on wasn't unique. The comments on that answer briefly mention that being the cause.