AxiomDB

AxiomDB is a database engine written in Rust, designed to be fast, correct, and modern — while remaining compatible with the MySQL wire protocol so existing applications can connect without driver changes.

Goals

Two Usage Modes

┌─────────────────────┐         ┌──────────────────────────┐
│   SERVER MODE       │         │   EMBEDDED MODE          │
│                     │         │                          │
│  TCP :3306          │         │  Direct function call    │
│  MySQL wire proto   │         │  C FFI / Rust API        │
│  PHP, Python, Node  │         │  No network, no daemon   │
└─────────────────────┘         └──────────────────────────┘
          └─────────────────┬─────────────────┘
                            │
                    Same Rust engine

Current Status

AxiomDB is under active development. Phases 1–6 are substantially complete:

• ✅ Storage engine — mmap-based 16 KB pages, freelist, heap pages, CRC32c checksums
• ✅ B+ Tree — Copy-on-Write, lock-free readers, prefix compression, range scan
• ✅ WAL — append-only, crash recovery, Group Commit, PageWrite bulk optimization
• ✅ Catalog — schema management, DDL change notifications, MVCC-consistent reads
• ✅ SQL layer — full DDL + DML parser, expression evaluator, semantic analyzer
• ✅ Executor — SELECT/INSERT/UPDATE/DELETE, JOIN, GROUP BY + aggregates, ORDER BY, subqueries, CASE WHEN, DISTINCT, TRUNCATE, ALTER TABLE
• ✅ Secondary indexes — CREATE INDEX, UNIQUE, query planner (index lookup + range)
• ✅ MySQL wire protocol — port 3306, COM_QUERY, prepared statements, pymysql compatible

Current concurrency model: read-only queries run concurrently, but mutating statements are still serialized through a database-wide Arc<RwLock<Database>> write guard. Row-level locking and true concurrent writers are planned for Phase 13.7+.

Performance highlights

What Makes AxiomDB Different

1. No double-write buffer

MySQL InnoDB uses a double-write buffer to protect against partial page writes, adding significant write overhead. AxiomDB uses a WAL-first architecture — pages are protected by the write-ahead log, eliminating this overhead entirely.

2. Lock-free read path

The B+ Tree uses Copy-on-Write semantics with an atomic root pointer, so the storage layer itself does not need per-page read latches. In the current server runtime, read-only queries execute concurrently, while mutating statements are still serialized by a database-wide RwLock write guard. Row-level write concurrency is the next planned step.

3. Smart collation out of the box

Most databases require explicit COLLATE declarations for correct Unicode sorting. AxiomDB defaults to UCA root collation (language-neutral Unicode ordering) and can be configured to behave like MySQL or PostgreSQL for migrations.

4. Strict mode always on

AxiomDB rejects data truncation, invalid dates (0000-00-00), and silent type coercions that MySQL allows by default. With SET AXIOM_COMPAT = 'mysql', lenient behavior is restored for migration scenarios.

5. Structured error messages

Inspired by the Rust compiler, every error includes: what went wrong, which table/column was involved, the offending value, and a hint for how to fix it.

Parser Performance

AxiomDB’s SQL parser is 9–17× faster than sqlparser-rs (the production standard used by Apache Arrow DataFusion and Delta Lake):

This is achieved through a zero-copy lexer (identifiers are &str slices into the input — no heap allocations) combined with a hand-written recursive descent parser.

Getting Started

AxiomDB is a relational database engine written in Rust. It supports standard SQL, ACID transactions, a Write-Ahead Log for crash recovery, and a Copy-on-Write B+ Tree for lock-free concurrent reads. This guide walks you through connecting to AxiomDB, choosing a usage mode, and running your first queries.

Choosing a Usage Mode

AxiomDB operates in two distinct modes that share the exact same engine code.

Server Mode

The engine runs as a standalone daemon that speaks the MySQL wire protocol on TCP port 3306 (configurable). Any MySQL-compatible client connects without installing custom drivers.

Application (PHP / Python / Node.js)
        │
        │ TCP :3306  (MySQL wire protocol)
        ▼
  axiomdb-server process
        │
        ▼
  axiomdb.db   axiomdb.wal

When to use server mode:

• Web applications with REST or GraphQL APIs
• Microservices where multiple processes share a database
• Any environment where you would normally use MySQL

Embedded Mode

The engine is compiled into your process as a shared library (.so / .dylib / .dll). There is no daemon, no network, and no port. Calls go directly to Rust code with microsecond latency.

Your Application (Rust / C++ / Python / Electron)
        │
        │ direct function call (C FFI / Rust crate)
        ▼
  AxiomDB engine (in-process)
        │
        ▼
  axiomdb.db   axiomdb.wal   (local files)

When to use embedded mode:

• Desktop applications (Qt, Electron, Tauri)
• CLI tools that need a local database
• Python scripts that need fast local storage without a daemon
• Any context where SQLite would be considered

Mode Comparison

Interactive Shell (CLI)

The axiomdb-cli binary connects directly to a database file — no server needed. It works like sqlite3 or psql:

# Open an existing database (or create a new one)
axiomdb-cli ./mydb.db

# Pipe SQL from a file
axiomdb-cli ./mydb.db < migration.sql

# One-liner
echo "SELECT COUNT(*) FROM users;" | axiomdb-cli ./mydb.db

Inside the shell:

AxiomDB 0.1.0 — interactive shell
Type SQL ending with ; to execute. Type .help for commands.

axiomdb> CREATE TABLE users (id INT, name TEXT);
OK (1ms)

axiomdb> INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');
2 rows affected (0ms)

axiomdb> SELECT * FROM users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
+----+-------+
2 rows (0ms)

axiomdb> .tables
users

axiomdb> .schema users
Table: users
  id    INT   NOT NULL
  name  TEXT  nullable

axiomdb> .quit
Bye.

Dot commands: .help · .tables · .schema [table] · .open <path> · .quit

Keyboard shortcuts (interactive mode): ↑ / ↓ history · Tab SQL completion · Ctrl-R reverse search · Ctrl-C cancel line · Ctrl-D exit. History is saved to ~/.axiomdb_history between sessions.

Server Mode — Connecting

Starting the Server

# Default: stores data in ./data, listens on port 3306
axiomdb-server

# Legacy env vars
AXIOMDB_DATA=/var/lib/axiomdb AXIOMDB_PORT=3307 axiomdb-server

# DSN bootstrap (Phase 5.15)
AXIOMDB_URL='axiomdb://0.0.0.0:3307/axiomdb?data_dir=/var/lib/axiomdb' axiomdb-server

The server is ready when you see:

INFO axiomdb_server: listening on 0.0.0.0:3306

In Phase 5.15, AXIOMDB_URL supports axiomdb://, mysql://, postgres://, and postgresql:// URI syntax. The alias schemes are parse aliases only: axiomdb-server still speaks the MySQL wire protocol only.

Supported server DSN fields:

• host and port from the URI authority
• data_dir from the query string

Unsupported query params are rejected explicitly instead of being ignored.

Connecting with the mysql CLI

mysql -h 127.0.0.1 -P 3306 -u root

No password is required in Phase 5. Any username from the allowlist (root, axiomdb, admin) is accepted. See the Authentication section below for details.

Connecting with Python (PyMySQL)

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    db='axiomdb',
    charset='utf8mb4',
)

with conn.cursor() as cursor:
    # CREATE TABLE with AUTO_INCREMENT
    cursor.execute("""
        CREATE TABLE users (
            id    BIGINT PRIMARY KEY AUTO_INCREMENT,
            name  TEXT   NOT NULL,
            email TEXT   NOT NULL
        )
    """)

    # INSERT — last_insert_id is returned in the OK packet
    cursor.execute("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')")
    print("inserted id:", cursor.lastrowid)

    # SELECT
    cursor.execute("SELECT id, name FROM users")
    for row in cursor.fetchall():
        print(row)

conn.close()

Parameterized Queries and ORMs (Prepared Statements)

When you pass parameters to cursor.execute(), PyMySQL (and any MySQL-compatible driver) automatically uses COM_STMT_PREPARE / COM_STMT_EXECUTE — the MySQL binary prepared statement protocol. AxiomDB supports this natively from Phase 5.10.

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', db='axiomdb')

with conn.cursor() as cursor:
    cursor.execute("""
        CREATE TABLE products (
            id    BIGINT PRIMARY KEY AUTO_INCREMENT,
            name  TEXT   NOT NULL,
            price DOUBLE NOT NULL,
            active BOOL  NOT NULL DEFAULT TRUE
        )
    """)
    conn.commit()

    # Parameterized INSERT — uses COM_STMT_PREPARE/EXECUTE automatically
    cursor.execute(
        "INSERT INTO products (name, price, active) VALUES (%s, %s, %s)",
        ('Wireless Keyboard', 49.99, True),
    )

    # NULL parameters work transparently
    cursor.execute(
        "INSERT INTO products (name, price, active) VALUES (%s, %s, %s)",
        ('USB-C Hub', 29.99, None),
    )

    # Parameterized SELECT
    cursor.execute("SELECT id, name, price FROM products WHERE price < %s", (50.0,))
    for row in cursor.fetchall():
        print(row)

    # Boolean column comparison works with integer literals (MySQL-compatible)
    cursor.execute("SELECT name FROM products WHERE active = %s", (1,))
    for row in cursor.fetchall():
        print(row)

conn.close()

ORMs such as SQLAlchemy use parameterized queries for all data-bearing operations. Connecting through the MySQL dialect works without any additional configuration:

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/axiomdb")

with engine.connect() as conn:
    result = conn.execute(
        text("SELECT id, name FROM products WHERE price < :max_price"),
        {"max_price": 40.0},
    )
    for row in result:
        print(row)

Connecting with PHP (PDO)

<?php
$pdo = new PDO(
    'mysql:host=127.0.0.1;port=3306;dbname=axiomdb',
    'root',
    '',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$stmt = $pdo->query('SELECT id, name FROM users LIMIT 5');
foreach ($stmt as $row) {
    echo $row['id'] . ': ' . $row['name'] . "\n";
}

Connecting with any MySQL GUI

Point MySQL Workbench, DBeaver, or TablePlus to 127.0.0.1:3306. No driver installation is required — the MySQL wire protocol is fully compatible.

Charset and collation

AxiomDB negotiates charset and collation at the MySQL handshake boundary. The client sends its preferred collation id in the HandshakeResponse41 packet; the server reads it and configures the session accordingly.

Supported charsets:

You can change the session charset at any time:

SET NAMES utf8mb4;                          -- sets client + connection + results
SET NAMES latin1 COLLATE latin1_bin;        -- with explicit collation
SET character_set_results = utf8mb4;        -- results charset only

Authentication

AxiomDB Phase 5 uses permissive authentication: the server accepts any password for usernames in the allowlist (root, axiomdb, admin, and the empty string). Both of the most common MySQL authentication plugins are supported with no client-side configuration required:

If your client connects with MySQL 8.0+ defaults and you see silent connection drops, your client is using caching_sha2_password — AxiomDB handles this automatically. No --default-auth flag or authPlugin option is needed.

Full password enforcement with stored credentials is planned for Phase 13 (Security).

Monitoring with SHOW STATUS

Monitoring tools, proxy servers, and health checks can query live server counters using the standard MySQL SHOW STATUS syntax:

SHOW STATUS
SHOW GLOBAL STATUS
SHOW SESSION STATUS
SHOW STATUS LIKE 'Threads%'
SHOW GLOBAL STATUS LIKE 'Com_%'

Available variables:

Session scope (SHOW STATUS, SHOW SESSION STATUS, SHOW LOCAL STATUS) returns per-connection values. Global scope (SHOW GLOBAL STATUS) returns server-wide totals. Session counters reset when a connection is closed or COM_RESET_CONNECTION is issued.

Connection Timeout Variables

AxiomDB exposes the same timeout variables that MySQL clients expect at the session level:

SET wait_timeout = 30;
SET interactive_timeout = 300;
SET net_read_timeout = 60;
SET net_write_timeout = 60;

SELECT @@wait_timeout;
SELECT @@interactive_timeout;
SELECT @@net_read_timeout;
SELECT @@net_write_timeout;

Rules:

• wait_timeout applies while a non-interactive connection is idle between commands.
• interactive_timeout applies instead when the client connected with CLIENT_INTERACTIVE.
• net_write_timeout bounds packet writes once a command is already executing.
• net_read_timeout is reserved for future in-flight protocol reads and is already validated/stored as a real session variable.
• COM_RESET_CONNECTION resets all four variables back to their defaults.

Trying to set one of these variables to 0 or to a non-integer value returns an error:

SET wait_timeout = 0;
-- ERROR ... wait_timeout must be a positive integer, got '0'

Embedded Mode — Rust API

Add AxiomDB to your Cargo.toml:

[dependencies]
axiomdb-embedded = { path = "../axiomdb/crates/axiomdb-embedded" }

Open a Database

use axiomdb_embedded::Db;

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut db = Db::open("./axiomdb.db")?;
    let mut db2 = Db::open_dsn("file:/tmp/axiomdb.db")?;
    let mut db3 = Db::open_dsn("axiomdb:///tmp/axiomdb")?;

    db.execute("CREATE TABLE users (id INT, name TEXT, age INT)")?;
    db.execute("INSERT INTO users VALUES (1, 'Alice', 30)")?;
    db.execute("INSERT INTO users VALUES (2, 'Bob', 25)")?;

    let (columns, rows) = db.query_with_columns(
        "SELECT id, name, age FROM users WHERE age > 20 ORDER BY name"
    )?;
    println!("{columns:?}");
    for row in rows {
        println!("{row:?}");
    }

    Ok(())
}

Db::open_dsn(...) accepts only local DSNs in Phase 5.15. Remote wire-endpoint DSNs such as postgres://... parse successfully in the shared parser but are rejected by the embedded API.

Explicit Transactions

#![allow(unused)]
fn main() {
let mut db = axiomdb_embedded::Db::open("./axiomdb.db")?;
db.begin()?;
db.execute("INSERT INTO accounts VALUES (1, 'Alice', 1000.0)")?;
db.execute("INSERT INTO accounts VALUES (2, 'Bob', 500.0)")?;
db.commit()?;
}

Embedded Mode — C FFI

For C, C++, Qt, or Java (JNI):

#include "axiomdb.h"

int main(void) {
    AxiomDb* db = axiomdb_open("./axiomdb.db");
    AxiomDb* db2 = axiomdb_open_dsn("file:/tmp/axiomdb.db");
    if (!db) { fprintf(stderr, "failed to open\n"); return 1; }

    axiomdb_execute(db, "CREATE TABLE users (id INT, name TEXT)");
    axiomdb_execute(db, "INSERT INTO users VALUES (1, 'Alice')");
    axiomdb_close(db);
    axiomdb_close(db2);
    return 0;
}

Python via ctypes

import ctypes

lib = ctypes.CDLL("./libaxiomdb.dylib")
lib.axiomdb_open.restype  = ctypes.c_void_p
lib.axiomdb_open_dsn.restype = ctypes.c_void_p
lib.axiomdb_close.argtypes = [ctypes.c_void_p]
lib.axiomdb_execute.restype = ctypes.c_longlong

db = lib.axiomdb_open(b"./axiomdb.db")
db2 = lib.axiomdb_open_dsn(b"file:/tmp/axiomdb.db")
lib.axiomdb_execute(db, b"CREATE TABLE t (id INT)")
lib.axiomdb_close(db)
lib.axiomdb_close(db2)

Your First Schema — End to End

The following example creates a minimal e-commerce schema, inserts sample data, and runs a join query — all within embedded mode.

-- Create tables
CREATE TABLE products (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    name        TEXT        NOT NULL,
    price       DECIMAL     NOT NULL,
    stock       INT         NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    product_id  BIGINT      NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity    INT         NOT NULL,
    placed_at   TIMESTAMP   NOT NULL
);

CREATE INDEX idx_orders_product ON orders (product_id);

-- Insert data
INSERT INTO products (name, price, stock) VALUES
    ('Wireless Keyboard', 49.99, 200),
    ('USB-C Hub',         29.99, 500),
    ('Mechanical Mouse',  39.99, 150);

INSERT INTO orders (product_id, quantity, placed_at) VALUES
    (1, 2, '2026-03-01 10:00:00'),
    (2, 1, '2026-03-02 14:30:00'),
    (1, 1, '2026-03-03 09:15:00');

-- Query with JOIN
SELECT
    p.name,
    o.quantity,
    p.price * o.quantity AS line_total,
    o.placed_at
FROM orders o
JOIN products p ON p.id = o.product_id
ORDER BY o.placed_at;

Expected output:

Bulk Insert — Best Practices

The way you issue INSERT statements has a large impact on throughput. AxiomDB is optimized for the multi-row VALUES form — one SQL string with all N rows:

-- Fast: one SQL string, all rows in one VALUES clause (~211K rows/s for 10K rows)
INSERT INTO products (name, price, stock) VALUES
  ('Widget A', 9.99, 100),
  ('Widget B', 14.99, 50),
  ('Widget C', 4.99, 200);

# Python — build one multi-row string, one execute() call
rows = [(f"product_{i}", i * 1.5, i * 10) for i in range(10_000)]
placeholders = ", ".join("(%s, %s, %s)" for _ in rows)
flat_values   = [v for row in rows for v in row]
cursor.execute(f"INSERT INTO products (name, price, stock) VALUES {placeholders}",
               flat_values)
conn.commit()

Why this matters: issuing N separate INSERT statements each pays its own parse + analyze overhead (~20 µs per string). A single multi-row string pays that cost once for all rows.

Next Steps

• SQL Reference — Data Types — full type system
• SQL Reference — DDL — CREATE TABLE, indexes, constraints
• SQL Reference — DML — SELECT, INSERT, UPDATE, DELETE
• Transactions — BEGIN, COMMIT, ROLLBACK, MVCC
• Performance — benchmark numbers and tuning tips

SQL Reference

This section covers the complete SQL dialect supported by AxiomDB.

• Data Types — all supported column types with storage sizes and usage examples
• DDL — Schema Definition — CREATE TABLE, CREATE INDEX, DROP TABLE, DROP INDEX, constraints
• DML — Queries & Mutations — SELECT, INSERT, UPDATE, DELETE with full clause reference
• Expressions & Operators — operators, functions, NULL semantics, LIKE, IN, BETWEEN

Data Types

AxiomDB implements a rich type system that covers the common SQL standard types as well as several extensions for modern workloads (UUID, JSON, VECTOR for AI embeddings, RANGE types for temporal and numeric overlaps).

Integer Types

-- Typical primary key
CREATE TABLE users (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    age  SMALLINT NOT NULL
);

-- Unsigned counter that never goes negative
CREATE TABLE page_views (
    page_id  INT  NOT NULL,
    views    UINT NOT NULL DEFAULT 0
);

Floating-Point Types

NaN is forbidden. The row codec rejects NaN values at encode time. IEEE 754 infinities are also not accepted by default.

-- Geospatial coordinates (4-byte precision is sufficient)
CREATE TABLE locations (
    id   INT   PRIMARY KEY,
    lat  REAL  NOT NULL,
    lon  REAL  NOT NULL
);

-- Scientific measurements requiring high precision
CREATE TABLE experiments (
    id      INT    PRIMARY KEY,
    result  DOUBLE NOT NULL
);

Exact Numeric — DECIMAL

Always use DECIMAL for money. Floating-point types cannot represent 0.1 + 0.2 exactly; DECIMAL always can.

CREATE TABLE invoices (
    id       BIGINT       PRIMARY KEY AUTO_INCREMENT,
    subtotal DECIMAL      NOT NULL,    -- DECIMAL without precision = DECIMAL(38,0)
    tax_rate DECIMAL      NOT NULL,
    total    DECIMAL      NOT NULL
);

-- Insert with exact values
INSERT INTO invoices (subtotal, tax_rate, total)
VALUES (199.99, 0.19, 237.99);

-- Arithmetic is always exact
SELECT subtotal * tax_rate AS computed_tax FROM invoices WHERE id = 1;
-- Returns: 37.9981  (never 37.99809999999...)

The internal codec stores DECIMAL as a 16-byte little-endian i128 mantissa followed by a 1-byte scale (total 17 bytes per non-NULL value).

Text Types

The codec encodes TEXT and VARCHAR with a 3-byte (u24) length prefix followed by raw UTF-8 bytes. This limits inline storage to 16,777,215 bytes; values larger than a page use TOAST (planned Phase 6).

-- Fixed-length codes (ISO country, state abbreviations)
CREATE TABLE countries (
    code  CHAR(2)      PRIMARY KEY,   -- 'US', 'DE', 'JP'
    name  VARCHAR(128) NOT NULL
);

-- Unlimited text content
CREATE TABLE blog_posts (
    id      BIGINT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(512) NOT NULL,
    body    TEXT         NOT NULL
);

-- Case-insensitive email lookup
CREATE TABLE users (
    id    BIGINT PRIMARY KEY AUTO_INCREMENT,
    email CITEXT NOT NULL UNIQUE
);
-- SELECT * FROM users WHERE email = 'ALICE@EXAMPLE.COM'
-- matches rows where email = 'alice@example.com'

Binary Type

CREATE TABLE attachments (
    id      BIGINT PRIMARY KEY AUTO_INCREMENT,
    name    TEXT   NOT NULL,
    content BYTEA  NOT NULL
);

-- Insert binary with hex literal
INSERT INTO attachments (name, content) VALUES ('icon.png', X'89504e47');

-- Display as hex
SELECT name, encode(content, 'hex') FROM attachments;

Date and Time Types

Prefer TIMESTAMPTZ over TIMESTAMP. Without a timezone, there is no way to determine the absolute instant when the server and client are in different timezones. TIMESTAMPTZ stores everything as UTC and converts on display.

CREATE TABLE events (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    title       TEXT        NOT NULL,
    starts_at   TIMESTAMPTZ NOT NULL,
    ends_at     TIMESTAMPTZ NOT NULL,
    duration    INTERVAL
);

INSERT INTO events (title, starts_at, ends_at, duration)
VALUES (
    'Team meeting',
    '2026-03-21 10:00:00+00',
    '2026-03-21 11:00:00+00',
    '1 hour'
);

INTERVAL — Calendar-Correct Arithmetic

INTERVAL separates months, days, and microseconds because they are not fixed durations:

• “1 month” added to January 31 gives February 28 (or 29).
• “1 day” during a DST transition can be 23 or 25 hours.

-- Add 1 month to a date (calendar-aware)
SELECT '2026-01-31'::DATE + INTERVAL '1 month';  -- 2026-02-28

-- Add 30 days (fixed)
SELECT '2026-01-31'::DATE + INTERVAL '30 days';  -- 2026-03-02

UUID

CREATE TABLE sessions (
    id         UUID   PRIMARY KEY DEFAULT gen_uuid_v7(),
    user_id    BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

UUID v7 vs v4 as Primary Key:

For new schemas, prefer UUID v7 (time-sortable) or BIGINT AUTO_INCREMENT.

Network Types

CREATE TABLE access_log (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    client_ip  INET   NOT NULL,
    network    CIDR,
    mac        MACADDR
);

JSON / JSONB

CREATE TABLE api_responses (
    id       BIGINT PRIMARY KEY AUTO_INCREMENT,
    endpoint TEXT   NOT NULL,
    payload  JSON   NOT NULL
);

INSERT INTO api_responses (endpoint, payload)
VALUES ('/users', '{"count": 42, "items": []}');

VECTOR — AI Embeddings

-- Store sentence embeddings from an AI model
CREATE TABLE documents (
    id        BIGINT      PRIMARY KEY AUTO_INCREMENT,
    content   TEXT        NOT NULL,
    embedding VECTOR(384) NOT NULL   -- e.g. all-MiniLM-L6-v2 output
);

-- Approximate nearest-neighbor search (ANN index required)
SELECT id, content
FROM documents
ORDER BY embedding <-> '[0.12, 0.34, ...]'::vector
LIMIT 10;

RANGE Types

RANGE types represent a continuous span of a base type, with inclusive/exclusive bounds. They support containment (@>), overlapping (&&), and exclusion constraints.

-- Prevent overlapping reservations using an exclusion constraint
CREATE TABLE room_reservations (
    room_id   INT     NOT NULL,
    period    TSRANGE NOT NULL,
    EXCLUDE USING gist(room_id WITH =, period WITH &&)
);

INSERT INTO room_reservations VALUES (1, '[2026-03-21 09:00, 2026-03-21 11:00)');
-- This next insert fails: the period overlaps with the existing row
INSERT INTO room_reservations VALUES (1, '[2026-03-21 10:00, 2026-03-21 12:00)');
-- ERROR: exclusion constraint violation

NULL in Every Type

Every column of every type can hold NULL unless declared NOT NULL. The row codec stores a compact null bitmap at the start of each row (1 bit per column), so NULL costs only 1 bit of overhead regardless of the underlying type size.

SELECT NULL + 5;         -- NULL  (any arithmetic with NULL propagates NULL)
SELECT NULL = NULL;      -- NULL  (not TRUE — use IS NULL instead)
SELECT NULL IS NULL;     -- TRUE
SELECT COALESCE(NULL, 0); -- 0   (return first non-NULL argument)

See Expressions & Operators for the full NULL semantics table.

DDL — Schema Definition Language

DDL statements define and modify the structure of the database: tables, columns, constraints, and indexes. All DDL operations are transactional in AxiomDB — a failed DDL statement is automatically rolled back.

CREATE DATABASE

Creates a new logical database in the persisted catalog.

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE analytics;
SHOW DATABASES;

Expected output includes:

CREATE DATABASE fails if the name already exists:

CREATE DATABASE analytics;
-- ERROR 1007 (HY000): Can't create database 'analytics'; database exists

DROP DATABASE

Removes a logical database from the catalog.

Syntax

DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

Behavior

• Removing a database also removes the tables it owns from SQL/catalog lookup.
• IF EXISTS suppresses the error for a missing database.
• The current connection cannot drop the database it has selected with USE.

DROP DATABASE analytics;

DROP DATABASE IF EXISTS scratch;

USE analytics;
DROP DATABASE analytics;
-- ERROR 1105 (HY000): Can't drop database 'analytics'; database is currently selected

CREATE TABLE

Basic Syntax

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name  data_type  [column_constraints...],
    ...
    [table_constraints...]
);

