AkurAI-Framework / site/content/docs/sql-executor.md

Latest change fa37e2544d31a1199954a395f9e5b3ebb237b17f - feat(sql): aggregates (COUNT/SUM/AVG/MIN/MAX) + GROUP BY (gpt-5.5) by Ólafur Búi Ólafsson

# SQL Executor

The `akurai-sqlexec` crate is the back half of the query engine. Where
`akurai-sql` turns SQL text into an AST, the executor *runs* that AST over the
`akurai-storage` copy-on-write B+tree. It is pure `std` with zero external
dependencies, like every shipped crate.

```rust
use akurai_sqlexec::{Executor, Outcome};

let mut db = Executor::open("app.db").unwrap();
db.execute("CREATE TABLE users (id INTEGER NOT NULL, name TEXT, age INTEGER)").unwrap();
db.execute("INSERT INTO users (id, name, age) VALUES (1, 'ada', 36)").unwrap();

if let Outcome::Rows(rs) = db.execute(
    "SELECT name, age FROM users WHERE age >= 18 ORDER BY age DESC LIMIT 10"
).unwrap() {
    println!("{:?}", rs.columns); // ["name", "age"]
    println!("{:?}", rs.rows);
}
```

Aggregate queries use the same scan path:

```rust
if let Outcome::Rows(rs) = db.execute(
    "SELECT age, COUNT(*), AVG(score) FROM users WHERE active = 1 GROUP BY age ORDER BY age"
).unwrap() {
    println!("{:?}", rs.columns); // ["age", "COUNT(*)", "AVG(score)"]
    println!("{:?}", rs.rows);
}
```

## Supported statements

The executor runs one statement per `execute` call and returns an `Outcome`:

| Statement | What it does | Outcome |
|-----------|--------------|---------|
| `CREATE TABLE t (col TYPE [NOT NULL], …)` | Registers a table schema. Rejects a duplicate table. | `Created(table)` |
| `INSERT INTO t (cols) VALUES (vals)` | Validates against the schema, materializes one typed row, writes it. | `Affected(1)` |
| `UPDATE t SET col = val [, …] [WHERE …]` | Scans, filters by `WHERE`, rewrites each matching row in place (same rowid), re-validating `SET` values against the column types. | `Affected(n)` |
| `DELETE FROM t [WHERE …]` | Scans, filters by `WHERE`, removes each matching row. Without `WHERE`, clears the table. | `Affected(n)` |
| `SELECT <*|cols|aggregates> FROM t [WHERE …] [GROUP BY col] [ORDER BY col [ASC|DESC]] [LIMIT n]` | Scans, filters, optionally groups and aggregates, sorts, limits, and projects. | `Rows(ResultSet)` |

A `ResultSet` carries the projected `columns` (names, left to right) and `rows`
(each a `Vec<Value>`, one cell per projected column).

### WHERE

`WHERE` supports the comparison operators `=`, `!=` / `<>`, `<`, `<=`, `>`, `>=`
between a column and a literal (either order), combined with `AND`, `OR`, `NOT`,
and parentheses — `AND` binds tighter than `OR`. Comparisons use numeric
semantics across `INTEGER`/`REAL`, and lexical order for `TEXT`. Any comparison
that involves a `NULL` or two incomparable types evaluates to false rather than
erroring, so a single odd row never aborts a scan.

### ORDER BY and LIMIT

`ORDER BY <column> [ASC|DESC]` sorts the surviving rows; `NULL` sorts first.
`LIMIT n` then truncates to the first `n` rows.

### Aggregates and GROUP BY

`SELECT` supports `COUNT(*)`, `COUNT(col)`, `SUM(col)`, `AVG(col)`, `MIN(col)`,
and `MAX(col)`. `COUNT(*)` counts all rows in the current input or group;
`COUNT(col)` counts non-`NULL` cells. `SUM` and `AVG` ignore `NULL` cells and
return `NULL` when there are no numeric inputs. `SUM` over integer input returns
an integer unless the sum overflows, in which case it falls back to a float.
`AVG` returns a float. `MIN` and `MAX` ignore `NULL` and use the executor's total
value ordering.

`GROUP BY` is intentionally narrow: one column only. A grouped select may project
the group column and aggregate calls. `WHERE` runs before grouping, and
`ORDER BY`/`LIMIT` run on the grouped result set.

## Row and key model

Each table owns three kinds of keys in the shared B+tree:

- **Catalog** — `\x00cat:<table>` holds the encoded schema (column names, types,
  `NOT NULL` flags). The `\x00` prefix is reserved; table names are SQL
  identifiers and never begin with it, so catalog and data keys can never
  collide.
- **Rowid counter** — `\x00seq:<table>` holds a big-endian `u64` of the last
  rowid handed out. It is seeded at `CREATE TABLE` and bumped per insert, so
  rowids keep climbing across reopens.
- **Data** — `<table>:<rowid_be>` holds one encoded row. The 8-byte big-endian
  rowid makes data keys sort in insertion order, and a `SELECT` scans the whole
  table with a single half-open `range` over the `<table>:` prefix.

A row is encoded as a self-describing sequence of cells: a length prefix, then
for each column a one-byte type tag (`NULL`/`INT`/`FLOAT`/`STR`) followed by its
payload. Integers and floats are fixed 8-byte big-endian; text is a length plus
UTF-8 bytes.

## Persistence

Every `CREATE TABLE`, `INSERT`, `UPDATE`, and `DELETE` ends with a storage-layer
`commit()`, which fsyncs the new pages and atomically swaps the B+tree root.
Reopening the database file therefore sees every committed table and row (and
every committed update or delete), and the rowid sequence resumes where it left
off.

## Limitations (this slice)

This is a deliberately tight slice — CREATE / INSERT / UPDATE / DELETE / SELECT
end to end. Not yet implemented:

- **Joins and subqueries.**
- **Indexes** (including HNSW / vector search) — every `SELECT` is a full table
  scan.
- **Transactions beyond the storage layer's per-statement `commit`** — there is
  no multi-statement `BEGIN`/`COMMIT`/`ROLLBACK`.