# SQL support

inillucent reads and runs SQLite's dialect of SQL on its own storage. This page lists the SQL that
runs, the places where the answer differs from SQLite's, and the statements inillucent refuses by
name.

The comparison is against a pinned build of SQLite 3.53.4. [Feature comparison](feature-comparison.md)
has every probed case, table by table. [Pragmas](pragmas.md) lists every pragma.

## Terms used on this page

| Term | Meaning |
|---|---|
| [pragma](glossary.md) | a SQLite statement that reads or changes a setting of the database, such as `PRAGMA page_size` |
| [collation](glossary.md) | a rule for comparing and sorting text, such as `NOCASE` |
| [affinity](glossary.md) | the type a column prefers, applied to a value when it is written |
| [rowid](glossary.md) | the integer key of a row in an ordinary table |
| [storage class](glossary.md) | the type a single stored value has: `NULL`, `INTEGER`, `REAL`, `TEXT` or `BLOB` |
| [WAL](glossary.md) | write ahead log: changes are written to a log first and copied into the file later |
| [HNSW](glossary.md) | the graph index inillucent uses for nearest neighbor vector search |
| probe | a script of SQL run through `inillucent-shell` and through the pinned `sqlite3`, with the two outputs compared byte by byte |
| exit code 3 | the code the command line returns for a construct the engine has not built. Over a binding or MCP the status is `unsupported` |

## How much matches SQLite

The feature probe runs 416 SQL scripts through `inillucent-shell` and through the pinned `sqlite3`.
Each script gets its own fresh database. The probe compares every byte of standard output and
standard error.

```mermaid
pie showData
    title The 416 probed cases
    "Same bytes as SQLite" : 402
    "A different answer" : 6
    "Vector search, no SQLite answer" : 6
    "Accepted here, refused by the pinned SQLite" : 2
```