Column Constraints

NOT NULL

Rejects any attempt to insert or update a row with a NULL value in this column.

CREATE TABLE employees (
    id    BIGINT NOT NULL,
    name  TEXT   NOT NULL,
    dept  TEXT            -- nullable: dept may be unassigned
);

DEFAULT

Provides a value when the column is omitted from INSERT.

CREATE TABLE orders (
    id         BIGINT   PRIMARY KEY AUTO_INCREMENT,
    status     TEXT     NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    priority   INT      NOT NULL DEFAULT 0
);

-- Default values are used automatically
INSERT INTO orders (status) VALUES ('shipped');
-- Row: id=<auto>, status='shipped', created_at=<now>, priority=0

PRIMARY KEY

Declares a column (or set of columns) as the primary key. A primary key:

• Implies NOT NULL
• Creates a unique B+ Tree index automatically
• Is used for REFERENCES in foreign keys

-- Single-column primary key
CREATE TABLE users (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name TEXT   NOT NULL
);

-- Composite primary key (declared as table constraint)
CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity   INT    NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

UNIQUE

Guarantees no two rows share the same value in this column (or set of columns). NULL values are excluded from uniqueness checks — multiple NULLs are allowed.

CREATE TABLE accounts (
    id       BIGINT PRIMARY KEY AUTO_INCREMENT,
    email    TEXT   NOT NULL UNIQUE,
    username TEXT   NOT NULL UNIQUE
);

AUTO_INCREMENT / SERIAL

Automatically generates a monotonically increasing integer for each new row. The counter starts at 1 and increments by 1 for each inserted row. The following forms are all equivalent:

-- MySQL-style
id BIGINT PRIMARY KEY AUTO_INCREMENT

-- PostgreSQL-style shorthand (SERIAL = INT AUTO_INCREMENT, BIGSERIAL = BIGINT AUTO_INCREMENT)
id SERIAL    PRIMARY KEY
id BIGSERIAL PRIMARY KEY

Behavior:

CREATE TABLE users (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name TEXT   NOT NULL
);

-- Omit the AUTO_INCREMENT column — the engine generates the value
INSERT INTO users (name) VALUES ('Alice');   -- id = 1
INSERT INTO users (name) VALUES ('Bob');     -- id = 2

-- Retrieve the last generated ID (current session only)
SELECT LAST_INSERT_ID();   -- returns 2
SELECT lastval();          -- PostgreSQL alias — same result

-- Multi-row INSERT: LAST_INSERT_ID() returns the ID of the FIRST row in the batch
INSERT INTO users (name) VALUES ('Carol'), ('Dave');  -- ids: 3, 4
SELECT LAST_INSERT_ID();   -- returns 3

-- Explicit non-NULL value bypasses the sequence and does NOT advance it
INSERT INTO users (id, name) VALUES (100, 'Eve');
-- id=100; sequence remains at 4; next auto id will be 5

LAST_INSERT_ID() returns 0 if no auto-increment INSERT has been performed in the current session. See LAST_INSERT_ID() in expressions for the full function reference.

TRUNCATE resets the counter:

TRUNCATE TABLE users;
INSERT INTO users (name) VALUES ('Frank');  -- id = 1 (reset by TRUNCATE)

REFERENCES — Foreign Keys

Declares a foreign key relationship to another table’s primary key.

CREATE TABLE orders (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    placed_at  TIMESTAMP NOT NULL
);

ON DELETE actions:

ON UPDATE actions: Same options as ON DELETE — apply when the referenced primary key is updated.

Current limitation: Only ON UPDATE RESTRICT (the default) is enforced. ON UPDATE CASCADE and ON UPDATE SET NULL return NotImplemented and are planned for Phase 6.10. Write ON UPDATE RESTRICT or omit the clause entirely for correct behaviour today.

CREATE TABLE order_items (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id   BIGINT NOT NULL
        REFERENCES orders(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    product_id BIGINT NOT NULL
        REFERENCES products(id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    quantity   INT    NOT NULL,
    unit_price DECIMAL NOT NULL
);

CHECK

Validates that a condition is TRUE for every row. A row where the CHECK condition evaluates to FALSE or NULL is rejected.

CREATE TABLE products (
    id     BIGINT  PRIMARY KEY AUTO_INCREMENT,
    name   TEXT    NOT NULL,
    price  DECIMAL NOT NULL CHECK (price > 0),
    stock  INT     NOT NULL CHECK (stock >= 0),
    rating REAL    CHECK (rating IS NULL OR (rating >= 1.0 AND rating <= 5.0))
);

Table-Level Constraints

Table constraints apply to multiple columns and are declared after all column definitions.

CREATE TABLE shipments (
    id           BIGINT    PRIMARY KEY AUTO_INCREMENT,
    order_id     BIGINT    NOT NULL,
    warehouse_id INT       NOT NULL,
    shipped_at   TIMESTAMP,
    delivered_at TIMESTAMP,

    -- Named constraints (recommended for meaningful error messages)
    CONSTRAINT fk_shipment_order
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,

    CONSTRAINT chk_delivery_after_shipment
        CHECK (delivered_at IS NULL OR delivered_at >= shipped_at),

    CONSTRAINT uq_one_active_shipment
        UNIQUE (order_id, warehouse_id)
);

IF NOT EXISTS

Suppresses the error when the table already exists. Useful in migration scripts.

CREATE TABLE IF NOT EXISTS config (
    key   TEXT NOT NULL UNIQUE,
    value TEXT NOT NULL
);

Full Example — E-commerce Schema

CREATE TABLE users (
    id         BIGINT      PRIMARY KEY AUTO_INCREMENT,
    email      TEXT        NOT NULL UNIQUE,
    name       TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ
);

CREATE TABLE categories (
    id   INT  PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE products (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    category_id INT         NOT NULL REFERENCES categories(id),
    name        TEXT        NOT NULL,
    description TEXT,
    price       DECIMAL     NOT NULL CHECK (price > 0),
    stock       INT         NOT NULL DEFAULT 0 CHECK (stock >= 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    user_id     BIGINT      NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    total       DECIMAL     NOT NULL CHECK (total >= 0),
    status      TEXT        NOT NULL DEFAULT 'pending',
    placed_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    shipped_at  TIMESTAMPTZ,
    CONSTRAINT chk_order_status CHECK (
        status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')
    )
);

CREATE TABLE order_items (
    order_id   BIGINT  NOT NULL REFERENCES orders(id)   ON DELETE CASCADE,
    product_id BIGINT  NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity   INT     NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL NOT NULL CHECK (unit_price > 0),
    PRIMARY KEY (order_id, product_id)
);

CREATE INDEX

Indexes accelerate lookups and range scans. AxiomDB automatically creates a unique B+ Tree index for every PRIMARY KEY and UNIQUE constraint. Additional indexes are created explicitly. CREATE INDEX works on both heap tables and clustered (PRIMARY KEY) tables.

Basic Syntax

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name (column [ASC|DESC], ...)
[WITH (fillfactor = N)]
[WHERE condition];

fillfactor controls how full a B-Tree leaf page gets before splitting (10–100, default 90). Lower values leave room for future inserts without triggering splits. See Fill Factor for details.

Examples

-- Standard index
CREATE INDEX idx_users_email ON users (email);

-- Composite index: queries filtering by (user_id, placed_at) benefit
CREATE INDEX idx_orders_user_date ON orders (user_id, placed_at DESC);

-- Unique index (equivalent to UNIQUE column constraint)
CREATE UNIQUE INDEX uq_products_sku ON products (sku);

-- Partial index: index only active products (reduces index size)
CREATE INDEX idx_active_products ON products (category_id)
WHERE deleted_at IS NULL;

-- Fill factor: append-heavy time-series table (leaves 30% free for inserts)
CREATE INDEX idx_ts ON events(created_at) WITH (fillfactor = 70);

-- Fill factor + partial index combined
CREATE UNIQUE INDEX uq_active_email ON users(email)
WHERE deleted_at IS NULL
-- WITH clause can appear before or after WHERE (both are accepted)

When to Add an Index

• Columns appearing in WHERE, JOIN ON, or ORDER BY clauses on large tables
• Foreign key columns (AxiomDB does not auto-index FK columns — add them explicitly)
• Columns used in range queries (BETWEEN, >, <)

See Indexes for the query planner interaction and composite index column ordering rules.

DROP TABLE

Removes a table and all its data permanently.

DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

-- Safe drop: fails if referenced by other tables
DROP TABLE products;

-- Drop without error if already gone
DROP TABLE IF EXISTS temp_import;

-- Drop even if referenced (removes FK constraints first)
DROP TABLE categories CASCADE;

Dropping a table is immediate and permanent. There is no RECYCLE BIN. Make sure you have a backup or are inside a transaction if you need to recover.

DROP INDEX

Removes an index. The table and its data are not affected.

DROP INDEX [IF EXISTS] index_name;

DROP INDEX idx_users_email;
DROP INDEX IF EXISTS idx_old_lookup;

ALTER TABLE

Modifies the structure of an existing table. All four forms are blocking operations — no concurrent DDL is allowed while an ALTER TABLE is in progress.

Add Column

Adds a new column at the end of the column list. If existing rows are present, they are rewritten to include the default value for the new column. If no DEFAULT clause is given, existing rows receive NULL for that column.

ALTER TABLE table_name ADD COLUMN column_name data_type [NOT NULL] [DEFAULT expr];

-- Add a nullable column (existing rows get NULL)
ALTER TABLE users ADD COLUMN phone TEXT;

-- Add a NOT NULL column with a default (existing rows get 0)
ALTER TABLE orders ADD COLUMN priority INT NOT NULL DEFAULT 0;

-- Add a column with a string default
ALTER TABLE products ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

A column with NOT NULL and no DEFAULT cannot be added to a non-empty table — existing rows would have no value to fill in and would violate the constraint. Provide a DEFAULT value, or add the column as nullable first and back-fill the data before adding the constraint.

Drop Column

Removes a column from the table. All existing rows are rewritten without the dropped column’s value. The column name must exist unless IF EXISTS is used.

ALTER TABLE table_name DROP COLUMN column_name [IF EXISTS];

-- Remove a column (fails if the column does not exist)
ALTER TABLE users DROP COLUMN phone;

-- Remove a column only if it exists (idempotent, safe in migrations)
ALTER TABLE users DROP COLUMN phone IF EXISTS;

Dropping a column is permanent. The data stored in that column is discarded when rows are rewritten and cannot be recovered without a backup.

Dropping a column that is part of a UNIQUE index or a FOREIGN KEY is rejected with an error. Drop the index or constraint first, then drop the column. Dropping a PRIMARY KEY column is not allowed on clustered tables (the PK is the physical storage key).

Modify Column

Changes the data type or nullability of an existing column. All existing rows are rewritten, coercing their stored values to the new type.

ALTER TABLE table_name MODIFY COLUMN column_name new_type [NOT NULL];

-- Widen an integer column to 64 bits (existing values preserved)
ALTER TABLE events MODIFY COLUMN count BIGINT;

-- Convert integers to text (always safe, values become their decimal string)
ALTER TABLE codes MODIFY COLUMN code TEXT;

-- Add a NOT NULL constraint (fails if any row has NULL in that column)
ALTER TABLE orders MODIFY COLUMN status TEXT NOT NULL;

Rules and restrictions:

• Narrowing casts (e.g. BIGINT → INT, TEXT → INT) are applied with strict coercion. If any existing value cannot be represented in the new type the statement fails and no rows are changed.
• A column that is part of a secondary index (UNIQUE or otherwise) cannot have its type changed. Drop the index first, modify the column, then recreate the index.
• The PRIMARY KEY column’s type cannot be changed on a clustered table.
• Changing nullability from nullable to NOT NULL is allowed only when every existing row has a non-NULL value for that column.

Rename Column

Renames an existing column. This is a catalog-only operation — no rows are rewritten because the positional encoding is not affected by column names.

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- Rename a column
ALTER TABLE users RENAME COLUMN full_name TO display_name;

-- Rename to fix a typo
ALTER TABLE orders RENAME COLUMN shiped_at TO shipped_at;

Rename Table

Renames the table itself. This is a catalog-only operation.

ALTER TABLE old_name RENAME TO new_name;

-- Rename during a refactoring
ALTER TABLE user_profiles RENAME TO profiles;

-- Rename a staging table after a migration
ALTER TABLE orders_import RENAME TO orders;

Rebuild To Clustered

Migrates a legacy heap table that already has PRIMARY KEY metadata into clustered storage.

ALTER TABLE table_name REBUILD;

Example:

-- After opening an older AxiomDB database where `users` is still heap-backed
ALTER TABLE users REBUILD;

Behavior:

• walks the existing PRIMARY KEY index in logical key order
• rebuilds the table into a clustered PRIMARY KEY tree
• rebuilds every non-primary index so it stores clustered PK bookmarks instead of heap RecordIds
• swaps the catalog metadata atomically at the end of the statement

Common errors:

ALTER TABLE logs REBUILD;
-- ERROR 1105 (HY000): ALTER TABLE REBUILD requires a PRIMARY KEY on 'logs'

ALTER TABLE users REBUILD;
-- ERROR 1105 (HY000): table 'users' is already clustered

Not Yet Supported

The following ALTER TABLE forms are planned for Phase 4.22b and later:

• MODIFY COLUMN / ALTER COLUMN — changing a column’s data type
• ADD CONSTRAINT — adding a CHECK, UNIQUE, or FOREIGN KEY after table creation
• DROP CONSTRAINT — removing a named constraint
• Dropping columns that participate in a constraint

TRUNCATE TABLE

Removes all rows from a table without dropping its structure, and resets the AUTO_INCREMENT counter to 1. The table schema, indexes, and constraints are preserved.

TRUNCATE TABLE table_name;

-- Wipe a staging table before re-importing
TRUNCATE TABLE import_staging;

-- AUTO_INCREMENT is always reset after TRUNCATE
CREATE TABLE log_events (id INT AUTO_INCREMENT PRIMARY KEY, msg TEXT);
INSERT INTO log_events (msg) VALUES ('start'), ('end');  -- ids: 1, 2
TRUNCATE TABLE log_events;
INSERT INTO log_events (msg) VALUES ('restart');          -- id: 1

Returns Affected { count: 0 } (MySQL convention). See also TRUNCATE TABLE in the DML reference for a comparison with DELETE FROM table.

ANALYZE

Refreshes per-column statistics used by the query planner to choose between an index scan and a full table scan.

ANALYZE;                          -- all tables in the current schema
ANALYZE TABLE table_name;         -- specific table, all indexed columns
ANALYZE TABLE table_name (col);   -- specific table, one column only

ANALYZE computes exact row_count and NDV (number of distinct non-NULL values) for each target column by scanning the full table. Results are stored in the axiom_stats system catalog and are immediately available to the planner.

-- After a bulk import, refresh stats so the planner uses correct selectivity:
INSERT INTO products SELECT * FROM products_staging;
ANALYZE TABLE products;

-- Check a single column after targeted inserts:
ANALYZE TABLE orders (status);

See Index Statistics for how NDV and row_count affect query planning decisions.

DML — Queries and Mutations

DML statements read and modify table data: SELECT, INSERT, UPDATE, and DELETE. All DML operations participate in the current transaction and are subject to MVCC isolation.

SELECT

Full Syntax

SELECT [DISTINCT] select_list
FROM table_ref [AS alias]
     [JOIN ...]
[WHERE condition]
[GROUP BY column_list]
[HAVING condition]
[ORDER BY column_list [ASC|DESC] [NULLS FIRST|LAST]]
[LIMIT n [OFFSET m]];

Basic Projections

-- All columns
SELECT * FROM users;

-- Specific columns with aliases
SELECT id, email AS user_email, name AS full_name
FROM users;

-- Computed columns
SELECT
    name,
    price * 1.19 AS price_with_tax,
    UPPER(name)  AS name_upper
FROM products;

DISTINCT

Removes duplicate rows from the result. Two rows are duplicates if every selected column has the same value (NULL = NULL for this purpose only).

-- All distinct status values in the orders table
SELECT DISTINCT status FROM orders;

-- All distinct (category_id, status) pairs
SELECT DISTINCT category_id, status FROM products ORDER BY category_id;

FROM and JOIN

Simple FROM

SELECT * FROM products;
SELECT p.* FROM products AS p WHERE p.price > 50;

INNER JOIN

Returns only rows where the join condition matches in both tables.

SELECT
    u.name,
    o.id   AS order_id,
    o.total,
    o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'shipped'
ORDER BY o.placed_at DESC;

LEFT JOIN

Returns all rows from the left table; columns from the right table are NULL when there is no matching row.

-- All users, including those with no orders
SELECT
    u.id,
    u.name,
    COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY total_orders DESC;

RIGHT JOIN

Returns all rows from the right table; left table columns are NULL on no match. Less common — most RIGHT JOINs can be rewritten as LEFT JOINs by swapping tables.

SELECT p.name, SUM(oi.quantity) AS total_sold
FROM order_items oi
RIGHT JOIN products p ON p.id = oi.product_id
GROUP BY p.id, p.name;

FULL OUTER JOIN

Returns all rows from both tables. Matched rows are joined normally. Unmatched rows from either side are padded with NULL on the missing side.

AxiomDB extension over the MySQL wire protocol. MySQL does not support FULL OUTER JOIN. AxiomDB clients connecting via the MySQL wire protocol can use it, but standard MySQL clients may not send it.

-- Audit: find users with no orders AND orders with no valid user
SELECT
    u.id   AS user_id,
    u.name AS user_name,
    o.id   AS order_id,
    o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.id;

Both FULL JOIN and FULL OUTER JOIN are accepted.

ON vs WHERE semantics:

• ON predicates are evaluated before null-extension. Rows that do not satisfy ON are treated as unmatched and receive NULLs.
• WHERE predicates run after the full join is materialized. Adding WHERE u.id IS NOT NULL removes unmatched right rows from the result.

-- ON vs WHERE: only keep rows where the user side is not NULL
SELECT u.id, o.id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
WHERE u.id IS NOT NULL;     -- removes the (NULL, 13) row

Nullability: In SELECT * over a FULL OUTER JOIN, all columns from both tables are marked nullable even if the catalog defines them as NOT NULL, because either side can be null-extended.

CROSS JOIN

Cartesian product — every row from the left table combined with every row from the right table. Use with care: m × n rows.

-- Generate all combinations of size and color for a product grid
SELECT sizes.label AS size, colors.label AS color
FROM sizes
CROSS JOIN colors
ORDER BY sizes.sort_order, colors.sort_order;

Multi-Table JOIN

SELECT
    u.name       AS customer,
    p.name       AS product,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN users       u  ON u.id  = o.user_id
JOIN order_items oi ON oi.order_id  = o.id
JOIN products    p  ON p.id  = oi.product_id
WHERE o.status = 'delivered'
ORDER BY o.placed_at DESC, p.name;

WHERE

Filters rows before aggregation. Accepts any boolean expression.

-- Equality and comparison
SELECT * FROM products WHERE price > 100 AND stock > 0;

-- NULL check
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM orders WHERE shipped_at IS NOT NULL;

-- BETWEEN (inclusive on both ends)
SELECT * FROM orders
WHERE placed_at BETWEEN '2026-01-01' AND '2026-03-31';

-- IN list
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');

-- LIKE pattern matching (% = any sequence, _ = exactly one character)
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM products WHERE name LIKE 'USB-_';

-- NOT variants
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');
SELECT * FROM products WHERE name NOT LIKE 'Test%';

Subqueries

A subquery is a SELECT statement nested inside another statement. AxiomDB supports five subquery forms, each with full NULL semantics identical to PostgreSQL and MySQL.

Scalar Subqueries

A scalar subquery appears anywhere an expression is valid (SELECT list, WHERE, HAVING, ORDER BY). It must return exactly one column. If it returns zero rows, the result is NULL. If it returns more than one row, AxiomDB raises CardinalityViolation (SQLSTATE 21000).

-- Compare each product price against the overall average
SELECT
    name,
    price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC;

-- Find the most recently placed order date
SELECT * FROM orders
WHERE placed_at = (SELECT MAX(placed_at) FROM orders);

-- Use a scalar subquery in HAVING
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) AS cnt FROM orders GROUP BY user_id) AS sub);

If the subquery returns more than one row, AxiomDB raises:

ERROR 21000: subquery must return exactly one row, but returned 3 rows

Use LIMIT 1 or a unique WHERE predicate to guarantee a single row.

IN Subquery

expr [NOT] IN (SELECT col FROM ...) tests whether a value appears in the set of values produced by the subquery.

-- Orders for users who have placed more than 5 orders total
SELECT * FROM orders
WHERE user_id IN (
    SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 5
);

-- Products never sold
SELECT * FROM products
WHERE id NOT IN (
    SELECT DISTINCT product_id FROM order_items
);

NULL semantics — fully consistent with the SQL standard:

The third row is the subtle case: x NOT IN (subquery with NULLs) returns NULL, not FALSE. This means NOT IN combined with a subquery that may produce NULLs can silently exclude rows. A safe alternative is NOT EXISTS.

EXISTS / NOT EXISTS

[NOT] EXISTS (SELECT ...) tests whether the subquery produces at least one row. The result is always TRUE or FALSE — never NULL.

-- Users who have at least one paid order
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = 'paid'
);

-- Products with no associated order items
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

The select list inside an EXISTS subquery does not matter — SELECT 1, SELECT *, and SELECT id all behave identically. The engine only checks for row existence.

Correlated Subqueries

A correlated subquery references columns from the outer query. AxiomDB re-executes the subquery for each outer row, substituting the current outer column values.

-- For each order, fetch the user's name (correlated scalar subquery in SELECT list)
SELECT
    o.id,
    o.total,
    (SELECT u.name FROM users u WHERE u.id = o.user_id) AS customer_name
FROM orders o;

-- Orders whose total exceeds the average total for that user (correlated in WHERE)
SELECT * FROM orders o
WHERE o.total > (
    SELECT AVG(total) FROM orders WHERE user_id = o.user_id
);

-- Active products with above-average stock in their category
SELECT * FROM products p
WHERE p.stock > (
    SELECT AVG(stock) FROM products WHERE category_id = p.category_id
);

Correlated subqueries with large outer result sets can be slow (O(n) re-executions). For performance-critical paths, rewrite them as JOINs with aggregation.

Derived Tables (FROM Subquery)

A subquery in the FROM clause is called a derived table. It must have an alias. AxiomDB materializes the derived table result in memory before executing the outer query.

-- Top spenders, computed as a subquery and then filtered
SELECT customer_name, total_spent
FROM (
    SELECT u.name AS customer_name, SUM(o.total) AS total_spent
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE o.status = 'delivered'
    GROUP BY u.id, u.name
) AS spending
WHERE total_spent > 500
ORDER BY total_spent DESC;

-- Percentile bucketing: compute rank in a subquery, filter in outer
SELECT *
FROM (
    SELECT
        id,
        name,
        price,
        RANK() OVER (ORDER BY price DESC) AS price_rank
    FROM products
) AS ranked
WHERE price_rank <= 10;

GROUP BY and HAVING

GROUP BY collapses rows with the same values in the specified columns into a single output row. Aggregate functions operate over each group.

-- Orders per user
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
GROUP BY user_id
ORDER BY revenue DESC;

-- Monthly revenue
SELECT
    DATE_TRUNC('month', placed_at) AS month,
    COUNT(*)   AS orders,
    SUM(total) AS revenue,
    AVG(total) AS avg_order_value
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE_TRUNC('month', placed_at)
ORDER BY month;

HAVING filters groups after aggregation (analogous to WHERE for rows).

-- Only users with more than 5 orders
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

-- Only categories with average price above 50
SELECT category_id, AVG(price) AS avg_price
FROM products
WHERE deleted_at IS NULL
GROUP BY category_id
HAVING AVG(price) > 50;

ORDER BY

Sorts the result. Multiple columns are sorted left to right.

-- Descending by total, then ascending by name as tiebreaker
SELECT user_id, SUM(total) AS revenue
FROM orders
GROUP BY user_id
ORDER BY revenue DESC, user_id ASC;

NULLS FIRST / NULLS LAST

Controls where NULL values appear in the sort order.

-- Show NULL shipped_at rows at the bottom (unshipped orders last)
SELECT id, total, shipped_at
FROM orders
ORDER BY shipped_at ASC NULLS LAST;

-- Show most recent shipments first; unshipped at top
SELECT id, total, shipped_at
FROM orders
ORDER BY shipped_at DESC NULLS FIRST;

