iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
An ORDER BY clause guarantees the order of the expressions you list, not the order of rows that tie on all of them. If a test compares query output to a fixed sequence and two rows share every sort value, the database is free to return them in either order. The test then passes or fails depending on which legal order the engine produces that run. The fix is to add a final sort expression that makes the combined key unique, or to stop asserting on sequence when sequence does not matter.
What the database promises when sort values tie
PostgreSQL’s documentation on sorting rows states that a particular output ordering can only be guaranteed when a sort step is explicitly chosen, and that later ORDER BY expressions only resolve ties left by earlier ones. If every expression in the list is equal for two rows, the documented behavior leaves their relative position unspecified.
MySQL’s Reference Manual is more direct in its section on LIMIT query optimization: “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.”
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsBoth engines describe the same contract. The order is defined for the keys you list, and undefined beyond them. Nothing in that contract says that tied rows will shuffle on every run, and a stable-looking result is not evidence that the order is guaranteed. It is only evidence that the plan chosen so far happens to produce that sequence.
#1 Best Overall
How a passing test becomes a flaky one
Consider a query that lists recent events:
SELECT id, created_at FROM events ORDER BY created_at;
This query specifies chronological order. If events 1 and 2 share the timestamp 10:00, and event 3 is at 10:05, the documented result is “event 3 is last” and nothing more. A test that asserts the exact list [1, 2, 3] is asserting a tie order the query never defined. Today’s plan may return 1, 2, 3. A later change to indexes, the row count, the LIMIT clause, or the server version may return 2, 1, 3, and the test breaks with no change to the application code.
Rank #2
The flakiness is therefore a property of the test’s assumption rather than of random behavior in the database. The same test can pass for months and then fail after an unrelated schema change, which is why it is hard to reproduce on demand.
Fixing the query when order is part of the contract
If the feature really requires a particular sequence, make the sort key unique. Add a column that is unique within the result set as the final term:
SELECT id, created_at FROM events ORDER BY created_at, id;
MySQL’s own LIMIT documentation uses the same technique, sorting by a category column and then by id to resolve ties. Two cautions apply:
- The tiebreaker must be unique among the rows the query returns. In a join, qualify the column (for example
e.id), and confirm that the column is unique in the joined output, not only in its base table. - Choose a key that does not change between runs. An auto-generated primary key is usually stable; a value derived from wall-clock time or a random number is not.
Pagination makes the problem worse
With LIMIT and OFFSET, a non-unique ordering affects which rows a page contains, not only how they are arranged. Rows that tie on the sort key can straddle a page boundary, so one row may appear on two pages while another appears on none. PostgreSQL’s SELECT documentation recommends an ORDER BY that constrains results to a unique order whenever LIMIT is used, and notes that plan choices can vary with LIMIT and OFFSET values, which changes the subset that is returned.
Recommended Free Tools
A paginated test should therefore check a combined key such as ORDER BY created_at, id and verify that consecutive pages do not overlap and together cover the expected rows. The documentation establishes the ordering issue. It does not establish how databases behave when rows change between separate page requests, so tests for that case should be written as a separate concern.
Best Value
Choosing the right test assertion
Before changing the query, decide what the test is supposed to prove. The table below maps the three common situations to a fix.
| Situation | Is row order part of the contract? | Are page boundaries relevant? | Recommended approach |
|---|---|---|---|
| The test checks which rows or values come back | No | No | Compare the results as an unordered collection, or sort both sides in the test before comparing |
| The feature requires a sequence and sort keys are unique | Yes | No | Keep the ORDER BY and assert the exact sequence |
| The feature requires a sequence but sort keys can tie | Yes | No | Add a unique final sort expression to the query, then assert the sequence |
| A paginated listing is tested | Usually yes, for the overall order | Yes | Use a unique combined ordering, then check that pages neither overlap nor skip rows |
The key rule is that an incidental row order should never become the test contract. If the test would still be correct after the engine returned the same rows in a different legal order, the assertion should not depend on that order.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Diagnosing a test that already fails intermittently
Work through these checks in order. They are diagnostic suggestions drawn from the documented behavior. Each one can rule a cause in or out, but none of them proves that a particular failure was caused by tied rows.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →- Find duplicate sort values. For the example above, run
SELECT created_at, COUNT(*) FROM events GROUP BY created_at HAVING COUNT(*) > 1;. Check every expression in the ORDER BY list, not only the first. - Compare the query with and without the LIMIT clause, and with the same OFFSET values the test uses. A change in result order between those variants points to plan-dependent tie handling.
- Check whether the failing run and the passing run used different indexes or plans. Differences in index definitions, table statistics, or the number of rows can change the plan.
- Compare database versions and collations between environments. Collation changes how string values compare, so it can change what counts as a tie and how values are ordered.
- Add the unique tiebreaker, rerun the test many times in the same environment, and confirm that the assertion passes consistently. A stable result after the change is consistent with the diagnosis but does not measure how often the original test would have failed.
What the sources do and do not establish
The PostgreSQL, MySQL, and Microsoft Learn documentation for ORDER BY describe how these engines define ordering. They establish that ties beyond the listed keys are not defined and that plan choices can affect which tied rows come first. They do not measure how often this causes flaky tests in real projects, and they do not show that any specific application has experienced it. Treat the risk as a direct consequence of the documented contract, and fix it in the test and the query before it appears as an intermittent failure.
When you need a specific sequence, define it in SQL. When you do not, let the test say so.
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.
