Query language

Typed SQL:
JOIN, DML, and Heap DDL.

The query language is a limited native subset. It includes a parser, nominal type checking, three-valued logic, WAL-protected DML, and a bounded Heap DDL set. It is not a complete SQL dialect.

Area	Supported now	Not supported
SELECT	Qualified / unqualified columns, wildcard, LIMIT, typed expressions, AS aliases, postfix :: casts	DISTINCT, window functions, subqueries in projection
FROM / JOIN	AS and shorthand aliases, chained INNER JOIN … ON, NestedLoopJoin, HashJoin, IndexJoin	Outer joins, USING, join reordering, merge join
Predicates	AND / OR / NOT, comparisons, IS NULL, parentheses	IN / BETWEEN / LIKE, subqueries
DML	Single-row INSERT with an explicit column list or declaration order, UPDATE, DELETE, optional WHERE	Defaults, RETURNING, UPSERT
ORDER BY	Multi source-column keys, ASC / DESC, NULLS FIRST / LAST	Aliases, ordinals, arbitrary sort expressions
Aggregates	COUNT(*) / COUNT / SUM / MIN / MAX, source-column GROUP BY	HAVING, DISTINCT aggregates, grouping expressions, ROLLUP
DDL	Heap CREATE TABLE (Physical Types v2), DROP TABLE, ALTER TABLE (rename, nullable ADD, DROP, SET/DROP NOT NULL), CREATE/DROP INDEX	PRIMARY KEY / UNIQUE / FK, IF EXISTS, qualified names, LSM/range composition, general ALTER COLUMN TYPE

Nominal types

Schema columns keep both a physical representation and an optional nominal semantic type. HIR requires nominal compatibility in comparisons, so contextual NULL typing cannot make UserId = TeamId legal. Self joins distinguish two occurrences of the same TableId with query-local RelationBindingId values.

physical: UINT64
semantic: UserId

UserId ≠ TeamId   even when both are u64

NULL is a database value

Database NULL is an explicit ScalarValue::Null. Rust Option remains reserved for absent clauses or metadata. Comparisons with NULL yield UNKNOWN; IS NULL / IS NOT NULL are the explicit tests. AND / OR / NOT use SQL three-valued logic. WHERE and JOIN ON keep only TRUE; FALSE and UNKNOWN are rejected.

Bool(true)  → TRUE
Bool(false) → FALSE
NULL        → UNKNOWN

NULL = NULL     → UNKNOWN
NULL IS NULL    → TRUE

JOIN

An alias hides the underlying table name. Qualified columns resolve through the exposed relation name; unqualified columns are accepted only when exactly one visible relation provides the name. Each ON can see the complete left subtree and its current right relation, but not later joins. NestedLoopJoin is the default. After ANALYZE, a simple equi INNER JOIN of two scans may select HashJoin or Index Nested-Loop Join when that cost is strictly lower. Operators preserve duplicates in deterministic left-major, right-minor order.

SELECT e.name, m.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.active IS NOT NULL
ORDER BY e.name ASC NULLS LAST
LIMIT 20

DML

Typed DML uses the same compiler, transaction, full-page WAL, rollback, and recovery path as heap writes. Database::execute returns query rows or an explicit AffectedRows(u64); query rejects mutating statements. Omitted nullable INSERT columns become NULL; omitted non-nullable columns are rejected. UPDATE evaluates every right-hand side against the original row, so SET a = b, b = a swaps.

INSERT INTO users (id, name) VALUES (1, 'Ada');
UPDATE users SET name = 'Ada Lovelace' WHERE id = 1;
DELETE FROM users WHERE name IS NULL;

DDL

Heap CREATE TABLE, DROP TABLE, ALTER TABLE, and CREATE/DROP INDEX are transactional. ALTER supports rename table/column, nullable ADD, restricted DROP, and SET/DROP NOT NULL. Postfix :: casts are exact-width; there is no implicit numeric widening. LSM and partitioned tables are not part of this SQL DDL surface.

CREATE TABLE events (
    id BIGINT NOT NULL,
    payload BYTEA NOT NULL
);
ALTER TABLE events ADD COLUMN note TEXT;
SELECT id::TEXT, payload FROM events;
DROP TABLE events;

Sort and aggregates

The ordinary plan is Scan/Join → Filter → Sort → Project → Limit. The aggregate plan is Scan/Join → Filter → Aggregate → Limit. Keys resolve against the complete FROM / JOIN scope before projection, so a query may sort by a column it does not return.

COUNT(*) counts rows; COUNT(column) ignores NULL. A lone global COUNT(column) over SeqScan can count presence without materializing rows. Numeric SUM uses checked arithmetic and strips nominal meaning. MIN / MAX preserve the input SemanticType. NULLs at a grouping key share one group, unlike expression NULL = NULL, which remains UNKNOWN. Grouped queries currently reject ORDER BY.

SELECT team_id, COUNT(*), SUM(score), MAX(score)
FROM scores
GROUP BY team_id

Indexes and ANALYZE

create_index and SQL CREATE INDEX register a non-unique single-column Heap BTree after a transactional backfill. DROP INDEX retires that registration. Subsequent heap and SQL DML maintains registered indexes. Eligible equality and IS NULL predicates can select a point IndexScan; analyzed two-sided Int64/UInt64 bounds can select a range IndexScan. ANALYZE is explicit and is not maintained by DML.

create_index(table, column)
    → transactional backfill
    → register in IndexCatalog
    → DML maintains the index
    → ANALYZE writes a cost snapshot
    → planner may choose IndexScan

Writes across storages

create_tables still composes one heap file per table. Range-partitioned tables and mixed Heap+LSM catalogs commit through the coordinator log. Derived columnar projections are never authoritative. Concurrent writers are not available. Serializable isolation is not available.

Typed SQL:JOIN, DML, and Heap DDL.