Default behavior: ASC sorts NULL last; DESC sorts NULL first (same as PostgreSQL).

LIMIT and OFFSET

-- First 10 rows
SELECT * FROM products ORDER BY name LIMIT 10;

-- Rows 11-20 (page 2 with page size 10)
SELECT * FROM products ORDER BY name LIMIT 10 OFFSET 10;

-- Common pagination pattern
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;   -- page 3 (0-indexed) of 20 items per page

For large offsets (> 10,000), consider keyset pagination instead: WHERE id > :last_seen_id ORDER BY id LIMIT 20

INSERT

INSERT … VALUES

Tables whose schema has an explicit PRIMARY KEY now use clustered storage for SQL-visible INSERT, SELECT, UPDATE, and DELETE. The clustered SQL path now supports:

• single-row VALUES
• multi-row VALUES
• INSERT ... SELECT
• AUTO_INCREMENT
• explicit transactions and savepoints
• SELECT full scans over clustered leaves
• SELECT PK point lookups and PK range scans
• SELECT secondary lookups through PK bookmarks stored in the secondary key
• UPDATE in-place rewrite when the row still fits in the owning leaf
• UPDATE relocation fallback when the row grows and must be rewritten structurally
• UPDATE through PK predicates or secondary bookmark probes with transaction rollback/savepoint safety
• DELETE through PK predicates, PK ranges, secondary bookmark probes, or full clustered scans
• DELETE rollback/savepoint restore through exact clustered row images in WAL

Current clustered boundary after 39.18:

• clustered DELETE is still delete-mark first, and clustered VACUUM table performs the later physical purge
• clustered VACUUM table now frees overflow chains and dead secondary bookmark entries
• clustered child-table foreign-key enforcement still remains future work

When a table has an AUTO_INCREMENT column, omit it from the column list and AxiomDB generates the next sequential ID automatically. Use LAST_INSERT_ID() (or the PostgreSQL alias lastval()) immediately after the INSERT to retrieve the generated value.

CREATE TABLE users (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name TEXT   NOT NULL
);

-- Single row — id is generated automatically
INSERT INTO users (name) VALUES ('Alice');
-- id=1

SELECT LAST_INSERT_ID();   -- returns 1

For multi-row INSERT, LAST_INSERT_ID() returns the ID generated for the first row of the batch (MySQL semantics). Subsequent rows receive consecutive IDs.

INSERT INTO users (name) VALUES ('Bob'), ('Carol'), ('Dave');
-- ids: 2, 3, 4
SELECT LAST_INSERT_ID();   -- returns 2 (first of the batch)

Supplying an explicit non-NULL value in the AUTO_INCREMENT column bypasses the sequence and does not advance it.

INSERT INTO users (id, name) VALUES (100, 'Eve');
-- id=100; sequence not advanced; next LAST_INSERT_ID() still returns 2

The same AUTO_INCREMENT contract now applies to clustered explicit-PK tables: AxiomDB bootstraps the next value by scanning the clustered rows for the current maximum instead of falling back to heap metadata.

See Expressions — Session Functions for full LAST_INSERT_ID() / lastval() semantics.

-- Single row
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);

-- Multiple rows in one statement (more efficient than individual INSERTs)
INSERT INTO products (name, price, stock) VALUES
    ('Keyboard', 49.99, 100),
    ('Mouse',    29.99, 200),
    ('Monitor', 299.99,  50);

INSERT … DEFAULT VALUES

Inserts a single row using all column defaults. Useful when every column has a default.

CREATE TABLE audit_events (
    id         BIGINT      PRIMARY KEY AUTO_INCREMENT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    event_type TEXT        NOT NULL DEFAULT 'unknown'
);

INSERT INTO audit_events DEFAULT VALUES;
-- Row: id=1, created_at=<now>, event_type='unknown'

INSERT … SELECT

Inserts rows generated by a SELECT statement. Useful for bulk copies and migrations.

-- Copy all active users to an archive table
INSERT INTO users_archive (id, email, name, created_at)
SELECT id, email, name, created_at
FROM users
WHERE deleted_at IS NOT NULL;

-- Compute and store aggregates
INSERT INTO monthly_revenue (month, total)
SELECT
    DATE_TRUNC('month', placed_at),
    SUM(total)
FROM orders
WHERE status = 'delivered'
GROUP BY 1;

UPDATE

Modifies existing rows. All matching rows are updated in a single statement.

UPDATE table_name
SET column = expression [, column = expression ...]
[WHERE condition];

-- Mark a specific order as shipped
UPDATE orders
SET status = 'shipped', shipped_at = CURRENT_TIMESTAMP
WHERE id = 42;

-- Apply a 10% discount to all products in a category
UPDATE products
SET price = price * 0.90
WHERE category_id = 5 AND deleted_at IS NULL;

-- Reset all pending orders older than 7 days to cancelled
UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending'
  AND placed_at < CURRENT_TIMESTAMP - INTERVAL '7 days';

An UPDATE without a WHERE clause updates every row in the table. This is rarely what you want. Always double-check before running unbounded updates in production.

DELETE

Removes rows from a table.

DELETE FROM table_name [WHERE condition];

-- Delete a specific row
DELETE FROM sessions WHERE id = 'abc123';

-- Delete all expired sessions
DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;

-- Soft delete pattern (prefer UPDATE to mark rows inactive)
UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 7;
-- Then filter: SELECT * FROM users WHERE deleted_at IS NULL;

When parent FK references exist, DELETE FROM t keeps the row-by-row path so RESTRICT/CASCADE/SET NULL FK enforcement still fires correctly.

TRUNCATE TABLE

Removes all rows from a table and resets its AUTO_INCREMENT counter to 1. The table structure, indexes, and constraints are preserved.

TRUNCATE TABLE table_name;

-- Empty a staging table before a fresh import
TRUNCATE TABLE import_staging;

-- After truncate, AUTO_INCREMENT restarts from 1
CREATE TABLE counters (id INT AUTO_INCREMENT PRIMARY KEY, label TEXT);
INSERT INTO counters (label) VALUES ('a'), ('b');  -- ids: 1, 2
TRUNCATE TABLE counters;
INSERT INTO counters (label) VALUES ('c');          -- id: 1 (reset)

TRUNCATE TABLE returns Affected { count: 0 }, matching MySQL convention.

TRUNCATE vs DELETE — when to use each:

TRUNCATE TABLE fails with an error if any FK constraint references the table as the parent. Delete or truncate child tables first, then truncate the parent.

Both DELETE FROM t (no WHERE) and TRUNCATE TABLE t use the same bulk-empty root-rotation machinery internally and are fully transactional.

Session Variables

Session variables hold connection-scoped state. Read them with SELECT @@name and change them with SET name = value.

Reading session variables

SELECT @@autocommit;        -- 1 (autocommit on) or 0 (autocommit off)
SELECT @@in_transaction;    -- 1 inside an active transaction, 0 otherwise
SELECT @@version;           -- '8.0.36-AxiomDB-0.1.0'
SELECT @@character_set_client;   -- 'utf8mb4'
SELECT @@transaction_isolation;  -- 'REPEATABLE-READ'

Supported variables

Changing session variables

-- Switch to manual transaction mode (used by SQLAlchemy, Django ORM, etc.)
SET autocommit = 0;
SET autocommit = 1;   -- restore

-- Character set (accepted for ORM compatibility, utf8mb4 is always used internally)
SET NAMES 'utf8mb4';
SET character_set_client = 'utf8mb4';

-- Control coercion strictness (see Strict Mode below)
SET strict_mode = OFF;
SET sql_mode = '';

@@in_transaction — transaction state check

SELECT @@in_transaction;    -- 0 — no transaction active

INSERT INTO t VALUES (1);   -- starts implicit txn when autocommit=0
SELECT @@in_transaction;    -- 1 — inside transaction

COMMIT;
SELECT @@in_transaction;    -- 0 — transaction closed

Use @@in_transaction to verify transaction state before issuing a COMMIT or ROLLBACK. This avoids the warning generated when COMMIT is called with no active transaction.

AXIOM_COMPAT and collation

@@axiom_compat controls the high-level compatibility behavior of the session. @@collation controls how text values are compared, sorted, and grouped.

SET AXIOM_COMPAT = 'mysql';          -- CI+AI text semantics (default collation = 'es')
SET AXIOM_COMPAT = 'postgresql';     -- exact binary text semantics
SET AXIOM_COMPAT = 'standard';       -- default AxiomDB behavior (binary)
SET AXIOM_COMPAT = DEFAULT;          -- reset to 'standard'

SET collation = 'es';                -- explicit CI+AI fold for this session
SET collation = 'binary';            -- explicit exact byte order
SET collation = DEFAULT;             -- restore compat-derived default

binary collation (default)

Exact byte-order string comparison — current AxiomDB default:

• 'a' != 'A', 'a' != 'á'
• LIKE is case-sensitive and accent-sensitive
• GROUP BY, DISTINCT, ORDER BY, MIN/MAX(TEXT) all use raw byte order

es collation — CI+AI fold

A lightweight session-level CI+AI fold: NFC normalize → lowercase → strip combining accent marks. No ICU / CLDR dependency.

• 'Jose' = 'JOSE' = 'José' compare equal
• LIKE 'jos%' matches José
• GROUP BY, DISTINCT, COUNT(DISTINCT ...) collapse accent/case variants into one group
• ORDER BY sorts by folded text first, raw text as a tie-break for determinism
• MIN/MAX(TEXT) and GROUP_CONCAT(DISTINCT/ORDER BY ...) respect the fold

-- Binary (default): José and jose are different rows
SELECT name FROM users GROUP BY name;
-- → 'José', 'jose', 'JOSE'

-- Es: all three fold to "jose" — one group
SET AXIOM_COMPAT = 'mysql';
SELECT name FROM users GROUP BY name;
-- → 'José'   (or whichever variant appears first)

-- Explicit collation independent of compat mode:
SET collation = 'es';
SELECT * FROM products WHERE name = 'widget';  -- matches Widget, WIDGET, wídget

Index safety: When @@collation = 'es', AxiomDB automatically falls back from text index lookups to full table scans for correctness. Binary-ordered B-Tree keys do not match es-folded predicates, so using the index would silently miss rows. Non-text indexes (INT, BIGINT, DATE, etc.) are unaffected.

Note: @@collation and @@collation_connection are separate variables. @@collation_connection is the transport charset (set during handshake or via SET NAMES). @@collation is the executor-visible text-comparison behavior added by AXIOM_COMPAT.

Full layered collation (per-database, per-column, ICU locale) is planned for Phase 13.13.

ON_ERROR

@@on_error controls what happens to the current transaction when a statement fails. It applies to all pipeline stages: parse errors, semantic errors, and executor errors.

SET on_error = 'rollback_statement';    -- default
SET on_error = 'rollback_transaction';
SET on_error = 'savepoint';
SET on_error = 'ignore';
SET on_error = DEFAULT;                  -- reset to rollback_statement

Both quoted strings and bare identifiers are accepted:

SET on_error = rollback_statement;      -- same as 'rollback_statement'

Modes

rollback_statement (default) — When a statement fails inside an active transaction, only that statement’s writes are rolled back. The transaction stays open. This matches MySQL’s statement-level rollback behavior.

BEGIN;
INSERT INTO t VALUES (1);          -- ok
INSERT INTO t VALUES (1);          -- ERROR: duplicate key
-- transaction still active, id=1 is the only write that will commit
INSERT INTO t VALUES (2);          -- ok
COMMIT;                            -- commits id=1 and id=2

rollback_transaction — When any statement fails inside an active transaction, the entire transaction is rolled back immediately. @@in_transaction becomes 0.

SET on_error = 'rollback_transaction';

BEGIN;
INSERT INTO t VALUES (1);          -- ok
INSERT INTO t VALUES (1);          -- ERROR: duplicate key → whole txn rolled back
SELECT @@in_transaction;           -- 0 — transaction is gone

savepoint — Same as rollback_statement when a transaction is already active. When autocommit = 0, the key difference appears on the first DML in an implicit transaction: savepoint preserves the implicit transaction after a failing first DML, while rollback_statement closes it.

SET autocommit = 0;
SET on_error = 'savepoint';

INSERT INTO t VALUES (999);        -- fails (dup key)
SELECT @@in_transaction;           -- 1 — implicit txn stays open
INSERT INTO t VALUES (1);          -- ok, continues in the same txn
COMMIT;

ignore — Ignorable SQL errors (parse errors, semantic errors, constraint violations, type mismatches) are converted to session warnings and the statement is reported as success. Non-ignorable errors (I/O failures, WAL errors, storage corruption) still return ERR; if one happens inside an active transaction, AxiomDB eagerly rolls that transaction back before returning the error.

SET on_error = 'ignore';

INSERT INTO t VALUES (1);          -- ok
INSERT INTO t VALUES (1);          -- duplicate key → silently ignored
SHOW WARNINGS;                     -- shows code 1062 + original message
INSERT INTO t VALUES (2);          -- ok, continues
COMMIT;                            -- commits id=1 and id=2

In a multi-statement COM_QUERY, ignore continues executing later statements after an ignored error.

-- Single COM_QUERY with three statements:
INSERT INTO t VALUES (1); INSERT INTO t VALUES (1); INSERT INTO t VALUES (2);
-- First succeeds, second is ignored (dup), third succeeds.
-- Only the ignored statement's OK packet carries warning_count > 0.

Inspecting the current mode

SELECT @@on_error;                  -- 'rollback_statement'
SELECT @@session.on_error;          -- same
SHOW VARIABLES LIKE 'on_error';     -- on_error | rollback_statement

COM_RESET_CONNECTION resets @@on_error to rollback_statement.

Strict Mode

AxiomDB operates in strict mode by default. In strict mode, an INSERT or UPDATE that cannot coerce a value to the column’s declared type returns an error immediately (SQLSTATE 22018). This prevents silent data corruption.

CREATE TABLE products (name TEXT, stock INT);

-- Strict mode (default): error on bad coercion
INSERT INTO products VALUES ('Widget', 'abc');
-- ERROR 22018: cannot coerce 'abc' (Text) to INT

To enable permissive mode, disable strict mode for the session:

SET strict_mode = OFF;
-- or equivalently:
SET sql_mode = '';

In permissive mode, AxiomDB first tries the strict coercion. If it fails, it falls back to a best-effort conversion (e.g. '42abc' → 42, 'abc' → 0), stores the result, and emits warning 1265 instead of returning an error:

SET strict_mode = OFF;

CREATE TABLE products (name TEXT, stock INT);
INSERT INTO products VALUES ('Widget', '99abc');
-- Succeeds — stock stored as 99; warning emitted

SHOW WARNINGS;
-- Level    Code   Message
-- ─────────────────────────────────────────────────────────────────────
-- Warning  1265   Data truncated for column 'stock' at row 1

For multi-row INSERT, the row number in warning 1265 is 1-based and identifies the specific row that triggered the fallback:

INSERT INTO products VALUES ('A', '10'), ('B', '99x'), ('C', '30');
SHOW WARNINGS;
-- Warning  1265   Data truncated for column 'stock' at row 2

Re-enable strict mode at any time:

SET strict_mode = ON;
-- or equivalently:
SET sql_mode = 'STRICT_TRANS_TABLES';

SET strict_mode = DEFAULT also restores the server default (ON).

SHOW WARNINGS

After any statement that completes with warnings, query the warning list:

-- Warning from no-op COMMIT
COMMIT;               -- no active transaction — emits warning 1592
SHOW WARNINGS;
-- Level    Code   Message
-- ───────────────────────────────────────────────
-- Warning  1592   There is no active transaction

-- Warning from permissive coercion (strict_mode = OFF)
SET strict_mode = OFF;
INSERT INTO products VALUES ('Widget', '99abc');
SHOW WARNINGS;
-- Level    Code   Message
-- ─────────────────────────────────────────────────────────────────────
-- Warning  1265   Data truncated for column 'stock' at row 1

SHOW WARNINGS returns the warnings from the most recent statement only. The list is cleared before each new statement executes.

SHOW TABLES

Lists all tables in the current schema (or a named schema).

SHOW TABLES;
SHOW TABLES FROM schema_name;

The result set has a single column named Tables_in_<schema>:

SHOW TABLES;
-- Tables_in_public
-- ────────────────
-- users
-- orders
-- products
-- order_items

SHOW COLUMNS / DESCRIBE

Returns the column definitions of a table.

SHOW COLUMNS FROM table_name;
DESCRIBE table_name;
DESC table_name;            -- shorthand

All three forms are equivalent. The result has six columns:

CREATE TABLE users (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name TEXT   NOT NULL,
    bio  TEXT
);

DESCRIBE users;
-- Field  Type    Null  Key  Default  Extra
-- ─────────────────────────────────────────────────
-- id     BIGINT  NO    PRI  NULL     auto_increment
-- name   TEXT    NO         NULL
-- bio    TEXT    YES        NULL

The Key and Default columns are stubs in the current release and do not yet reflect all constraints or computed defaults. Full metadata is tracked internally in the catalog and will be exposed in a future release.

Practical Examples — E-commerce Queries

Checkout: Atomic Order Placement

BEGIN;

-- Verify stock before committing
SELECT stock FROM products WHERE id = 1 AND stock >= 2;
-- If no row returned, rollback

INSERT INTO orders (user_id, total, status)
VALUES (99, 99.98, 'paid');

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 1, 2, 49.99);

UPDATE products SET stock = stock - 2 WHERE id = 1;

COMMIT;

Revenue Report — Last 30 Days

SELECT
    p.name                          AS product,
    SUM(oi.quantity)                AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders  o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.placed_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
  AND o.status IN ('paid', 'shipped', 'delivered')
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 10;

User Activity Summary

SELECT
    u.id,
    u.name,
    u.email,
    COUNT(o.id)   AS total_orders,
    SUM(o.total)  AS lifetime_value,
    MAX(o.placed_at) AS last_order
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status != 'cancelled'
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.name, u.email
ORDER BY lifetime_value DESC NULLS LAST;

Multi-Statement Queries

AxiomDB accepts multiple SQL statements separated by ; in a single COM_QUERY call. Each statement executes sequentially, and the client receives one result set per statement.

-- Three statements in one call
CREATE TABLE IF NOT EXISTS sessions (
    id         UUID NOT NULL,
    user_id    INT  NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO sessions (id, user_id) VALUES (gen_random_uuid(), 42);
SELECT COUNT(*) FROM sessions WHERE user_id = 42;

How it works (protocol):

Each intermediate result set is sent with the SERVER_MORE_RESULTS_EXISTS flag (0x0008) set in the EOF/OK status bytes, telling the client to read the next result set. The final result set has the flag cleared.

Behavior on error:

If any statement fails, execution stops at that point and an error packet is sent. Statements after the failing one are not executed.

-- If INSERT fails (e.g. UNIQUE violation), SELECT is not executed
INSERT INTO users (email) VALUES ('duplicate@example.com');
SELECT * FROM users WHERE email = 'duplicate@example.com';

ALTER TABLE — Constraints

ADD CONSTRAINT UNIQUE

-- Named unique constraint (recommended for DROP CONSTRAINT later)
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

-- Anonymous unique constraint (auto-named)
ALTER TABLE users ADD UNIQUE (username);

ADD CONSTRAINT UNIQUE creates a unique index internally. Fails with IndexAlreadyExists if a constraint/index with that name already exists on the table, or UniqueViolation if the column already has duplicate values.

ADD CONSTRAINT CHECK

ALTER TABLE orders ADD CONSTRAINT chk_positive_amount CHECK (amount > 0);
ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);

The CHECK expression is validated against all existing rows at the time of the ALTER TABLE. If any row fails the check, the statement returns CheckViolation. After the constraint is added, every subsequent INSERT and UPDATE on the table evaluates the expression.

DROP CONSTRAINT

-- Drop by name (works for both UNIQUE and CHECK constraints)
ALTER TABLE users DROP CONSTRAINT uq_users_email;

-- Silent no-op if the constraint does not exist
ALTER TABLE users DROP CONSTRAINT IF EXISTS uq_users_old;

DROP CONSTRAINT searches first in indexes (for UNIQUE constraints), then in the named constraint catalog (for CHECK constraints).

ADD CONSTRAINT FOREIGN KEY (Phase 6.5)

Adds a foreign key constraint after the table is created. Validates all existing rows before persisting — fails if any existing value violates the new constraint.

ALTER TABLE orders
  ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Fails if any existing user_id value has no matching row in users.

Limitations

-- Not yet supported:
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);
-- → NotImplemented: ADD CONSTRAINT PRIMARY KEY — requires full table rewrite

Prepared Statements — Binary Protocol

AxiomDB supports the full MySQL binary prepared statement protocol, including large parameter transmission via COM_STMT_SEND_LONG_DATA.

Large parameters (BLOB / TEXT)

When a parameter value is too large to send in a single COM_STMT_EXECUTE packet, client libraries split it into multiple COM_STMT_SEND_LONG_DATA chunks before execute. AxiomDB buffers all chunks and assembles the final value at execute time.

Python (PyMySQL):

import pymysql, os

conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", db="test")
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS files (id INT, data LONGBLOB)")

# PyMySQL automatically uses COM_STMT_SEND_LONG_DATA for values > 8 KB
large_blob = os.urandom(64 * 1024)   # 64 KB binary data
cur.execute("INSERT INTO files VALUES (%s, %s)", (1, large_blob))
conn.commit()

Binary parameters (BLOB, LONGBLOB, MEDIUMBLOB, TINYBLOB) are stored as raw bytes — 0x00 bytes and non-UTF-8 sequences are preserved exactly.

Text parameters (VARCHAR, TEXT, LONGTEXT) are decoded with the connection’s character_set_client after all chunks are assembled, so multibyte characters split across chunk boundaries are reconstructed correctly.

Parameter type mapping

COM_STMT_RESET

Calling mysql_stmt_reset() (or the equivalent in any MySQL driver) clears any pending long-data buffers for that statement without deallocating the prepared statement itself. The statement can then be re-executed with fresh parameters.

SHOW STATUS counter

SHOW STATUS LIKE 'Com_stmt_send_long_data' reports how many long-data chunks have been received by the current session (session scope) or by the server since startup (global scope).

SHOW STATUS LIKE 'Com_stmt_send_long_data';
-- Variable_name                | Value
-- Com_stmt_send_long_data      | 3

Expressions and Operators

An expression is any construct that evaluates to a value. Expressions appear in SELECT projections, WHERE conditions, ORDER BY clauses, CHECK constraints, and DEFAULT values.

Operator Precedence

From highest to lowest binding (higher = evaluated first):

Use parentheses to make complex expressions explicit:

-- Without parens: AND binds tighter than OR
SELECT * FROM orders WHERE status = 'paid' OR status = 'shipped' AND total > 100;
-- Parsed as: status = 'paid' OR (status = 'shipped' AND total > 100)

-- Explicit grouping
SELECT * FROM orders WHERE (status = 'paid' OR status = 'shipped') AND total > 100;

Arithmetic Operators

Integer division truncates toward zero: 7 / 2 = 3.

Division by zero raises a runtime error (22012 division_by_zero).

SELECT
    price,
    price * 0.19        AS tax,
    price * 1.19        AS price_with_tax,
    ROUND(price, 2)     AS rounded
FROM products;

Comparison Operators

SELECT * FROM products WHERE price = 49.99;
SELECT * FROM products WHERE stock <> 0;
SELECT * FROM orders   WHERE total >= 100;

Boolean Operators

NULL Semantics — Three-Valued Logic

AxiomDB implements SQL three-valued logic: every boolean expression evaluates to TRUE, FALSE, or UNKNOWN (which SQL represents as NULL in boolean context). The rules below are critical for writing correct WHERE clauses.

AND truth table

OR truth table

NOT truth table

Key consequences

-- NULL compared to anything is UNKNOWN, not TRUE or FALSE
SELECT NULL = NULL;      -- UNKNOWN (NULL, not TRUE)
SELECT NULL <> NULL;     -- UNKNOWN
SELECT NULL = 1;         -- UNKNOWN

-- WHERE filters only rows where condition is TRUE
-- Rows where the condition is UNKNOWN are excluded
SELECT * FROM users WHERE age = NULL;    -- always returns 0 rows!
SELECT * FROM users WHERE age IS NULL;   -- correct NULL check

-- UNKNOWN in AND
SELECT * FROM orders WHERE total > 100 AND NULL;  -- 0 rows (UNKNOWN is filtered)

-- UNKNOWN in OR
SELECT * FROM orders WHERE total > 100 OR NULL;   -- rows where total > 100

IS NULL / IS NOT NULL

These predicates are the correct way to check for NULL. They always return TRUE or FALSE, never UNKNOWN.

-- Find unshipped orders
SELECT * FROM orders WHERE shipped_at IS NULL;

-- Find orders that have been shipped
SELECT * FROM orders WHERE shipped_at IS NOT NULL;

-- Combine with other conditions
SELECT * FROM users WHERE deleted_at IS NULL AND age > 18;

BETWEEN

BETWEEN low AND high is inclusive on both ends. Equivalent to >= low AND <= high.

-- Products priced between $10 and $50 inclusive
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- Orders placed in Q1 2026
SELECT * FROM orders
WHERE placed_at BETWEEN '2026-01-01 00:00:00' AND '2026-03-31 23:59:59';

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;

