Positional INSERT (no column list) stores values unbound to column names — SELECT * shows the row, column projection and WHERE predicates silently miss it

[emanzx]

Summary

An INSERT written without a column list (INSERT INTO t VALUES (…)) stores its values without binding them to column names. The row is created and SELECT * returns it correctly — but every named column projection and every WHERE predicate silently misses it.

Because SELECT * and count(*) both look correct, the data appears present while being unreachable by any query that names a column. No error is raised at any point.

The same statement written with an explicit column list works correctly.

Environment

• nodedb 0.4.0 — reproduced identically on both:
  • tag v0.4.0 @ 38bfc3084
  • main @ 63cdbb460 (the fix(auth): materialize trust superuser identity #198 merge)
• Single standalone node, fresh data dir, release build, Linux x86_64
• Client: psql 16.14 over pgwire
• Config: minimal — [auth] mode = "trust", default [server] ports

Found while independently verifying the #198 fix; it is not a #198 regression — it predates that PR (confirmed on the v0.4.0 tag).

Reproduction

CREATE COLLECTION probe (id INT PRIMARY KEY, note TEXT);

-- positional insert (no column list)
INSERT INTO probe VALUES (42, 'stored?');

SELECT count(*) FROM probe;                          -- 1          ✅
SELECT * FROM probe;                                 -- 42|stored? ✅  data is stored
SELECT id FROM probe;                                -- (empty)    ❌  named projection returns nothing
SELECT count(*) FROM probe WHERE id = 42;            -- 0          ❌  predicate misses the row
SELECT count(*) FROM probe WHERE note = 'stored?';   -- 0          ❌

Control — the same collection, same values, with a column list:

INSERT INTO probe (id, note) VALUES (43, 'named');

SELECT id FROM probe;                                -- 43         ✅
SELECT count(*) FROM probe WHERE id = 43;            -- 1          ✅

SELECT * FROM probe ORDER BY id after both inserts returns the positional row with its values present but its columns evidently unnamed, alongside the correctly-bound named row.

Observed vs expected

	observed	expected
count(*)	1	1
SELECT *	42|stored?	42|stored?
SELECT id	(empty)	42
WHERE id = 42	0 rows	1 row
WHERE note = 'stored?'	0 rows	1 row

Expected behaviour: a positional INSERT binds values to the collection's declared columns in declaration order, so the row is indistinguishable from the equivalent named insert.

Why this one is unpleasant

• No error, at any stage. The insert reports success, the row counts, and the most common sanity check (SELECT *) shows the data exactly as written.
• Every filtered read silently under-reports. A WHERE scan over a table populated by positional inserts returns zero rows without indicating anything is wrong — the failure looks like "no matching data" rather than "data is unreachable".
• INSERT INTO t VALUES (…) is the most common INSERT form in scripts, fixtures, migrations and ORM-generated SQL, so the blast radius is wide.

This is the same silent-zero-rows family as #156 / #170 / #171, but with a different trigger (the insert path rather than the predicate path), and it is worse in one respect: the data is genuinely stored, so the discrepancy only surfaces when someone compares a SELECT * against a filtered query.

Suggested direction

Resolve the column list at parse/plan time for positional inserts — bind VALUES positionally against the collection's declared column order and fail loudly on arity mismatch — so both insert forms converge on the same stored representation.

If a positional insert genuinely cannot be bound (unknown arity, schemaless collection, etc.), rejecting the statement would be far safer than persisting a row that reads back only under SELECT *.

Notes

Happy to run variants (schemaless vs strict collections, other engines, arity mismatch, INSERT … SELECT) or test a patch — I have both builds standing on an isolated rig.