Document and kv engines accept NULL primary keys; multiple NULL-pk rows coexist and IS NULL answers differ between scan and aggregate paths

[emanzx]

Version / build tested against

origin/main @ 2886155

Deployment mode

Origin — single node (local)

Engine(s) involved

Document (schemaless), Key-Value

Summary

The document (schemaless) and kv engines accept NULL into a declared PRIMARY KEY — both as an explicit NULL value and by omitting the column — and multiple NULL-pk rows coexist in one collection. Uniqueness enforcement works for real values (a duplicate is correctly rejected) but NULL bypasses it entirely, along with the implied NOT NULL. On top of that, the stored NULL-pk rows are internally inconsistent: SELECT ... WHERE id IS NULL returns both rows while SELECT count(*) ... WHERE id IS NULL returns 1 — the same predicate gives different answers on the scan and aggregate paths, suggesting one row stores an explicit null and the other has the field absent, and the two paths treat absence differently. document_strict correctly rejects both inserts, so the hole is confined to the engines that route through the schemaless write path.

Steps to reproduce

CREATE COLLECTION pk2 (id INT PRIMARY KEY, v TEXT);

INSERT INTO pk2 (id, v) VALUES (NULL, 'explicit-null');   -- INSERT 0 1   <- expected ERROR 23502
INSERT INTO pk2 (v) VALUES ('omitted-pk');                -- INSERT 0 1   <- expected ERROR 23502

SELECT id, v FROM pk2;
--  id |       v
-- ----+---------------
--     | explicit-null
--     | omitted-pk
-- (2 rows)                  <- two rows with NULL primary key coexist

SELECT count(*) FROM pk2 WHERE id IS NULL;   -- 1     <- WRONG, 2 rows match
SELECT id, v FROM pk2 WHERE id IS NULL;      -- 2 rows <- same predicate, different answer

-- Uniqueness machinery does exist for real values:
INSERT INTO pk2 (id, v) VALUES (1, 'ok');    -- INSERT 0 1
INSERT INTO pk2 (id, v) VALUES (1, 'dup');   -- ERROR: duplicate key ... primary-key uniqueness (correct)

-- kv accepts it too:
CREATE COLLECTION pk4 (k TEXT PRIMARY KEY, v TEXT) WITH (engine = 'kv');
INSERT INTO pk4 (v) VALUES ('kv-omitted');   -- OK           <- expected ERROR 23502

-- document_strict rejects both (correct), though via a serialization error rather than 23502:
CREATE COLLECTION pk3 (id INT PRIMARY KEY, v TEXT) WITH (engine = 'document_strict');
INSERT INTO pk3 (id, v) VALUES (NULL, 'strict-null');
-- ERROR: serialization error (binary_tuple): bad request: column 'id' is NOT NULL but no value provided

Expected behavior

PRIMARY KEY implies NOT NULL on every engine: both inserts raise 23502 (not_null_violation) and no NULL-pk row is ever stored. Predicates over the pk column return the same row set on every execution path. (Secondary: the strict engine's rejection would ideally surface as a clean 23502 constraint error rather than a serialization error (binary_tuple) message, so drivers classify it correctly.)

Actual behavior

Document and kv engines commit NULL-pk rows — several per collection — so the primary key is neither unique nor non-null for these rows. The rows then behave inconsistently: the aggregate path counts 1 while the scan path returns 2 for the identical IS NULL predicate, so applications cannot even reliably find the damage. DELETE ... WHERE id IS NULL removes 2 rows, agreeing with the scan path and disagreeing with count(*).

What actually happened? (check all that are true)

• Acknowledged/committed data was lost, corrupted, or silently wrong — rows violating the declared key constraint are committed and readback of them is inconsistent; other stored data 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.)

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)

Unknown.

Environment & logs

Linux x86_64, release build from a fresh data directory, trust mode, pgwire via psql 16. No relevant server log lines — every accepting statement completes without warning.

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).