LIKE — Pattern Matching

LIKE matches strings against a pattern.

Pattern matching is case-sensitive by default. Use CITEXT columns or ILIKE for case-insensitive matching.

-- Emails from example.com
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Names starting with 'Al'
SELECT * FROM users WHERE name LIKE 'Al%';

-- Exactly 5-character codes
SELECT * FROM products WHERE sku LIKE '_____';

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@test.%';

-- Escape a literal %
SELECT * FROM products WHERE description LIKE '50\% off' ESCAPE '\';

IN — Membership Test

IN checks whether a value matches any element in a list.

-- Multiple status values
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');

-- Numeric list
SELECT * FROM products WHERE category_id IN (1, 3, 7);

-- NOT IN
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

NOT IN (list) returns UNKNOWN (no rows) if any element in the list is NULL. Use NOT EXISTS or explicit NULL checks when the list may contain NULLs.

-- Safe: explicit list with no NULLs
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

-- Dangerous if user_id can be NULL:
SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM banned_users);
-- If banned_users contains even one NULL user, this returns 0 rows!
-- Safe alternative:
SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM banned_users b WHERE b.id = o.user_id AND b.id IS NOT NULL
);

Scalar Functions

Numeric Functions

String Functions

String Concatenation — ||

The || operator concatenates two string values. It is the SQL-standard alternative to CONCAT() and works in any expression context.

-- Build a full name from two columns
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- Append a suffix
SELECT sku || '-v2' AS new_sku FROM products;

-- NULL propagates: if either operand is NULL the result is NULL
SELECT 'hello' || NULL;   -- NULL

Use COALESCE to guard against NULL operands:

SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name
FROM users;

CAST — Explicit Type Conversion

CAST(expr AS type) converts a value to the specified type. Use it when an implicit coercion would be rejected in strict mode (the default).

-- Text-to-number: always works when the text is a valid number
SELECT CAST('42' AS INT);        -- 42
SELECT CAST('3.14' AS REAL);     -- 3.14
SELECT CAST('100' AS BIGINT);    -- 100

-- Use CAST to store a text literal in a numeric column
INSERT INTO users (age) VALUES (CAST('30' AS INT));

Supported CAST pairs (Phase 4.16):

Conditional Functions

-- COALESCE: display a fallback when the column is NULL
SELECT name, COALESCE(phone, 'N/A') AS contact FROM users;

-- NULLIF: convert 'unknown' to NULL (for aggregate functions to ignore)
SELECT AVG(NULLIF(rating, 0)) AS avg_rating FROM products;

-- CASE: categorize order size
SELECT
    id,
    total,
    CASE
        WHEN total < 50   THEN 'small'
        WHEN total < 200  THEN 'medium'
        WHEN total < 1000 THEN 'large'
        ELSE                   'enterprise'
    END AS order_size
FROM orders;

CASE WHEN — Conditional Expressions

CASE WHEN is a general-purpose conditional expression that can appear anywhere an expression is valid: SELECT projections, WHERE clauses, ORDER BY, GROUP BY, HAVING, and as arguments to aggregate functions.

AxiomDB supports two forms: searched CASE (any boolean condition per branch) and simple CASE (equality comparison against a single value).

Searched CASE

Evaluates each WHEN condition left to right and returns the THEN value of the first condition that is TRUE. If no condition matches and an ELSE is present, the ELSE value is returned. If no condition matches and there is no ELSE, the result is NULL.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE default_result]
END

-- Categorize orders by total amount
SELECT
    id,
    total,
    CASE
        WHEN total < 50    THEN 'small'
        WHEN total < 200   THEN 'medium'
        WHEN total < 1000  THEN 'large'
        ELSE                    'enterprise'
    END AS order_size
FROM orders;

-- Compute a human-readable status label, including NULL handling
SELECT
    id,
    CASE
        WHEN shipped_at IS NULL AND status = 'paid' THEN 'awaiting shipment'
        WHEN shipped_at IS NOT NULL                 THEN 'shipped'
        WHEN status = 'cancelled'                   THEN 'cancelled'
        ELSE                                             'unknown'
    END AS display_status
FROM orders;

Simple CASE

Compares a single expression against a list of values. Equivalent to a searched CASE using = for each WHEN comparison.

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE default_result]
END

-- Map status codes to display labels
SELECT
    id,
    CASE status
        WHEN 'pending'   THEN 'Pending Payment'
        WHEN 'paid'      THEN 'Paid'
        WHEN 'shipped'   THEN 'Shipped'
        WHEN 'delivered' THEN 'Delivered'
        WHEN 'cancelled' THEN 'Cancelled'
        ELSE                  'Unknown'
    END AS status_label
FROM orders;

NULL Semantics in CASE

In a searched CASE, a WHEN condition that evaluates to UNKNOWN (NULL in boolean context) is treated the same as FALSE — it does not match, and evaluation continues to the next branch. This means a NULL condition never triggers a THEN clause.

In a simple CASE, the comparison expression = value uses standard SQL equality, which returns UNKNOWN when either side is NULL. As a result, WHEN NULL never matches. Use a searched CASE with IS NULL to handle NULL values explicitly.

-- Simple CASE: WHEN NULL never matches (NULL <> NULL in equality)
SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'no match' END;
-- Result: 'no match'

-- Correct way to handle NULL in a simple CASE: use searched form
SELECT
    CASE
        WHEN status IS NULL THEN 'no status'
        ELSE status
    END AS safe_status
FROM orders;

CASE in ORDER BY — Controlled Sort Order

CASE can produce a sort key that cannot be expressed with a single column reference.

-- Sort orders: unshipped first (status='paid'), then by recency
SELECT id, status, placed_at
FROM orders
ORDER BY
    CASE WHEN status = 'paid' AND shipped_at IS NULL THEN 0 ELSE 1 END,
    placed_at DESC;

CASE in GROUP BY — Dynamic Grouping

-- Group products by price tier and count items per tier
SELECT
    CASE
        WHEN price < 25   THEN 'budget'
        WHEN price < 100  THEN 'mid-range'
        ELSE                   'premium'
    END      AS tier,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
WHERE deleted_at IS NULL
GROUP BY
    CASE
        WHEN price < 25   THEN 'budget'
        WHEN price < 100  THEN 'mid-range'
        ELSE                   'premium'
    END
ORDER BY avg_price;

Design note: AxiomDB evaluates CASE expressions during row processing in the executor’s expression evaluator. Short-circuit evaluation guarantees that branches after the first matching WHEN are never evaluated, which prevents side effects (e.g., division by zero in an unreachable branch).

Date / Time Functions

Current date / time

Date component extractors

val accepts DATE, TIMESTAMP, or a text string coercible to a date. Returns NULL if the input is NULL or not a valid date type.

SELECT year(NOW()),  month(NOW()),  day(NOW());   -- e.g. 2025, 3, 25
SELECT hour(NOW()),  minute(NOW()), second(NOW()); -- e.g. 14, 30, 45

DATE_FORMAT — format a date as text

DATE_FORMAT(ts, format_string) → TEXT

Formats a DATE or TIMESTAMP value using MySQL-compatible format specifiers. Returns NULL if either argument is NULL or the format string is empty.

Unknown specifiers are passed through literally (%X → %X).

-- Format a stored timestamp as ISO date
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;
-- '2025-03-25'

-- European date format
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');
-- '25/03/2025'

-- Full datetime
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
-- '2025-03-25 14:30:45'

-- NULL input → NULL output
SELECT DATE_FORMAT(NULL, '%Y-%m-%d');  -- NULL

STR_TO_DATE — parse a date string

STR_TO_DATE(str, format_string) → DATE | TIMESTAMP | NULL

Parses a text string into a date or timestamp using MySQL-compatible format specifiers (same table as DATE_FORMAT above).

• Returns DATE if the format contains only date components.
• Returns TIMESTAMP if the format contains any time components (%H, %i, %s).
• Returns NULL on any parse failure — never raises an error (MySQL behavior).
• Returns NULL if either argument is NULL.

2-digit year rule (%y): 00–69 → 2000–2069; 70–99 → 1970–1999.

-- Parse ISO date → Value::Date
SELECT STR_TO_DATE('2025-03-25', '%Y-%m-%d');

-- Parse European date → Value::Date
SELECT STR_TO_DATE('25/03/2025', '%d/%m/%Y');

-- Parse datetime → Value::Timestamp
SELECT STR_TO_DATE('2025-03-25 14:30:00', '%Y-%m-%d %H:%i:%s');

-- Extract components from a parsed date
SELECT year(STR_TO_DATE('2025-03-25', '%Y-%m-%d'));  -- 2025

-- Round-trip: parse then format
SELECT DATE_FORMAT(STR_TO_DATE('2025-03-25', '%Y-%m-%d'), '%d/%m/%Y');
-- '25/03/2025'

-- Invalid date → NULL (Feb 30 does not exist)
SELECT STR_TO_DATE('2025-02-30', '%Y-%m-%d');  -- NULL

-- Bad format → NULL (never an error)
SELECT STR_TO_DATE('not-a-date', '%Y-%m-%d');  -- NULL

FIND_IN_SET — search a comma-separated list

FIND_IN_SET(needle, csv_list) → INT

Returns the 1-indexed position of needle in the comma-separated string csv_list. Returns 0 if not found. Comparison is case-insensitive. Returns NULL if either argument is NULL.

SELECT FIND_IN_SET('b', 'a,b,c');   -- 2
SELECT FIND_IN_SET('B', 'a,b,c');   -- 2  (case-insensitive)
SELECT FIND_IN_SET('z', 'a,b,c');   -- 0  (not found)
SELECT FIND_IN_SET('a', '');        -- 0  (empty list)
SELECT FIND_IN_SET(NULL, 'a,b,c'); -- NULL

Useful for querying rows where a column holds a comma-separated tag list:

SELECT * FROM articles WHERE FIND_IN_SET('rust', tags) > 0;

-- DATE_TRUNC and DATE_PART (PostgreSQL-compatible aliases)
SELECT DATE_TRUNC('month', placed_at) AS month, COUNT(*) FROM orders GROUP BY 1;
SELECT DATE_PART('year', created_at) AS signup_year FROM users;

Session Functions

Session functions return state that is specific to the current connection and is not visible to other sessions.

-- Commonly called by ORMs on connect to verify server identity
SELECT version();             -- '8.0.36-AxiomDB-0.1.0'
SELECT current_user();        -- 'root'
SELECT current_database();    -- 'axiomdb'

Semantics:

• Returns 0 if no AUTO_INCREMENT INSERT has occurred in the current session.
• For a single-row INSERT, returns the generated ID.
• For a multi-row INSERT (INSERT INTO t VALUES (...), (...), ...), returns the ID generated for the first row of the batch (MySQL semantics). Subsequent rows receive consecutive IDs.
• Inserting an explicit non-NULL value into an AUTO_INCREMENT column does not advance the sequence and does not update LAST_INSERT_ID().
• TRUNCATE TABLE resets the sequence to 1 but does not change the session’s LAST_INSERT_ID() value.

CREATE TABLE items (id BIGINT PRIMARY KEY AUTO_INCREMENT, name TEXT);

-- Single-row INSERT
INSERT INTO items (name) VALUES ('Widget');
SELECT LAST_INSERT_ID();      -- 1
SELECT lastval();             -- 1

-- Multi-row INSERT
INSERT INTO items (name) VALUES ('Gadget'), ('Gizmo'), ('Doohickey');
SELECT LAST_INSERT_ID();      -- 2 (first generated ID in the batch)

-- Explicit value — does not change LAST_INSERT_ID()
INSERT INTO items (id, name) VALUES (99, 'Special');
SELECT LAST_INSERT_ID();      -- still 2

-- Use inside the same statement (e.g., insert a child row)
INSERT INTO orders (user_id, item_id) VALUES (42, LAST_INSERT_ID());

Aggregate Functions

SELECT
    COUNT(*)        AS total_rows,
    COUNT(email)    AS rows_with_email,   -- excludes NULL
    SUM(total)      AS gross_revenue,
    AVG(total)      AS avg_order_value,
    MIN(placed_at)  AS first_order,
    MAX(placed_at)  AS last_order
FROM orders
WHERE status != 'cancelled';

GROUP_CONCAT — String Aggregation

GROUP_CONCAT concatenates non-NULL values across the rows of a group into a single string. It is MySQL’s most widely-used aggregate function for collecting tags, roles, categories, and comma-separated lists without a client-side join.

string_agg(expr, separator) is the PostgreSQL-compatible alias.

Syntax

GROUP_CONCAT([DISTINCT] expr [ORDER BY col [ASC|DESC], ...] [SEPARATOR 'str'])

string_agg(expr, separator)

Behavior

• NULL values are skipped — they do not appear in the result and do not add a separator.
• An empty group (no rows) or a group where every value is NULL returns NULL.
• A single value returns that value with no separator added.
• Result is truncated to 1 MB (1,048,576 bytes) maximum.

-- Basic: comma-separated tags per post
SELECT post_id, GROUP_CONCAT(tag ORDER BY tag ASC)
FROM post_tags
GROUP BY post_id;
-- post 1 → 'async,db,rust'
-- post 2 → 'rust,web'
-- post 3 (all NULL tags) → NULL

-- Custom separator
SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ' | ')
FROM post_tags
WHERE post_id = 1;
-- → 'async | db | rust'

-- DISTINCT: deduplicate before joining
SELECT GROUP_CONCAT(DISTINCT tag ORDER BY tag ASC)
FROM tags;
-- Duplicate 'rust' rows → 'async,db,rust' (appears once)

-- string_agg PostgreSQL alias
SELECT string_agg(tag, ', ')
FROM post_tags
WHERE post_id = 2;
-- → 'rust, web' (or 'web, rust' — insertion order)

-- HAVING on a GROUP_CONCAT result
SELECT post_id, GROUP_CONCAT(tag ORDER BY tag ASC) AS tags
FROM post_tags
GROUP BY post_id
HAVING GROUP_CONCAT(tag ORDER BY tag ASC) LIKE '%rust%';
-- Only posts that have the 'rust' tag

-- Collect integers as text
SELECT GROUP_CONCAT(n ORDER BY n ASC) FROM nums;
-- 1, 2, 3 → '1,2,3'

BLOB / Binary Functions

AxiomDB stores binary data as the BLOB / BYTES type and provides functions for encoding, decoding, and measuring binary values.

Usage examples

-- Store binary data encoded as base64
INSERT INTO files (name, data)
VALUES ('logo.png', FROM_BASE64('iVBORw0KGgoAAAANSUhEUgAA...'));

-- Retrieve as base64 for transport
SELECT name, TO_BASE64(data) AS data_b64 FROM files;

-- Check byte size of a blob
SELECT name, OCTET_LENGTH(data) AS size_bytes FROM files;

-- Hex encoding (PostgreSQL / MySQL ENCODE style)
SELECT ENCODE(data, 'hex') FROM files;          -- → 'deadbeef...'
SELECT DECODE('deadbeef', 'hex');               -- → binary bytes

-- OCTET_LENGTH vs LENGTH for text
SELECT LENGTH('héllo');       -- 5 (characters)
SELECT OCTET_LENGTH('héllo'); -- 6 (UTF-8 bytes: é = 2 bytes)

UUID Functions

AxiomDB generates and validates UUIDs server-side. No application-level library needed — the DB handles UUID primary keys directly.

Usage

-- Auto-generate a UUID primary key at insert time
CREATE TABLE events (
    id   UUID NOT NULL,
    name TEXT NOT NULL
);

INSERT INTO events (id, name)
VALUES (gen_random_uuid(), 'page_view');

-- Use UUID v7 for tables that benefit from time-ordered inserts
INSERT INTO events (id, name)
VALUES (uuid_generate_v7(), 'checkout');

-- Validate an incoming UUID string before inserting
SELECT is_valid_uuid('550e8400-e29b-41d4-a716-446655440000');  -- TRUE
SELECT is_valid_uuid('not-a-uuid');                             -- FALSE
SELECT is_valid_uuid(NULL);                                     -- NULL

UUID v4 vs UUID v7 — which to use?

-- UUID v4: fully random, best for security-sensitive IDs
-- Format: xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (122 random bits)
SELECT gen_random_uuid();
-- → 'f47ac10b-58cc-4372-a567-0e02b2c3d479'

-- UUID v7: time-ordered prefix, best for primary keys on B+ Tree indexes
-- Format: [48-bit ms timestamp]-[12-bit rand]-[62-bit rand]
SELECT uuid_generate_v7();
-- → '018e2e3a-1234-7abc-8def-0123456789ab'
--    ^^^^^^^^^^^ always increasing

Features

Advanced AxiomDB capabilities beyond basic SQL.

• Transactions — BEGIN, COMMIT, ROLLBACK, SAVEPOINT, MVCC, isolation levels
• Catalog & Schema — system tables, SHOW TABLES, DESCRIBE, introspection queries
• Indexes — B+ Tree indexes, composite indexes, partial indexes, query planning

Transactions

A transaction is a sequence of SQL operations that execute as a single atomic unit: either all succeed (COMMIT) or none of them take effect (ROLLBACK). AxiomDB implements full ACID transactions backed by a Write-Ahead Log and Multi-Version Concurrency Control.

Basic Transaction Control

BEGIN;
-- ... SQL statements ...
COMMIT;   -- make all changes permanent

BEGIN;
-- ... SQL statements ...
ROLLBACK; -- undo all changes since BEGIN

Simple Example — Money Transfer

BEGIN;

-- Debit the sender
UPDATE accounts SET balance = balance - 250.00 WHERE id = 1;

-- Credit the receiver
UPDATE accounts SET balance = balance + 250.00 WHERE id = 2;

-- Both succeed together, or neither succeeds
COMMIT;

If the connection drops after the first UPDATE but before COMMIT, the WAL records both the transaction start and the mutation. During crash recovery, AxiomDB sees no COMMIT record for this transaction and discards the partial change. Account 1 keeps its original balance.

Phases 39.11 and 39.12 extend that internal durability model to the clustered-index storage rewrite: clustered rows now have WAL-backed rollback/savepoint support and crash recovery by primary key plus exact row image. Phase 39.13 makes the first SQL-visible clustered cut: CREATE TABLE with an explicit PRIMARY KEY now creates clustered metadata and a clustered table root. Phase 39.14 extends that cut to clustered INSERT: SQL writes now record clustered WAL/undo directly against the clustered PK tree, and Phase 39.15 opens clustered SELECT over that same storage, Phase 39.16 extends the same transaction contract to clustered UPDATE, and 39.17 now extends it to clustered DELETE as delete-mark plus exact row-image undo. 39.18 adds clustered VACUUM: once a clustered delete-mark is old enough to be physically safe, VACUUM table_name purges the dead row, frees any overflow chain it owned, and cleans dead bookmark entries from clustered secondary indexes. 39.22 adds zero-allocation in-place UPDATE: when all SET columns are fixed-size (INT, BIGINT, REAL, BOOL, DATE, TIMESTAMP), field bytes are patched directly in the page buffer without decoding the row, and ROLLBACK reverses only the changed bytes via UndoClusteredFieldPatch — no full row image stored in the undo log.

CREATE TABLE users (id INT PRIMARY KEY, email TEXT UNIQUE);

BEGIN;
INSERT INTO users VALUES (1, 'alice@example.com');
ROLLBACK;

That rollback now restores clustered INSERT, clustered UPDATE, and clustered DELETE state: the clustered base row goes back to its exact previous row image, and any bookmark-bearing secondary entries are deleted or reinserted to match when the statement rewrote them.

Autocommit

When no explicit BEGIN is issued, each statement executes in its own implicit transaction and is committed automatically on success. This is the default mode.

-- Each of these is its own transaction
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob',   'bob@example.com');

To group multiple statements atomically, always use explicit BEGIN ... COMMIT.

SAVEPOINT — Partial Rollback

Savepoints mark a point within a transaction to which you can roll back without aborting the entire transaction. ORMs (Django, Rails, Sequelize) use savepoints internally for partial error recovery.

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 99.99);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 42, 1);
-- Suppose this fails a CHECK constraint

ROLLBACK TO SAVEPOINT after_order;
-- The order row still exists; only the order_item is rolled back

-- Try again with corrected data
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 42, 0);
-- Still fails — give up entirely
ROLLBACK;

You can have multiple savepoints with different names:

BEGIN;
SAVEPOINT sp1;
-- ... work ...
SAVEPOINT sp2;
-- ... more work ...
ROLLBACK TO SAVEPOINT sp1;   -- undo everything since sp1
RELEASE SAVEPOINT sp1;       -- destroy the savepoint (optional cleanup)
COMMIT;

MVCC — Multi-Version Concurrency Control

AxiomDB uses MVCC plus a server-side Arc<RwLock<Database>>.

Today that means:

• read-only statements (SELECT, SHOW, metadata queries) run concurrently
• mutating statements (INSERT, UPDATE, DELETE, DDL, BEGIN/COMMIT/ROLLBACK) are serialized at whole-database granularity
• a read that is already running keeps its snapshot while another session commits
• row-level locking, deadlock detection, and SELECT ... FOR UPDATE are planned for Phases 13.7, 13.8, and 13.8b

This is good for read-heavy workloads, but it is still below MySQL/InnoDB and PostgreSQL for write concurrency because they already lock at row granularity.

How It Works

When a transaction starts, it receives a snapshot — a consistent view of the database as it existed at that moment. Other transactions may commit new changes while your transaction runs, but your snapshot does not change.

Time →

Txn A (snapshot at T=100):    BEGIN → reads → reads → COMMIT
                               |          |          |
Txn B:                         |  INSERT  |  COMMIT  |
                               |          |          |
Txn A sees the world as it was at T=100.
Txn B's inserts are not visible to Txn A.

This is implemented via the Copy-on-Write B+ Tree: when Txn B writes a page, it creates a new copy rather than overwriting the original. Txn A holds a pointer to the old root and continues reading the old version. When Txn A commits, the old pages become eligible for reclamation.

No Per-Page Read Latches

Readers access immutable snapshots and owned page copies, so they do not take per-page latches in the storage layer. The current server runtime still uses a database-wide RwLock, so the real guarantee today is:

• many reads can run together
• writes do not run in parallel with other writes

Current Write Behavior

Two sessions do not currently mutate different rows in parallel. Instead, the server queues mutating statements behind the database-wide write guard. lock_timeout applies to that wait today.

This means you should not yet build on assumptions such as:

• row-level deadlock detection
• 40001 serialization_failure retries for ordinary write-write conflicts
• SELECT ... FOR UPDATE / SKIP LOCKED job-queue patterns

Those behaviors are planned, but not implemented yet.

Isolation Levels

AxiomDB currently accepts three wire-visible isolation names:

• READ COMMITTED
• REPEATABLE READ (session default)
• SERIALIZABLE

READ COMMITTED and REPEATABLE READ have distinct snapshot behavior today. SERIALIZABLE is accepted and stored, but currently uses the same frozen-snapshot policy as REPEATABLE READ; true SSI is still planned.

READ COMMITTED

Each statement within the transaction sees data committed before that statement began. A second SELECT within the same transaction may see different data if another transaction committed between the two SELECTs.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

SELECT balance FROM accounts WHERE id = 1;  -- sees T=100: balance = 1000
-- Txn B commits: UPDATE accounts SET balance = 900 WHERE id = 1
SELECT balance FROM accounts WHERE id = 1;  -- sees T=110: balance = 900 (changed!)

COMMIT;

Use READ COMMITTED when:

• You need maximum concurrency
• Each statement needing the freshest possible data is acceptable
• You are running analytics that can tolerate non-repeatable reads

REPEATABLE READ (default)

The entire transaction sees the snapshot from the moment BEGIN was executed. No matter how many other transactions commit, your reads return the same data.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

SELECT balance FROM accounts WHERE id = 1;  -- snapshot at T=100: balance = 1000
-- Txn B commits: UPDATE accounts SET balance = 900 WHERE id = 1
SELECT balance FROM accounts WHERE id = 1;  -- still sees T=100: balance = 1000

COMMIT;

Use REPEATABLE READ when:

• You need consistent data across multiple reads in one transaction
• Running reports or multi-step calculations where consistency matters
• Implementing optimistic locking patterns

Isolation Level Comparison

SERIALIZABLE

SERIALIZABLE is accepted for MySQL/PostgreSQL compatibility, but today it uses the same frozen snapshot as REPEATABLE READ. The engine does not yet run Serializable Snapshot Isolation conflict tracking.

E-commerce Checkout — Current Safe Pattern

Until row-level locking lands, the supported stock-reservation pattern is a guarded UPDATE ... WHERE stock >= ? plus affected-row checks.

BEGIN;

-- Reserve stock atomically; application checks that each UPDATE affects 1 row.
UPDATE products SET stock = stock - 2 WHERE id = 1 AND stock >= 2;
UPDATE products SET stock = stock - 1 WHERE id = 3 AND stock >= 1;

-- Create the order header
INSERT INTO orders (user_id, total, status)
VALUES (99, 149.97, 'paid');

-- Create order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (LAST_INSERT_ID(), 1, 2, 49.99),
    (LAST_INSERT_ID(), 3, 1, 49.99);

COMMIT;

If any step fails (constraint violation, connection drop, server crash), the WAL ensures the entire transaction is rolled back on recovery.

Transaction Performance Tips