| Result | Cases | What it means |
|---|---|---|
| Same bytes as SQLite | 402 | 402 of 416 probed cases produce SQLite's exact output. That is 96.6% of all cases and 98.5% of the 408 cases SQLite can answer |
| A different answer | 6 | both engines answer, and the output differs. Each one is listed [below](#the-fourteen-cases-that-differ) |
| Only inillucent answers | 6 | vector search. SQLite has no vector search, so there is no SQLite output to compare |
| Accepted here, refused by the pinned SQLite | 2 | `DELETE` and `UPDATE` with `ORDER BY ... LIMIT` |
| Refused here, answered by SQLite | 0 | no probed case |

The probe is `tools/feature-probe/`. Many of its cases are also checked in as tests in
`crates/inillucent-compat/tests/differential/semantics.rs`. A change to one of those answers, in either
direction, fails the build.

The 416 cases are a list somebody wrote. Some constructs are outside that list, and inillucent
refuses 12 of them by name. They are listed in [What is refused](#what-is-refused).

### Counted against SQLite's own lists

A second tool, `node tools/feature-probe/registers.js`, asks SQLite for its own lists of functions,
pragmas, modules, collations and dot commands, and asks inillucent for the same lists.

| List | SQLite 3.53.4 | inillucent | What is missing here |
|---|---|---|---|
| Pragmas in `pragma_list` | 67 | 67 | none |
| Function names in the library | 177 | 172 of the 177, plus 18 vector functions | `fts3_tokenizer`, `fts5`, `fts5_get_locale`, `fts5_insttoken`, `fts5_locale` |
| Modules for `CREATE VIRTUAL TABLE` | 19 | 16 of the 19, plus 5 of its own: `chunk_text`, `inillucent_search`, `ivfflat`, `json_each` and `json_tree` | `fts3tokenize`, `fts4aux`, `pragma_module_list` |
| Collations | 5 | 5 | none |
| Shell dot commands | 65 | 63 | `.expert`, `.session` |

The register holds the 71 pragmas this engine recognises. That is SQLite's 67 plus `defensive`,
`encryption`, `key` and `rekey`. SQLite sets `defensive` through `sqlite3_db_config`, and
inillucent also answers it as a pragma. The other three are for [encryption at rest](encryption.md).
`tools/doc-facts/check.mjs` fails when a page names a different count.

### Where the function counts come from

`PRAGMA function_list` in SQLite's own shell reports 218 names. 41 of them are extensions the
`sqlite3` shell program adds, such as `readfile`, `writefile`, `sha3`, `zipfile`, `ieee754`, the
`decimal` functions and the `shell_*` helpers. An application that links the SQLite library does
not get them. That leaves 177 library names.

inillucent answers 172 of those 177. The five it does not have are FTS5 and FTS3 internals:

- `fts5` and `fts3_tokenizer` hand a C pointer to the caller.
- `fts5_locale`, `fts5_get_locale` and `fts5_insttoken` belong to FTS5's locale support, which
  inillucent does not have.

inillucent's register holds 190 built in function names: the 172, plus 18 vector functions SQLite
does not have. `inillucent functions` prints 213 rows, because it prints one row for each name and
argument count.

```sh
inillucent functions --output json
node tools/feature-probe/registers.js    # compares both registers name by name
```

`inillucent functions` also lists what the connection registered: functions an application added
through `create_scalar_function` or `create_aggregate_function`, and `embed(TEXT)` in a build with
the `embed` feature. Those rows have `builtin = 0`. The release build checked for this page has no
`embed` feature, so `embed('hello')` returns exit code 3.

A build with the `embed` feature registers three functions that run a model:

| Function | What it returns |
|---|---|
| `embed(TEXT)` | the text's embedding, 768 numbers as 3,072 bytes |
| `embed_tokens(TEXT)` | how many tokens the embedding model sees in the text, before its 1,900 token limit is applied. `SELECT id FROM chunk WHERE embed_tokens('search_document: ' || body) > 1900` finds the rows `embed()` would cut |
| `rerank(QUESTION, PASSAGE)` | how relevant the passage is to the question, from 0 to 1, from a reranker. It scores one pair per call, so a search table that names `question` is faster for many rows |

All three return `NULL` for a `NULL` argument, and all three are `direct_only`. A build without the
`embed` feature answers each with the status `unsupported` and exit code 3. See
[Embeddings](embeddings.md) and [Vector search](vector-search.md).

### Where a registered function may be called

A function an application registers carries two flags, `direct_only` and `innocuous`. The flags
decide whether the schema may call the function. The schema means a `DEFAULT`, a `CHECK`, a
generated column, an index expression, the `WHERE` of a partial index, a view or a trigger.

| Flags on the function | Called from a statement | Called from the schema |
|---|---|---|
| `direct_only` (the default for an application's function) | yes | refused with `may only be used from top-level SQL` |
| `innocuous` | yes | yes |
| neither | yes | only while `PRAGMA trusted_schema` is on |
| a built in function | yes | yes |

`PRAGMA trusted_schema` is off by default in this build, as it is in the pinned SQLite. The refusal
comes when the statement that reads the schema is bound. For an index expression the refusal comes
at `CREATE INDEX`, because that is when inillucent evaluates the expression.

`embed(TEXT)` is `direct_only`. `embed(TEXT)` loads a 275 MB model, so a `CHECK` that named it would
load the model on every insert.

## What runs

Every row in this table ran through the probe with the same output as SQLite, unless the row says
otherwise.

| Area | What runs |
|---|---|
| Queries | `SELECT` with `WHERE`, `GROUP BY`, `HAVING`, `DISTINCT`, `ORDER BY` with `NULLS FIRST` and `NULLS LAST`, `LIMIT` and `OFFSET`. `VALUES` as a statement and in `FROM` |
| Joins | inner, `LEFT`, `RIGHT` and `FULL OUTER`, `CROSS`, `NATURAL`, `USING`, self joins. The planner picks a hash join, an index nested loop or a scan |
| Compound queries | `UNION`, `UNION ALL`, `EXCEPT`, `INTERSECT` |
| Subqueries | in `WHERE`, `IN`, `EXISTS`, as a value, and as a table in `FROM`, correlated or not. Row values in comparisons and in `IN`, with a value list or a subquery, such as `(a, b) IN (SELECT x, y FROM s)`. Recursive CTEs with `ORDER BY`, `LIMIT` and `OFFSET` |
| Common table expressions | `WITH`, `WITH RECURSIVE`, `MATERIALIZED` and `NOT MATERIALIZED`, and `WITH` on `INSERT`, `UPDATE` and `DELETE`. A recursive CTE produces rows while the query above it reads them, so `WHERE n > 3 LIMIT 2` over a recursion with no end stops after two matching rows. A CTE that reads itself anywhere but the `FROM` clause of its last arm fails with `circular reference`, and two reads of itself in one arm fail with `multiple references to recursive table` or `multiple recursive references`, as in SQLite |
| Window functions | all eleven window functions and every aggregate function, `json_group_array`, `json_group_object`, `median` and `percentile` included, `PARTITION BY`, `ROWS`, `RANGE` and `GROUPS` frames, every `EXCLUDE` clause, `FILTER`, and named `WINDOW` clauses |
| Writes | `INSERT`, `UPDATE`, `DELETE` and `REPLACE`, every `OR` conflict clause, `RETURNING`, `UPDATE ... FROM`, and `ON CONFLICT ... DO UPDATE` and `DO NOTHING`, with a target that is a column list, a partial index (the `WHERE` must match the index) or an expression such as `ON CONFLICT(lower(name))`. `DO UPDATE` runs the `CHECK`, `NOT NULL` and `STRICT` checks and fires the table's `UPDATE` triggers |
| Tables | `CREATE TABLE`, `CREATE TABLE ... AS SELECT`, `WITHOUT ROWID`, `STRICT`, `VIRTUAL` and `STORED` generated columns, `AUTOINCREMENT`. A generated column has its declared affinity and collation, takes `STRICT`, `NOT NULL`, `UNIQUE` and `ON CONFLICT`, and can be a foreign key parent or child |
| Indexes | unique, descending, partial, on an expression, with `COLLATE`, on a `WITHOUT ROWID` table. `REINDEX`, `INDEXED BY`, and `ANALYZE`, which writes `sqlite_stat1` in each database it measures, `ANALYZE aux` included |
| Views and triggers | `CREATE VIEW`, which stores the body and reports an error in it when the view is read. `CREATE TRIGGER` with `BEFORE`, `AFTER` and `INSTEAD OF`, `UPDATE OF`, `WHEN`, `RAISE` with a message that is any expression, and recursive triggers. A `RETURNING` clause may also call `RAISE(ABORT, ...)`, `RAISE(FAIL, ...)` and `RAISE(ROLLBACK, ...)`, which fail the statement with the message. `UPDATE` and `DELETE` on a view run its `INSTEAD OF UPDATE` and `INSTEAD OF DELETE` triggers, and the triggers are still there after the file is closed and opened again |
| `ALTER TABLE` | `RENAME TO`, `RENAME COLUMN`, `ADD COLUMN` (which writes no row when the default is a literal), `DROP COLUMN`, and the SQLite 3.53 forms `ALTER COLUMN c SET NOT NULL`, `ALTER COLUMN c DROP NOT NULL`, `ADD CONSTRAINT n CHECK (...)` and `DROP CONSTRAINT`. `RENAME` rewrites `REFERENCES` in other tables, view and trigger bodies, `UPDATE OF` lists, upsert targets and indexes, and fails with SQLite's `error in view v` message when a dependent view or trigger would break. `DROP COLUMN` follows SQLite's rules and is refused when a view reads the column. `PRAGMA legacy_alter_table` changes `RENAME` as in SQLite. `RENAME TO` on a virtual table such as an FTS5 table also renames its shadow tables, and renaming a table or a column carries a vector index built over it. On a connection with `PRAGMA defensive` on, which the shell sets, a shadow table cannot be dropped, altered or given a trigger |
| Constraints | `NOT NULL`, `UNIQUE`, `PRIMARY KEY`, `CHECK`, `DEFAULT`, and foreign keys with all five actions, immediate or deferred, and `PRAGMA foreign_key_check`. A foreign key compares with the parent column's collation, and a row may reference itself |
| Values | type affinity on write, `CAST`, the `BINARY`, `NOCASE` and `RTRIM` collations, `LIKE`, `GLOB`, values larger than a page, in a key or an index as well. A key or an indexed value is limited to 1,048,576 bytes once encoded, see [the key limit](#smaller-differences) |
| Functions | 190 built in function names, including 30 JSON functions, the maths functions and the date and time functions. Functions, aggregates and collations an application defines |
| Transactions | `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `RELEASE`, `ROLLBACK TO`. A `ROLLBACK` also undoes `CREATE` and `DROP TABLE` |
| Several databases | `ATTACH` and `DETACH`, where the file name may be a parameter or any other expression, joins across files, and one transaction that commits to two files or to neither. Temporary tables, views and triggers |
| Schema and maintenance | `sqlite_schema` and `sqlite_master`, `VACUUM`, `VACUUM INTO`, `integrity_check` and `quick_check` |
| Plans | `EXPLAIN QUERY PLAN` as SQLite's tree, with attached schema names, automatic index lines for a join and for a correlated subquery, the loop SQLite turns a positive `EXISTS` into, `LEFT-JOIN` markers, `CO-ROUTINE` and `MATERIALIZE` nodes for derived tables and CTEs, `COMPOUND QUERY` and `MERGE` nodes for compound queries, and `SCALAR SUBQUERY` and `LIST SUBQUERY` nodes. A derived table is flattened into the query that reads it where SQLite flattens it. Some plans still differ, see [below](#how-inillucent-behaves-differently-from-sqlite). Plain `EXPLAIN` runs and prints a different program, see [below](#five-follow-from-how-inillucent-is-built) |
| Table valued functions | `generate_series`, `chunk_text(text, size, overlap, heading)`, which cuts a document into windows of characters for embedding, `json_each`, `json_tree`, a `pragma_*` function for every pragma that returns a value, such as `pragma_table_info('t')`, `pragma_foreign_key_check` and `pragma_user_version`, which can take its argument from an earlier `FROM` term, as in `FROM sqlite_master m, pragma_index_list(m.name)`, and any module an application registers. A schema name in front of the function, as in `main.generate_series(1, 3)`, is accepted |

### Rules a reader asks about

**`HAVING` with no `GROUP BY`.** A query that has an aggregate in its result columns is one group
over the whole table. `SELECT count(*) AS n FROM t HAVING n > 0` filters that one group. A query
with no `GROUP BY` and no aggregate is refused with `HAVING clause on a non-aggregate query`, which
is SQLite's message.

**Joins and subqueries follow SQLite's rules for what is evaluated and when.**

- A `WHERE` term over a compound subquery is pushed into each arm, as SQLite does.
- A `RIGHT` or `FULL` join after an inner join keeps its unmatched rows. Parenthesised joins work.
  A subquery may appear in a join's `ON`, in `RETURNING`, and in the `SET` and `WHERE` of an upsert.
- `INSERT ... VALUES ... UNION ALL VALUES` inserts every row. `LEFT JOIN json_each(NULL)` keeps the
  left row.
- `AND`, `OR`, `coalesce`, `ifnull` and `iif` skip the operands SQLite skips, so an operand that
  would fail is not evaluated. The select list of an `EXISTS` subquery is never evaluated.
- Rows produced before a run time error are returned before the error.
- A CTE that calls a volatile function and is used twice is evaluated once. A subquery that calls
  `random()` is evaluated for every row.
- A subquery with several columns works as a row value. Anywhere a single value is needed it fails
  with `sub-select returns N columns - expected 1`.
- An aggregate whose arguments read only the outer query belongs to the outer query when it is used
  inside a subquery. In `SELECT (SELECT max(t.a)) FROM t` the `max` runs over the rows of `t`.
- A view in `main` does not read a temporary table that shadows its base table.
- `INDEXED BY` may name a partial index where SQLite accepts it.
- `RETURNING` is evaluated right after each row is written, before `AFTER` triggers run.
- A column of a derived table, a CTE or a view that is a compound query takes its affinity from the
  first arm whose expression has one, as SQLite does. `SELECT 1 AS o UNION ALL SELECT x FROM r` over a
  `REAL` column `x` makes `o` a `REAL` column, so the 1 reads as 1.0. An arm that can hold text next
  to a numeric column, or a first arm that is a `CAST`, leaves the integers as they are.

**Constraints and conflicts.**

- A row that breaks two `UNIQUE` or `PRIMARY KEY` constraints of one table reports the one declared
  last. This holds for the primary key of a `WITHOUT ROWID` table too: with `x UNIQUE, y PRIMARY
  KEY` it reports `t.y`, and with `y PRIMARY KEY, x UNIQUE` it reports `t.x`.
- A `UNIQUE` constraint on the same columns as the primary key of a `WITHOUT ROWID` table is the
  same index, as in SQLite, and `PRAGMA index_list` shows one row for it.
- A `STRICT` datatype failure inside a transaction leaves the rows an `INSERT OR FAIL`,
  `OR IGNORE`, `OR REPLACE`, `UPDATE OR FAIL`, `OR IGNORE` or `OR REPLACE` wrote before the failing
  row. Outside a transaction the whole statement is undone.
- A `VIRTUAL` generated column is computed on every `INSERT` and `UPDATE`. A write is refused when
  the expression fails, for example `abs(-9223372036854775808)` with `integer overflow`.
- `GLOB`, `REGEXP` and `MATCH` take no `ESCAPE`. `a GLOB b ESCAPE c` fails with `wrong number of
  arguments to function GLOB()`. An aggregate used as a window function keeps its argument count, so
  `sum() OVER ()` fails the same way.

**How a `WHERE` is searched.**

- An `OR` whose every arm can use the rowid or an index is answered with one search for each arm,
  and each row is read once, as SQLite's multi index `OR` does. `WHERE key = ?1 OR id = ?1` over
  100,000 rows reads a few rows instead of the table. `EXPLAIN QUERY PLAN` prints `MULTI-INDEX OR`.
  An `OR` with one arm that needs a scan scans the table.
- `x IN (...)` and `x NOT IN (...)` over eight or more values, written out or returned by a
  subquery, are answered by a binary search of the sorted values instead of a comparison with each
  one. It keeps SQLite's `NULL` rules: a value that matches nothing is `NULL` when the list holds a
  `NULL`, so `NOT IN` keeps no row in that case.

**Messages and ordering.** `ORDER BY` or `LIMIT` before a compound operator is an error with
SQLite's wording. A circular CTE fails with `circular reference: name`. `GROUP BY` takes the direction
of an `ORDER BY` with the same number of terms, which decides the order of tied groups. `CREATE TABLE
... AS` stores the query's values and does not apply the new column's affinity. `MATCH` on a plain
column fails for each row it is evaluated on. An unknown function's error keeps the name as it was
written. `NULLS FIRST` and `NULLS LAST` on an index key are refused, as in SQLite.

**JSON and dates.** JSON5 input accepts Unicode whitespace, and an unpaired surrogate escape is read
as SQLite reads it. A blob that is not JSONB is read as JSON text. `json_each` and `json_tree` give
the same `id` values as SQLite when called with a path. A date function accepts a blob as the format or
as a modifier, `subsec` as the time value, and `timediff` works in both directions.

**A correlated `IN` subquery.** inillucent answers `a IN (SELECT ...)` with a correlated subquery by
rewriting it as `EXISTS`. The rewrite keeps SQLite's `NULL` rules:

| Left side | Subquery rows | Result |
|---|---|---|
| any value, including `NULL` | none | false |
| `NULL` | one or more | `NULL` |
| a value with no match | a list that holds a `NULL` | `NULL` |

A correlated `IN` subquery that uses `GROUP BY`, `LIMIT` or a compound query is refused with exit
code 3. The rewrite moves the equality into the subquery's `WHERE`, and a `WHERE` runs before
grouping and before a limit, so the answer would be wrong.

## Extensions

| Extension | What is there |
|---|---|
| JSON | a binary storage form, and all 30 function names, `json_*` and `jsonb_*` |
| FTS5 | full text search with `MATCH`, `t('query')` and `t = 'query'`, `bm25()`, `highlight()`, `snippet()`, the `rank` setting, external content and contentless tables with the `delete` command, and `fts5vocab`. An external content table reads its rows from the named table or view, so `content='v'` works when `v` is a view and `content_rowid` names the view's rowid column. The tokenizers are `ascii`, `unicode61` and `porter` |
| FTS3 and FTS4 | the `fts3` and `fts4` modules with `matchinfo()` and `offsets()`. The query language is SQLite's default one: a leading `-` excludes a term, `OR` and `NEAR` or `NEAR/n` in capitals are operators, and `AND` and `NOT` are ordinary words. `OR` binds tighter than two words side by side, a `-` term applies to the whole query, and parentheses group nothing, as in SQLite. `fts4aux` and `fts3tokenize` are missing |
| R-Tree | `rtree`, `rtree_i32` and `geopoly` |
| Others | `dbstat`, `sqlite_dbpage`, `bytecode`, `tables_used`, `completion`, `zipfile`, `fsdir` |
| `inillucent_search` | inillucent's own table that combines vector and keyword search. [Vector search](vector-search.md) covers it |

The internal tables of every extension are stored in the same file as ordinary tables. They commit
and roll back with the transaction that wrote them.

## The fourteen cases that differ

14 of the 416 probed cases do not produce SQLite's bytes.

| Case | What inillucent does | What SQLite 3.53.4 does | Why |
|---|---|---|---|
| `VECTOR(n)` column with `vector_distance_cos` | answers | has no vector type | vector search |
| `vector_distance_l2` and `vector_dot` | answers | has no such functions | vector search |
| `CREATE INDEX ... USING inillucent_hnsw` | builds an HNSW index | has no vector index | vector search |
| a vector `ORDER BY` with a `WHERE` filter | answers through the index | has no vector index | vector search |
| `CREATE VIRTUAL TABLE ... USING inillucent_search` | creates the table | has no such module | vector search |
| the operators `<->`, `<#>`, `<=>`, `<+>`, `<~>`, `<%>` | answers, with pgvector's meanings | has no such operators | vector search |
| `DELETE ... ORDER BY ... LIMIT` | deletes the rows | the pinned build refuses it | accepted |
| `UPDATE ... ORDER BY ... LIMIT` | updates the rows | the pinned build refuses it | accepted |
| `PRAGMA page_size`, `page_count` | `32768`, and 5 pages | `4096`, and 2 pages | page size |
| `.recover` | one line of 17 differs: `PRAGMA page_size = '32768'` | `PRAGMA page_size = '4096'` | page size |
| `EXPLAIN SELECT 1` | inillucent's operator list in SQLite's columns | SQLite's bytecode program | no bytecode |
| `.vfslist` | two file systems, `win32` and `memdb` | four file systems | no SQLite library |
| `.stats on` | inillucent's page cache counters | SQLite's memory allocator counters | no SQLite library |
| `.limit` | `trigger_depth 1000` | `trigger_depth 100` | the reference build |

### Six are vector search

SQLite has no vector search. The six vector cases all answer in inillucent. There is no SQLite
output for them to match, so they never count as the same. [Vector search](vector-search.md)
describes the features.

### Two are accepted here and refused by the pinned SQLite

SQLite answers `DELETE` and `UPDATE` with `ORDER BY ... LIMIT` only when the library is compiled
with `SQLITE_ENABLE_UPDATE_DELETE_LIMIT`. The pinned build is compiled without that option and
refuses both with a syntax error. Many other SQLite builds have the option. inillucent always
accepts both. inillucent refuses an `ORDER BY` with no `LIMIT` on a `DELETE` or `UPDATE`, as SQLite
does.

### Five follow from how inillucent is built

**`PRAGMA page_size` reports 32768.** inillucent writes 32 KiB pages. SQLite's default is 4096.
`PRAGMA page_count` differs for the same reason. `.recover` prints the page size in one of its 17
lines, so `.recover` differs on that one line. `PRAGMA page_size = 4096` on a new file does not
change the page size in this build.

**`EXPLAIN` prints inillucent's operators.** SQLite compiles a statement into a bytecode program,
and `EXPLAIN` lists that program. inillucent does not compile bytecode. Its `EXPLAIN` uses SQLite's
eight columns, header and widths (`addr opcode p1 p2 p3 p4 p5 comment`), and the rows are
inillucent's own chain of operators. The `bytecode('...')` table function reads the same rows.

**`.vfslist` lists the two file systems this build has.** The output uses the reference's format,
four lines per file system. `szOsFile` is the size of a C structure inside the SQLite library, and
that library is not part of inillucent, so the numbers differ.

**`.stats` prints inillucent's own counters.** The output uses the same two columns. The counters
are page cache bytes, fetches, hits, misses and rewarms, frames cooled and evicted, and pages read
and written. SQLite's lookaside slots and page cache overflow bytes describe SQLite's memory
allocator, which inillucent does not have.

Printing SQLite's numbers in these cases would report facts about a library that is not in the
program. None of these five will change.

### One is two reference builds that disagree

`.limit` reports `trigger_depth 1000` here. The downloaded `sqlite3.exe` reports 100, because it
was compiled with `SQLITE_MAX_TRIGGER_DEPTH=100`, as its own `PRAGMA compile_options` shows. The
pinned SQLite source defaults to 1000, and a reference built locally from that source reports 1000.
The other twelve lines of `.limit` agree. No value can match both references.

## What is refused

A construct inillucent has not built fails with exit code 3 and the status `unsupported`. A real
error, such as a missing table, fails with exit code 1. A script can tell the two apart without
reading the message. `inillucent capabilities` lists each refused construct as a row with the value
`no`.

### SQL that SQLite 3.53.4 answers and inillucent refuses

Each row was run against the release build and against the pinned `sqlite3`. Each fails here with
exit code 3.

| Construct | Example | What to write instead |
|---|---|---|
| a full text `MATCH` under an `OR` whose pattern reads another table's row | `f MATCH q.w OR rowid = 3` | a constant or bound pattern, or two queries joined with `UNION` |
| a correlated `IN` subquery with `GROUP BY`, `LIMIT` or a compound query | `a IN (SELECT a FROM t i WHERE i.id = o.id LIMIT 1)` | `EXISTS` with the condition written out |
| an FTS5 tokenizer inillucent does not have | `tokenize='trigram'` | `ascii`, `unicode61` or `porter` |
| FTS5 `detail='none'` or `detail='column'` | `fts5(body, detail='none')` | leave `detail` out. The index stores full positions |
| FTS5 `columnsize=0` | `fts5(body, columnsize=0)` | leave `columnsize` out |
| FTS5 `contentless_delete=1` | `fts5(body, content='', contentless_delete=1)` | a contentless table, removing rows with the `delete` command and the text they were indexed with |

### Refused by both engines

These rows are listed in `inillucent capabilities` as `no`. The pinned `sqlite3` refuses each of
them too.

| Construct | inillucent's message |
|---|---|
| a compound query ordered by an expression | `1st ORDER BY term does not match any column in the result set`, with status `syntax` and exit code 1 |
| `INSERT`, `UPDATE` or `DELETE` on a view that has no `INSTEAD OF` trigger for that statement | `cannot modify v because it is a view`, with exit code 1 as in SQLite. An `INSTEAD OF` trigger writes through a view. `INSERT ... ON CONFLICT` on a view fails with `cannot UPSERT a view` |
| `EXPLAIN EXPLAIN` | `unsupported: nested EXPLAIN` |
| `RETURNING` inside a trigger | `cannot use RETURNING in a trigger`, with status `syntax` and exit code 1 |
| `UPDATE` or `DELETE` of a `sqlite_schema` row | `table sqlite_master may not be modified`, with status `syntax` and exit code 1 |

### Refused for other reasons

| Construct | Why |
|---|---|
| `load_extension()` | inillucent has no C extension interface. FTS5, R-Tree and vector search are built in |
| `CREATE INDEX ... USING inillucent_hnsw (a, b)` | a vector index covers one column. SQLite has no vector index |
| `.expert` and `.session` | the two of `sqlite3`'s 65 dot commands the shell does not have |
| `fts5()`, `fts5_locale()`, `fts5_get_locale()`, `fts5_insttoken()` | a call fails with `unable to use function ... in the requested context` |
| `fts3_tokenizer()` | a call fails with `no such function` |
| the modules `fts4aux` and `fts3tokenize` | `CREATE VIRTUAL TABLE` fails with `no such module`. `fts5vocab` is the FTS5 equivalent and is present |
| opening a SQLite file | inillucent writes its own file format. Import a SQLite file with [`inillucent migrate`](migrating.md) |

## How inillucent behaves differently from SQLite

These differences change how an application runs, and they do not show up in a probe's output.

| Topic | inillucent | SQLite 3.53.4 |
|---|---|---|
| File format | `.rdb`, with its own log segments | SQLite's format |
| Page size | 32 KiB | 4 KiB by default |
| Page cache | 4,096 frames of 32 KiB, 128 MiB, fixed when the file is opened. `PRAGMA cache_size` reports `-131072`. A frame's memory is allocated the first time the frame is used | `cache_size`, 2 MiB by default |
| Journal modes | all six. `delete` is the default. `PRAGMA journal_mode = wal` selects the write ahead log, and the file reopens in WAL mode | all six, `delete` by default |
| Writers | one at a time. A second writer waits up to `PRAGMA busy_timeout` (5000 ms by default) and then fails with `busy` | one at a time |
| Readers during a write | a reader in another process reads the last committed state and does not wait. It waits only while the writer folds the log into the file, and under `PRAGMA locking_mode = exclusive` or with a database attached | in WAL mode, a reader does not wait |
| Processes on one file | several, with `PRAGMA locking_mode = normal`, the default | several |
| Threads | one. The engine is single threaded | serialised or multithreaded |
| A transaction larger than the page cache | allowed under `delete`, `truncate` and `persist`. Under `wal`, `memory` and `off`, the transaction fails when it changes more pages than the cache holds | spills to the journal |
| A `SELECT` result | computed when the first row is stepped. Later steps return rows already computed | computed one row per step |
| `AND` and `OR` | both operands are evaluated, so an error in the operand SQLite would skip, such as `abs(-9223372036854775808)` after a true left side of `OR`, is raised | the right operand is skipped when the left side decides the result |
| `DELETE ... LIMIT` and `UPDATE ... LIMIT` | accepted | accepted only in a build made with `SQLITE_ENABLE_UPDATE_DELETE_LIMIT`. The pinned `sqlite3` is built without it, and many distribution builds enable it |
| `PRAGMA page_size` before the first write | ignored, because the page size is part of inillucent's file format. The default is 32768. `PRAGMA page_size = n` prints nothing, as in SQLite. `PRAGMA aux.page_size = n` is ignored too | sets the page size |
| `PRAGMA page_count`, `freelist_count` and `max_page_count` | count pages of 32768 bytes, so the same data needs fewer pages, and a `max_page_count` limit is reached later. `max_page_count` is enforced: a statement that would grow the file past it fails with `database or disk is full` and the status `full`, and only that statement is rolled back. The limit is held on the open file and is not written to it | count pages of 4096 bytes. A statement that meets the limit rolls back the whole transaction in the cases that were measured |
| `PRAGMA auto_vacuum` and `incremental_vacuum` | `FULL` gives back the free pages at each commit and `INCREMENTAL` gives back up to N pages when `PRAGMA incremental_vacuum(N)` runs. On a file that has tables, a new mode takes effect when `VACUUM` rebuilds the file | the same |
| `PRAGMA cache_size` default | `-131072`. A value outside 32 bits reads as 0 | `-2000` |
| `PRAGMA busy_timeout` default | 5000 ms | 0 in the `sqlite3` shell |
| `sqlite_schema.rootpage` | the first table has root page 4, because inillucent keeps its own catalog trees on the first pages. The other columns match | the first table has root page 2 |
| a `WITHOUT ROWID` table whose primary key term is `DESC` | a query with no `ORDER BY` returns rows in ascending key order. Range predicates, seeks and `ORDER BY` on the key return the same rows as SQLite | the rows come back in descending key order |
| `EXPLAIN QUERY PLAN` where the planners choose differently | the same tree, with the loops this planner chose. It does not seek an index by `IN (SELECT ...)`, so it prints `SCAN` and a bloom filter under the `LIST SUBQUERY` node. It does not turn `EXISTS` into a join, walk an index to avoid a sort, or sort only the last term of an `ORDER BY`. A positive `EXISTS` over one table with no index for its key prints the loop and automatic index lines SQLite prints for the join it makes, such as `SEARCH b EXISTS USING AUTOMATIC PARTIAL COVERING INDEX (k=?)`, and this engine reads that table once into a hash table. A correlated subquery whose one table is a derived table or a view that reads nothing from the outer row, such as a grouped view looked up by `agg.s = o.v`, runs the view's query once and probes the rows the same way. It does so when the outer side of the comparison is a column of the outer row. A comparison against an expression of the outer row, such as `agg.s = o.v * 5`, still runs the view's query again for each outer row. Any other correlated subquery over such a table keeps its node and prints the same two lines under it, where SQLite prints them once `ANALYZE` has given the tables row counts and prints `SCAN` before that. It may join a table or a derived table in another order. A join keyed on an expression, such as `a.k = b.k + 1`, prints the key as `(<expr>=?)`, where SQLite builds its index on the plain column of the other table and joins in the other order. An `OR` answered by one search per arm prints `MULTI-INDEX OR` with no line for each arm. The rows of the query match | `SEARCH` by the subquery's rows, an `EXISTS` loop, an index walk, `USE TEMP B-TREE FOR LAST TERM OF ORDER BY` |
| `mmap_size`, `wal_autocheckpoint`, `journal_size_limit`, `temp_store = FILE`, `synchronous = EXTRA`, `threads`, the heap limits and `cache_spill` | stored and read back as SQLite reports them, and they do not change how inillucent runs. It maps no pages, has one thread, and folds the log at checkpoints | each changes how SQLite runs |
| a `.dump` that SQLite wrote for an FTS5 table, loaded into `inillucent-shell` | the shell runs the `CREATE VIRTUAL TABLE` that the `INSERT INTO sqlite_schema` row holds, skips the table's `_data`, `_idx` and `_docsize` tables, which are in SQLite's block format, and inserts the rows of `_content` through the table so the index is built here. A table with `content='other'` is rebuilt from `other` when the dump's transaction ends. Settings in `_config` are set again with the table's commands. The table answers `MATCH` in the same session | the private tables are loaded as they are, and the table is usable after the file is reopened |
| a row of many columns | a row is stored in one page. A table of 2,000 columns of numbers holds rows at the default page size when each number fits 32 bits, and about 1,925 numbers fit when each needs eight bytes. A row of more fails with `a row of N columns does not fit one page`, and `inillucent migrate` of such a table fails with the status `unsupported`. A page smaller than 32 KiB holds fewer columns | up to 2,000 columns, with rows of any width stored across overflow pages |
| `$1`, `$2` placeholders | `$N` binds the Nth value, as `?N` does and as PostgreSQL does. `SET a = $2 WHERE id = $1` takes `a` from the second value | `$1` is a name, numbered by the order names first appear, so the same statement takes `a` from the first value |

**Multiple processes.** Two processes can write the same file, one at a time. The number of rows in
the file equals the number of commits acknowledged. `crates/inillucent-compat/tests/durability/process_concurrency.rs`
checks this with two real writer processes, and `crates/inillucent-compat/tests/durability/process_storm.rs`
with four writers and two readers killed at random, graded byte for byte against what each writer
acknowledged. Two of its cases rewrite values of 4 to 20 KB in autocommit statements, let the
readers write too, and end with every process killed at the same moment, which is how an
application stopped by a service manager leaves the file.

**`BEGIN` is deferred, as in SQLite.** `BEGIN` and `BEGIN DEFERRED` take no lock. The first
statement inside the transaction takes the lock it needs, so a read transaction reads beside another
process's open write transaction. `BEGIN IMMEDIATE` and `BEGIN EXCLUSIVE` take the write lock at
once. A transaction that reads first and then writes fails with `busy` when another process
committed after its first read: roll it back and run it again, or start it with `BEGIN IMMEDIATE`.

**A reader during a write.** A write transaction holds the RESERVED lock, which readers share, so a
query in another process reads the last committed state while the transaction runs.
`crates/inillucent-compat/tests/durability/process_readers.rs` checks that every answer such a
reader gives is a state some commit produced. The writer takes the EXCLUSIVE lock only to fold the
log into the database file. Pages a transaction changes beyond what its page cache holds go to a
spill file of the writer's own, so a large transaction does not stop readers either. A
fold on the way out of a statement waits 50 milliseconds for readers and is otherwise left to a
later statement or to close. A connection under `PRAGMA locking_mode = exclusive`, or one with a
database attached, still takes EXCLUSIVE for the whole transaction.

**`PRAGMA locking_mode = exclusive`.** `normal` is the default, as in SQLite. With `exclusive`, the
engine keeps the file locked between statements, so a second process waits for the first to close.
A program that never opens a second connection can use `exclusive` to save work: under `normal`, the
engine reads the file header and the end of the log again before each statement. A statement that
reads no table, such as `SELECT 1` or `SELECT json_extract(?1, '$.a')`, takes no lock and reads
neither, which is what SQLite does.

**The end of the log on Windows.** Under `normal`, a connection learns whether another process
committed by asking the open log segment its length before each statement. On Windows that question
cost more than the rest of an autocommit point read. A process now holds an opportunistic lock on
its open log segment while no other process has the segment open, and answers the question from the
length its own writes left, with no call into the operating system. When another process opens the
segment, its open waits until this process has stopped using that length, so every commit the
other process makes is seen by the next statement here. One process stopped in a
debugger holds up other processes' opens of its log segment until it is resumed or ends. Setting the
environment variable `INILLUCENT_LOG_OPLOCK=0` turns the lock off, and every statement asks the
length as before. Linux and macOS ask the length on every statement.

**A large transaction.** A transaction on a database file can change more pages than the page cache
holds, in every journal mode and both locking modes. A changed page the cache cannot keep goes to a
temporary spill file of the connection's own and is read back from there, and the fold after the
commit writes it into the database file. Measured with a 64 page cache: one transaction inserting
3,000 rows of 3,000 bytes commits under `wal`, `memory` and `delete`, with
`locking_mode` `normal` and `exclusive`.

### Smaller differences

| Topic | inillucent | SQLite 3.53.4 |
|---|---|---|
| `SELECT * FROM pragma_foreign_keys` | fails with `no such table: pragma_foreign_keys`. Use `PRAGMA foreign_keys` | returns one row with the setting |
| `inillucent vector-search` result columns | the columns are the table's columns and `distance`. A table whose primary key is not an `INTEGER PRIMARY KEY` also gets a `rowid` column in front, and a table with an `INTEGER PRIMARY KEY` shows that key once | no vector search |
| an `inillucent_search` insert with a vector of the wrong length | fails with status `syntax` and a message that says the vector is not of the declared width and gives both widths | no vector search |
| the longest key | a key of an index or a primary key is limited to 1,048,576 bytes once encoded. A longer one fails with `an index or primary key entry is longer than 1,048,576 bytes` before any page is written. A zero byte in a text or a blob takes two bytes once encoded | a key can be up to the gigabyte limit of a value |

A vector written to an `inillucent_search` table as JSON text, such as `'[1,0,0,0]'`, is accepted
and stored as a blob.

## The usage corpus

The 416 probed cases cover one feature each. `differential::usage_corpus` covers how applications use
SQLite. Its 1,719 cases are scripts under `compat/corpus/usage`. They contain the statements a
driver sends when it connects, the catalog queries an ORM runs to read a schema, the table rebuild a
migration tool runs, ordinary create, read, update and delete code, and the error messages those
tools parse. Each script runs through `inillucent-shell` and through the pinned `sqlite3` shell with
`.mode quote` and `.headers on`, and the two outputs are compared byte for byte. The comparison
includes column names, storage classes and error text.

`compat/corpus/usage/known.toml` lists the cases whose outputs still differ. A case that differs and
is not listed fails the test. A listed case that now matches also fails, so an entry comes out when
its case is fixed. Each entry has a `kind`. `deviation` marks a difference that stays on purpose and
is described on this page. `defect` marks a difference that has not been fixed yet. Setting
`INILLUCENT_USAGE_REPORT=<file>` writes every difference, with both outputs, to that file as JSON.

## Reproducing the probe

```sh
cargo build --release --bin inillucent-shell
pwsh tools/sqlite-reference.ps1          # the pinned SQLite 3.53.4, on Windows
bash tools/sqlite-reference.sh           # the same, on Linux
node tools/feature-probe/run.js          # all 416 cases
node tools/feature-probe/registers.js    # SQLite's own lists against inillucent's
```

The results are written to `_agent_output/feature-probe/results.json`. The file holds each script,
both outputs and the verdict. [Repository](repository.md) covers the test runner and the other
checks.