Sort commit statuses of a pipeline by id in the sort direction

What does this MR do and why?

Unquarantines the example API::CommitStatuses GET /projects/:id/repository/commits/:sha/statuses ci commit exists reporter user return commit statuses sort by desc pipeline_id behaves like get commit statuses returns all commit statuses (spec/requests/api/commit_statuses_spec.rb:130) and fixes its cause in lib/api/commit_statuses.rb.

Cause (flaky-testdataset-specific)

GET /projects/:id/repository/commits/:sha/statuses with order_by=pipeline_id orders only by that column:

def order_and_sort_statuses(statuses)
  sort_direction = params[:sort].presence || DEFAULT_SORT_DIRECTION
  order_column = ALLOWED_SORT_VALUES.include?(params[:order_by]) ? params[:order_by] : DEFAULT_SORT_VALUE
  statuses.order(order_column => sort_direction)
end

There is no tie-breaker, so statuses that belong to the same pipeline come back in whatever order the query plan happens to produce. The spec expected ids ascending within each pipeline:

context 'return commit statuses sort by desc pipeline_id' do
  let(:params) { { all: 1, order_by: "pipeline_id", sort: "desc" } }
  let(:expected_statuses) { develop.statuses.order(id: :asc).ids + master.statuses.order(id: :asc).ids }

