Proposal: Opt-in field/key ordering preservation in query output

[msanchezdev]

Opening as a discussion to gauge appetite and refine the design before any implementation. It revisits #2715 (closed wontfix, 2024) in light of the current execution engine, with a design built to address the exact reason that issue was closed — and a working proof-of-concept (engine + live HTTP demo) to show the blocker no longer holds: #7386.

Summary

Let SurrealDB return object fields in the order the query expressed them, instead of always alphabetically — as an opt-in, default-off policy that leaves today's sorted behaviour unchanged.

SELECT name, age, email FROM person PRESERVE ORDER;
-- today:  { age: …, email: …, name: … }
-- opt-in: { name: …, age: …, email: … }

#2715 was closed on the grounds that objects are stored as sorted B-trees, so output is necessarily sorted. That's true — and this proposal does not change it. The sorted form stays fully canonical for storage and comparison; field order is carried as presentation-only metadata. The PoC below proves this is both possible and cheap.

Why now (what changed since #2715)

Field order is a presentation concern that can ride alongside the canonical sorted value without altering it:

• Storage encoding stays byte-identical (sorted).
• Ord/Eq/Hash stay defined on the sorted form — so comparison, dedup, index keys, and complex Record IDs (Bug: The alphabetical order of the properties of an object-based Record ID is query-significant #4053) are unchanged.
• Ordering is layered on top and consulted only when producing output.

Correctness guardrail (#4053)

#4053 established that object-key order is load-bearing: complex Record IDs sort object keys before storage, which is what makes range scans correct. This proposal preserves that exactly — the ordering metadata is excluded from Ord/Eq/Hash and never touches Record IDs or keys. It is structurally impossible for this feature to change comparison or storage semantics. (The PoC has a test asserting two objects that differ only in display order remain == and hash-equal.)

Proposed design

1. Presentation-order side-car on Object. The value keeps its canonical sorted map and gains an optional display-order permutation. None = today's behaviour, zero overhead. To guarantee byte-identical encoding, the PoC keeps the existing revisioned/storekey map as an inner type and delegates to it — nothing about the wire format is re-implemented.

2. Opt-in surface (two tiers, default off):

• Capability gate — --allow-experimental field_ordering lets an operator enable the feature at all.
• DB-scoped activation — DEFINE CONFIG [OVERWRITE | IF NOT EXISTS] FIELDS ORDER [ SORTED | WRITTEN | DECLARED ], introspectable via INFO FOR DB (round-trips like the GraphQL/API configs; needs matching ALTER/REMOVE CONFIG).

A 3-valued enum, not a boolean, future-proofs the lifecycle: today the default is SORTED; later, flipping the default makes the same surface an opt-out — no new grammar, no breaking change. (An OPTION-style flag was considered and rejected: it's a per-connection execution mode, can't carry an order, and can't name three states.)

3. Per-query override — PRESERVE ORDER (a trailing clause):

SELECT name, age, email FROM person PRESERVE ORDER;

Forces written order for one statement. Placed at the end (after FETCH/TIMEOUT), so it never collides with ORDER BY — the PoC parses both in the same query (… ORDER BY name LIMIT 5 PRESERVE ORDER) without ambiguity.

4. Semantics — "how to order fields the query did not explicitly list":

• SORTED — today, alphabetical everywhere.
• Non-SORTED — explicit projections/literals (SELECT b, a, { b, a }, RETURN {…}) always honour written order.
• WRITTEN vs DECLARED differ only for implicit SELECT * of schemafull tables: insertion order vs DEFINE FIELD declaration order.

5. Phased rollout (grammar accepts all enum values from PR 1; behaviour rolls out per phase):

Phase	Behaviour	Persistence
A	explicit projection order (SELECT b, a)	output-only
B	object literals + RETURN + write echo	output-only
C1 (DECLARED)	schemafull SELECT * via DEFINE FIELD order	schema-derived
C2 (WRITTEN persisted)	schemaless per-record insertion order	on-disk (revision bump)

Proof of concept

A working PoC backs this discussion (#7386). It implements Phase A end-to-end behind the field_ordering capability.

Engine (5 passing tests):

• storekey_encoding_is_byte_identical — an object with a display-order side-car encodes to byte-for-byte the same bytes as without one, and round-trips back to the canonical value.
• order_excluded_from_eq_and_hash — objects differing only in display order are ==, hash-equal, Ord-equal.
• display_order_is_preserved, preserve_order_sets_written_display_order, parse_select_preserve_order — the operator applies written order and the clause parses + round-trips.

Live (HTTP /sql):

SELECT c, a, b FROM t PRESERVE ORDER;   →  {"c":3,"a":1,"b":2}   ← written order
SELECT c, a, b FROM t;                  →  {"a":1,"b":2,"c":3}   ← sorted (default, unchanged)

Honest scope of the PoC: only explicit projections reorder (Phase A); SELECT */record echoes/literals are Phases B/C. On output, only the JSON encoder was made order-aware — as a shortcut it leans on serde_json's preserve_order feature, but a production version would use a dedicated ordered JSON encoder and avoid any global change (noted inline in the PoC). CBOR/flatbuffers wire formats were left sorted. None of it touches storage or comparison.

Non-goals (for now)

• Client response-format / protocol-version negotiation.
• Any change to storage encoding, value comparison, or Record ID semantics.
• A per-query opt-out keyword (easy to add later).

Open questions (for discussion)

Proposer's recommendation in italics; these are prompts, not blockers.

1. Long-term default — stay opt-in, or eventually promote WRITTEN to default? (Ship opt-in; the enum makes a later flip non-breaking.)
2. Config keyword — FIELDS ORDER <enum> vs FIELD_ORDER / ORDERING? (A namespaced FIELDS block at database scope, matching DEFINE CONFIG GRAPHQL; a root-default/db-override layering is a possible follow-up.)

---

Related: revisits #2715; honours the invariant documented in #4053.

[LucyEgan]

I really like this, if theres no noticeable overhead/breaking change which i would imagine its pretty safe, then i would just have this on all the time as theres multiple times where im grouping fields based on their context and it becomes annoying looking through 30 fields when i know my order i want, ive resulted in 01 actualName, 02 actualName or [actualName, actualName] far too many times

[Isfirs]

Instead of adding the clause to the end, why not make it part of the SELECT at the start to make it more apparent in larger selects?

SELECT EXACT firstname, lastname FROM persons or something