Why MySQL LIMIT Can Reverse ORDER BY in LEFT JOIN Queries

I wrote a rather stupid bug today and documented it to avoid repeating the mistake.

When writing a paginated query that orders records by timestamp descending, the original SQL was:

select
    record.a,
    y.c
from
    (
        select
            a,b
        from
            x
        order by timestamp desc
        limit 0,10
    ) record
left join y
on record.b = y.d;

Later, new requirements added WHERE conditions, so the LIMIT 0,10 was moved outside the subquery, but the ORDER BY remained inside. The result unexpectedly became ascending instead of descending.

Rolling back to the original query and moving only the LIMIT to the outermost level revealed that the timestamp ordering became ascending, indicating the problem lies in the placement of LIMIT.

Removing LIMIT altogether made the timestamps appear unordered, suggesting MySQL may reorder rows based on the joined table's indexed columns when no LIMIT is present.

Further investigation showed that MySQL's ORDER BY clause inside a subquery does not guarantee ordering of the final result of a LEFT JOIN, because the outer query cannot see the inner ordering.

Additionally, without LIMIT, MySQL may optimize the join by not iterating over the left table in its original order, similar to Java's reordering optimizations.

Therefore, the simplest and most reliable solution is to place both ORDER BY and LIMIT clauses at the outermost query level; this does not significantly affect performance as long as appropriate indexes exist.