-4

I accidentally discovered an error in MariaDB that led to unexpected results. Below are the SQL queries along with their respective outcomes:

Essentially, my objective is to retrieve data for pagination, with 10 items per page. As you can observe, I am ordering the results by the 'preference_group_id' column of the 'preference' table. However, I find it perplexing that the first result already contains preferences 5 and 6, and yet they reappear in the second result. This issue persists both with and without using DISTINCT (thus, the DISTINCT clause is irrelevant to the issue).

Upon attempting to recreate the database by importing the complete SQL file, the problem ceases to exist. The results after this are as follows

Mark Smith
  • 138
  • 1
  • 8
  • 1
    [Please do not upload images of code/errors when asking a question](https://meta.stackoverflow.com/q/285551). See also [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) and [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) from the help centre. – Phil Aug 08 '23 at 04:34
  • Since I cannot reproduce the issue, I want to capture the results as evidence. – Mark Smith Aug 08 '23 at 05:03
  • The idea is you include the queries and results as text. You can even format the latter as a table – Phil Aug 08 '23 at 05:06

1 Answers1

2

You need more ordering criteria.

There are two results with preference_group_id = 1 and at least eight with preference_group_id = 2, presumably more.

Ordering of those sub-groups (grouped by preference_group_id) is undefined so you will get inconsistent results.

Try ordering by a.id as a secondary term...

... ORDER BY a.preference_group_id, a.id LIMIT ?, 10
Phil
  • 157,677
  • 23
  • 242
  • 245
  • I know how to fix the issue, but I didn't know the reason why the issue happened. Isn't MySQL using a 'virtual column' as the default for consistent results? Using 'a.id' as a secondary term is inconvenient in some contexts (e.g., when we allow users to sort the results with their expected order). As you can see, the issue is gone after I recreated the database. That means MySQL is not working consistently with the same input. – Mark Smith Aug 08 '23 at 05:02
  • I suggest you have a read of [SQL: What is the default Order By of queries?](https://stackoverflow.com/q/8746519/283366) and [What is the default order of records for a SELECT statement in MySQL?](https://dba.stackexchange.com/q/6051). You could be seeing insertion order which would be more consistent when recreating your DB but you simply cannot rely on it. – Phil Aug 08 '23 at 05:10