cockroachdb / cockroachdb/cockroach

sql: EPSC oracle should detect row-order divergences when ORDER BY is present

Open
#172,848 1 comment 0 reactions 0 assignees View on GitHub
A-testing C-enhancement O-agent T-testeng
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Is your feature request related to a problem? Please describe.**
The EPSC oracle compares rows between the simple and prepared query paths *modulo order* (see the row comparator and the "rows agree modulo order" test case). That is correct for queries with no ordering guarantee, but it means an explicit `ORDER BY` that the prepared path returns in a different order than the simple path goes undetected — a genuine ordering bug would be missed.

**Describe the solution you'd like**
When the statement's output order is fully determined (top-level `ORDER BY`, possibly with `LIMIT`), compare order-sensitively; otherwise keep the order-insensitive comparison.

**Describe alternatives you've considered**
Always comparing order-sensitively — rejected because queries without an ordering guarantee may legitimately return rows in different orders, producing false positives.

**Additional context**
The crux is deciding when order is actually determined: `ORDER BY` on a non-unique key still permits ties in any order, so strict ordering should only be enforced to the extent the sort key makes the result deterministic (e.g. compare the projected `ORDER BY` key sequence, or only enforce strict order when the sort key is provably unique). Follow-up raised during review of the EPSC oracle work.

Jira issue: CRDB-66115

Contributor guide

Open the contributing guide

Research direction

Start with the EPSC oracle's row comparator and the "rows agree modulo order" test case mentioned in the issue. Determine how the statement's top-level ORDER BY, LIMIT, projected sort keys, and key uniqueness can establish deterministic output order. Done means ordered queries detect relevant divergences while unordered queries and legitimate ties remain order-insensitive, with tests covering both behaviors.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases, testing
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Quiet
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.