P6 Implemented Phase 6

Phase 6 — SQL Surface Completeness

Phase 6 grows the hand-written SQL frontend (sql.rs → exec.rs) to cover the common, OLTP-shaped majority of the PostgreSQL and SQLite surface, so the SQL an application actually issues parses and runs the same on Twill. It is surface-completeness work, not a PostgreSQL re-implementation — every change is additive frontend growth, so STORAGE_TRAIT_VERSION and ENGINE_ABI_VERSION never moved and the C1–C8 conformance suite, branching, and scale-to-zero are untouched. All five stages (6A–6E) are implemented and gated by tests.

Overview

Phase 6 is the first phase that adds no new storage capability and no new C symbol: it is pure frontend growth. A statement still flows sql.rs (lexer + recursive-descent parser → Stmt AST) → exec.rs (evaluate against the MVCC store) → conn.rs (transaction state machine + commit durability); Phase 6 widens what the first two accept and evaluate, with catalog.rs/conn.rs/wal.rs carrying the stateful items (constraints, savepoints, schema evolution). The only WAL growth is backward-compatible additive catalog facts, so replay of an older log is unchanged.

Because the seam never moves, the invariants below hold for every stage: STORAGE_TRAIT_VERSION stays 2… well, 3 after the additive stats() surface (#53) — but unmoved by Phase 6; ENGINE_ABI_VERSION stays 3; anything still unsupported returns a clean ENGINE_ERR_SQL — reject, never mis-parse.

The five stages

StageLandsWhereTests
6A — expression & single-table CASE, CAST/::, IN/BETWEEN, string concat ||, NULLS FIRST/LAST, OFFSET, RETURNING, upsert (ON CONFLICT / OR REPLACE/OR IGNORE with excluded.*), INSERT … SELECT, COALESCE/NULLIF/GREATEST/LEAST/iif sql.rs (Expr::Case/Cast, parser), exec.rs tests/expr_6a.rs — 12
6B — multi-table & aggregation Joins (INNER/LEFT/RIGHT/FULL/CROSS, ON/USING), qualified names, DISTINCT, set ops (UNION/INTERSECT/EXCEPT), derived tables & non-recursive CTEs, non-correlated and correlated subqueries, views, join-driven DML, GROUP BY/HAVING grouped aggregation. The largest stage. sql.rs, exec.rs (nested-loop join over a materialized column namespace), catalog.rs (views) tests/relational_6b.rs — 24
6C — function library A curated OLTP-common set of string / math / date-time / UUID / JSON scalar functions, evaluated once at execution so replay stays deterministic. Date-time on the SQLite date()/strftime() model (ISO-8601 Text / epoch Integer); minimal JSON accessor pack (json_extract, ->/->>, json_array). exec.rs dispatch, datetime.rs, json.rs tests/functions_6c.rs — 7
6D — constraints, schema evolution & savepoints DEFAULT/CHECK/UNIQUE (single & composite), composite PRIMARY KEY, AUTOINCREMENT/SERIAL, ALTER TABLE … ADD/DROP/RENAME COLUMN / RENAME TO, SAVEPOINT/RELEASE/ROLLBACK TO. DDL stays autocommit-only. sql.rs (Stmt::AlterTable/Savepoint), catalog.rs, conn.rs, wal.rs (additive ops) tests/constraints_6d.rs — 8
6E — dialect shims Placeholder forms (? / $1 / :name → 1-based binds), identifier quoting (backtick), the case-sensitive LIKE vs ILIKE split, SET/SHOW/PRAGMA/EXPLAIN and isolation-level/session no-ops — concentrated at the pgwire boundary so the core stays one dialect. lex.rs, sql.rs tests/dialect_6e.rs — 5

Why it stays additive (the seam never moves)

Phase 6 is the proof that the architecture's central bet pays off: a whole class of capability — application-grade SQL — was added without touching the storage seam. Three properties fall out:

  • No storage-trait change. Every stage is parser/executor work; the engine still reaches durable state only through trait Storage. STORAGE_TRAIT_VERSION was unchanged by Phase 6 (it sits at 3 from the earlier additive stats() surface, #53), and the C1–C8 conformance suite + branching battery stayed green untouched.
  • No ABI change. No C symbol was added or removed; ENGINE_ABI_VERSION stays 3, and the frozen engine.h contract and the Bun wrapper's pinned EXPECTED_ABI_VERSION are unaffected.
  • Backward-compatible WAL. The only durable growth is additive catalog facts (constraints, view definitions, ALTER reshape ops); an older log replays unchanged, and the new facts branch and scale-to-zero with the database like every other WAL record.

Relationship to the PostgREST-compat work

Some of the executor surface Phase 6 formalizes (:: casts, GROUP BY/HAVING, LIMIT … OFFSET, scalar functions, json_agg/json_build_object) first grew under the PostgREST-compat track (#27). Phase 6 stages it deliberately and fills the gaps; the PostgREST-specific glue (version probe, catalog reflection, data-path rewriting) stays in crates/server (introspect.rs/reflect.rs/datapath.rs), never the engine. Foreign keys remain tracked metadata (parsed, persisted in CreateTable, reflected to clients), not engine-enforced referential integrity in this phase.

Deliberate scope boundaries (documented, not accidental)

  • DDL stays autocommit-only. CREATE/DROP/ALTER inside an explicit transaction returns ENGINE_ERR_TXN; row DML is fully transactional. SAVEPOINT nests within a transaction (6D) but does not lift the DDL rule.
  • FK is metadata, not enforcement. Inline REFERENCES / table-level FOREIGN KEY parse and persist and reflect to clients; the engine does not enforce referential integrity this phase.
  • Correlation is one level. Correlated scalar/EXISTS subqueries evaluate per outer row; correlation under aggregation or nested deeper than one level stays out (ENGINE_ERR_SQL).
  • Recursive CTEs, SIMILAR TO/POSIX regex, INTERVAL arithmetic, full JSONB indexing, standalone CREATE SEQUENCE, scalar B-tree secondary indexes stay out by design — see the per-row status in SQL Compatibility & Mapping.
  • Reject, never mis-parse. Anything still unsupported returns a clean ENGINE_ERR_SQL; the surface grows by widening the parser, never by silently accepting-and-ignoring.

Tests

  • crates/engine/tests/expr_6a.rs (12), relational_6b.rs (24), functions_6c.rs (7), constraints_6d.rs (8), dialect_6e.rs (5) — one binary per stage, each asserting the new surface and that unsupported neighbours still reject cleanly.
  • The existing crates/engine/tests/engine.rs, ffi.rs, and group-commit/MVCC suites stay green: Phase 6 widened the frontend without disturbing transaction, durability, or ABI behaviour.

Related

SQL Compatibility & MappingThe per-feature status map this phase implements — what's ✅ / ◑ / ✗ across types, DDL, queries, expressions, and functions. Engine CoreThe sql.rs → exec.rs → conn.rs pipeline Phase 6 widens, parser-first. Storage InterfaceThe seam Phase 6 never moves — pure frontend growth, no trait or ABI change. Roadmap & Build SequenceThe additive build order whose Phase 6 SQL-completeness milestone this map implements.
Twill DB — internal development specification. Implementation map. · Author