• Keep transactions short. Long-running transactions hold MVCC versions in memory longer, increasing memory pressure.
• Avoid user interaction within a transaction. Never open a transaction and wait for a user to click a button.
• For bulk inserts into clustered tables, wrap all rows in a single BEGIN ... COMMIT block. Phase 40.1 introduces ClusteredInsertBatch: rows are staged in memory, sorted by primary key, and flushed at COMMIT using the rightmost-leaf batch append path. This reduces O(N) CoW page-clone operations to O(N / leaf_capacity) page writes — delivering 55.9K rows/s for 50K sequential PK rows vs MySQL 8.0 InnoDB’s ~35K rows/s (+59%).
• For bulk loads, consider committing every 50,000–100,000 rows to limit WAL growth while keeping the batch-insert speedup.

WAL Fsync Pipeline — Current Server Commit Path

Every durable DML commit still needs WAL fsync, but AxiomDB no longer relies on the old timer-based group-commit window for batching. The server now uses an always-on leader-based fsync pipeline:

• one connection becomes the fsync leader
• later commits queue behind that leader if their WAL entry is already buffered
• if the leader’s fsync covers a later commit’s LSN, that later commit returns without paying another fsync

Catalog and Schema Introspection

AxiomDB maintains an internal catalog that records logical databases, tables, columns, and indexes. The catalog is persisted in system heaps rooted from the meta page and is exposed through convenience commands plus catalog-backed SQL resolution.

Databases

Fresh databases always bootstrap a default logical database named axiomdb. Existing databases created before multi-database support are upgraded lazily on open and their legacy tables remain owned by axiomdb.

SHOW DATABASES;

Example output:

CREATE DATABASE analytics;
USE analytics;
SELECT DATABASE();

Expected result:

System Tables

The catalog exposes six system tables in the axiom schema. They are always readable without any special privileges.

axiom_tables

Contains one row per user-visible table.

Phase 39.13 adds physical-layout metadata to these rows even though the introspection surface is still being expanded. The important rule today is:

• explicit PRIMARY KEY table → clustered table root
• no explicit PRIMARY KEY → heap table root

The catalog now keeps that distinction even before clustered DML is exposed.

-- List all user tables
SELECT schema_name, table_name, column_count
FROM axiom_tables
ORDER BY schema_name, table_name;

axiom_columns

Contains one row per column, in declaration order.

-- All columns of the orders table
SELECT col_index, col_name, data_type, not_null, default_value
FROM axiom_columns
WHERE table_name = 'orders'
ORDER BY col_index;

axiom_indexes

Contains one row per index (including automatically generated PK and UNIQUE indexes).

-- All indexes on the products table
SELECT index_name, is_unique, is_primary, columns
FROM axiom_indexes
WHERE table_name = 'products'
ORDER BY is_primary DESC, index_name;

Convenience Commands

SHOW DATABASES

Lists all logical databases persisted in the catalog.

SHOW DATABASES;

USE

Changes the selected database for the current connection. Unqualified table names are resolved inside that database.

USE analytics;
SHOW TABLES;

If the database does not exist, AxiomDB returns MySQL error 1049:

USE missing_db;
-- ERROR 1049 (42000): Unknown database 'missing_db'

SHOW TABLES

Lists all tables in the current schema.

SHOW TABLES;

Example output:

SHOW TABLES LIKE

Filters by a LIKE pattern.

SHOW TABLES LIKE 'order%';

DESCRIBE (or DESC)

Shows the column structure of a table.

DESCRIBE users;
-- or:
DESC products;

Example output:

Introspection Queries

Because the catalog is exposed as regular tables, you can write arbitrary SQL against it.

Find all NOT NULL columns across all tables

SELECT table_name, col_name, data_type
FROM axiom_columns
WHERE not_null = TRUE
ORDER BY table_name, col_index;

Find tables with no indexes

SELECT t.table_name
FROM axiom_tables t
LEFT JOIN axiom_indexes i ON i.table_id = t.id
WHERE i.id IS NULL
ORDER BY t.table_name;

Find foreign key columns that lack an index

-- Assumes FK columns follow the naming convention: <table>_id
SELECT c.table_name, c.col_name
FROM axiom_columns c
LEFT JOIN axiom_indexes i
    ON i.table_id = c.table_id
   AND i.columns LIKE c.col_name || '%'
WHERE c.col_name LIKE '%_id'
  AND c.col_name <> 'id'
  AND i.id IS NULL
ORDER BY c.table_name, c.col_name;

Column count per table

SELECT table_name, column_count
FROM axiom_tables
ORDER BY column_count DESC;

Catalog Bootstrap

The catalog is bootstrapped on the very first open() call. AxiomDB allocates the catalog roots, inserts the default database axiomdb, and makes the catalog durable before the database accepts traffic. Subsequent opens detect the initialized roots and skip the bootstrap path.

The bootstrap is idempotent: if AxiomDB crashes during bootstrap, the incomplete transaction has no COMMIT record in the WAL, so crash recovery discards it and the next open() re-runs the bootstrap from scratch.

Schema Visibility Rules

The default schema is public. All tables created without an explicit schema prefix belong to public. System tables live in the axiom schema and are always visible.

-- These are equivalent if the default schema is 'public'
CREATE TABLE users (...);
CREATE TABLE public.users (...);

-- System tables require the axiom. prefix or are accessible without schema
SELECT * FROM axiom_tables;          -- works
SELECT * FROM axiom.axiom_tables;   -- also works

Indexes

Indexes are B+ Tree data structures that allow AxiomDB to find rows matching a condition without scanning the entire table. Every index is a Copy-on-Write B+ Tree stored in the same .db file as the table data.

Current Storage Model

Today AxiomDB exposes two SQL-visible table layouts:

• tables without an explicit PRIMARY KEY still use the classic heap + index path
• tables with an explicit PRIMARY KEY now bootstrap clustered storage at CREATE TABLE time

That new clustered SQL boundary is now wider through 39.18:

• the table root is clustered from day one
• PRIMARY KEY catalog metadata points at that clustered root
• INSERT on clustered tables now works through the clustered PK tree
• SELECT on clustered tables now works through the clustered PK tree and clustered secondary bookmarks
• UPDATE on clustered tables now rewrites rows directly in the clustered PK tree
• DELETE on clustered tables now applies clustered delete-mark through the clustered PK tree
• VACUUM table_name on clustered tables now physically purges safe dead rows, frees overflow chains, and cleans dead secondary bookmarks
• ALTER TABLE legacy_table REBUILD now migrates legacy heap+PRIMARY KEY tables into clustered layout and rebuilds secondary indexes as PK-bookmark indexes

Internally, the storage rewrite already has clustered insert, point lookup, range scan, same-leaf update, delete-mark, structural rebalance / relocate-update, secondary PK bookmarks, and overflow-backed clustered rows for large payloads, and explicit-PK CREATE TABLE now records that layout in SQL metadata. Phase 39.14 made the first executor-visible clustered write cut, 39.15 opened the read side, 39.16 brought UPDATE onto that same clustered path, and 39.17 now does the same for logical clustered DELETE: PK lookups/ranges, clustered secondary bookmark probes, in-place delete-mark, and rollback-safe WAL all stay on clustered storage. 39.18 closes the first clustered maintenance slice too: VACUUM now purges physically dead clustered cells and their overflow/secondary debris instead of leaving clustered cleanup as a future-only promise.

That internal rewrite is still honest about its current boundary:

• relocate-update rewrites only the current inline version
• clustered delete is still delete-mark first, then later VACUUM
• large clustered rows can already spill to overflow pages internally, but SQL only explicit-PK tables expose clustered layout at DDL time
• clustered covering reads still degrade to fetching the clustered row body; a true clustered index-only optimization is still future work
• clustered child-table foreign-key enforcement still remains future work

Index Statistics and Query Planner

AxiomDB maintains per-column statistics to help the query planner choose between an index scan and a full table scan.

How it works

When you create an index, AxiomDB automatically computes:

• row_count — total visible rows in the table
• ndv (number of distinct values) — exact count of distinct non-NULL values

The planner uses selectivity = 1 / ndv for equality predicates. If selectivity > 20% of rows would be returned, a full table scan is cheaper than an index scan, so the planner uses the table scan.

ndv = 3,   rows = 10,000  →  selectivity = 33%  > 20%  →  Scan
ndv = 100, rows = 10,000  →  selectivity = 1%   < 20%  →  Index

ANALYZE command

Run ANALYZE to refresh statistics after bulk inserts or deletes:

-- Analyze a specific table (all indexed columns)
ANALYZE TABLE users;

-- Analyze a specific column only
ANALYZE TABLE orders (status);

Statistics are automatically computed at CREATE INDEX time. Run ANALYZE when:

• Significant data was added after the index was created
• Query plans seem wrong (e.g., full scan when index would be faster)

Automatic staleness detection

After enough row changes (>20% of the analyzed row count), the planner automatically uses conservative defaults (ndv = 200) until the next ANALYZE. This prevents stale statistics from causing poor query plans.

Composite Indexes

A composite index covers two or more columns. The query planner uses it when the WHERE clause contains equality conditions on the leading columns.

CREATE INDEX idx_user_status ON orders(user_id, status);

-- Uses composite index: both leading columns matched
SELECT * FROM orders WHERE user_id = 42 AND status = 'active';

-- Also uses index via prefix scan: leading column only
SELECT * FROM orders WHERE user_id = 42;

-- Does NOT use index: leading column absent from WHERE
SELECT * FROM orders WHERE status = 'active';

Fill Factor

Fill factor controls how full a B-Tree leaf page is allowed to get before it splits. A lower fill factor leaves intentional free space on each page, reducing split frequency for workloads that add rows after index creation.

-- Append-heavy time-series table: pages fill to 70% before splitting.
CREATE INDEX idx_ts ON events(created_at) WITH (fillfactor = 70);

-- Compact read-only index: fill pages completely.
CREATE UNIQUE INDEX uq_email ON users(email) WITH (fillfactor = 100);

-- Default (90%) — equivalent to omitting WITH:
CREATE INDEX idx_x ON t(x);

Range and default

Valid range: 10–100. Default: 90 (matches PostgreSQL’s BTREE_DEFAULT_FILLFACTOR). fillfactor = 100 reproduces the current behavior exactly — pages fill completely before splitting.

Effect on splits

With fillfactor = F:

• Leaf page splits when it reaches ⌈F × ORDER_LEAF / 100⌉ entries (instead of at full capacity).
• After a split, both new pages hold roughly F/2 % of capacity — leaving room for future inserts without triggering another split.
• Internal pages always fill to capacity (not user-configurable).

Automatic Indexes

AxiomDB automatically creates a unique B+ Tree index for:

• Every PRIMARY KEY declaration
• Every UNIQUE column constraint or UNIQUE table constraint

For clustered tables, the automatically created PRIMARY KEY metadata row reuses the clustered table root instead of allocating a second heap-era PK tree. UNIQUE secondary indexes still allocate ordinary B+ Tree roots, but 39.14 now maintains their entries as secondary_key ++ pk_suffix bookmarks during SQL-visible clustered INSERT.

Multi-row INSERT on Indexed Tables

Multi-row INSERT ... VALUES (...), (... ) statements now stay on a grouped heap/index path even when the target table already has a PRIMARY KEY or secondary indexes.

INSERT INTO users VALUES
  (1, 'a@example.com'),
  (2, 'b@example.com'),
  (3, 'c@example.com');

This matters because indexed tables used to fall back to per-row maintenance on this workload. The grouped path keeps the same SQL-visible behavior:

• duplicate PRIMARY KEY / UNIQUE values inside the same statement still fail
• a failed multi-row statement does not leak partial committed rows
• partial indexes still include only rows whose predicate matches

Startup Integrity Verification

When a database opens, AxiomDB verifies every catalog-visible index against the heap-visible rows reconstructed after WAL recovery.

• If the tree is readable but its contents diverge from the heap, AxiomDB rebuilds the index automatically from table contents before serving traffic.
• If the tree cannot be traversed safely, open fails with IndexIntegrityFailure instead of guessing.

This check applies to both embedded mode and server mode because both call the same startup verifier.

Creating Indexes Manually

CREATE [UNIQUE] INDEX index_name ON table_name (col1 [ASC|DESC], col2 ...);
CREATE INDEX idx_users_name   ON users   (name);
CREATE INDEX idx_orders_user  ON orders  (user_id, placed_at DESC);
CREATE UNIQUE INDEX uq_sku    ON products (sku);

See DDL — CREATE INDEX for the full syntax.

When Indexes Help

The query planner considers an index when:

1. The leading column(s) of the index appear in a WHERE equality or range condition.
2. The index columns match the ORDER BY direction and order (avoids a sort step).
3. The index is selective enough that scanning it is cheaper than a full table scan.

-- Good: leading column (user_id) used in WHERE
CREATE INDEX idx_orders_user ON orders (user_id, placed_at DESC);
SELECT * FROM orders WHERE user_id = 42 ORDER BY placed_at DESC;

-- Bad: leading column not in WHERE — index not used
SELECT * FROM orders WHERE placed_at > '2026-01-01';
-- Solution: create a separate index on placed_at
CREATE INDEX idx_orders_date ON orders (placed_at);

Composite Index Column Order

The order of columns in a composite index determines which query patterns it accelerates. The B+ Tree is sorted by the concatenated key (col1, col2, ...).

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

This index accelerates:

• WHERE user_id = 42
• WHERE user_id = 42 AND status = 'paid'

This index does NOT accelerate:

• WHERE status = 'paid' (leading column not constrained)

Rule of thumb: put the highest-selectivity, most frequently filtered column first.

Partial Indexes

A partial index covers only the rows matching a WHERE predicate. This reduces index size and maintenance cost.

-- Index only pending orders (the common access pattern)
CREATE INDEX idx_pending_orders ON orders (user_id)
WHERE status = 'pending';

-- Index only non-deleted users
CREATE INDEX idx_active_users ON users (email)
WHERE deleted_at IS NULL;

The query planner uses a partial index only when the query’s WHERE clause implies the index’s predicate.

Index Key Size Limit

The B+ Tree stores encoded keys up to 768 bytes. For most column types this is never an issue:

• INT, BIGINT, UUID, TIMESTAMP — fixed-size, always well under the limit.
• TEXT, VARCHAR — a 760-character value will just fit. If you index a column with very long strings (> 750 characters), rows exceeding the limit are silently skipped at CREATE INDEX time and return IndexKeyTooLong on INSERT.

Query Planner — Phase 6.3

The planner rewrites the execution plan before running the scan. Currently recognized patterns:

Equality lookup — exact match on the leading indexed column:

-- Uses B-Tree point lookup (O(log n) instead of O(n))
SELECT * FROM users WHERE email = 'alice@example.com';
SELECT * FROM orders WHERE id = 42;

This includes the PRIMARY KEY. A query like WHERE id = 42 does not need a redundant secondary index on id.

Range scan — upper and lower bound on the leading indexed column:

-- Uses B-Tree range scan
SELECT * FROM orders WHERE created_at > '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM products WHERE price >= 10.0 AND price <= 50.0;

Full scan fallback — any pattern not recognized above:

-- Falls back to full table scan (no index for OR, function, or non-leading column)
SELECT * FROM users WHERE email LIKE '%gmail.com';
SELECT * FROM orders WHERE status = 'paid' OR total > 1000;

Partial Indexes

A partial index covers only the rows matching a WHERE predicate. This reduces index size, speeds up maintenance, and — for UNIQUE indexes — restricts uniqueness enforcement to the matching subset.

-- Only active users need unique emails.
CREATE UNIQUE INDEX uq_active_email ON users(email) WHERE deleted_at IS NULL;

-- Index only pending orders for fast user lookups.
CREATE INDEX idx_pending ON orders(user_id) WHERE status = 'pending';

Partial UNIQUE indexes

The uniqueness constraint applies only among rows satisfying the predicate. Rows that do not satisfy the predicate are never inserted into the index.

-- alice deleted, then re-created: no conflict.
INSERT INTO users VALUES (1, 'alice@x.com', '2025-01-01'); -- deleted
INSERT INTO users VALUES (2, 'alice@x.com', NULL);          -- active ✅
INSERT INTO users VALUES (3, 'alice@x.com', NULL);          -- ❌ UniqueViolation
INSERT INTO users VALUES (4, 'alice@x.com', '2025-06-01');  -- deleted ✅

Planner support

The planner uses a partial index only when the query’s WHERE clause implies the index predicate. If the implication cannot be verified, the planner falls back to a full scan or a full index — always correct.

-- Uses partial index (WHERE contains `deleted_at IS NULL`):
SELECT * FROM users WHERE email = 'alice@x.com' AND deleted_at IS NULL;

-- Falls back to full scan (predicate not in WHERE):
SELECT * FROM users WHERE email = 'alice@x.com';

Foreign Key Constraints

Foreign key constraints ensure referential integrity between tables. Every non-NULL value in the FK column of the child table must reference an existing row in the parent table.

-- Inline REFERENCES syntax
CREATE TABLE orders (
  id      INT PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE
);

