Flaky tests from nondeterministic ordering: the ORDER BY you never wrote

The diff with nothing wrong in it

A test fails in CI. You open the assertion diff and stare at it for a solid thirty seconds, because both sides contain exactly the same three invoice IDs. Expected: [1042, 1043, 1044]. Actual: [1043, 1042, 1044]. Same elements. Different order. Someone clicks re-run, it goes green, the PR merges, and everyone agrees the test is "just flaky."

It is flaky. But it's flaky for one of the most specific, most fixable reasons in the entire flaky-test taxonomy: the test asserts on an ordering that nothing in the system ever promised. In the failure data we see across suites, nondeterministic ordering sits comfortably in the top handful of root causes, alongside shared state and async timing. Unlike async timing, it has a crisp signature, a mechanical detection method, and a fix you can apply in an afternoon.

This post is about that one root cause class. Where it hides, why it passes on laptops and fails in CI, how to spot it in your failure history, and why "rerun until green" is a genuinely bad answer if your CI gate is part of a change-management control.

Where unpromised ordering comes from

The canonical source is a SQL query with no ORDER BY. Postgres, MySQL, and friends make no guarantee about row order without one. None. The order you observe is an artifact of the execution plan, and the plan changes under you: table size crosses a threshold and the planner switches from index scan to sequential scan, parallel workers merge results in completion order, autovacuum rewrites page layout, or Postgres's synchronize_seqscans starts your scan mid-table because another session is already scanning it.

Here's the shape of the bug in a pytest suite:

def test_lists_customer_invoices(db, client):
    create_invoice(db, id=1042, customer_id=7)
    create_invoice(db, id=1043, customer_id=7)
    create_invoice(db, id=1044, customer_id=7)

    resp = client.get("/customers/7/invoices")

    # The endpoint runs: SELECT * FROM invoices WHERE customer_id = %s
    # No ORDER BY. This assertion is a coin flip wearing a suit.
    assert [i["id"] for i in resp.json()] == [1042, 1043, 1044]

On a laptop this passes hundreds of times in a row. The table is tiny, the rows were just inserted, heap order matches insert order, and the planner does the same boring thing every run. The test isn't stable. It's lucky, at scale, in a stable environment. CI takes away the stable environment.

Databases are the biggest offender but not the only one. The same class shows up anywhere iteration order is deliberately or incidentally undefined:

• Go maps. The runtime randomizes map iteration order on purpose, specifically so you can't depend on it. Any test that ranges over a map and appends to a slice before asserting is rolling dice.
• Python sets. Dicts have been insertion-ordered since 3.7, which lulls people into thinking sets are too. They aren't, and PYTHONHASHSEED randomization means set iteration order can differ per process.
• Concurrent completion order. Firing N requests with a worker pool and asserting on results in arrival order. Promise.all preserves input order; hand-rolled worker queues and errgroup collectors frequently don't.
• Filesystem listings. readdir order is not alphabetical on all filesystems. Tests that glob fixtures and assert on the concatenation break the day CI switches runner images.

The Go version, for the record:

func TestExportHeaders(t *testing.T) {
    fields := map[string]string{"id": "ID", "amount": "Amount", "status": "Status"}

    var headers []string
    for _, label := range fields { // iteration order is randomized by design
        headers = append(headers, label)
    }

    // Passes roughly 1 in 6 runs. The other 5 get filed as "CI flakiness."
    want := []string{"ID", "Amount", "Status"}
    if !reflect.DeepEqual(headers, want) {
        t.Errorf("got %v, want %v", headers, want)
    }
}

That one at least fails often enough that someone fixes it quickly. The database version can pass for eighteen months and then start failing twice a week when the seeded dataset grows past the planner's threshold for a parallel scan. Nobody connects the failure to the fixture PR that merged three weeks earlier, so it gets attributed to ambient CI flakiness and reflexive retries.

The detection signature

Here's the good news: of all flaky-test root causes, this one has the cleanest fingerprint. When ordering is the problem, the assertion diff contains the same multiset of elements on both sides. Not similar elements. The same ones, permuted.

That makes test detection almost mechanical. If you're triaging by hand, sort both sides of any suspicious diff; if they become identical, you're done diagnosing. If you're triaging at scale, look for these correlations in your failure history:

• The test fails intermittently on the same assertion line every time, never on setup or a different assertion.
• Failure rate changed after a fixture or seed-data change, a database version bump, or a parallelism change in CI, with no change to the test itself.
• The test never fails locally and never fails in single-threaded debug runs, because both of those environments have quieter planners and no concurrent scan interference.

You can also force the issue instead of waiting for it. For database ordering, run your suite against a copy of the schema where you've deliberately perturbed the plan: disable synchronize_seqscans, then enable it; run with max_parallel_workers_per_gather = 0, then 4. For hash-based ordering in Python, run the suite twice with different PYTHONHASHSEED values. A test that flips between those runs has an ordering dependency, full stop. This is a cheap nightly job and it converts "mystery flake we'll see again in Q3" into "deterministic failure with a stack trace, today."

This is also where cross-build history earns its keep. A single red build tells you almost nothing; the pattern across two hundred builds tells you everything. BuildPulse does this detection by tracking every test outcome across runs and flagging tests whose failures don't correlate with code changes, which surfaces exactly these order-sensitive tests before your engineers have internalized "oh, that one, just retry it" as tribal knowledge. Once a test carries that reputation, its signal is gone even on the days it fails for a real reason.

Fix it at the layer that made the promise

There are two legitimate fixes, and choosing between them requires answering one product question: does the user actually care about this order?

If order matters to the user, the bug is in the code, not the test. An invoice list a customer sees should have a defined order, so add the ORDER BY to the query and let the test keep asserting strictly. One trap here: ORDER BY created_at alone is not a fix if timestamps can tie, and in tests that insert three rows inside a millisecond, they tie constantly. You've swapped an obvious flake for a rarer one. Always add a unique tiebreaker:

SELECT id, amount, status
FROM invoices
WHERE customer_id = $1
ORDER BY created_at DESC, id DESC;

If order doesn't matter, the bug is in the assertion. Stop asserting sequence when you mean membership. Every framework has a native way to say "these collections contain the same things":

• Python: assertCountEqual, or sorted(actual) == sorted(expected)
• Go: cmpopts.SortSlices with go-cmp, or assert.ElementsMatch in testify
• JavaScript: sort both sides before toEqual, or expect(actual).toEqual(expect.arrayContaining(expected)) plus a length check

What you should not do is "fix" the test by massaging fixtures until the accidental order becomes stable again, or by adding a retry loop around the assertion. Both approaches preserve the false promise and hand the same incident to a future teammate with less context. If you want a broader tour of remediation patterns beyond this root cause, we've written up how to actually fix flaky tests rather than pave over them.

The compliance angle: why "just rerun it" is evidence too

If you're at a SOC 2 or ISO 27001 shop, or anywhere in fintech or healthtech, your CI gate probably isn't just a convenience. It's part of your change-management control: changes are tested before release, and the green check is the evidence. That framing changes the math on ordering flakes.

A test that fails on a permutation and passes on retry produces an audit trail that reads "control failed, control was re-executed, control passed," with no recorded reason. Multiply that by forty retries a week and you have a control that demonstrably flickers, operated by engineers who have been trained to override it on reflex. No auditor needs to be hostile to find that uncomfortable, and no VP wants to explain it during evidence collection.

The defensible pattern is the opposite of the silent retry: detect the flaky test, quarantine it explicitly with an owner and a ticket, keep it running so you retain its history, and record when it was fixed and restored to the gate. That gives you a documented exception with a remediation trail instead of an unexplained pattern of overrides. It's the difference between "we identified a nondeterministic ordering defect in test X on March 3, quarantined it, and restored it on March 11" and a shrug. Our writeup on quarantining flaky tests covers how to do this without letting quarantine become a landfill.

Ordering flakes are a good place to start that discipline precisely because they're so tractable. The detection is mechanical, the fix is small, and the closure is provable: after the fix, the test's failure rate across builds drops to zero and stays there, and you have the history to show it.

The takeaway

When a flaky test's diff shows the same elements in a different order, you are not looking at mysterious CI flakiness. You are looking at a missing ORDER BY, a ranged-over map, or a worker pool collecting results in completion order. Sort the diff to confirm the diagnosis, decide whether the order was ever a real promise, and fix it in the query or in the assertion accordingly. Then check your failure history for the rest of the family, because this root cause never travels alone. A suite that stops lying about order is a suite whose red builds mean something again, and that's the entire point of having one.