cockroachdb / cockroachdb/cockroach
sql: EPSC oracle should detect row-order divergences when ORDER BY is present
- 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
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