Search code examples
mysqlsqllimitoffset

MySQL Default Sort Order with Limits and Offsets - Some Items Ignored - Why?

I ran into a very interesting MySQL query issue that only seems to apply when no sorting order is specified and limits & offsets are used in the query. One of the items from my table is repeated as a result when no sorting order is specified. This doesn't seem to make sense based on how the ordering should work.

I've created a simple database and was able to reproduce the original problem I ran into:

Structure: enter image description here

Data: enter image description here

When using SQL queries such as:

SELECT SQL_CALC_FOUND_ROWS * FROM test WHERE test_customer = '1' LIMIT 4 OFFSET 4

As I adjust the offset, the value "yes" doesn't appear in the last offset query. Element 13 is repeated. That doesn't seem to make sense.

Anyone know why this happening?

For a more detailed explanation of what's going on, please see this full example that shows the default results versus ascending order results:

https://eamster.tk/mysql_debug_test/mysql_debug.php

Any help is appreciated. I'm just trying to understand what's going on... for now, I'll just work around the issue by specifying the ORDER BY clause.

Solution

  • SQL tables and result sets (with no ORDER BY) represent unordered sets. Ordering is arbitrary and capricious. It can change from query execution to query execution.

    This query:

    SELECT SQL_CALC_FOUND_ROWS *
FROM test
WHERE test_customer = '1'
LIMIT 4 OFFSET 4;

    Returns an arbitrary 4 rows for the customer. The OFFSET means nothing.

    If you want data in a particular order, then use ORDER BY. Otherwise, the data is in an arbitrary order that can change from query execution to query execution. There is no "default ordering".

    So:

    SELECT SQL_CALC_FOUND_ROWS *
FROM test
WHERE test_customer = 1
ORDER BY testid
LIMIT 4 OFFSET 4;

    Note that I removed the single quotes from the =. The value is an integer, so use a numeric constant.