-- Table-level FOREIGN KEY syntax
CREATE TABLE order_items (
  id         INT PRIMARY KEY,
  order_id   INT,
  product_id INT,
  CONSTRAINT fk_order   FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
  CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

-- Add FK after the fact
ALTER TABLE orders
  ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- Remove a FK constraint
ALTER TABLE orders DROP CONSTRAINT fk_user;

ON DELETE Actions

Enforcement Examples

CREATE TABLE users  (id INT PRIMARY KEY, email TEXT);
CREATE TABLE orders (id INT PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE);

INSERT INTO users  VALUES (1, 'alice@x.com');
INSERT INTO orders VALUES (10, 1);            -- ✅ user 1 exists

-- INSERT with missing parent → error
INSERT INTO orders VALUES (20, 999);
-- ERROR 23503: Foreign key constraint fails: 'orders.user_id' = '999'

-- DELETE parent with CASCADE → child rows automatically deleted
DELETE FROM users WHERE id = 1;
SELECT COUNT(*) FROM orders;  -- → 0 (orders were cascaded)

-- DELETE parent with RESTRICT (default) → blocked if children exist
CREATE TABLE invoices (id INT PRIMARY KEY, order_id INT REFERENCES orders(id));
INSERT INTO users   VALUES (2, 'bob@x.com');
INSERT INTO orders  VALUES (30, 2);
INSERT INTO invoices VALUES (1, 30);
DELETE FROM orders WHERE id = 30;
-- ERROR 23503: foreign key constraint "fk_invoices_order_id": invoices.order_id references this row

NULL FK Values

A NULL value in a FK column is always allowed — it does not reference any parent row. This follows SQL standard MATCH SIMPLE semantics.

INSERT INTO orders VALUES (99, NULL);  -- ✅ NULL user_id is always allowed

ON UPDATE

Only ON UPDATE RESTRICT (the default) is enforced. Updating a parent key while child rows reference it is rejected. ON UPDATE CASCADE and ON UPDATE SET NULL are planned for Phase 6.10.

Current Limitations

• Only single-column FKs are supported. Composite FKs — FOREIGN KEY (a, b) REFERENCES t(x, y) — are planned for Phase 6.10.
• ON UPDATE CASCADE / ON UPDATE SET NULL are planned for Phase 6.10.
• FK validation uses B-Tree range scans via the FK auto-index (Phase 6.9). Falls back to full table scan for pre-6.9 FKs.

Bloom Filter Optimization

AxiomDB maintains an in-memory Bloom filter for each secondary index. The filter allows the query executor to skip B-Tree page reads entirely when a lookup key is definitively absent from the index.

How It Works

When the planner chooses an index lookup for a WHERE col = value condition, the executor checks the Bloom filter before touching the B-Tree:

• Filter says no → key is 100% absent. Zero B-Tree pages read. Empty result returned immediately.
• Filter says maybe → normal B-Tree lookup proceeds.

The filter is a probabilistic data structure: it never produces false negatives (a key that exists will always get a “maybe”), but can produce false positives (a key that does not exist may occasionally get a “maybe” instead of “no”). The false positive rate is tuned to 1% — at most 1 in 100 absent-key lookups will still read the B-Tree.

Lifecycle

Dirty Filters

After a DELETE or UPDATE, the filter is marked dirty: it may still return “maybe” for keys that were deleted. This does not affect correctness — the B-Tree lookup simply finds no matching row. It only means that some absent keys may not benefit from the zero-I/O shortcut until the filter is rebuilt via ANALYZE TABLE (available since Phase 6.12).

Dropping an Index

-- MySQL syntax (required when the server is in MySQL wire protocol mode)
DROP INDEX index_name ON table_name;
DROP INDEX IF EXISTS idx_old ON table_name;

Dropping an index frees all B-Tree pages, reclaiming disk space immediately.

Dropping an index that backs a PRIMARY KEY or UNIQUE constraint requires dropping the constraint first (via ALTER TABLE DROP CONSTRAINT).

Index Introspection

-- All indexes on a table
SELECT index_name, is_unique, is_primary, columns
FROM axiom_indexes
WHERE table_name = 'orders'
ORDER BY is_primary DESC, index_name;

-- Root page of each index (useful for storage analysis)
SELECT index_name, root_page_id
FROM axiom_indexes;

Index-Only Scans (Covering Indexes)

When every column referenced by a SELECT is already stored as a key column of the chosen index, AxiomDB can satisfy the query entirely from the B-Tree — no heap page read is needed. This is called an index-only scan.

Example

CREATE INDEX idx_age ON users (age);

-- Index-only scan: only column needed (age) is the index key.
SELECT age FROM users WHERE age = 25;

The executor reads the matching B-Tree leaf entries, extracts the age value from the encoded key bytes, and returns the rows without ever touching the heap.

INCLUDE syntax — declaring covering intent

You can declare additional columns as part of a covering index using the INCLUDE clause:

CREATE INDEX idx_name_dept ON employees (name) INCLUDE (department, salary);

INCLUDE columns are recorded in the catalog metadata so the planner knows the index covers those columns. Note: physical storage of INCLUDE column values in B-Tree leaf nodes is deferred to a future covering-index phase. Until then, the planner uses INCLUDE to correctly identify IndexOnlyScan opportunities, but the values are read from the key portion of the B-Tree entry.

MVCC and the 24-byte header read

Index-only scans still perform a lightweight visibility check per row. For each B-Tree entry, the executor reads only the 24-byte RowHeader (the slot header containing txn_id_created, txn_id_deleted, and sequence number) to determine whether the row is visible to the current transaction snapshot. The full row payload is never decoded.

Non-Unique Secondary Index Key Format

Non-unique secondary indexes store the indexed column values together with the row’s RecordId as the B-Tree key:

key = encode_index_key(col_vals) || encode_rid(rid)   // 10-byte RecordId suffix

This ensures every B-Tree entry is globally unique even when multiple rows share the same indexed value — making INSERT safe without a DuplicateKey error.

When looking up all rows with a given indexed value, the executor performs a range scan with synthetic bounds:

lo = encode_index_key(val) || [0x00; 10]   // smallest possible RecordId
hi = encode_index_key(val) || [0xFF; 10]   // largest possible RecordId

Phase 39.9 adds a second, internal-only secondary-key path for the clustered rewrite: there the physical entry is secondary_key ++ missing_primary_key_columns so a future clustered executor can jump from a secondary entry to the owning PRIMARY KEY row without depending on a heap slot. Phases 39.11 and 39.12 already add internal WAL/rollback and crash recovery for clustered rows by primary key and exact row image, but that path is still not SQL-visible yet.

B+ Tree Implementation Details

AxiomDB’s B+ Tree is a Copy-on-Write structure backed by the StorageEngine trait. Key properties:

• ORDER_INTERNAL = 223: up to 223 separator keys and 224 child pointers per internal node
• ORDER_LEAF = 217: up to 217 (key, RecordId) pairs per leaf node
• 16 KB pages: both internal and leaf nodes fit exactly in one page
• AtomicU64 root: root page swapped atomically — readers are lock-free
• CoW semantics: writes copy the path from root to the modified leaf; old versions are visible to concurrent readers until they finish

See B+ Tree Internals for the on-disk format and the derivation of the ORDER constants.

Embedded Mode

AxiomDB can run in-process — inside your application, with no TCP server, no daemon, no network round-trips. This is the SQLite model: the database is a library you link against, not a process you connect to.

The embedded crate ships two APIs:

Build profiles

# Cargo.toml
[dependencies]
axiomdb-embedded = { path = "...", features = ["desktop"] }  # default
# axiomdb-embedded = { path = "...", features = ["async-api"] }  # + tokio

The desktop build produces a ~1.1 MB dynamic library. The server binary (with full wire protocol) is ~2.1 MB. You get a leaner binary by only linking what you need.

Rust API

Opening a database

#![allow(unused)]
fn main() {
use axiomdb_embedded::Db;

// Creates ./myapp.db and ./myapp.wal if they don't exist.
// Runs crash recovery automatically if the WAL has uncommitted entries.
// Also verifies every catalog-visible index before returning the handle.
let mut db = Db::open("./myapp.db")?;
let mut db2 = Db::open_dsn("file:/tmp/myapp.db")?;
let mut db3 = Db::open_dsn("axiomdb:///tmp/myapp")?;
}

Remote DSNs such as postgres://user@127.0.0.1:5432/app are not supported by embedded mode in Phase 5.15 and return DbError::InvalidDsn.

DDL and DML

#![allow(unused)]
fn main() {
db.execute("CREATE TABLE users (id INT NOT NULL, name TEXT, score REAL)")?;

let affected = db.execute("INSERT INTO users VALUES (1, 'Alice', 9.5)")?;
assert_eq!(affected, 1);

let affected = db.execute("UPDATE users SET score = 10.0 WHERE id = 1")?;
assert_eq!(affected, 1);

let affected = db.execute("DELETE FROM users WHERE score < 5.0")?;
}

SELECT — rows only

#![allow(unused)]
fn main() {
let rows = db.query("SELECT * FROM users WHERE score > 8.0")?;
for row in &rows {
    // row is Vec<Value> — one Value per column
    println!("{:?}", row);
}
}

SELECT — rows + column names

Use query_with_columns when you need the column names at runtime (building a table display, serializing to JSON, passing headers to a UI component, etc.).

#![allow(unused)]
fn main() {
let (columns, rows) = db.query_with_columns("SELECT id, name FROM users")?;

println!("columns: {:?}", columns); // ["id", "name"]

for row in &rows {
    for (col, val) in columns.iter().zip(row.iter()) {
        println!("{col} = {val}");
    }
}
}

Full QueryResult (metadata + last_insert_id)

#![allow(unused)]
fn main() {
use axiomdb_sql::result::QueryResult;

match db.run("INSERT INTO users VALUES (2, 'Bob', 7.2)")? {
    QueryResult::Affected { count, last_insert_id } => {
        println!("inserted {count} row, id = {:?}", last_insert_id);
    }
    QueryResult::Rows { columns, rows } => { /* SELECT */ }
    QueryResult::Empty => { /* DDL */ }
}
}

Explicit transactions

#![allow(unused)]
fn main() {
db.begin()?;
db.execute("INSERT INTO orders VALUES (1, 100.0)")?;
db.execute("UPDATE inventory SET qty = qty - 1 WHERE id = 42")?;
db.commit()?;

// Or:
db.begin()?;
// ... something goes wrong ...
db.rollback()?;
}

Error handling

#![allow(unused)]
fn main() {
match db.query("SELECT * FROM nonexistent") {
    Ok(rows) => { /* ... */ }
    Err(e) => {
        eprintln!("query failed: {e}");
        // Also accessible as a string for display/logging:
        if let Some(msg) = db.last_error() {
            eprintln!("last error: {msg}");
        }
    }
}
}

Async (Tokio)

use axiomdb_embedded::async_db::AsyncDb;

#[tokio::main]
async fn main() {
    let db = AsyncDb::open("./myapp.db").await?;
    let db2 = AsyncDb::open_dsn("file:/tmp/myapp.db").await?;
    db.execute("CREATE TABLE t (id INT)").await?;

    let (columns, rows) = db.query_with_columns("SELECT * FROM t").await?;
}

AsyncDb wraps Db in an Arc<Mutex<Db>> and runs each operation in tokio::task::spawn_blocking, keeping the async executor unblocked.

Persist and reopen

The database persists on disk. Close it (drop the Db) and reopen it from another process or session:

#![allow(unused)]
fn main() {
{
    let mut db = Db::open("./data.db")?;
    db.execute("CREATE TABLE log (ts BIGINT, msg TEXT)")?;
    db.execute("INSERT INTO log VALUES (1700000000, 'started')")?;
} // db is dropped here — WAL is flushed, file lock released

// Later — in the same process or a different one:
let mut db = Db::open("./data.db")?;
let rows = db.query("SELECT * FROM log")?;
assert_eq!(rows.len(), 1);
}

C API

Link against libaxiomdb_embedded.{so,dylib,dll} or the static libaxiomdb_embedded.a.

Header

#include "axiomdb.h"

A minimal axiomdb.h to copy into your project:

#pragma once
#include <stdint.h>
#include <stddef.h>

typedef struct AxiomDb    AxiomDb;
typedef struct AxiomRows  AxiomRows;

/* Type codes — same as SQLite for easy porting */
#define AXIOMDB_NULL     0
#define AXIOMDB_INTEGER  1   /* Bool, Int, BigInt, Date (days), Timestamp (µs) */
#define AXIOMDB_REAL     2   /* Real, Decimal */
#define AXIOMDB_TEXT     3   /* Text, UUID */
#define AXIOMDB_BLOB     4   /* Bytes */

/* Open / close */
AxiomDb*    axiomdb_open        (const char* path);
AxiomDb*    axiomdb_open_dsn    (const char* dsn);
void        axiomdb_close       (AxiomDb* db);

/* Execute DML/DDL — returns rows affected, or -1 on error */
int64_t     axiomdb_execute     (AxiomDb* db, const char* sql);

/* Query — returns result set, or NULL on error */
AxiomRows*  axiomdb_query       (AxiomDb* db, const char* sql);

/* Result set accessors */
int64_t     axiomdb_rows_count        (const AxiomRows* rows);
int32_t     axiomdb_rows_columns      (const AxiomRows* rows);
const char* axiomdb_rows_column_name  (const AxiomRows* rows, int32_t col);
int32_t     axiomdb_rows_type         (const AxiomRows* rows, int64_t row, int32_t col);
int64_t     axiomdb_rows_get_int      (const AxiomRows* rows, int64_t row, int32_t col);
double      axiomdb_rows_get_double   (const AxiomRows* rows, int64_t row, int32_t col);
const char* axiomdb_rows_get_text     (const AxiomRows* rows, int64_t row, int32_t col);
const uint8_t* axiomdb_rows_get_blob  (const AxiomRows* rows, int64_t row, int32_t col, size_t* len);
void        axiomdb_rows_free         (AxiomRows* rows);

/* Last error message for this db handle — NULL if last operation succeeded */
const char* axiomdb_last_error  (const AxiomDb* db);

Complete example

#include <stdio.h>
#include <stdint.h>
#include "axiomdb.h"

int main(void) {
    AxiomDb* db = axiomdb_open("./app.db");
    AxiomDb* db2 = axiomdb_open_dsn("file:/tmp/app.db");
    if (!db) { fprintf(stderr, "failed to open db\n"); return 1; }

    axiomdb_execute(db,
        "CREATE TABLE IF NOT EXISTS products ("
        "  id INT NOT NULL, name TEXT, price REAL, active INTEGER"
        ")");

    axiomdb_execute(db, "INSERT INTO products VALUES (1, 'Widget', 9.99, 1)");
    axiomdb_execute(db, "INSERT INTO products VALUES (2, 'Gadget', 24.50, 1)");
    axiomdb_execute(db, "INSERT INTO products VALUES (3, 'Donut', 1.25, 0)");

    AxiomRows* rows = axiomdb_query(db,
        "SELECT id, name, price FROM products WHERE active = 1");

    if (!rows) {
        fprintf(stderr, "query error: %s\n", axiomdb_last_error(db));
        axiomdb_close(db);
        return 1;
    }

    /* Print header */
    int32_t ncols = axiomdb_rows_columns(rows);
    for (int32_t c = 0; c < ncols; c++) {
        printf("%-12s", axiomdb_rows_column_name(rows, c));
    }
    printf("\n");

    /* Print rows */
    int64_t nrows = axiomdb_rows_count(rows);
    for (int64_t r = 0; r < nrows; r++) {
        for (int32_t c = 0; c < ncols; c++) {
            switch (axiomdb_rows_type(rows, r, c)) {
                case AXIOMDB_INTEGER:
                    printf("%-12lld", (long long)axiomdb_rows_get_int(rows, r, c));
                    break;
                case AXIOMDB_REAL:
                    printf("%-12.2f", axiomdb_rows_get_double(rows, r, c));
                    break;
                case AXIOMDB_TEXT:
                    printf("%-12s", axiomdb_rows_get_text(rows, r, c));
                    break;
                case AXIOMDB_NULL:
                    printf("%-12s", "NULL");
                    break;
                default:
                    printf("%-12s", "?");
            }
        }
        printf("\n");
    }

    axiomdb_rows_free(rows);
    axiomdb_close(db);
    axiomdb_close(db2);
    return 0;
}

Output:

id          name        price
1           Widget      9.99
2           Gadget      24.50

Type mapping

Python (ctypes)

import ctypes, os

lib = ctypes.CDLL("./libaxiomdb_embedded.dylib")  # or .so on Linux

lib.axiomdb_open.restype = ctypes.c_void_p
lib.axiomdb_open.argtypes = [ctypes.c_char_p]

lib.axiomdb_execute.restype = ctypes.c_int64
lib.axiomdb_execute.argtypes = [ctypes.c_void_p, ctypes.c_char_p]

lib.axiomdb_query.restype = ctypes.c_void_p
lib.axiomdb_query.argtypes = [ctypes.c_void_p, ctypes.c_char_p]

lib.axiomdb_rows_count.restype = ctypes.c_int64
lib.axiomdb_rows_count.argtypes = [ctypes.c_void_p]

lib.axiomdb_rows_get_text.restype = ctypes.c_char_p
lib.axiomdb_rows_get_text.argtypes = [ctypes.c_void_p, ctypes.c_int64, ctypes.c_int32]

lib.axiomdb_rows_free.argtypes = [ctypes.c_void_p]
lib.axiomdb_close.argtypes = [ctypes.c_void_p]

db = lib.axiomdb_open(b"./app.db")
lib.axiomdb_execute(db, b"CREATE TABLE IF NOT EXISTS t (id INT, name TEXT)")
lib.axiomdb_execute(db, b"INSERT INTO t VALUES (1, 'hello')")

rows = lib.axiomdb_query(db, b"SELECT id, name FROM t")
for r in range(lib.axiomdb_rows_count(rows)):
    id_  = lib.axiomdb_rows_get_text(rows, r, 0)
    name = lib.axiomdb_rows_get_text(rows, r, 1)
    print(f"id={id_.decode()}, name={name.decode()}")

lib.axiomdb_rows_free(rows)
lib.axiomdb_close(db)

Build the shared library

# Dynamic library (.dylib / .so / .dll)
cargo build --release -p axiomdb-embedded

# Static library (.a) — for iOS, embedded systems, Unity AOT
cargo build --release -p axiomdb-embedded
# → target/release/libaxiomdb_embedded.a

# With async support
cargo build --release -p axiomdb-embedded --features async-api

Output files are in target/release/:

• macOS: libaxiomdb_embedded.dylib
• Linux: libaxiomdb_embedded.so
• Windows: axiomdb_embedded.dll
• All platforms: libaxiomdb_embedded.a (static)

Error Reference

AxiomDB returns structured errors with a SQLSTATE code, a human-readable message, and optional detail fields. Understanding these codes allows applications to handle specific failure scenarios correctly (for example: catching a uniqueness violation to show a “email already taken” message rather than a generic crash page).

Error Format

Every error from AxiomDB is represented as an ErrorResponse struct with these fields:

{
  "sqlstate": "23505",
  "severity": "ERROR",
  "message": "unique key violation on index 'users_email_idx'",
  "detail": "Key (value)=(alice@example.com) is already present in index users_email_idx.",
  "hint": "A row with the same value already exists in index users_email_idx. Use INSERT ... ON CONFLICT to handle duplicates."
}

{
  "sqlstate": "42601",
  "severity": "ERROR",
  "message": "SQL syntax error: unexpected token 'FORM'",
  "position": 9
}

Always use sqlstate for programmatic handling. The message text may change between versions; SQLSTATE codes are stable.

When using the MySQL wire protocol, the error is delivered as a MySQL error packet with the SQLSTATE code in the sql_state field (5 bytes following the # marker).

JSON Error Format

For clients that need structured errors without screen-scraping message strings, AxiomDB supports a JSON error format that carries all ErrorResponse fields in the MySQL ERR packet:

SET error_format = 'json';

After this, every ERR packet carries a JSON string instead of plain text:

{"code":1064,"sqlstate":"42601","severity":"ERROR","message":"SQL syntax error: unexpected token 'FORM'","position":9}

{"code":1062,"sqlstate":"23505","severity":"ERROR","message":"unique key violation on index 'users_email_idx'","detail":"Key (value)=(alice@example.com) is already present in index users_email_idx."}

Reset to plain text with SET error_format = 'text'. This setting is per-connection and does not persist after disconnect.

Integrity Constraint Violations (Class 23)

These errors indicate that an INSERT, UPDATE, or DELETE violated a declared constraint. The application should handle them and return a user-facing message.

23505 — unique_violation

A row with the same value already exists in a column or set of columns declared UNIQUE or PRIMARY KEY.

CREATE TABLE users (email TEXT NOT NULL UNIQUE);
INSERT INTO users VALUES ('alice@example.com');
INSERT INTO users VALUES ('alice@example.com');  -- ERROR 23505

The error message identifies both the index and the offending value:

Duplicate entry 'alice@example.com' for key 'users_email_uq'

The detail field (available in JSON format) provides a PostgreSQL-style message:

Key (value)=(alice@example.com) is already present in index users_email_uq.

Typical application response: Show “An account with this email already exists.”

try:
    db.execute("INSERT INTO users (email) VALUES (?)", [email])
except AxiomDbError as e:
    if e.sqlstate == '23505':
        return {"error": "Email already taken"}
    raise

23503 — foreign_key_violation

Child insert / update — parent key does not exist

An INSERT or UPDATE references a value in the FK column that has no matching row in the parent table.

INSERT INTO orders (user_id, total) VALUES (99999, 100);
-- ERROR 23503: Foreign key constraint fails: 'orders.user_id' = '99999'

Typical response: Validate that the referenced entity exists before inserting, or surface “Referenced record not found.”

Parent delete — children still reference it (RESTRICT / NO ACTION)

A DELETE on the parent table was blocked because child rows reference the row being deleted and the FK action is RESTRICT or NO ACTION (the default).

-- orders.user_id REFERENCES users(id) ON DELETE RESTRICT
DELETE FROM users WHERE id = 1;
-- ERROR 23503: foreign key constraint "fk_orders_user": orders.user_id references this row

Typical response: Either delete child rows first, use ON DELETE CASCADE, or prevent parent deletion in the application layer.

Cascade depth exceeded

A chain of ON DELETE CASCADE constraints exceeded the maximum depth of 10 levels.

-- If table chain A→B→C→...→K (11 levels all with CASCADE) and you delete from A:
DELETE FROM a WHERE id = 1;
-- ERROR 23503: foreign key cascade depth exceeded limit of 10

Typical response: Restructure the schema to reduce cascade depth, or perform the deletes manually level-by-level.

SET NULL on a NOT NULL column

ON DELETE SET NULL is defined on a foreign key column that was declared NOT NULL.

-- orders.user_id is NOT NULL, but ON DELETE SET NULL is declared
DELETE FROM users WHERE id = 1;
-- ERROR 23503: cannot set FK column orders.user_id to NULL: column is NOT NULL

Typical response: Either remove the NOT NULL constraint from the FK column, or change the action to ON DELETE RESTRICT or ON DELETE CASCADE.

23502 — not_null_violation

An INSERT or UPDATE attempted to store NULL in a NOT NULL column.

INSERT INTO users (name, email) VALUES (NULL, 'bob@example.com');
-- ERROR 23502: null value in column "name" violates not-null constraint

Typical application response: Validate required fields on the client before submitting.

23514 — check_violation

A row failed a CHECK constraint.

INSERT INTO products (name, price) VALUES ('Widget', -5.00);
-- ERROR 23514: new row for relation "products" violates check constraint "chk_price_positive"

Startup / Open Errors

These errors happen before a SQL statement runs. They are returned by Db::open(...), Db::open_dsn(...), AsyncDb::open(...), or server startup, so there is no SQLSTATE-bearing result set yet.

IndexIntegrityFailure — open refused because an index is not trustworthy

On every open, AxiomDB now verifies each catalog-visible index against the heap-visible rows reconstructed after WAL recovery.

• If an index is readable but missing entries or contains extra entries, AxiomDB rebuilds it automatically before accepting traffic.
• If the index tree cannot be traversed safely, open fails with DbError::IndexIntegrityFailure.

Example Rust handling:

#![allow(unused)]
fn main() {
match axiomdb_embedded::Db::open("./data.db") {
    Ok(db) => { /* ready */ }
    Err(axiomdb_core::DbError::IndexIntegrityFailure { table, index, reason }) => {
        eprintln!("database refused to open: {table}.{index}: {reason}");
    }
    Err(other) => return Err(other),
}
}

Cardinality Errors (Class 21)

21000 — cardinality_violation

A scalar subquery returned more than one row. Scalar subqueries (a SELECT used where a single value is expected) must return exactly one row. Zero rows yield NULL; more than one row is an error.

-- Suppose users contains Alice and Bob
SELECT (SELECT name FROM users) AS single_name FROM orders;
-- ERROR 21000: subquery must return exactly one row, but returned 2 rows

Fix: add a WHERE condition that makes the result unique, or use LIMIT 1 if you intentionally want only the first row:

-- Safe: guaranteed single row via primary key
SELECT (SELECT name FROM users WHERE id = o.user_id) AS customer_name
FROM orders o;

-- Safe: explicit LIMIT 1 when you want "any one" result
SELECT (SELECT name FROM users ORDER BY created_at LIMIT 1) AS oldest_user
FROM config;

try:
    db.execute("SELECT (SELECT name FROM users) FROM orders")
except AxiomDbError as e:
    if e.sqlstate == '21000':
        # The subquery returned multiple rows — add a WHERE clause
        ...

Undefined Object Errors (Class 42)

These errors indicate a reference to an object (table, column, index) that does not exist in the catalog. They are typically programming errors caught in development.

42P01 — undefined_table

A statement referenced a table or view that does not exist.

SELECT * FROM nonexistent_table;
-- ERROR 42P01: relation "nonexistent_table" does not exist

42703 — undefined_column

A statement referenced a column that does not exist in the specified table.

SELECT typo_column FROM users;
-- ERROR 42703: column "typo_column" does not exist in table "users"

42P07 — duplicate_table

CREATE TABLE was called for a table that already exists (without IF NOT EXISTS).

CREATE TABLE users (...);
CREATE TABLE users (...);
-- ERROR 42P07: relation "users" already exists

42701 — duplicate_column

ALTER TABLE ... ADD COLUMN was called for a column that already exists in the table.

CREATE TABLE users (id BIGINT PRIMARY KEY, email TEXT NOT NULL);
ALTER TABLE users ADD COLUMN email TEXT;
-- ERROR 42701: column "email" already exists in table "users"

Fix: Use a different column name, or check the current schema with DESCRIBE users before adding the column.

42702 — ambiguous_column

An unqualified column name appears in multiple tables in the FROM clause.

-- Both users and orders have a column named "id"
SELECT id FROM users JOIN orders ON orders.user_id = users.id;
-- ERROR 42702: column reference "id" is ambiguous

-- Fix: qualify the column
SELECT users.id FROM users JOIN orders ON orders.user_id = users.id;

Database Catalog Errors

These errors are surfaced primarily through the MySQL wire protocol when a client uses CREATE DATABASE, DROP DATABASE, USE, the handshake database, or COM_INIT_DB.

1049 (42000) — Unknown database

The requested database does not exist in the persisted catalog.

USE missing_db;
-- ERROR 1049 (42000): Unknown database 'missing_db'

This same error is returned if a client connects with database=missing_db in the initial MySQL handshake.

Fix: create the database first with CREATE DATABASE missing_db, or switch to an existing one from SHOW DATABASES.

1007 (HY000) — Database already exists

CREATE DATABASE was called for a name already present in the catalog.

CREATE DATABASE analytics;
CREATE DATABASE analytics;
-- ERROR 1007 (HY000): Can't create database 'analytics'; database exists

Fix: choose a different name, or treat the existing database as reusable.

1105 (HY000) — Active database cannot be dropped

The current connection attempted to drop the database it has selected.

USE analytics;
DROP DATABASE analytics;
-- ERROR 1105 (HY000): Can't drop database 'analytics'; database is currently selected

Fix: switch to another database such as axiomdb, then run DROP DATABASE.

Transaction Errors (Class 40)

40001 — serialization_failure

A concurrent write conflict was detected. The transaction must be retried.

-- Two transactions try to update the same row simultaneously.
-- The second one receives:
-- ERROR 40001: could not serialize access due to concurrent update

The application must catch this and retry the transaction. This is normal and expected behavior under high concurrency, not a bug.

40P01 — deadlock_detected

Two transactions are each waiting for a lock held by the other.

-- Txn A holds lock on row 1, waiting for row 2
-- Txn B holds lock on row 2, waiting for row 1
-- → AxiomDB detects the cycle and aborts one transaction with 40P01
-- ERROR 40P01: deadlock detected

Prevention: Access rows in a consistent order across all transactions. If you always acquire locks on (accounts with lower id) before (accounts with higher id), deadlocks cannot form between two such transactions.

I/O and System Errors (Class 58)

58030 — io_error

The storage engine encountered an operating system I/O error.

ERROR 58030: could not write to file "axiomdb.db": No space left on device

Possible causes:

• Disk full — free space or expand the volume
• File permissions — ensure the AxiomDB process can write to the data directory
• Hardware error — check dmesg / system logs for disk errors

Syntax and Parse Errors (Class 42)

42601 — syntax_error

The SQL statement is not syntactically valid.

SELECT FORM users;  -- 'FORM' is not a keyword
-- ERROR 42601: syntax error at or near "FORM"
-- Position: 8

42883 — undefined_function

A function name was called that does not exist.

SELECT unknown_function(1);
-- ERROR 42883: function "unknown_function" does not exist

Data Errors (Class 22)

22001 — string_data_right_truncation

A TEXT or VARCHAR value exceeds the column’s declared length.

CREATE TABLE codes (code CHAR(3));
INSERT INTO codes VALUES ('TOOLONG');
-- ERROR 22001: value too long for type CHAR(3)

22003 — numeric_value_out_of_range

A numeric value exceeds the range of its declared type.

INSERT INTO users (age) VALUES (99999);  -- age is SMALLINT
-- ERROR 22003: integer out of range for type SMALLINT

22012 — division_by_zero

Division by zero in an arithmetic expression.

SELECT 10 / 0;
-- ERROR 22012: division by zero

22018 — invalid_character_value_for_cast

A value cannot be implicitly coerced to the target type. This error is raised when AxiomDB is in strict mode (the default) and a conversion is attempted that would discard data or is not defined.

-- Text with non-numeric characters inserted into an INT column (strict mode):
INSERT INTO users (age) VALUES ('42abc');
-- ERROR 22018: cannot coerce '42abc' (Text) to INT: '42abc' is not a valid integer

-- A type pair with no implicit conversion:
SELECT 3.14 + DATE '2026-01-01';
-- ERROR 22018: cannot coerce 3.14 (Real) to Date: no implicit numeric promotion between these types

Hint: Use explicit CAST for conversions that AxiomDB does not apply automatically:

INSERT INTO users (age) VALUES (CAST('42' AS INT));   -- explicit — always works
SELECT CAST(3 AS REAL) + 1.5;                         -- explicit widening

Permissive mode: if your application requires MySQL-style lenient coercion ('42abc' silently converted to 42), disable strict mode for the session:

SET strict_mode = OFF;   -- or: SET sql_mode = ''

In permissive mode, failed coercions fall back to a best-effort conversion and emit warning 1265 instead of returning 22018. Use SHOW WARNINGS after bulk loads to audit any truncated values. See Strict Mode for full details.

Implicit coercions that always succeed (no error)

The following conversions happen automatically without raising 22018:

Connection Protocol Errors (Class 08)

MySQL 1153 / 08S01 — ER_NET_PACKET_TOO_LARGE

Returned when an incoming MySQL logical command payload exceeds the connection’s current max_allowed_packet limit.

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

What triggers it:

• A COM_QUERY whose SQL text exceeds @@max_allowed_packet bytes.
• A COM_STMT_PREPARE or COM_STMT_EXECUTE packet above the limit.
• A HandshakeResponse41 above the default 64 MiB limit (rare in practice).
• A multi-packet logical command whose total reassembled payload exceeds the limit, even if each individual physical fragment is below the limit.

What happens after the error: The server closes the connection immediately. The stream cannot be safely reused because the framing layer cannot determine where the next command begins.

Fix: Raise max_allowed_packet before sending the large command:

SET max_allowed_packet = 134217728;  -- 128 MiB

Or reconnect after the error — the new connection starts with the server default.

Disk-Full Errors (Class 53)

53100 — disk_full

Returned when the OS reports that the volume is full (ENOSPC) or over quota (EDQUOT) during a durable write — a WAL append, WAL fsync, storage grow, or mmap flush.

ERROR 53100: disk full during 'wal commit fsync': no space left on device
HINT: The database volume is full or over quota. Free disk space and restart
      the server to restore write access. The database is now in read-only
      degraded mode.

What happens after the error:

AxiomDB enters read-only degraded mode immediately. In this mode:

The mode persists until the server process is restarted. There is no way to return to read-write mode without restarting.

Fix:

1. Free disk space or remove the quota restriction.
2. Restart the server — AxiomDB will reopen in read-write mode if space is available.

Complete SQLSTATE Reference

Performance

AxiomDB is designed to outperform MySQL on specific workloads by eliminating several layers of redundant work: double-buffering, the double-write buffer, row-by-row query evaluation, and thread-per-connection overhead. This page presents current benchmark numbers and guidance on how to write queries and schemas that stay fast.

Benchmark Results

All benchmarks run on Apple M2 Pro (12 cores), 32 GB RAM, NVMe SSD, single-threaded, warm data (all pages in OS page cache unless noted).

SQL Parser Throughput

Compared to sqlparser-rs (the common Rust SQL parser library):

The speed advantage comes from two decisions:

1. logos DFA lexer — compiles the token patterns to a Deterministic Finite Automaton at compile time. Token scanning is O(n) with a very small constant.
2. Zero-copy tokens — Ident and QuotedIdent tokens are &'src str slices into the original input. No heap allocation occurs during lexing.

Storage Engine Throughput

Wire Protocol Throughput (Phase 5.14)

End-to-end throughput measured via the MySQL wire protocol (pymysql client, autocommit mode, 1 connection, localhost). Includes: network round-trip, protocol encode/decode, parse, analyze, execute, WAL, MmapStorage.

The 185 q/s SELECT result reflects a 3.3× improvement in Phase 5.14 over the prior 56 q/s baseline. Read-only transactions (SELECT, SHOW, etc.) no longer fsync the WAL — see Benchmarks → Phase 5.14 for the technical explanation.

Remaining bottlenecks:

• INSERT (single connection): one fdatasync per autocommit statement; enable Group Commit for concurrent workloads (see below)

Primary-Key Lookups After 6.16

Phase 6.16 removes the planner blind spot that still treated WHERE id = ... as a scan on PK-only tables. The PRIMARY KEY B+Tree is now used for single-table equality and range lookups.

Measured with python3 benches/comparison/local_bench.py --scenario select_pk --rows 5000 --table on the same machine:

The old debt was “planner never reaches the PK B+Tree”. That is now closed. The remaining gap is smaller and sits after planning: row materialization and MySQL packet serialization still cost more than MariaDB/MySQL on this path.

DELETE WHERE / UPDATE After 5.20

Phase 5.19 removed the old-key delete bottleneck for DELETE ... WHERE and the old-key half of UPDATE. Phase 5.20 finishes the real UPDATE fix for the benchmark schema by preserving the heap RecordId when the new row fits in the same slot, which makes selective index skipping correct.

Measured with python3 benches/comparison/local_bench.py --scenario all --rows 50000 --table on the same machine:

Compared to the 4.6K rows/s pre-5.19 DELETE-WHERE baseline that originally triggered this work, AxiomDB now stays in the same order of magnitude as MySQL and MariaDB on the same local benchmark. More importantly, compared to the 52.9K rows/s post-5.19 / pre-5.20 UPDATE baseline, the stable-RID path raises AxiomDB UPDATE throughput to 648K rows/s on the same 50K-row benchmark.

The main remaining write-path bottleneck is now INSERT, not UPDATE.

Indexed UPDATE ... WHERE After 6.20

Phase 6.17 removed the old full-scan candidate discovery path for indexed UPDATE predicates. Phase 6.20 then removed the dominant apply-side costs on the default PK-range benchmark: candidate heap reads are batched by page, no-op rows skip physical mutation, stable-RID rewrites batch their WAL append, and index maintenance only runs when a key, predicate membership, or RID really changes.

Measured with python3 benches/comparison/local_bench.py --scenario update_range --rows 5000 --table on the same machine:

Compared to the 6.17 result (85.2K rows/s), the 6.20 apply fast path is a 4.3x improvement on the same benchmark and now exceeds the documented local MySQL result. The remaining gap is specifically MariaDB’s tighter clustered-row update path, not AxiomDB’s old discovery-side O(n) scan.

INSERT in Explicit Transactions After 5.21

Phase 5.21 adds transactional INSERT staging for consecutive INSERT ... VALUES statements inside one explicit transaction. Instead of writing heap + WAL + index roots per statement, AxiomDB now buffers eligible rows and flushes them together on COMMIT or the next barrier statement.

Measured with python3 benches/comparison/local_bench.py --scenario insert --rows 50000 --table on the same machine:

This path targets one specific workload: many separate INSERT statements inside BEGIN ... COMMIT. Autocommit throughput remains a different problem and depends on the server-side fsync path.

Multi-row INSERT on Indexed Tables After 6.18

Phase 6.18 fixes the immediate multi-row VALUES path for indexed tables. A statement such as:

INSERT INTO bench_users VALUES
  (1, 'u1', 18, TRUE, 100.0, 'u1@b.local'),
  (2, 'u2', 19, FALSE, 100.1, 'u2@b.local'),
  (3, 'u3', 20, TRUE, 100.2, 'u3@b.local');

now uses grouped heap/index apply even when the target table has a PRIMARY KEY or secondary indexes. Before 6.18, that path still fell back to per-row maintenance on indexed tables.

Measured with python3 benches/comparison/local_bench.py --scenario insert_multi_values --rows 5000 --table on the benchmark schema with PRIMARY KEY (id):

Prepared Statement Plan Cache (Phase 5.13)

COM_STMT_PREPARE compiles the SQL once (parse + analyze). Every subsequent COM_STMT_EXECUTE reuses the compiled plan — no re-parsing, no catalog scan:

Schema invalidation (correctness guarantee): after ALTER TABLE, DROP TABLE, CREATE INDEX, etc., the cached plan is re-analyzed automatically on the next execute. The schema_version counter in Database increments on every successful DDL; each connection polls it lock-free (Arc<AtomicU64>) before each execute.

LRU eviction: each connection caches up to max_prepared_stmts_per_connection (default 1024) compiled plans. The least-recently-used plan is evicted silently when the limit is reached. Configurable in axiomdb.toml.

WAL Fsync Pipeline (6.19, closed with a documented gap)

Phase 6.19 replaced the old timer-based CommitCoordinator with an always-on leader-based WAL fsync pipeline. The runtime behavior changed, but the key single-connection autocommit benchmark remains a documented gap.

Measured with:

python3 benches/comparison/local_bench.py --scenario insert_autocommit --rows 1000 --table --engines axiomdb

Current result:

End-to-End INSERT Throughput

Full pipeline: parse → analyze → execute → WAL → MmapStorage. Measured with executor_e2e benchmark (MmapStorage + real WAL, release build, Apple M2 Pro NVMe).

How to achieve this throughput in your application:

-- Fast: one SQL string with N value rows (211K rows/s)
INSERT INTO orders (user_id, amount) VALUES
  (1, 49.99), (2, 12.50), (3, 99.00), -- ... up to thousands of rows
  (1000, 7.99);

-- Slower: N separate INSERT strings (35K rows/s — parse+analyze per row)
INSERT INTO orders VALUES (1, 49.99);
INSERT INTO orders VALUES (2, 12.50);
-- ...

The difference between the two approaches is 6× in throughput. The bottleneck in the per-string case is parse + analyze overhead per SQL string (~20 µs/string), not the storage write.

Four-Engine Native Benchmark (2026-03-24)

All four engines measured locally on Apple M2 Pro, same machine, no Docker overhead, 10,000-row table (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), value INT). Each engine was given equivalent hardware resources.

Engines tested:

• MariaDB 12.1 — port 3306
• MySQL 8.0 — port 3310
• PostgreSQL 16 — port 5433
• AxiomDB — port 3309

INSERT batch — 2× faster than MariaDB

AxiomDB reaches 36K r/s vs MariaDB’s 18K r/s (2× faster) and MySQL’s 16K r/s (2.25× faster). The gap comes from the same three optimizations described above: HeapChain::insert_batch() (O(P) page writes), record_insert_batch() (O(1) WAL write), and a single parse+analyze pass for all N rows.

SELECT * — on par with MySQL, 11× behind PostgreSQL

AxiomDB SELECT (212K r/s) is marginally faster than MySQL 8.0 (189K r/s) and on par with the full-pipeline expectation. PostgreSQL’s 2.3M r/s reflects its shared buffer pool: after the first scan, all 10K rows fit in PostgreSQL’s hot in-memory buffer and subsequent queries never touch disk. AxiomDB’s mmap approach relies on the OS page cache for the same effect — the gap closes when pages are hot, but PostgreSQL’s buffer pool gives it an edge on repeated same-connection scans because it bypasses the OS cache layer entirely.

DELETE (no WHERE) — 3× faster than MariaDB, 40× faster than MySQL

AxiomDB deletes 10,000 rows in 9.6 ms (1M r/s). MariaDB takes 31 ms; MySQL 8.0 takes 407 ms. The AxiomDB advantage comes from two optimizations working together:

1. WalEntry::Truncate — a single 51-byte WAL entry replaces 10,000 per-row Delete entries. MySQL InnoDB writes one undo log record per row before marking it deleted — for 10K rows this is 10K undo writes plus 10K page modifications.
2. HeapChain::delete_batch() — groups deletions by page, reads each page once, marks all slots dead, writes back once. 10K rows across 50 pages = 100 page operations instead of 30,000.

Row Codec Throughput

Row encoding is fast because:

• The codec iterates values once with a fixed dispatch per type.
• The null bitmap is written as bytes with bit shifts — no per-column branch on NULL.
• Variable-length types (Text, Bytes) use a 3-byte length prefix that avoids the 4-byte overhead of a full u32.

Why AxiomDB Is Fast — Architecture Reasons

1. No Double-Buffering

MySQL InnoDB maintains its own Buffer Pool in addition to the OS page cache. The same data lives in RAM twice.

MySQL:   Disk → OS page cache → InnoDB Buffer Pool → Query
                (copy 1)            (copy 2)

AxiomDB: Disk → OS page cache → Query
                (mmap — single copy)

AxiomDB uses mmap to map the .db file directly. The OS page cache IS the buffer. When a page is hot, it is served from L2/L3 cache with zero copies.

2. No Double-Write Buffer

MySQL writes each 16 KB page to a special “doublewrite buffer” area on disk before writing it to its actual location. This prevents torn-page corruption but costs two disk writes per page.

AxiomDB uses a WAL + per-page CRC32c checksum. The WAL record is small (tens of bytes for the changed key-value pair). On recovery, AxiomDB replays the WAL to reconstruct any page that has a checksum mismatch. No doublewrite buffer needed.

3. Lock-Free Concurrent Reads

The Copy-on-Write B+ Tree uses an AtomicU64 to store the root page ID. Readers load the root pointer with Acquire semantics and traverse the tree without acquiring any lock. Writers swap the root pointer with Release semantics after finishing the copy chain.

A running SELECT does not stall any INSERT or UPDATE. Both proceed in parallel.

4. Async I/O with Tokio

The server mode uses Tokio async I/O. 1,000 concurrent connections run on approximately 8 OS threads. MySQL’s thread-per-connection model requires 1,000 OS threads for 1,000 connections, consuming ~8 GB in stack space alone.

Performance Budget

The following table defines the minimum acceptable performance for each critical operation. Benchmarks that fall below the “acceptable maximum” column are treated as blockers before any phase is closed.

Index Usage Guide

Rules of Thumb

1. Every foreign key column needs an index — AxiomDB does not auto-index FK columns. Without an index, every FK check during DELETE/UPDATE scans the child table linearly.

2. Put the most selective column first in composite indexes — A query filtering WHERE user_id = 42 AND status = 'paid' benefits most from (user_id, status) if user_id is more selective (fewer distinct values match).

3. Covering indexes eliminate heap lookups — If all columns in a SELECT are in the index, AxiomDB returns results directly from the index without touching heap pages.

4. Partial indexes reduce size — CREATE INDEX ... WHERE deleted_at IS NULL indexes only active rows. If 90% of rows are soft-deleted, the partial index is 10× smaller than a full index.

5. BIGINT AUTO_INCREMENT beats UUID v4 for PK — UUID v4 inserts at random positions in the B+ Tree, causing ~40% more page splits than sequential integers. Use UUID v7 if you need UUIDs (time-sortable prefix).

Query Patterns to Avoid

Unindexed range scans on large tables

-- Slow: scans every row in orders (no index on placed_at)
SELECT * FROM orders WHERE placed_at > '2026-01-01';

-- Fix: create the index
CREATE INDEX idx_orders_date ON orders (placed_at);

Leading wildcard LIKE

-- Slow: cannot use index on 'name' (leading %)
SELECT * FROM users WHERE name LIKE '%smith%';

-- Better: full-text search index (planned Phase 8)
-- Acceptable workaround for small tables: use LOWER() + LIKE on indexed column

SELECT * with wide rows

-- Fetches all columns including large TEXT blobs for every row
SELECT * FROM documents WHERE category_id = 5;

-- Better: select only what the UI needs
SELECT id, title, created_at FROM documents WHERE category_id = 5;

NOT IN with nullable subquery

-- Returns 0 rows if the subquery contains a single NULL
SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM banned_users);

