SQL

Hyphae's relational core is deliberately bounded: a closed, versioned set of statement shapes rather than a promise to run arbitrary SQL. Supported shapes are individually tested; anything outside them fails at bind time with a typed error instead of running as undefined behavior. The complete grammar lives in the versioned SQL semantics contract; this page explains the shape of that boundary and shows verified examples.

Call it "an indexed, fail-closed relational core" or "a bounded relational engine" — not "a SQL engine," "a SQL database," or "SQL compatibility." There is no universal-SQL claim anywhere in this project.

Why bounded, not universal

Every operation Hyphae admits has to bind to an index and run within an explicit resource budget — no free table scans, no unbounded joins, no silent disk spill. A plan that cannot bind to an access path fails closed with HYSQL011 rather than falling back to a slow scan. Statements that parse but ask for an unadmitted shape — a range filter on a non-key column, a scan without LIMIT, a second join — fail earlier, at bind time, with sql_invalid_syntax.

Admitted shapes

Shape	Verified example
Primary-key lookup	`SELECT ... WHERE id = ?`
Bounded scan (mandatory `LIMIT`)	`SELECT id, body FROM notes ORDER BY id LIMIT 10`
Primary-key range	`SELECT ... WHERE id >= ? ORDER BY id LIMIT 5`
Exact-key DML	`UPDATE notes SET stars = ? WHERE id = ?`; also `DELETE`, `MERGE`
Secondary-index equality	`SELECT` filters and exact-key DML on an indexed column
One exact indexed `INNER JOIN` form	see the semantics contract
Windows over the primary key	`ROW_NUMBER()` / `RANK()` with one `PARTITION BY` column
One nonrecursive materialized CTE form	see the semantics contract

In a bounded scan, ORDER BY must list the complete primary key, in catalog order, and LIMIT is mandatory. SELECT ... WHERE stars >= ? ORDER BY id fails with sql_invalid_syntax because the range predicate targets a non-key column. Free-form aggregation, arbitrary ORDER BY expressions, and disk spill all fail closed rather than degrading.

There are no subqueries, no UNION, and no outer join in the grammar. Every mutation accepts an explicit --durability strict|group|memory flag (see Transactions and proofs), and hyphae explain --data-dir "$D" sql --statement '...' returns bounded plan text without executing anything, for example PrimaryKeyLookup(table=4).

Grouped queries: GROUP BY, HAVING, aggregates

Grouped queries admit total and primary-key-prefix grouped aggregates (COUNT, SUM, MIN, MAX, AVG) with HAVING and an ORDER BY over group keys or aggregates, both with aliases:

hyphae sql --data-dir "$D" execute --statement \
  'SELECT kind, COUNT(*) AS n, SUM(stars) AS total FROM notes
   GROUP BY kind HAVING COUNT(*) >= 2 ORDER BY total DESC LIMIT 10'

{
  "commit": null,
  "result": {
    "columns": ["kind", "n", "total"],
    "rows": [["article", 3, 10], ["memo", 3, 8]],
    "type": "rows"
  },
  "snapshot": { "catalog_version": 3, "visible_csn": 8, ... }
}

HAVING's right-hand side must be a literal — a bound parameter inside HAVING fails closed, which is the one place in the grammar where a parameter is not admitted anywhere else a literal is.

DISTINCT, OFFSET, BETWEEN, aliases

SELECT DISTINCT is admitted for plain projections only. OFFSET is admitted with LIMIT still mandatory. BETWEEN desugars to >= AND <= and accepts bound parameters. Every projection item, plain or aggregate, admits one AS <identifier> alias.

hyphae sql --data-dir "$D" execute --statement \
  'SELECT DISTINCT kind FROM notes ORDER BY id LIMIT 10'
# → columns ["kind"], rows [["article"], ["memo"]]

hyphae sql --data-dir "$D" execute --statement \
  'SELECT id, body FROM notes ORDER BY id LIMIT 2 OFFSET 2'
# → rows for id 3 and 4 — the first two matching rows are skipped, not returned

hyphae sql --data-dir "$D" execute --statement \
  'SELECT id, body, stars FROM notes WHERE id BETWEEN ? AND ? ORDER BY id LIMIT 10' \
  --parameter 2 --parameter 5
# → rows for id 2..5 inclusive

hyphae sql --data-dir "$D" execute --statement \
  'SELECT id AS note_id, body AS text FROM notes ORDER BY id LIMIT 3'
# → columns ["note_id", "text"], rows [[1, "first offline note"], ...]

Prepared statements

hyphae sql --data-dir "$D" prepared --statement '...' [--parameter ...] prepares, executes, and deallocates one query inside a retained session. The response includes both the rows and the read snapshot — catalog version, visible_csn, root digest — so a caller running SQL alongside a structure or search read can correlate all three against the same point in time.

Fail-closed by design: HYSQL011 and sql_invalid_syntax

Two distinct failure modes cover everything outside the admitted grammar:

sql_invalid_syntax — the statement parses but asks for a shape the grammar does not admit: a range predicate on a non-key column, a scan missing LIMIT, a parameter inside HAVING.
HYSQL011 — the plan cannot bind to an access path (index or primary key) at all, so Hyphae refuses to fall back to an unbounded scan.

Both fail before any partial mutation is staged. Neither is a bug to work around with a different query shape that happens to compile — they are the boundary itself, and the fix is almost always to add an index, add the missing key predicate, or restructure the query into an admitted shape.