[Epic] P0 — Query, Transaction & Catalog Correctness (v0.4)

[habibtalib]

Epic — v0.4 production-hardening, phase P0: Query, Transaction & Catalog Correctness

The bugs a user hits in the first hour: wrong results on the most common read/write shapes, data loss on restart, and DDL that bricks the data directory. These are already filed individually (below); this epic groups them as the P0 workstream. Goal: the engine returns correct results for common read/write shapes and survives routine DDL.

Wrong results / stale reads

• DELETE committed inside an explicit transaction leaves a stale primary-key point-lookup: scans show the row gone, WHERE pk = ... still returns it #148 — committed DELETE in a txn leaves a stale PK point-lookup
• A PK point-lookup miss poisons that key's reads for the rest of the session; INSERT/UPDATE don't invalidate the cached miss #152 — PK point-lookup miss poisons the key's reads for the session
• Table-qualified column references in WHERE silently match zero rows (except TEXT primary-key equality) #156 — table-qualified column refs in WHERE match zero rows
• GROUP BY output drops select-list aliases on group-key columns, reorders columns, and nulls unaliased aggregates #150 — GROUP BY drops aliases, reorders columns, nulls aggregates
• count(*) on a document collection never decrements after DELETE — counts drift upward permanently #159 — count(*) never decrements after DELETE

Data loss / boot failure (critical)

• document_strict rows lose all column data after a graceful daemon restart (count and PK index survive) #149 — document_strict rows lose all column data after a graceful restart
• DROP USER leaves dangling catalog owner references that fail the boot integrity check — data directory becomes unbootable #154 — DROP USER → dangling catalog owner refs → data dir unbootable

Multi-database / graph / DDL scoping

• Collections created in a CREATE DATABASE'd database are unreachable: catalog reads always resolve against the default database #151 — collections in a CREATE DATABASE'd db are unreachable
• MATCH ... IN <collection> ignores the collection scope, and DROP COLLECTION leaves its graph edges queryable forever #155 — MATCH … IN ignores scope; DROP COLLECTION leaves edges
• DROP + CREATE of a BITEMPORAL collection resurrects the old versioned-store history under the new collection #158 — DROP+CREATE of a BITEMPORAL collection resurrects old history
• CREATE COLLECTION engine spellings diverge: WITH (engine=...) + BITEMPORAL builds a broken schema; ENGINE = suffix silently ignores the timeseries engine #157 — CREATE COLLECTION engine spellings diverge

Success: read-your-writes/deletes holds on WHERE pk= and scans; GROUP BY and qualified predicates return correct rows; non-default databases are reachable; DROP is complete and never bricks boot; document_strict survives restart.

Tracked in .planning/v1-production-readiness/GITHUB-ISSUES.md.