-- Fix: filter NULLs explicitly
SELECT * FROM orders WHERE user_id NOT IN (
    SELECT id FROM banned_users WHERE id IS NOT NULL
);

Measuring Performance

EXPLAIN (planned)

EXPLAIN SELECT * FROM orders WHERE user_id = 42 ORDER BY placed_at DESC;

Running the Built-in Benchmarks

# B+ Tree benchmarks
cargo bench --bench btree -p axiomdb-index

# Storage engine benchmarks
cargo bench --bench storage -p axiomdb-storage

# Compare before/after an optimization
cargo bench -- --save-baseline before
# ... make change ...
cargo bench -- --baseline before

Benchmarks use Criterion.rs and report mean, standard deviation, and throughput in a format compatible with critcmp for historical comparison.

Optimization Results — All-Visible Flag + Prefetch (2026-03-24)

Two storage-level optimizations implemented on branch research/pg-internals-comparison, inspired by PostgreSQL internals analysis:

All-Visible Page Flag (optim-A)

After the first sequential scan on a stable table (all rows committed, none deleted), AxiomDB sets bit 0 of PageHeader.flags. Subsequent scans skip per-slot MVCC visibility tracking for those pages — 1 flag check per page instead of N per-slot comparisons.

Impact on DELETE: scan_rids_visible() (used before batch delete) goes faster because most pages are all-visible after INSERT → COMMIT. Measured improvement on 10K-row DELETE: 10ms → 7ms (+30%).

Sequential Scan Prefetch Hint (optim-C)

MmapStorage now calls madvise(MADV_SEQUENTIAL) before every sequential heap scan. The OS kernel begins async read-ahead for following pages, overlapping I/O with processing of the current page.

Impact: Measurable on cold-cache workloads (pages not in OS page cache). No regression on warm cache.

Benchmark after both optimizations (wire protocol, Apple M2 Pro)

Architecture Overview

AxiomDB is organized as a Cargo workspace of purpose-built crates. Each crate has a single responsibility and depends only on crates below it in the stack. The layering prevents circular dependencies and makes each component independently testable.

Layer Diagram

┌─────────────────────────────────────────────────────────────────────┐
│                          ENTRY POINTS                               │
│                                                                     │
│  axiomdb-server        axiomdb-embedded                             │
│  (TCP daemon,          (Rust API + C FFI,                           │
│   MySQL wire protocol)  in-process library)                         │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────────────┐
│                        NETWORK LAYER                                │
│                                                                     │
│  axiomdb-network                                                    │
│  └── mysql/                                                         │
│      ├── codec.rs    (MySqlCodec — 4-byte packet framing)           │
│      ├── packets.rs  (HandshakeV10, HandshakeResponse41, OK, ERR)   │
│      ├── auth.rs     (mysql_native_password SHA1 + caching_sha2_password)│
│      ├── charset.rs  (charset/collation registry, encode_text/decode_text)│
│      ├── session.rs  (ConnectionState — typed charset fields,       │
│      │               prepared stmt cache, pending long data)        │
│      ├── handler.rs  (handle_connection — async task per TCP conn)  │
│      ├── result.rs   (QueryResult → result-set packets, charset-aware)│
│      ├── error.rs    (DbError → MySQL error code + SQLSTATE)        │
│      └── database.rs (Arc<RwLock<Database>> wrapper)                │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────────────┐
│                       QUERY PIPELINE                                │
│                                                                     │
│  axiomdb-sql                                                        │
│  ├── lexer     (logos DFA, zero-copy tokens)                        │
│  ├── parser    (recursive descent, LL(1)/LL(2))                     │
│  ├── ast       (Stmt, Expr, SelectStmt, InsertStmt, ...)            │
│  ├── analyzer  (BindContext, col_idx resolution, catalog lookup)    │
│  ├── eval      (expression evaluator, three-valued NULL logic,      │
│  │              CASE WHEN searched + simple form, short-circuit)    │
│  ├── result    (QueryResult, ColumnMeta, Row — executor return type)│
│  ├── table     (TableEngine — heap DML; clustered guard rails today)│
│  ├── index_integrity (startup index-vs-heap verifier; skips clustered)│
│  └── executor/ (mod.rs facade + select/insert/update/delete/ddl/   │
│                 join/aggregate/shared modules; same execute() API; │
│                 GROUP BY + HAVING + ORDER BY + LIMIT/OFFSET +      │
│                 INSERT … SELECT)                                   │
│                                                                     │
│  [query planner, optimizer — Phase 6]                               │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────────────┐
│                    TRANSACTION LAYER                                │
│                                                                     │
│  axiomdb-mvcc          (TxnManager, snapshot isolation, SSI)        │
│  axiomdb-wal           (WalWriter, WalReader, crash recovery)       │
│  axiomdb-catalog       (CatalogBootstrap, CatalogReader, schema)    │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────────────┐
│                     INDEX LAYER                                     │
│                                                                     │
│  axiomdb-index         (BTree CoW, RangeIter, prefix compression)   │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────────────┐
│                    STORAGE LAYER                                    │
│                                                                     │
│  axiomdb-storage       (StorageEngine trait, MmapStorage,           │
│                         MemoryStorage, FreeList, heap pages)        │
└──────────────────────────────┬──────────────────────────────────────┘
                               │
┌──────────────────────────────▼──────────────────────────────────────┐
│                     TYPE FOUNDATION                                 │
│                                                                     │
│  axiomdb-types         (Value, DataType, row codec)                 │
│  axiomdb-core          (DbError, RecordId, TransactionSnapshot,     │
│                         PageId, LsnId, common types)               │
└─────────────────────────────────────────────────────────────────────┘
                               │
                    ┌──────────▼────────┐
                    │   axiomdb.db      │  ← mmap pages (16 KB each)
                    │   axiomdb.wal     │  ← WAL append-only log
                    └───────────────────┘

Crate Responsibilities

axiomdb-core

The dependency-free foundation. Contains:

• DbError — the single error enum used by all other crates, using thiserror
• dsn — shared DSN parser and typed normalized output:
  • ParsedDsn
  • WireEndpointDsn
  • LocalPathDsn
• RecordId — physical location of a row: (page_id: u64, slot_id: u16), 10 bytes
• TransactionSnapshot — snapshot ID and visibility predicate for MVCC
• PageId, LsnId — type aliases that document intent

No crate in the workspace depends on a crate above axiomdb-core.

axiomdb-types

SQL value representation and binary serialization:

• Value — the in-memory enum (Null, Bool, Int, BigInt, Real, Decimal, Text, Bytes, Date, Timestamp, Uuid)
• DataType — schema descriptor for a column’s type (mirrors axiomdb-core::DataType but with full type system including parameterized types)
• encode_row / decode_row — binary codec from &[Value] to &[u8] and back
• encoded_len — O(n) size computation without allocation

axiomdb-storage

The raw page I/O layer:

• StorageEngine trait — read_page, write_page, alloc_page, free_page, flush
• MmapStorage — maps the .db file with memmap2; pages are directly accessible as &Page references into the mapped region
• MemoryStorage — Vec<Page> in RAM for tests and in-memory databases
• FreeList — bitmap tracking free pages; scans left-to-right for the first free bit
• Page — 16 KB struct with 64-byte header (magic, type, checksum, page_id, LSN, free_start, free_end) and 16,320-byte body
• Heap page format — slotted page with null bitmap and tuples growing from the end toward the beginning
• Same-slot tuple rewrite helpers — used by the stable-RID UPDATE path to overwrite a row in place when the new encoded row still fits inside the existing slot

axiomdb-index

The Copy-on-Write B+ Tree:

• BTree — the public tree type; wraps a StorageEngine and an AtomicU64 root
• RangeIter — lazy iterator for range scans; traverses the tree to cross leaf boundaries
• InternalNodePage / LeafNodePage — #[repr(C)] structs with bytemuck::Pod for zero-copy serialization
• prefix module — CompressedNode for in-memory prefix compression of internal keys

axiomdb-wal

Append-only Write-Ahead Log:

• WalWriter — appends WalEntry records with CRC32c checksums; manages file header
• WalReader — stateless; opens a file handle per scan; supports both forward and backward iteration (backward scan uses entry_len_2 at the tail of each record)
• WalEntry — binary-serializable record with LSN, txn_id, entry type, table_id, key, old_value, new_value, and checksum
• EntryType::UpdateInPlace — stable-RID same-slot UPDATE record used by rollback and crash recovery to restore the old tuple image at the same (page_id, slot_id)
• Crash recovery state machine — CRASHED → RECOVERING → REPLAYING_WAL → VERIFYING → READY

axiomdb-catalog

Schema persistence and lookup:

• CatalogBootstrap — creates the three system tables (axiom_tables, axiom_columns, axiom_indexes) in the meta page on first open
• CatalogReader — reads schema from the system tables for use by the analyzer and executor; uses a TransactionSnapshot for MVCC-consistent reads
• Schema types: TableDef, ColumnDef, IndexDef
• TableDef now carries root_page_id plus TableStorageLayout::{Heap, Clustered}
• CatalogWriter::create_table_with_layout(...) allocates either a heap or clustered table root

axiomdb-mvcc

Transaction management and snapshot isolation:

• TxnManager — assigns transaction IDs, tracks active transactions, assigns snapshots on BEGIN
• RowHeader — embedded in each heap row: (xmin, xmax, deleted) for visibility
• MVCC visibility function — determines whether a row version is visible to a snapshot

axiomdb-sql

The SQL processing pipeline:

• lexer — logos-based DFA; ~85 tokens; zero-copy &'src str identifiers
• ast — all statement types: SelectStmt, InsertStmt, UpdateStmt, DeleteStmt, CreateTableStmt, CreateIndexStmt, DropTableStmt, DropIndexStmt, AlterTableStmt
• expr — Expr enum for the expression tree: BinaryOp, UnaryOp, Column, Literal, IsNull, Between, Like, In, Case, Function, Param { idx: usize } (positional ? placeholder resolved at execute time)
• parser — recursive descent; expression sub-parser with full operator precedence; parses GROUP BY, HAVING, ORDER BY with NULLS FIRST/LAST, LIMIT/OFFSET, SELECT DISTINCT, INSERT … SELECT, and both forms of CASE WHEN
• analyzer — BindContext / BoundTable; resolves col_idx for JOINs
• eval/ — directory module rooted at eval/mod.rs; exports the same evaluator API as before, but splits internals into context.rs (collation and subquery runners), core.rs (recursive Expr evaluation), ops.rs (comparisons, boolean logic, IN, LIKE), and functions/ (scalar built-ins by family)
• result — QueryResult enum (Rows / Affected / Empty), ColumnMeta (name, data_type, nullable, table_name), Row = Vec<Value>; the contract between the executor and all callers (embedded API, wire protocol, CLI)
• index_integrity — startup-time verification that compares every catalog-visible index against heap-visible rows after WAL recovery and rebuilds readable divergent indexes before open returns; clustered tables are currently skipped because their PRIMARY KEY metadata reuses the clustered root
• executor/ — directory module rooted at executor/mod.rs; the facade still exports execute, execute_with_ctx, and last_insert_id_value, but the implementation is now split into shared.rs, select.rs, joins.rs, aggregate.rs, insert.rs, update.rs, delete.rs, bulk_empty.rs, ddl.rs, and staging.rs. Capabilities remain the same: GROUP BY with hash-based aggregation (COUNT(*), COUNT(col), SUM, MIN, MAX, AVG with proper NULL exclusion), HAVING post-filter, ORDER BY with multi-column sort keys and per-column NULLS FIRST/LAST control, LIMIT n OFFSET m for pagination, SELECT DISTINCT with NULL-equality dedup (two NULL values are considered equal for deduplication), and INSERT … SELECT for bulk copy and aggregate materialization
• clustered tables now enter the catalog through CREATE TABLE ... PRIMARY KEY ...
• 39.14 adds a dedicated clustered INSERT branch in executor/insert.rs
• 39.15 adds a dedicated clustered SELECT branch in executor/select.rs
• 39.16 adds a dedicated clustered UPDATE branch in executor/update.rs
• 39.17 adds a dedicated clustered DELETE branch in executor/delete.rs
• 39.18 adds clustered VACUUM maintenance in axiomdb-sql/src/vacuum.rs
• 39.19 adds legacy heap→clustered rebuild in executor/ddl.rs
• Stable-RID UPDATE fast path — same-slot heap rewrite that preserves RecordId when the new encoded row fits and makes untouched-index skipping safe
• UPDATE apply fast path — indexed UPDATE now batches candidate heap reads, filters no-op rows before heap mutation, batches UpdateInPlace WAL append, and groups per-index delete+insert/root persistence on the remaining rows
• Transactional INSERT staging — explicit transactions can buffer consecutive INSERT ... VALUES rows in SessionContext, then flush them through one grouped heap/index pass at the next barrier statement or COMMIT
• Indexed multi-row INSERT batch path — the immediate INSERT ... VALUES (...), (... ) path now reuses the same grouped physical apply helpers as staged flushes even when the table has PRIMARY KEY or secondary indexes; the immediate path keeps strict same-statement UNIQUE checking and therefore does not reuse the staged committed_empty shortcut
• clustered INSERT branch — explicit-PK tables now bypass heap staging entirely, derive PK bytes from clustered primary-index metadata, write directly through clustered_tree, maintain clustered secondary bookmarks, and make rollback delete undo keys from the current catalog root instead of trusting stale pre-split roots
• clustered rebuild branch — legacy heap+PRIMARY KEY tables now rebuild into a fresh clustered root, rebuild secondaries as PK-bookmark indexes, flush those new roots, then swap catalog metadata and defer old-page free until commit

