Datetime index mis-collates sub-second values: ORDER BY incoherent between ASC/DESC; indexed range + ORDER BY silently drops rows

Description

With a standard (non-unique) index defined on a datetime field, ORDER BY over that field mis-collates whole-second values against sub-second values within the same second — and the mis-ordering is incoherent between ASC and DESC. Combined with a range lower bound at a whole second (the natural shape of a day/hour bucket query), rows are silently dropped from the result set.

Without the index, ordering and results are correct — the defect is in the index-backed order/scan path, not the generic sort.

Steps to reproduce

DEFINE INDEX idx_ts ON t FIELDS ts;
CREATE t SET ts = d'2026-08-28T09:00:00Z';
CREATE t SET ts = d'2026-08-28T09:00:00.152Z';
CREATE t SET ts = d'2026-08-28T09:00:00.611Z';
CREATE t SET ts = d'2026-08-28T09:00:00.917Z';
CREATE t SET ts = d'2026-08-28T09:00:01.070Z';

SELECT VALUE ts FROM t ORDER BY ts ASC;
SELECT VALUE ts FROM t ORDER BY ts DESC;
SELECT VALUE ts FROM t WHERE ts >= d'2026-08-28T09:00:00Z' ORDER BY ts ASC LIMIT 20;

Run e.g. via surreal sql --endpoint memory --namespace test --database test.

Actual output

-- ORDER BY ts ASC: the whole second sorts AFTER its own sub-seconds
[ .152Z, .611Z, .917Z, 09:00:00Z, 09:00:01.070Z ]

-- ORDER BY ts DESC: the whole second sorts as SMALLEST (contradicting ASC)
[ 09:00:01.070Z, .917Z, .611Z, .152Z, 09:00:00Z ]

-- ranged + ORDER BY + LIMIT: 3 of 5 rows SILENTLY DROPPED
[ 09:00:00Z, 09:00:01.070Z ]

ASC claims 09:00:00Z > 09:00:00.917Z; DESC claims 09:00:00Z < 09:00:00.152Z — no consistent ordering satisfies both.

The third query is the dangerous one: WHERE ts >= <whole second> with ORDER BY ts starts the sorted seek at the whole-second key's (mis-collated) position, so every sub-second row of that same second lands behind the cursor and never reaches the result. Any "process one day/hour at a time" pattern (export, archival, pagination) silently loses the first second's sub-second rows.

Expected output

All five rows, ordered 00Z < .152 < .611 < .917 < 01.070 (and the reverse for DESC); the ranged query returns all five.

Affected versions

Reproduced on:

• official surreal CLI 3.0.1 (memory engine) — output above
• Rust SDK surrealdb 3.2.4 (surrealdb-core 3.2.4), both mem:// and the surrealkv backend (0.21.4)
• surrealdb 3.3.0-beta.3 — identical failure

With no index on ts, all three queries are correct on all versions tested — which suggests the datetime→index-key encoding drops or mis-places the sub-second component.

Environment

Linux x86_64. In the SDK repros, EXPLAIN FULL confirms the correct-result variants run the range as a residual filter (all rows pass); the row loss appears only when the sort is index-served.