Search code examples
joinmariadblimitoffset

Strange results from MariaDB

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

Solution

  • 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