Minted row identity has two values: SELECT id returns a key no predicate matches, and scan and aggregate disagree on id = <value>

[farhan-syah]

Version / build tested against

origin/main @ dd2ed01

Deployment mode

Origin — single node (local)

Engine(s) involved

Document (schemaless)

Summary

A row whose identity is minted — a schemaless collection with no declared PRIMARY KEY — carries that identity in two encodings, and the read paths disagree about which one is id. RETURNING id answers the decimal surrogate. SELECT id answers the 8-hex storage key. Only the decimal addresses the row on the scan path, so a client cannot use the value it just read to fetch that row again. SELECT * omits id entirely. The scan and aggregate paths also answer the same equality predicate differently: WHERE id = '1' returns the row while count(*) over it returns 0, and the reverse holds for the hex form.

Steps to reproduce

CREATE COLLECTION t (v TEXT);                       -- no declared PRIMARY KEY

INSERT INTO t (v) VALUES ('x') RETURNING id;
--  id
-- ----
--   1                       <- decimal surrogate

SELECT id FROM t;
--    id
-- ----------
--  00000001                 <- 8-hex storage key, different value, same row

SELECT * FROM t;
--  v
-- ---
--  x                        <- no id column at all

-- Which value addresses the row? They disagree by path.
SELECT v FROM t WHERE id = '1';                  -- x         (scan finds it)
SELECT v FROM t WHERE id = '00000001';           -- (0 rows)  <- the value SELECT id printed

SELECT count(*) FROM t WHERE id = '1';           -- 0         <- disagrees with the scan above
SELECT count(*) FROM t WHERE id = '00000001';    -- 1         <- disagrees with the scan above

-- IS NULL is consistent on both paths, so this is not the #293 symptom:
SELECT v FROM t WHERE id IS NULL;                -- (0 rows)
SELECT count(*) FROM t WHERE id IS NULL;         -- 0

Expected behavior

One identity per row, one value, addressable everywhere.

• RETURNING id and SELECT id answer the same string.
• The value either query returns addresses the row: WHERE id = <that value> returns it.
• The scan path and the aggregate path answer any predicate over id identically.
• SELECT * includes id when SELECT id does.

Actual behavior

Two encodings of one surrogate exist and different paths pick different ones.

Producer	Site	Value
Planner mints the identity	nodedb/src/control/planner/sql_plan_convert/dml/insert.rs:159,168	s.as_u32().to_string() → 1
Handler writes the row	nodedb/src/data/executor/handlers/point/insert.rs:68	surrogate_to_doc_id → 00000001
Handler answers RETURNING	nodedb/src/data/executor/handlers/point/insert.rs:283	passes document_id → 1
from_stored fills id when the body carries none	nodedb/src/data/executor/handlers/returning_doc.rs:50-68	whatever it is handed
Read paths build the row image	nodedb/src/data/executor/row_shape.rs:77	storage key → 00000001

Two separate defects follow.

1. The displayed identity is not the addressable one. SELECT id returns the storage key. No scan predicate matches on it. The decimal that RETURNING gives does match. A client that stores what it read cannot fetch the row with it.
2. The scan and aggregate paths disagree on id = <value>. The aggregate path evaluates the row image, which carries the storage key. The equality predicate on the identity column resolves to a point-get or index lookup keyed on document_id and never builds a row image, so it matches the decimal instead.

Defect 2 is the same class as #293 — one predicate, two row sets — on the equality operator rather than IS NULL. #293's fix required the identity at matches_with_resolved_schema, which aligns every path that evaluates a row image. The pk-equality route does not reach that primitive, so it was not covered.

nodedb/src/data/executor/handlers/point/insert.rs:107 carries a stale comment stating the existence probe "uses document_id as the row key". The probe uses row_key, the hex form. The comment is wrong in the way the ambiguity predicts.

What actually happened? (check all that are true)

• Acknowledged/committed data was lost, corrupted, or silently wrong — reads of id return a value that does not address the row, and one predicate returns two different row sets; stored rows are intact
• The server crashed, hung, or failed to start
• A security or isolation boundary was crossed
• Core functionality is broken with no acceptable workaround
• A workaround exists (rewrite the query, avoid one path, etc.) — keep the value from RETURNING id and never read id back through SELECT

Proposed severity

SEV-2 — High: major functionality broken or silently-wrong results; stored data intact

Reproducibility

Always — every attempt

Last known-good version / commit (if a regression)

Not a clean regression. Both encodings predate #310. sparse_row_to_doc has injected the storage key on the aggregate and join paths since well before it.

#310 changed the failure mode of SELECT id rather than introducing it. On 66d0a225c the scan path carried no id at all, so SELECT id returned nothing and id IS NULL matched every row — the #293 symptom. #310 fixed that by injecting the storage key on the scan path, which made the encoding mismatch visible as a wrong value instead of an absent one.

Environment & logs

Linux x86_64, release build from a fresh data directory, trust mode, pgwire via psql 16. Measured through the wire test harness on dd2ed0164. No server log lines — every statement above completes without warning.

Design note for triage: the addressable identity is the decimal document_id, which both RETURNING and the scan route already use, so keeping the hex storage key on read paths is likely the wrong half. Settling which value is canonical comes before any code. A single newtype built at one point, required wherever id is injected, compared, or returned, is what stops the two forms drifting apart again.

Before submitting

• I searched existing issues and this is not a duplicate.
• I reproduced this on a released tag or a current main build (not a stale local branch).
• This is not a security vulnerability (those go to a private advisory).