An ORDER BY clause guarantees the order of its sort expressions, not the order of rows that tie on all of them. If a test asserts an exact sequence of rows and the query leaves some of those rows tied, the test is relying on an order the database never promised. It may pass for months and then fail after a plan change, a new index, a different LIMIT, or an upgrade. The fix is to add a final sort expression that makes the combined key unique, or to stop asserting sequence when sequence does not matter.
What ORDER BY actually promises
ORDER BY arranges rows by the expressions you list, from left to right. Each later expression only breaks ties left by the earlier ones. When the full list of expressions still produces equal keys for two rows, the SQL standard-style reading that most engines follow is that those rows have no defined relative order.
The PostgreSQL 18 documentation for sorting rows says that a particular output ordering can only be guaranteed if the sort step is explicitly chosen, and that without an explicit sort, order is unspecified and depends on execution details. Its SELECT reference makes the same point for pagination: without a deterministic ordering, repeated executions can select different subsets of rows.
The MySQL Reference Manual, in its LIMIT query optimization section, is more direct about ties. If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. That sentence is the core of this problem: the same query text can produce different legal results.
#1 Best Overall
How a test becomes flaky
Consider a table of events and a test that checks the most recent activity:
SELECT id, created_at FROM events ORDER BY created_at;Suppose the fixture inserts three rows:
| id | created_at |
|---|---|
| 3 | 2026-03-01 08:15:00 |
| 1 | 2026-03-01 09:00:00 |
| 2 | 2026-03-01 09:00:00 |
Row 3 must come first, because it has the earliest timestamp. Rows 1 and 2 share a timestamp, so both [3, 1, 2] and [3, 2, 1] are valid results. A test that compares the output to the list [3, 1, 2] passes whenever the engine happens to return that order and fails when it returns the other legal one.
The failure is intermittent in the sense that the query text and data never change, yet the observed tie order can. Nothing in the query has changed, which is why these failures are often blamed on the environment or the database rather than the test.
Why the tie order changes
The documentation establishes that tie order depends on execution details, and it names the plan itself as a factor. The following conditions can change which legal order a given run returns, and they are the first things to check when a test starts failing:
- Index availability or use. An index on
created_atmay let the engine read rows in one order, while a sequential scan followed by a sort may produce another. - LIMIT and OFFSET values. PostgreSQL notes that plan choices may vary with LIMIT and OFFSET, and MySQL notes that LIMIT can affect the order of tied rows.
- Table statistics and data volume. As data grows, the planner may switch strategies, which changes the sequence in which tied rows come out.
- Engine version. A database upgrade can change plan choices even when the schema and query stay the same.
- Collation. Collation can change how text sort keys compare between environments. This is a diagnostic check rather than a documented cause of any particular failure, so verify it instead of assuming it.
None of these means the database is buggy. Each is a legitimate outcome of an unspecified tie order.
Fix 1: add a unique final sort key
When the test contract depends on sequence, make the query define a total order. Append a column that is unique within the result set:
SELECT id, created_at FROM events ORDER BY created_at, id;Now the two rows with the same timestamp are sorted by id, so the result is [3, 1, 2] every time. MySQL’s own tie-resolution example follows the same pattern, ordering by a category column and then by id.
Three conditions make this work:
- The added column must be unique among the rows the query returns. A primary key of the base table is unique for a single-table query, but in a join you need a key that is unique across the joined result, such as a composite of the two table keys.
- The added column should be stable. Sorting by a value that changes between test runs, such as a generated timestamp, reintroduces the problem.
- The sort must be in the SQL. Sorting results in application code after a non-unique query only hides the issue if the test sorts by the same complete key you intended.
Fix 2: stop asserting sequence when sequence does not matter
Many tests check membership or values, not order. In that case, the most honest assertion is one that ignores order. Two common approaches work:
- Sort both the actual and expected rows in the test code by a complete key, then compare the lists.
- Compare the results as multisets or unordered collections, which most test frameworks support through a dedicated assertion.
This keeps the test from depending on an incidental database row order, which is not part of the feature contract. It also avoids the need to change production SQL just to satisfy a test.
Pagination and LIMIT/OFFSET
A non-unique ORDER BY matters more with pagination than in a single-result test. Rows that are equal on the sort key can straddle a page boundary, and each page request is planned independently.
Consider four rows with timestamps A at 08:00, B and C at 09:00, and D at 10:00, queried with ORDER BY created_at LIMIT 2 for page one and OFFSET 2 for page two. If page one returns A and B, and page two runs under a different plan that returns B and D, then B appears on both pages and C never appears. Each query is individually legal under the documented behavior, but the combined result is wrong.
PostgreSQL’s guidance for LIMIT is to use an ORDER BY that constrains results to a unique order. Applied here, the fix is ORDER BY created_at, id on every page query, with the same final key in each request. Keyset pagination, which filters by the last seen values of the full key rather than by OFFSET, avoids the offset-plan dependency altogether.
Free tools Windows power users keep installed
One-click scans. No signup required.
A separate concern is data that changes between page requests, such as inserts or deletes. The cited ordering documentation does not establish how snapshot consistency behaves across all engines, so test that case as its own scenario rather than assuming the unique key covers it.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choosing a test strategy
Use the following questions to choose the approach for each test:
| Question | If yes | If no |
|---|---|---|
| Is row order part of the feature contract? | Add a unique final sort key in the SQL and assert the exact sequence. | Compare results without regard to sequence. |
| Must the query return a deterministic sequence for users? | Make the production query define a total order with a unique key. | Ordering by the business column alone may be acceptable, but the test should not assert tie order. |
| Must page boundaries stay stable? | Use a unique combined ordering on every page query, and consider keyset pagination. | A single-result check with no paging needs no tiebreaker for paging, though ties may still affect sequence assertions. |
Diagnosing a flaky failure
When a test fails intermittently, work through these steps in order:
- Print the full result set from the failing run, including every column named in ORDER BY.
- Check for duplicate values in each ORDER BY expression. If the rows tie on every expression, the test is relying on an unspecified order.
- Compare the query plan for the failing and passing cases. In PostgreSQL, use
EXPLAIN; in MySQL, useEXPLAINas well, and note any difference in index use or sort steps. - Check whether LIMIT or OFFSET values differ between the runs or environments.
- Confirm the database version and collation match between the environments where the test passes and fails.
- Add a unique final sort key, or switch to an order-insensitive assertion, and rerun the test repeatedly to confirm the change removes the intermittent failure.
The checks above are diagnostic steps. They do not establish that any one of them caused a given failure in a given application.
What the evidence does and does not establish
The PostgreSQL and MySQL documentation establish that ties in ORDER BY have no defined relative order, and that the observed order can depend on the execution plan. From that behavior, a test that asserts an exact order for tied rows is fragile. This is an inference from documented behavior, not a measured rate. No published figure says how often non-unique ordering causes flaky tests in practice, and this article does not supply one.
The sources describe SQL engine behavior. They do not say whether any particular application has experienced this failure, and they do not cover every engine’s behavior for every query shape. Microsoft’s Transact-SQL reference for ORDER BY also addresses unique ordering for pagination, and the same test-writing principles apply there, but the points above rest on the PostgreSQL and MySQL statements.
Quick Recap
“
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
