bug(sqlserver-cdc): UUID primary-key ordering mismatch can lose changes during backfill

[hzxa21]

Describe the bug

SQL Server CDC accepts uniqueidentifier primary keys and maps them to RisingWave varchar, but the CDC backfill event-range check compares the resulting strings. SQL Server orders uniqueidentifier values differently from canonical UUID strings. Consequently, snapshot progress and CDC event filtering can disagree about which keys have already been scanned, potentially losing changes or producing incorrect results during backfill.

This is a correctness issue with UUID primary-key ordering during snapshot/CDC merging, not a lack of UUID column decoding support. It also applies when a UUID component determines the ordering of a composite primary key.

Evidence status: confirmed ordering mismatch and affected code path by source inspection; a deterministic live SQL Server + RisingWave reproduction has not yet been run. The scenario below is a proposed regression test, not a reported executed test.

Affected code

Inspected main at 6666dd6a1af39df23add684dce9d2defa95a252d:

• SQL Server snapshot pagination uses upstream WHERE pk > @P1 ORDER BY pk, hence SQL Server's native UUID ordering.
• mark_cdc_chunk_inner forwards an eligible CDC event only if its key compares <= current_pos.
• cmp_pk_unsigned_aware special-cases unsigned integers, but UUID strings fall through to the normal datum comparator.

SQL Server's UUID ordering gives the final six bytes higher significance than the leading bytes; see Microsoft's explanation.

Proposed reproduction / regression test

Use deliberately inverted keys rather than random UUIDs:

-- Run in SQL Server to inspect native UUID order versus textual order.
DECLARE @keys TABLE (id uniqueidentifier PRIMARY KEY);
INSERT INTO @keys VALUES
  ('FFFFFFFF-0000-0000-0000-000000000001'), -- A
  ('00000000-0000-0000-0000-000000000002'), -- B
  ('00000000-0000-0000-0000-000000000003'); -- C
SELECT id FROM @keys ORDER BY id; -- A, B, C
SELECT id FROM @keys ORDER BY CONVERT(varchar(36), id); -- B, C, A

1. Create a CDC-enabled upstream table with these keys and a mutable payload; create the corresponding RisingWave CDC table.
2. Control snapshot batching/barriers so A and B have been emitted and current_pos = B, while backfill remains unfinished with C still unread.
3. Update or delete A upstream and ensure its CDC event is processed while that progress boundary is active and passes the CDC offset check.
4. A is in the already-scanned range according to SQL Server (A < B), so the event must be forwarded. RisingWave's string comparison instead gives A > B, hiding the event. A is not revisited by subsequent native snapshot pages, potentially leaving a stale value or an undeleted row.
5. Compare complete downstream contents with upstream after backfill and CDC catch-up, not just row counts. Cover the opposite misclassification (an unscanned key considered scanned) as well.

Expected behavior / fix direction

All UUID primary-key comparisons used to merge SQL Server snapshots with CDC must agree with upstream ordering, including composite keys. Preserve upstream type information and use a SQL Server UUID comparator in the actual event-range checks. Until that is implemented and tested, reject unsupported key ordering by default.

Regression coverage should include controlled concurrent updates, deletes, and inserts; composite keys; and recovery during unfinished backfill.

Recovery scope

The ordering mismatch alone does not establish that restarting a static snapshot reader skips rows. get_new_pos takes the last snapshot row rather than a string-order maximum, and the saved key is passed back to SQL Server for native pagination. A dedicated UUID restart test remains useful; static recovery data loss has not been demonstrated here.

History and related work

• test(mssql-cdc): enrich tests and small refactor #18081 (merged 2024-08-21) added GUID decoding and explicit uniqueidentifier -> varchar mapping.
• fix(mssql-cdc): support uuid and varchar as primary key data type #22157 (merged 2025-06-10; commit ffb4231e935e429694b75c05e31a44a7e4414c37) enabled UUID/varchar primary-key pagination by binding ScalarImpl::Utf8 as a SQL parameter. It added the static 2,000-row UUID fixture, but no UUID ordering comparator or concurrent UUID mutation coverage. Backports: fix(mssql-cdc): support uuid and varchar as primary key data type (#22157) #22187 (release-2.2), fix(mssql-cdc): support uuid and varchar as primary key data type (#22157) #22186 (release-2.3), fix(mssql-cdc): support uuid and varchar as primary key data type (#22157) #22188 (release-2.4).
• Open PR fix(cdc): enforce cross-system ordering for CDC keys #26444 adds a default rejection for SQL Server UUID primary-key ordering. Its explicit bypass restores the previous behavior and does not fix the mismatch; its static UUID fixture uses that bypass to retain decoding/pagination coverage. This issue tracks proper support beyond that guard.

Error message / deployment

No live reproduction logs or deployment version: this report is based on the pinned main source above. The failure mode can be incorrect data rather than an explicit error.

[xiangjinwu]

https://techcommunity.microsoft.com/blog/azuredbsupport/lesson-learned-497understanding-the-ordering-of-uniqueidentifier-in-sql-server/4156066
The link above contains self-conflicting information, as the comment on it pointed out.

https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/compare-guid-uniqueidentifier-values
This link is clearer.