A sequential scan plus in-memory sort keeps insertion (id) order for this tiny dataset, and the spec passes. When Postgres instead satisfies ORDER BY commit_id DESC by walking a (commit_id, …) index on p_ci_builds backwards, the ties come out in reverse id order and the eq assertion fails. The plan depends on table statistics, not on example order: in the 2025-08-11 failure the --only-failures retry (the example alone, fresh database) failed again with the same output, which is what broke master that day (rspec integration pg16 4/20, gitlab-org/quality/engineering-productivity/master-broken-incidents#15362 (closed)).

CI failure, identical shape in all three traces I checked (jobs 10982161876, 11257440076, 11605714336):

Failure/Error: expect(statuses_id).to eq(expected_statuses)

  expected: [172, 174, 170, 171, 173, 175]
       got: [174, 172, 175, 173, 171, 170]

The two pipelines are in the right (descending) order; within each pipeline the ids are reversed.

This is also a real API bug, not only a test problem: without a deterministic tie-breaker, offset pagination of order_by=pipeline_id can skip or duplicate statuses between pages.

Fix

  • lib/api/commit_statuses.rb: when order_by=pipeline_id, break ties with id in the same direction as sort (ORDER BY commit_id ASC, id ASC / commit_id DESC, id DESC). id is unique, so the order is fully determined by the data instead of the query plan. order_by=id is unchanged.
  • Behaviour change: with sort=desc, statuses of the same pipeline now come back newest first, so the whole response follows sort. With sort=asc they come back oldest first, which is also what the default plan returned before. This order was never defined: it depended on the query plan (see the revert check below).
  • spec/requests/api/commit_statuses_spec.rb: expect id descending within each pipeline in the sort by desc pipeline_id example (the sort by asc pipeline_id example already expected id ascending), remove the quarantine: metadata, and add an example that pages through the pipeline_id order (per_page: 2) and checks every status comes back exactly once.
  • doc/api/commits.md, the order_by parameter description and doc/api/openapi/openapi_v3.yaml (regenerated): say that with order_by=pipeline_id, statuses of the same pipeline are also sorted by id in the sort direction. The history block gets a Changed entry for 19.6.
  • spec/support/rspec_order_todo.yml: remove the file, as scripts/rspec_check_order_dependence does when it passes.

Database

Changed query, generated with to_sql for order_by=pipeline_id&all=1, first page (LIMIT 20 OFFSET 0), select list shortened:

SELECT p_ci_builds.* FROM p_ci_builds
WHERE p_ci_builds.commit_id IN (
  SELECT p_ci_pipelines.id FROM p_ci_pipelines
  WHERE p_ci_pipelines.project_id = <project_id>
    AND (p_ci_pipelines.source IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR p_ci_pipelines.source IS NULL)
    AND p_ci_pipelines.sha = '<sha>')
ORDER BY p_ci_builds.commit_id DESC, p_ci_builds.id DESC   -- sort=asc: commit_id ASC, id ASC
LIMIT 20 OFFSET 0

Plans from postgres.ai (gitlab-production-ci, EXPLAIN (ANALYZE, BUFFERS)) for gitlab-org/gitlab and a master commit from 2026-11-03 with 2 pipelines and 478 jobs. Every run returns rows.

Query Joe command Execution Buffers Sort
master, sort=asc: ORDER BY commit_id ASC 576637 3.6 ms 720 hit top-N heapsort, 30 kB
This MR, sort=asc: commit_id ASC, id ASC 576633 / 576635 2.2 ms / 2.6 ms 720 hit top-N heapsort, 31 kB
master, sort=desc: ORDER BY commit_id DESC 576619 3.5 ms 720 hit top-N heapsort, 30 kB
This MR, sort=desc: commit_id DESC, id DESC 576617 3.4 ms 720 hit top-N heapsort, 35 kB
This MR, sort=desc, last page (OFFSET 460) 576621 3.1 ms 720 hit quicksort, 148 kB
First query on a fresh clone (cold), master / this MR 576631 / 576612 46 ms / 729 ms, almost all disk reads 87 hit, 633 read top-N heapsort

Warm runs were taken in one sequence on the same clone, after a first query had read the rows into the cache. The cold first run is dominated by reading the same 478 rows, whichever query runs first.

The plan shape is the same as on master. master's ORDER BY commit_id already reads every status of the SHA's pipelines and sorts them in memory: no index leads with (commit_id, id) (p_ci_builds has (commit_id, stage_idx, created_at), (commit_id, status, type) and others), so it never streamed from an index. The id tie-breaker only adds a second sort key to that in-memory sort. Rows read and buffers are identical, and the extra sort cost is under 1 ms. The row set is bounded by the statuses of one commit's pipelines, and the default order_by=id path already sorts the same rows. No new index is needed.

Plan excerpt, `sort=desc` (warm, earlier run of the same `commit_id DESC, id DESC` order, command 573563, partitions without rows omitted)
Limit  (actual time=2.473..2.487 rows=20 loops=1)
  Buffers: shared hit=720
  ->  Sort  (actual time=2.472..2.484 rows=20 loops=1)
        Sort Key: p_ci_builds.commit_id DESC, p_ci_builds.id DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Nested Loop  (actual time=0.637..1.670 rows=478 loops=1)
              ->  HashAggregate  (actual time=0.393..0.400 rows=2 loops=1)
                    Group Key: p_ci_pipelines.id
                    ->  Append  (actual time=0.280..0.391 rows=2 loops=1)
                          ->  Index Scan using ci_pipelines_117_project_id_sha_idx on ci_pipelines_117  (actual time=0.028..0.120 rows=2 loops=1)
                                Index Cond: ((project_id = <project_id>) AND ((sha)::text = '<sha>'::text))
              ->  Append  (actual time=0.147..0.517 rows=239 loops=2)
                    ->  Index Scan using ci_builds_117_commit_id_stage_idx_created_at_idx on ci_builds_117  (actual time=0.010..0.358 rows=239 loops=2)
                          Index Cond: (commit_id = p_ci_pipelines.id)

Feature flag: skipped. This makes an undefined ordering deterministic; there is no new behaviour to roll out gradually.

References

Closes https://gitlab.com/gitlab-org/quality/test-failure-issues/-/issues/18755

Related: https://gitlab.com/gitlab-org/quality/test-failure-issues/-/issues/17012 (duplicate, same test hash) and the quarantine issue #525204 (104 flakiness reports between 2025-03 and 2025-10; quarantined in !209715 (merged) on 2025-10-23).

Guide: https://docs.gitlab.com/development/testing_guide/unhealthy_tests/

How to set up and validate locally

Tested against master at bd474164c255 (code with an explicit if order_column == 'pipeline_id' branch). The final code, statuses.order(order_column => sort_direction, id: sort_direction) on master at 6dee43bba3f7, produces byte-identical SQL for all four order_by/sort combinations (to_sql checked; ActiveRecord drops the duplicate id key when order_by=id), so the plans above apply unchanged. Re-run on the final code: the :130/:137/:144 loop, 15/15 with the default plan and 14/15 with the forced plan; the one failure was GRPC::DeadlineExceeded from the local test Gitaly (500 before the ordering code ran). The whole file passed every GET example in each run. The only failures were in POST /projects/:id/statuses/:sha examples, which fail the same way on master's code in this environment and are not touched by this MR. RuboCop is clean.

The CI failure needs the planner to pick a backward index scan, so locally it is forced with a small RSpec helper (not committed) that runs SET enable_seqscan = off; SET enable_sort = off on the CI connection before each example and resets it afterwards:

# tmp/force_index_scan.rb
RSpec.configure do |config|
  config.before { Ci::ApplicationRecord.connection.execute('SET enable_seqscan = off; SET enable_sort = off') }
  config.after  { Ci::ApplicationRecord.connection.execute('RESET enable_seqscan; RESET enable_sort') }
end
bin/rspec spec/requests/api/commit_statuses_spec.rb --seed 18755 --require ./tmp/force_index_scan.rb

Without a tie-breaker the result flips with the plan: on master's lib/api/commit_statuses.rb, the default plan returns ids ascending within each pipeline and the forced backward index scan returns them descending for sort=desc. With this MR both plans return id in the sort direction.

Check Result
Whole file, --seed 18755, default plan and forced plan ✅ 65 examples, 0 failures (both)
sort by desc pipeline_id (:130) and sort by asc pipeline_id (:137), each plan ✅ 4/4 pass
CI seeds 27762 / 14248 / 38815 (whole file, no helper) ✅ 3/3 pass
Order dependence (forced plan): defined, reverse, random seeds 6762 / 13979 / 63010 / 34925 / 10235 ✅ all pass
Pagination example (:144), each plan ✅ pass
30-run loop of :130, :137 and :144 (forced plan) ✅ 30/30 pass
Revert check: master's lib/api/commit_statuses.rb ✅ sort by desc pipeline_id (:130) and the pagination example (:144) fail with the default plan. sort by asc pipeline_id (:137) passes on master too, because the natural order for commit_id ASC is already id ASC; it locks that order in.
bundle exec rubocop lib/api/commit_statuses.rb spec/requests/api/commit_statuses_spec.rb ✅ no offenses

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

🤖 Generated with Claude Code

Edited by Max Fan

Merge request reports

Loading
Loading