axiomdb-network

The MySQL wire protocol implementation. Lives in crates/axiomdb-network/src/mysql/:

Connection lifecycle

TCP accept
  │
  ▼  (seq 0)
Server → HandshakeV10
  │       20-byte random challenge, capabilities, server version
  │       auth_plugin_name = "caching_sha2_password"
  │
  ▼  (seq 1)
Client → HandshakeResponse41
  │       username, auth_response (SHA1-XOR token or caching_sha2 token),
  │       capabilities, auth_plugin_name
  │
  ▼  (seq 2)  — two paths depending on the plugin negotiated:
  │
  │  mysql_native_password path:
  │  └── Server → OK  (permissive mode: username in allowlist → accepted)
  │
  │  caching_sha2_password path (MySQL 8.0+ default):
  │  ├── Server → AuthMoreData(0x03)  ← fast_auth_success indicator
  │  ├── Client → empty ack packet    ← pymysql sends this automatically
  │  └── Server → OK
  │
  ▼  COMMAND LOOP
  │
  ├── COM_QUERY (0x03)        → parse SQL → intercept? → execute → result packets
  ├── COM_PING  (0x0e)        → OK
  ├── COM_INIT_DB (0x02)      → updates current_database in ConnectionState + OK
  ├── COM_RESET_CONNECTION (0x1f) → resets ConnectionState, preserves transport lifecycle metadata + OK
  ├── COM_STMT_PREPARE (0x16) → parse SQL with ? placeholders → stmt_ok packet
  ├── COM_STMT_SEND_LONG_DATA (0x18) → append raw bytes to stmt-local buffers, no reply
  ├── COM_STMT_EXECUTE (0x17) → merge long data + decode params → substitute → execute → result packets
  ├── COM_STMT_RESET (0x1a)   → clear stmt-local long-data state → OK
  ├── COM_STMT_CLOSE (0x19)   → remove from cache, no response
  └── COM_QUIT  (0x01)        → close

Explicit lifecycle state machine (5.11c)

5.11c moved transport/runtime concerns out of ConnectionState into mysql/lifecycle.rs. ConnectionState still owns SQL session variables, prepared statements, warnings, and session counters. ConnectionLifecycle owns only:

• current transport phase
• client capability flags relevant to lifecycle policy
• timeout policy per phase
• socket-level configuration (TCP_NODELAY, SO_KEEPALIVE)

COM_RESET_CONNECTION recreates ConnectionState::new() and resets session timeout variables to their defaults, but it does not recreate ConnectionLifecycle. That means the connection remains interactive or non-interactive according to the original handshake, even after reset.

Prepared statements (prepared.rs)

Prepared statements allow a client to send SQL once and execute it many times with different parameters, avoiding repeated parsing and enabling binary parameter encoding that is more efficient than string escaping.

Protocol flow:

Client → COM_STMT_PREPARE  (SQL with ? placeholders)
  │
Server reads the SQL, counts ? placeholders, assigns a stmt_id.
  │
Server → Statement OK packet
  │       stmt_id: u32
  │       num_columns: u16  (columns in the result set, or 0 for DML)
  │       num_params:  u16  (number of ? placeholders)
  │       followed by num_params parameter-definition packets + EOF
  │       followed by num_columns column-definition packets + EOF
  │
Client → COM_STMT_SEND_LONG_DATA (optional, repeatable)
  │       stmt_id: u32
  │       param_id: u16
  │       raw chunk bytes
  │
Server appends raw bytes to stmt-local state, sends no response.
  │
Client → COM_STMT_EXECUTE
  │       stmt_id: u32
  │       flags: u8  (0 = CURSOR_TYPE_NO_CURSOR)
  │       iteration_count: u32  (always 1)
  │       null_bitmap: ceil(num_params / 8) bytes  (one bit per param)
  │       new_params_bound_flag: u8  (1 = type list follows)
  │       param_types: [u8; num_params * 2]  (type byte + unsigned flag)
  │       param_values: binary-encoded values for non-NULL params
  │
Server → result set packets  (same text-protocol format as COM_QUERY)
  │
Client → COM_STMT_CLOSE (stmt_id)   — no response expected

Binary parameter decoding (decode_binary_value):

Each parameter is decoded according to its MySQL type byte:

NULL parameters are identified by the null-bitmap before the type list is read; they produce Value::Null without consuming any bytes from the value region.

Long-data buffering (COM_STMT_SEND_LONG_DATA):

PreparedStatement owns stmt-local pending buffers:

#![allow(unused)]
fn main() {
pub struct PreparedStatement {
    // ...
    pub pending_long_data: Vec<Option<Vec<u8>>>,
    pub pending_long_data_error: Option<String>,
}
}

Rules:

• chunks are appended as raw bytes in handler.rs
• COM_STMT_SEND_LONG_DATA never takes the Database mutex
• the next COM_STMT_EXECUTE consumes pending long data before inline values
• long data wins over both the inline execute payload and the null bitmap
• state is cleared immediately after every execute attempt
• COM_STMT_RESET clears only this long-data state, not the cached plan

AxiomDB follows MariaDB’s COM_STMT_SEND_LONG_DATA model here: accumulate raw bytes per placeholder and decode them only at execute time. That keeps chunked multibyte text correct without dragging the command through the engine path.

Parameter substitution — AST-level plan cache (substitute_params_in_ast):

COM_STMT_PREPARE runs parse + analyze once and stores the resulting Stmt in PreparedStatement.analyzed_stmt. On each COM_STMT_EXECUTE, substitute_params_in_ast walks the cached AST and replaces every Expr::Param { idx } node with Expr::Literal(params[idx]) in a single O(n) tree walk (~1 µs), then calls execute_stmt() directly — bypassing parse and analyze entirely.

The ? token is recognized by the lexer as Token::Question and emitted by the parser as Expr::Param { idx: N } (0-based position). The semantic analyzer passes Expr::Param through unchanged because the type is not yet known; type resolution happens at execute time once the binary-encoded parameter values are decoded from the COM_STMT_EXECUTE packet.

value_to_sql_literal converts each decoded Value to the appropriate Expr::Literal variant:

• Value::Null → Expr::Literal(Value::Null)
• Value::Int / BigInt / Real → numeric literal node
• Value::Text → text literal node (single-quote escaping preserved at the protocol boundary, not needed in the AST)
• Value::Date / Timestamp → date/timestamp literal node

ConnectionState — per-connection session state:

#![allow(unused)]
fn main() {
pub struct ConnectionState {
    pub current_database: String,
    pub autocommit: bool,
    // Typed charset state — negotiated at handshake, updated by SET NAMES
    client_charset: &'static CharsetDef,
    connection_collation: &'static CollationDef,
    results_collation: &'static CollationDef,
    pub variables: HashMap<String, String>,
    pub prepared_statements: HashMap<u32, PreparedStatement>,
    pub next_stmt_id: u32,
}
}

The three charset fields are typed references into the static charset.rs registry. from_handshake_collation_id(id: u8) initializes all three from the collation id the client sends in the HandshakeResponse41 packet. Unsupported ids are rejected before auth with ERR 1115 (ER_UNKNOWN_CHARACTER_SET). SET NAMES <charset> updates all three; individual SET character_set_client = … updates only the relevant field.

decode_client_text(&[u8]) -> Result<String, DbError> decodes inbound SQL/identifiers. encode_result_text(&str) -> Result<Vec<u8>, DbError> encodes outbound text columns. Both are non-lossy — they return DbError::InvalidValue rather than replacement characters.

#![allow(unused)]
fn main() {
pub struct PreparedStatement {
    pub stmt_id: u32,
    pub sql_template: String,            // original SQL with ? placeholders
    pub param_count: u16,
    pub analyzed_stmt: Option<Stmt>,     // cached parse+analyze result (plan cache)
    pub compiled_at_version: u64,        // global schema_version at compile time
    pub deps: PlanDeps,                  // per-table OID dependencies (Phase 40.2)
    pub generation: u32,                 // incremented on each re-analysis
    pub last_used_seq: u64,
    pub pending_long_data: Vec<Option<Vec<u8>>>,
    pub pending_long_data_error: Option<String>,
}
}

analyzed_stmt is populated by COM_STMT_PREPARE after parse + analyze succeed. On COM_STMT_EXECUTE, if analyzed_stmt is Some, the handler calls substitute_params_in_ast on the cached Stmt and invokes execute_stmt() directly, skipping the parse and analyze steps entirely. If analyzed_stmt is None (should not occur in normal operation), the handler falls back to the full parse + analyze path.

OID-based staleness check (Phase 40.2):

COM_STMT_EXECUTE uses a two-level check:

1. Fast (O(1) atomic compare): if compiled_at_version == current_global_schema_version, no DDL has occurred since compile → skip catalog scan entirely (zero I/O).
2. Slow (O(t) catalog reads, t = tables in deps): only when the global version has advanced. PlanDeps::is_stale() reads each table’s current schema_version from the catalog heap and compares to the cached snapshot. If all match → the DDL was on a different table → stamp the new global version and skip re-analysis.

This avoids re-analyzing prepared statements when CREATE INDEX ON other_table runs — only statements that actually reference the DDL-modified table are re-compiled. PostgreSQL uses the same approach via RelationOids in CachedPlanSource.

Each connection maintains its own HashMap<u32, PreparedStatement>. Statement IDs are assigned by incrementing next_stmt_id (starting at 1) and are local to the connection — the same ID on two connections refers to two different statements. COM_STMT_CLOSE removes the entry; subsequent COM_STMT_EXECUTE calls for the closed ID return an Unknown prepared statement error. COM_STMT_RESET leaves the entry in place and clears only the stmt-local long-data buffers plus any deferred long-data error.

Packet framing and size enforcement (codec.rs — subphase 5.4a)

Every MySQL message in both directions — client to server and server to client — uses the same 4-byte envelope:

[payload_length: u24 LE] [sequence_id: u8] [payload: payload_length bytes]

MySqlCodec implements tokio_util::codec::{Decoder, Encoder}. It holds a configurable max_payload_len (default 64 MiB) that matches the session variable @@max_allowed_packet.

Two-phase decoder algorithm:

1. Scan phase — walk physical packet headers without consuming bytes, accumulating total_payload. If total_payload > max_payload_len, return MySqlCodecError::PacketTooLarge { actual, max } before any buffer allocation. If any fragment is missing, return Ok(None) (backpressure).
2. Consume phase — advance the buffer and return (seq_id, Bytes). For a single physical fragment this is a zero-copy split_to into the existing BytesMut. For multi-fragment logical packets one contiguous BytesMut is allocated with capacity = total_payload to avoid per-fragment copies.

Multi-packet reassembly. MySQL splits commands larger than 16,777,215 bytes (0xFF_FFFF) across multiple physical packets. A fragment with payload_length = 0xFF_FFFF signals continuation; the final fragment has payload_length < 0xFF_FFFF. The limit applies to the reassembled logical payload, not to each individual fragment.

Live per-connection limit. handle_connection calls reader.decoder_mut().set_max_payload_len(n):

• After auth (from conn_state.max_allowed_packet_bytes())
• After a valid SET max_allowed_packet = N
• After COM_RESET_CONNECTION (restores DEFAULT_MAX_ALLOWED_PACKET)

Oversize behavior. On PacketTooLarge, the handler sends MySQL ERR 1153 / SQLSTATE 08S01 (“Got a packet bigger than ‘max_allowed_packet’ bytes”) and breaks the connection loop. The stream is never re-used — re-synchronisation after an oversize packet is unsafe.

Result set serialization (result.rs — subphase 5.5a)

AxiomDB has two result serializers sharing the same column_count + column_defs + EOF framing but differing in row encoding:

Both paths produce the same packet sequence shape:

column_count   (lenenc integer)
column_def_1   (lenenc strings: catalog, schema, table, org_table, name, org_name
                + 12-byte fixed section: charset, display_len, type_byte, flags, decimals)
…
column_def_N
EOF
row_1
…
row_M
EOF

Binary row packet layout:

0x00                      row header (always)
null_bitmap[ceil((N+2)/8)]  MySQL offset-2 null bitmap: column i → bit (i+2)
value_0 ... value_k         non-null values in column order (no per-cell headers)

The null bitmap uses MySQL’s prepared-row offset of 2 — bits 0 and 1 are reserved. Column 0 → bit 2, column 1 → bit 3, and so on.

Binary cell encoding per type:

Column type codes (shared between both serializers):

COM_QUERY OID-based plan cache (plan_cache.rs — Phase 40.2)

Repeated ad-hoc queries like SELECT * FROM users WHERE id = 42 arrive with different literal values on each call. The plan cache normalizes literals to ? placeholders, hashes the result, and caches the fully analyzed AST. Subsequent queries with the same structure (e.g., id = 99) skip parse + analyze (~5 µs) and reuse the cached Stmt.

Entry structure (CachedPlanSource):

#![allow(unused)]
fn main() {
struct CachedPlanSource {
    stmt: Stmt,                             // fully analyzed AST
    deps: PlanDeps,                         // (table_id, schema_version) per referenced table
    param_count: usize,                     // expected literal count for structural match
    generation: u32,                        // incremented on each re-store after stale eviction
    exec_count: u64,                        // lifetime hit counter
    last_used_seq: u64,                     // LRU clock value
    last_validated_global_version: u64,     // fast pre-check stamp
}
}

Two-level staleness check:

1. Fast (O(1)): if global_schema_version == last_validated_global_version, no DDL has occurred since last validation → cache hit with zero catalog I/O.
2. Slow (O(t) catalog reads): called only when the global version advanced. PlanDeps::is_stale() reads each table’s current schema_version from the catalog heap and compares to the cached snapshot. If any dep mismatches → evict. If all match → stamp the new global version (future lookups hit the fast path again).

Belt-and-suspenders invalidation:

• Lazy (primary): is_stale() at lookup time catches cross-connection DDL.
• Eager (secondary): invalidate_table(table_id) called immediately after same-connection DDL removes all entries whose deps include table_id. DDL functions in executor/ddl.rs also call bump_table_schema_version(table_id) via CatalogWriter so the per-table counter advances regardless of which connection holds the plan.

OID dependency extraction (plan_deps.rs):

extract_table_deps(stmt, catalog_reader, database) walks the analyzed Stmt and resolves every table reference to its (TableId, schema_version) at compile time:

• SELECT — FROM, JOINs, scalar subqueries in WHERE/HAVING/columns/ORDER BY/GROUP BY
• INSERT … SELECT — target table + all tables in the SELECT
• UPDATE, DELETE — target table + subqueries in WHERE
• EXPLAIN — recursive into the wrapped statement
• DDL statements — return empty PlanDeps (never cached)

LRU eviction: when max_entries (512) is reached, the entry with the lowest last_used_seq is evicted. O(n) scan over ≤512 entries — called only on capacity overflow, never on the hot lookup path.

ORM query interception (handler.rs)

MySQL drivers and ORMs send several queries automatically before any user SQL: SET NAMES, SET autocommit, SELECT @@version, SELECT @@version_comment, SELECT DATABASE(), SELECT @@sql_mode, SELECT @@lower_case_table_names, SELECT @@max_allowed_packet, SHOW WARNINGS, SHOW DATABASES.

intercept_special_query matches these by prefix/content and returns pre-built packet sequences without touching the engine. Without this interception, most clients fail to connect because they receive ERR packets for mandatory queries.

ON_ERROR session behavior (executor.rs, database.rs, subphase 5.2c)

ON_ERROR is implemented as one typed session enum shared by both layers that own statement execution:

This split is required by the current AxiomDB architecture. handler.rs intercepts SET and SELECT @@var before the engine, but database.rs owns the full parse -> analyze -> execute_with_ctx pipeline. A wire-only flag would leave embedded execution inconsistent; an executor-only flag would make the MySQL session variables lie.

Execution modes:

ignore reuses the existing SHOW WARNINGS path. For ignorable SQL/user errors, database.rs maps the original DbError to the corresponding MySQL warning code/message and returns QueryResult::Empty, which the serializer turns into an OK packet with warning_count > 0. For non-ignorable errors (DiskFull, WAL failures, storage/runtime corruption), the error still surfaces as ERR and the transaction is eagerly rolled back if one is active.

SHOW STATUS — server and session counters (status.rs, subphase 5.9c)

MySQL clients, ORMs, and monitoring tools (PMM, Datadog MySQL integration, ProxySQL) call SHOW STATUS on connect or periodically to query server health. Returning an error or empty result breaks these integrations.

Counter architecture:

Two independent counter stores keep telemetry decoupled from correctness:

Database owns an Arc<StatusRegistry>. Each handle_connection task clones the Arc once at connect time — the same pattern used by schema_version. The SHOW STATUS intercept never acquires the Database mutex; it reads directly from the cloned Arc<StatusRegistry> and the local SessionStatus. This means the query cannot block other connections.

RAII guards:

#![allow(unused)]
fn main() {
// Increments threads_connected +1 after auth; drops −1 on disconnect (even on error).
let _connected_guard = ConnectedGuard::new(Arc::clone(&status));

// Increments threads_running +1 for the duration of COM_QUERY / COM_STMT_EXECUTE.
let _running = RunningGuard::new(&status);
}

threads_connected and threads_running are always accurate with no manual bookkeeping because Rust’s drop guarantees run on early returns and panics.

Counters tracked:

SHOW STATUS syntax:

All four MySQL-compatible forms are intercepted before hitting the engine:

SHOW STATUS
SHOW SESSION STATUS
SHOW LOCAL STATUS
SHOW GLOBAL STATUS
-- Any of the above with LIKE filter:
SHOW STATUS LIKE 'Com_%'
SHOW GLOBAL STATUS LIKE 'Threads%'

LIKE filtering reuses like_match from axiomdb-sql (proper % / _ wildcard semantics, case-insensitive against variable names). Results are always returned in ascending alphabetical order.

DB lock strategy

The MySQL handler stores the opened engine in Arc<tokio::sync::RwLock<Database>>.

• read-only statements acquire db.read()
• mutating statements and transaction control acquire db.write()
• multiple reads run concurrently
• all writes are still serialized at whole-database granularity

This is the current runtime model. It is more advanced than the old Phase 5 Mutex<Database> design because read-only queries can now overlap, but it is still below MySQL/InnoDB and PostgreSQL for write concurrency because row-level locking is not implemented yet.

caching_sha2_password (MySQL 8.0+)

MySQL 8.0 changed the default authentication plugin from mysql_native_password to caching_sha2_password. When a client using the new default (e.g., PyMySQL ≥ 1.0, MySQL Connector/Python, mysql2 for Ruby) connects, the server must complete a 5-packet handshake instead of the 3-packet one:

The critical implementation detail is that the ack packet at seq=3 must be read before sending OK. If the server sends OK at seq=2 instead, the client has already queued the empty ack packet. The server then reads that empty packet as a COM_QUERY command (command byte 0x00 = COM_SLEEP, or simply an unknown command), which causes the connection to close silently — no error is reported to the application.

axiomdb-server

Entry point for server mode. Parses CLI flags (--data-dir, --port), opens the axiomdb-network::Database, starts a Tokio TCP listener, and spawns one handle_connection task per accepted connection, passing each task a clone of the Arc<RwLock<Database>>.

axiomdb-embedded

Entry point for embedded mode. Exposes:

• A safe Rust API (Database::open, Database::execute, Database::transaction)
• A C FFI (axiomdb_open, axiomdb_execute, axiomdb_close, axiomdb_free_string)

Query Lifecycle — From Wire to Storage

1. TCP bytes arrive on the socket
   │
2. axiomdb-network::mysql::codec::MySqlCodec decodes the 4-byte header
   → (sequence_id, payload)
   │
3. handler.rs inspects payload[0] (command byte)
   ├── 0x01 COM_QUIT  → close
   ├── 0x02 COM_INIT_DB → OK
   ├── 0x0e COM_PING  → OK
   ├── 0x16 COM_STMT_PREPARE → parse + analyze → store in PreparedStatement.analyzed_stmt → stmt_ok
   ├── 0x17 COM_STMT_EXECUTE → substitute_params_in_ast(cached_stmt, params) → execute_stmt() ↓ (step 9)
   └── 0x03 COM_QUERY → continue ↓
   │
4. intercept_special_query(sql) — ORM/driver stubs
   ├── match → return pre-built packet sequence  (no engine call)
   └── no match → continue ↓
   │
5. db.lock() → execute_query(sql, &mut session)
   │
6. axiomdb-sql::tokenize(sql)
   → Vec<SpannedToken>  (logos DFA, zero-copy)
   │
7. axiomdb-sql::parse(tokens)
   → Stmt  (recursive descent; all col_idx = placeholder 0)
   │
8. axiomdb-sql::analyze(stmt, storage, snapshot)
   → Stmt  (col_idx resolved against catalog; names validated)
   │
9. Executor interprets the analyzed Stmt
   → reads from axiomdb-index (BTree lookups / range scans)
   → calls axiomdb-types::decode_row on heap page bytes
   → builds Vec<Vec<Value>> result rows
   │
10. WAL write (for INSERT / UPDATE / DELETE)
    → axiomdb-wal::WalWriter::append(WalEntry)
    │
11. Heap page write (for INSERT / UPDATE / DELETE)
    → axiomdb-storage::StorageEngine::write_page
    │
12. db.lock() released
    │
13. result::serialize_query_result(QueryResult, seq=1)
    → column_count + column_defs + EOF + rows + EOF  (Rows)
    → OK packet with affected_rows + last_insert_id  (Affected)
    │
14. MySqlCodec encodes each packet with 4-byte header → TCP send

For embedded mode, steps 1–4 and 12–14 are replaced by a direct Rust function call that returns a QueryResult struct.

Key Architectural Decisions

mmap over a custom buffer pool

AxiomDB maps the .db file with mmap. The OS page cache manages eviction (LRU) and readahead automatically. InnoDB maintains a separate buffer pool on top of the OS page cache, causing the same data to live in RAM twice. mmap eliminates the second copy.

Trade-off: we give up fine-grained control over eviction policy. The OS uses LRU, which is good for most database workloads. Custom eviction (e.g., clock-sweep with hot/cold separation) will be optional in a future phase.

Copy-on-Write B+ Tree

CoW means a write operation never modifies an existing page in place. Instead, it creates new pages for every node on the path from root to the modified leaf, then atomically swaps the root pointer. Readers who loaded the old root before the swap continue accessing a fully consistent old version with no locking.

Trade-off: writes amplify — modifying one leaf requires copying O(log n) pages. For a tree of depth 4 (enough for hundreds of millions of rows), this is 4 page copies per write. At 16 KB per page, that is 64 KB of write amplification per key insert.

WAL without double-write

The WAL records logical changes (key, old_value, new_value) rather than full page images. Each WAL record has a CRC32c checksum. On recovery, AxiomDB reads the WAL forward, identifies committed transactions, and replays their mutations. Pages with incorrect checksums are rebuilt from WAL records.

This eliminates MySQL’s doublewrite buffer (which writes each page twice to protect against torn writes) at the cost of a slightly more complex recovery algorithm.

logos for lexing, not nom

logos generates a compiled DFA from the token patterns at build time. The generated lexer runs in O(n) time with a fixed, small constant (typically 1–3 CPU instructions per byte). nom builds parser combinators at runtime with dynamic dispatch overhead. For a lexer processing millions of SQL statements per second, the constant factor matters: logos achieves 9–17× throughput over sqlparser-rs’s nom-based lexer.

Storage Engine

The storage engine is the lowest user-accessible layer in AxiomDB. It manages raw 16-kilobyte pages on disk or in memory, provides a freelist for page allocation, and exposes a simple trait that all higher layers depend on.

The StorageEngine Trait

#![allow(unused)]
fn main() {
pub trait StorageEngine: Send + Sync {
    fn read_page(&self, page_id: u64) -> Result<PageRef, DbError>;
    fn write_page(&self, page_id: u64, page: &Page) -> Result<(), DbError>;
    fn alloc_page(&self, page_type: PageType) -> Result<u64, DbError>;
    fn free_page(&self, page_id: u64) -> Result<(), DbError>;
    fn flush(&self) -> Result<(), DbError>;
    fn page_count(&self) -> u64;
    fn prefetch_hint(&self, start_page_id: u64, count: u64) { ... }
    fn set_current_snapshot(&self, snapshot_id: u64) { ... }
    fn deferred_free_count(&self) -> usize { ... }
}
}

All methods take &self — there is no &mut self anywhere in the trait. Mutable state is managed entirely through interior mutability:

• write_page: acquires a per-page exclusive RwLock (from PageLockTable) for the duration of the pwrite(2) call. Two transactions writing different pages proceed in full parallelism with zero contention.
• alloc_page: acquires Mutex<FreeList> only during the bitmap scan (microseconds), then acquires the page lock to initialise the new page.
• free_page: acquires Mutex<FreeList> briefly to add the page to the free bitmap.
• flush: acquires Mutex<FreeList> to persist the freelist, then calls fdatasync.