Feature matrix

SELECT region, amount FROM rows

SELECT amount AS total FROM rows

SELECT * FROM rows

SELECT DISTINCT region FROM rows

SELECT (SELECT MAX(amount) FROM rows) AS peak FROM rows LIMIT 1

SELECT amount * 2 + 1 AS scaled FROM rows

SELECT ROUND(amount / 3, 2) AS thirds FROM rows

Precision truncates to an integer and clamps to 0..30; halfway values round away from zero, matching SQLite.

SELECT region FROM rows WHERE region = 'west'

SELECT region FROM rows WHERE amount >= 10

SELECT active, amount FROM rows WHERE active = TRUE

SELECT region FROM rows WHERE region != NULL

SELECT region FROM rows WHERE joined >= DATE '2026-01-01'

SELECT region FROM rows WHERE joined >= TIMESTAMP '2026-01-01 00:00:00'

TIMESTAMP 'y-m-d h:m:s' with the time optional. A literal without a zone is UTC, as every datetime in a Minnow database is.

SELECT region, amount FROM rows WHERE amount >= $1 ORDER BY amount
-- bound: [6]

Values bind by 1-based number and may repeat; the compiled plan is cached on the SQL text and re-bound per execution.

SELECT region, amount FROM rows WHERE amount >= ? AND active = ? ORDER BY amount
-- bound: [6,true]

Each ? takes the next value in order. A statement uses either ? or $n placeholders, never both; PostgreSQL itself has no ? form.

SELECT r.region, d.label FROM rows r JOIN dims d ON d.region = r.region

SELECT r.region, d.label FROM rows r LEFT JOIN dims d ON d.region = r.region

SELECT region FROM rows WHERE amount > 5 AND region = 'west'

SELECT region FROM rows WHERE region IN ('west', 'east')

SELECT region FROM rows WHERE region NOT IN ('north')

SELECT region FROM rows WHERE region IN (SELECT region FROM dims)

SELECT region FROM rows WHERE amount > (SELECT AVG(amount) FROM rows)

SELECT region, COUNT(*) AS count FROM rows GROUP BY region

SELECT region, SUM(amount) AS total FROM rows GROUP BY ROLLUP(region)

ROLLUP/CUBE/GROUPING SETS desugar into a UNION ALL of grouped blocks. The GROUPING() marker is deliberately unsupported, so rollup NULLs and data NULLs are indistinguishable. SQLite itself has none of these.

SELECT region, active, COUNT(*) AS c FROM rows GROUP BY GROUPING SETS ((region), (active), ())

SELECT region, COUNT(*) AS count FROM rows GROUP BY region HAVING COUNT(*) > 1

SELECT COUNT(*) AS count FROM rows

SELECT SUM(amount) AS total FROM rows

SELECT AVG(amount) AS mean FROM rows

SELECT MIN(amount) AS low, MAX(amount) AS high FROM rows

SELECT region, amount FROM rows ORDER BY region, amount DESC

SELECT * FROM rows ORDER BY amount

SELECT amount FROM rows ORDER BY amount LIMIT 2

WITH west AS (SELECT amount FROM rows WHERE region = 'west') SELECT COUNT(*) AS count FROM west

WITH a AS (SELECT amount FROM rows), b AS (SELECT amount FROM a WHERE amount > 5) SELECT COUNT(*) AS count FROM b

WITH totals(place, total) AS (SELECT region, SUM(amount) FROM rows GROUP BY region) SELECT place, total FROM totals

A CTE names its own output columns. A recursive CTE takes the names before its step member, which refers to the working set by them.

SELECT d.total FROM (SELECT region, SUM(amount) AS total FROM rows GROUP BY region) d ORDER BY d.total

SELECT region FROM rows UNION SELECT region FROM dims ORDER BY region

SELECT region FROM rows UNION ALL SELECT region FROM dims

SELECT region, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount) AS rn FROM rows

SELECT region, RANK() OVER (ORDER BY amount) AS r FROM rows

SELECT region, DENSE_RANK() OVER (ORDER BY amount) AS dr FROM rows

INSERT INTO keyed (name, score) VALUES ('a', 1), ('b', 2)

Through execute(); query() stays read-only.

UPDATE keyed SET score = score + 1 WHERE score > 0

Requires a unique-key table; read-then-mutate, not serializable.

DELETE FROM keyed WHERE score < 0

Requires a unique-key table.

DELETE FROM keyed WHERE name = 'x' RETURNING name, score

RETURNING works on INSERT, UPDATE, and DELETE; inserts echo written values, updates return post-update values, deletes the rows as read.

INSERT INTO keyed (name, score) VALUES ('x', 9) ON CONFLICT (name) DO UPDATE SET score = EXCLUDED.score

Whole-row upsert: DO UPDATE must set every inserted column from EXCLUDED, and the conflict target is the unique key.

INSERT INTO keyed (name, score) VALUES ('x', 9), ('z', 1) ON CONFLICT (name) DO NOTHING

Rows whose key already exists at the statement's snapshot are skipped.

INSERT INTO keyed (name, score, bonus) VALUES ('x', 50, 9) ON CONFLICT (name) DO UPDATE SET score = EXCLUDED.score

Assigning a subset of inserted columns merges only those into existing rows; unassigned columns keep their stored values. Mixed update/insert batches publish atomically or roll back together.

SELECT region FROM rows WHERE amount > 5 OR region = 'west'

SELECT region FROM rows WHERE region LIKE 'w%'

% matches any run and _ matches one Unicode codepoint.

SELECT region FROM rows WHERE region IS DISTINCT FROM 'west'

Null-safe: NULL is not distinct from NULL.

SELECT region FROM rows WHERE active IS TRUE OR active IS UNKNOWN

IS [NOT] TRUE/FALSE/UNKNOWN never return UNKNOWN; they desugar to null-safe comparisons.

SELECT region FROM rows WHERE region LIKE 'we!%st' ESCAPE '!' OR region LIKE 'we%'

ESCAPE makes the next pattern character literal, wildcards included.

SELECT region FROM rows WHERE amount > ALL (SELECT amount FROM dims)

ANY/SOME/ALL with full three-valued logic; correlated forms are rejected. SQLite itself has no quantified comparisons.

SELECT region FROM rows WHERE region ILIKE 'WE%'

Case-insensitive LIKE, a PostgreSQL extension; SQLite's LIKE is case-insensitive by default instead.

SELECT region FROM rows WHERE MATCH(region) AGAINST 'west'

SELECT region FROM rows WHERE MATCH(*) AGAINST 'wes*'

SELECT region, BM25(region) AGAINST 'west' AS score FROM rows WHERE MATCH(region) AGAINST 'west' ORDER BY score DESC

SELECT region FROM rows WHERE amount > 0 ORDER BY amount * 2 DESC, region

SELECT region FROM rows WHERE amount BETWEEN 1 AND 5

SELECT region FROM rows WHERE amount BETWEEN SYMMETRIC 5 AND 1

SYMMETRIC accepts the bounds in either order.

SELECT region FROM rows WHERE region IS NULL

SELECT amount FROM rows WHERE region IS NOT NULL

SELECT region FROM rows WHERE EXISTS (SELECT 1 FROM dims)

Uncorrelated EXISTS only; correlated references still fail as unknown aliases.

SELECT CASE WHEN amount > 5 THEN 'big' ELSE 'small' END AS size FROM rows

SELECT region FROM rows r WHERE amount > (SELECT AVG(amount) FROM rows q WHERE q.region = r.region)

Equality-correlated subqueries decorrelate into derived-table joins at compile time; both executors run plain joins.

SELECT amount FROM rows r WHERE EXISTS (SELECT region FROM dims d WHERE d.region = r.region)

EXISTS joins the subquery's distinct correlation keys; NOT EXISTS becomes a left join checked with IS NULL.

SELECT r.region, (SELECT AVG(q.amount) FROM rows q WHERE q.region = r.region) AS regional FROM rows r

Correlated scalar aggregates decorrelate in the select list too, outside grouped queries.

WITH RECURSIVE n AS (SELECT MIN(amount) AS v FROM rows UNION ALL SELECT v + 1 FROM n WHERE v < 6) SELECT v FROM n

Linear delta recursion with UNION or UNION ALL, capped at 10,000 iterations and 1,000,000 rows. Plain WITH still rejects self-references.

WITH totals AS (SELECT MAX(score) AS top FROM keyed) DELETE FROM keyed WHERE score >= (SELECT top FROM totals) RETURNING name

WITH precedes INSERT/UPDATE/DELETE; the CTEs are visible to the statement's queries and subqueries.

SELECT region FROM rows INTERSECT SELECT region FROM dims

INTERSECT binds tighter than UNION and EXCEPT, per the SQL standard.

SELECT region FROM rows EXCEPT SELECT region FROM dims

SELECT region FROM rows INTERSECT ALL SELECT region FROM dims

Bag semantics; SQLite itself has no INTERSECT ALL.

SELECT region FROM rows EXCEPT ALL SELECT region FROM dims

Bag semantics; SQLite itself has no EXCEPT ALL.

SELECT COUNT(DISTINCT region) AS regions FROM rows

SELECT region, COUNT(*) FILTER (WHERE amount > 5) AS big FROM rows GROUP BY region

Desugars into a CASE inside the aggregate, so it works with every aggregate and DISTINCT.

SELECT SUM(amount) OVER (PARTITION BY region) AS total FROM rows

Default frame only: whole partition without OVER ordering, running with peers when ordered.

SELECT amount, amount - LAG(amount) OVER (ORDER BY amount, region) AS change, 100.0 * amount / SUM(amount) OVER () AS pct FROM rows

A window is an expression: the arithmetic around it is evaluated after the window has run, over the column it produced.

SELECT region, SUM(amount) AS total, ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC, region) AS rank, SUM(SUM(amount)) OVER () AS everything FROM rows GROUP BY region HAVING COUNT(*) > 0

Windows run after GROUP BY and HAVING, as the standard orders them, so they rank groups and their OVER clause reads the group's aggregates.

SELECT amount, FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount) AS lowest, LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest FROM rows

FIRST_VALUE/LAST_VALUE respect the frame; the default frame ends at the current peer group, as the standard specifies.

SELECT amount, NTILE(2) OVER (ORDER BY amount) AS half FROM rows

SELECT amount, PERCENT_RANK() OVER (ORDER BY amount) AS pr, CUME_DIST() OVER (ORDER BY amount) AS cd FROM rows

SELECT amount, SUM(amount) OVER (ORDER BY amount, joined ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS windowed FROM rows

ROWS frames take row-distance bounds; RANGE frames take UNBOUNDED and CURRENT ROW bounds, where CURRENT ROW spans the ordering peer group.

SELECT r.region FROM rows r RIGHT JOIN dims d ON d.region = r.region

Desugars to the mirrored LEFT JOIN; supported as the sole join of a block.

SELECT r.region FROM rows r JOIN dims d ON d.amount > r.amount

Executes as a nested-loop join (probe x build); equalities keep the hash path.

SELECT DISTINCT * FROM rows

Expands to DISTINCT over every wildcard output column once input schemas are known.

SELECT amount FROM rows LIMIT 5 OFFSET 2

OFFSET is accepted directly after LIMIT.

SELECT 1 + 1 AS two, UPPER('minnow') AS name

SELECT v.column1 AS n, v.column2 AS tag FROM (VALUES (1, 'one'), (2, 'two')) v

VALUES works standalone, as a set-operation member, and as a derived table with AS alias(col, ...) renaming; columns default to column1..columnN.

SELECT region, amount FROM rows ORDER BY region NULLS LAST, amount LIMIT $1 OFFSET $2
-- bound: [2,1]

LIMIT and OFFSET take placeholders; the plan re-binds per execution like any parameter.

SELECT amount FROM rows ORDER BY amount OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY

The standard fetch clause is a spelling of LIMIT; SQLite itself only speaks LIMIT.

SELECT amount FROM rows ORDER BY amount OFFSET 2

OFFSET no longer requires LIMIT. SQLite itself needs LIMIT -1 OFFSET n.

CREATE TABLE made (id INTEGER PRIMARY KEY, label TEXT NOT NULL, at TIMESTAMP)

Standard type names map onto the four logical types (widths parse and are ignored); one PRIMARY KEY or UNIQUE column becomes the unique key. Dropping or altering tables stays a programmatic concern.

CREATE TRIGGER keyed_audit AFTER INSERT ON keyed BEGIN INSERT INTO rows (region, amount) VALUES (NEW.name, NEW.score); END

AFTER and BEFORE row triggers on INSERT/UPDATE/DELETE, executed atomically inside the triggering commit with NEW/OLD references. Bodies: INSERT ... VALUES into keyless tables; UPDATE/DELETE against keyed tables. One cascade level is allowed; deeper chains error at write time.

CREATE TRIGGER keyed_before BEFORE INSERT ON keyed BEGIN INSERT INTO rows (region, amount) VALUES (NEW.name, NEW.score); END

BEFORE bodies stage ahead of the primary write but publish in the same atomic commit, so timing is a portability feature: atomicity and visibility are identical to AFTER.

CREATE TRIGGER keyed_counts AFTER INSERT ON keyed BEGIN UPDATE stats SET total = total + NEW.score WHERE region = NEW.name; END

UPDATE and DELETE trigger bodies run against keyed tables, reading current state each firing. Touching the same target row twice in one firing is rejected.

DROP TRIGGER droppable_audit

SELECT region FROM rows WHERE (amount > 5 AND region = 'west')

SELECT region FROM rows WHERE NOT active = TRUE

SELECT region || '-' || label AS tag FROM dims

|| concatenates strings and propagates NULL; non-string operands are a type error.

SELECT amount % 3 AS remainder FROM rows

Division and remainder by zero are NULL, matching SQLite.

SELECT CAST(amount AS INTEGER) AS whole, CAST(amount AS TEXT) AS label FROM rows

Standard type names map to the four logical types; integer targets truncate toward zero, and non-numeric strings fail rather than becoming 0.

SELECT "region", "rows"."amount" FROM "rows" WHERE "amount" > 5

Double-quoted identifiers are never keywords and keep their exact spelling.

SELECT region, amount FROM rows ORDER BY region NULLS LAST, amount

Without NULLS FIRST/LAST the default matches SQLite: NULLs first ascending, last descending.

SELECT COALESCE(region, 'unknown') AS region_label FROM rows

Arguments evaluate left to right; the first non-NULL value wins. All non-NULL arguments must share one type.

SELECT DATE_TRUNC('month', joined) AS joined_month FROM rows

Units: year, quarter, month, week (Monday start), day, hour, minute, second. Truncation is in UTC; the engine has no session time zone.

SELECT joined + INTERVAL '1 month' AS next_month, joined - INTERVAL '2 days 3 hours' AS earlier FROM rows WHERE joined IS NOT NULL

INTERVAL added to or subtracted from a datetime. Months are calendar arithmetic, so 31 January plus a month clamps to the end of February.

SELECT UPPER(label) AS u, LOWER(label) AS l, LENGTH(label) AS n, SUBSTR(label, 2, 3) AS mid, TRIM(label) AS t FROM dims

SUBSTRING is accepted as a spelling of SUBSTR; LENGTH and SUBSTR count characters, not UTF-16 units.

SELECT ABS(amount - 5) AS distance FROM rows

SELECT NULLIF(amount, 3) AS n, GREATEST(amount, 5) AS g, LEAST(amount, 5) AS l, FLOOR(amount) AS f, CEILING(amount) AS c, MOD(amount, 4) AS m, POWER(2, 3) AS p, SQRT(16) AS s FROM rows

GREATEST/LEAST ignore NULL arguments, matching PostgreSQL.

SELECT REPLACE(region, 'we', 'be') AS r, LTRIM(' x') AS lt, RTRIM('x ') AS rt, INSTR(region, 'st') AS i FROM rows WHERE region IS NOT NULL

SELECT EXTRACT(year FROM joined) AS y, EXTRACT(dow FROM joined) AS d FROM rows WHERE joined IS NOT NULL

Fields: year, quarter, month, week (ISO), day, hour, minute, second, epoch, dow — all in UTC. SQLite spells this strftime.

SELECT region, COUNT(DISTINCT amount) AS amounts, COUNT(DISTINCT active) AS states, SUM(amount) AS total FROM rows GROUP BY region

COUNT/SUM/AVG/MIN/MAX accept DISTINCT. Each one keeps its own set of values, so several can appear in one select, beside ordinary aggregates, inside expressions, and in HAVING.

SELECT r.region FROM rows r JOIN dims d ON d.region = r.region AND d.amount = r.amount

Multi-key conditions execute as a nested-loop join; single equalities keep the hash path.

SELECT r.region AS region, d.label AS label FROM rows r CROSS JOIN dims d

SELECT r.amount AS amount, d.label AS label FROM rows r FULL JOIN dims d ON d.region = r.region

Desugars into a union of two left joins, so it must be the sole join, with an equality ON and no grouping or DISTINCT yet.

SELECT region, amount FROM rows ORDER BY 2 DESC

Ordinals resolve to the select list at compile time; out-of-range ordinals are an error.

SELECT amount, LAG(amount) OVER (ORDER BY amount) AS previous, LEAD(amount, 1, -1) OVER (ORDER BY amount) AS next FROM rows

LAG/LEAD take a constant offset (default 1) and default value (default NULL), and require ORDER BY inside OVER.

INSERT INTO keyed (name, score) SELECT name || '2' AS name, score + 1 AS score FROM keyed

The SELECT runs at one snapshot and materializes before the batch write.

MERGE INTO keyed k USING (SELECT 'z' AS name, 9 AS score) s ON k.name = s.name WHEN MATCHED THEN UPDATE SET score = s.score WHEN NOT MATCHED THEN INSERT (name, score) VALUES (s.name, s.score)

One pass over the source decides each row's branch, and the branches apply as batched writes inside a single write scope — atomic, and firing the same triggers the equivalent INSERT, UPDATE, and DELETE would. The match must equate the target's unique key with a source value, which is how rows are addressed. Two source rows matching one target row is a cardinality violation, as the standard says, rather than a last-one-wins race. MATCHED BY SOURCE, MATCHED BY TARGET, and RETURNING are not supported.

BEGIN

Holds the same scope `write()` opens between statements instead of around a callback: writes stage into it, reads see what it staged, and COMMIT publishes them together. Schema changes are refused inside one, because the catalog commits outside the scope and a rollback could not take them back. A transaction left untouched past the idle bound rolls itself back, so an abandoned BEGIN cannot hold storage forever.

COMMIT

ROLLBACK

SELECT CHAR_LENGTH(region) AS n FROM rows WHERE region IS NOT NULL

SELECT OCTET_LENGTH(region) AS n FROM rows WHERE region IS NOT NULL

Counts the UTF-8 encoding's bytes.

SELECT SUBSTRING(region FROM 1 FOR 2) AS part FROM rows WHERE region IS NOT NULL

The position window is intersected with the string, so a start below 1 shortens the result instead of shifting it.

SELECT TRIM(LEADING 'w' FROM region) AS trimmed FROM rows WHERE region IS NOT NULL

SELECT TRIM(BOTH 'we' FROM region) AS trimmed FROM rows WHERE region IS NOT NULL

The trim string is removed as a whole repeated unit, per the standard; PostgreSQL reads a multi-character argument as a set of characters instead.

SELECT POSITION('es' IN region) AS at FROM rows WHERE region IS NOT NULL

SELECT LPAD(region, 6, '-') AS padded FROM rows WHERE region IS NOT NULL

SELECT OVERLAY(region PLACING 'X' FROM 1 FOR 1) AS masked FROM rows WHERE region IS NOT NULL

SELECT rows.* FROM rows

Output names follow the rule a bare * uses: the column's own name from one source, alias-qualified from several.

SELECT y.a AS a FROM rows AS y(a, b, c, d)

SELECT SUM(ALL amount) AS total FROM rows

SELECT s.amount AS amount FROM (SELECT amount FROM rows UNION SELECT amount FROM dims) s

SELECT amount FROM rows -- a comment

SELECT /* a comment */ amount FROM rows

SELECT rows.amount AS amount FROM rows, dims WHERE dims.region = rows.region

SELECT rows.amount AS amount FROM rows JOIN dims USING (region)

The joined columns are not merged the way the standard describes: they stay one per side, so `SELECT *` returns both and an unqualified reference to a join column is ambiguous. Qualify it, or name the side you want.

SELECT rows.amount AS amount FROM rows NATURAL JOIN dims

The shared columns are compared but not merged, so an unqualified reference to one is ambiguous — qualify it. NATURAL RIGHT JOIN is rejected, because the right-join mirror rewrites the sources the shared-column search reads.

SELECT CURRENT_DATE > DATE '2000-01-01' AS elapsed

Resolved once per execution, so every row of a statement sees one instant; results that read the clock never memoize.

SELECT CURRENT_TIMESTAMP > TIMESTAMP '2000-01-01 00:00:00' AS elapsed

SELECT LOCALTIME IS NOT NULL AS ticking

The engine has no TIME type, so LOCALTIME reads as an 'HH:MM:SS' string, like SQLite's CURRENT_TIME.

SELECT amount FROM rows WHERE (region, amount) = ('west', 10)

SELECT amount FROM rows WHERE (region, amount) IN (('west', 10), ('east', 3))

SELECT amount FROM rows WHERE (region, region) IS NOT NULL

SELECT 0x0A AS ten

SELECT 1_000 AS thousand

SELECT region FROM rows WHERE region IS NOT NULL ORDER BY region DESC FETCH FIRST 1 ROWS WITH TIES

The limit cannot be pushed into a scan, so these plans run unlimited and the ordered result is trimmed.

SELECT s.amount AS amount FROM (WITH inner_cte AS (SELECT amount FROM rows) SELECT amount FROM inner_cte) s

SELECT NTH_VALUE(amount, 2) OVER (ORDER BY amount) AS second FROM rows

SELECT SUM(amount) OVER w AS running FROM rows WINDOW w AS (ORDER BY amount)

SELECT COUNT(*) OVER (ORDER BY amount GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS peers FROM rows

SELECT COUNT(*) OVER (ORDER BY amount RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS others FROM rows

SELECT GROUPING(region) AS aggregated FROM rows GROUP BY ROLLUP(region)

A bitmask over the arguments, most significant first.

SELECT ANY_VALUE(amount) AS sample FROM rows

Which row of the group answers is implementation-dependent; this engine returns the minimum.

SELECT VAR_POP(amount) AS spread FROM rows

Built from COUNT and SUM rather than a dedicated accumulator: the variance is E(x2) - E(x)2. Bare VARIANCE and STDDEV are the sample forms, as in PostgreSQL.

SELECT STDDEV_POP(amount) AS spread FROM rows

SELECT EVERY(amount > 1) AS all_positive FROM rows

SELECT JSON_VALUE('{"a": 1}', '$.a') AS a

JSON documents are text in ordinary string columns, as in SQLite; the path subset is $, member steps, and array subscripts.

SELECT JSON_QUERY('{"a": [1, 2]}', '$.a') AS a

SELECT JSON_EXISTS('{"a": 1}', '$.a') AS present

SELECT '{"a": 1}' IS JSON OBJECT AS shaped

SELECT JSON_OBJECT('a' VALUE 1) AS document

SELECT JSON_ARRAY(1, 2) AS document

CREATE TABLE IF NOT EXISTS made (a INTEGER)

CREATE TABLE defaulted (id INTEGER PRIMARY KEY, tier TEXT DEFAULT 'basic')

A column with a DEFAULT is NOT NULL unless declared otherwise: the engine fills absent values from the default, so NULL and the default cannot both claim the slot.

CREATE TABLE keyed_clause (a INTEGER, b TEXT, PRIMARY KEY (a))

ALTER TABLE rows ADD COLUMN note TEXT

Existing rows have no value for the new column, so it is always nullable.

CREATE TABLE copied AS SELECT region FROM rows

DROP TABLE doomed

Takes the table's rows, catalog record, full-text index, and triggers. The blocks are retired through the commit rather than deleted, so a reader pinned to an older version keeps resolving them and the lease-aware collector reclaims them later. Refused while a view reads the table or another table's trigger writes to it — both would be left pointing at something that is not there. DROP TABLE CASCADE is refused too: nothing cascades, because there are no dependent objects to reach.

CREATE VIEW west AS SELECT region, amount FROM rows WHERE region = 'west'

The catalog stores the query text and the schema inferred from it, so a view answers the same questions a table does and reads expand it into the query it stands for — anywhere a read runs, including inside a write scope. A view is never a write target. CREATE OR REPLACE redefines one; a view stacked on it follows the new definition, and a cycle two redefinitions close is caught on the next read rather than recursed.

DROP VIEW doomed_view

CREATE TABLE checked (a INTEGER NOT NULL CHECK (a > 0), CONSTRAINT small CHECK (a < 100))

A row condition over the table's own columns, evaluated by the writer on every path that writes a row — insert, upsert, and update, which is checked against its post-image. A constraint fails only when it evaluates to false, so SQL's unknown passes: NULL satisfies CHECK (a > 0) unless the column is also NOT NULL.

CREATE TABLE children (id INTEGER PRIMARY KEY, parent INTEGER REFERENCES parents(id) ON DELETE CASCADE)

Single-column references to the parent's unique key, which is the column the engine can probe for existence and the one its keyed writes address rows by. Every write of a referencing column checks the parent exists, reading through the writing transaction so a child inserted beside its parent in one scope sees it; a NULL reference names no parent and is satisfied. ON DELETE takes RESTRICT (the default), CASCADE, and SET NULL, applied inside the deleting transaction. ON UPDATE has nothing to act on, because a unique key cannot change.

SELECT region FROM rows r WHERE EXISTS (SELECT region FROM dims d WHERE d.amount > r.amount)

Rejected with: support only equality conditions

Correlation must be a plain equality between one inner and one outer qualified column.

SELECT region FROM rows r WHERE region NOT IN (SELECT d.region FROM dims d WHERE d.region = r.region)

Rejected with: use NOT EXISTS

Correlated NOT IN has NULL semantics that the join rewrite cannot preserve; NOT EXISTS expresses the intent.

UPDATE rows SET amount = 1

Rejected with: UPDATE requires a table with a unique key

Deliberate: mutation segments address rows by unique key, so tables without one cannot be updated or deleted through any API.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Rejected with: Expected SELECT, found SET

The engine has one isolation level. Every read runs against a single version and every write scope commits atomically, so there is no weaker mode to relax into and no stronger one to ask for.

GRANT SELECT ON rows TO reader

Rejected with: Expected SELECT, found GRANT

An embedded, single-user database in the page has no principals to grant to.

SELECT x.amount FROM rows, LATERAL (SELECT amount FROM dims WHERE dims.region = rows.region) x

Rejected with: LATERAL sources are not supported

A lateral source re-executes per row of its left side, which the executors have no operator for.

SELECT LISTAGG(region, ',') AS regions FROM rows

Rejected with: Unsupported function: LISTAGG

Needs an ordered string accumulator in the vectorized group state, which the fixed count/sum/value accumulators cannot hold.

SELECT j.a FROM rows, JSON_TABLE(rows.region, '$' COLUMNS (a INTEGER PATH '$.a')) AS j

Rejected with: JSON_TABLE is not supported

A row-producing operator; the same gap as LATERAL.

SELECT amount FROM rows WHERE region SIMILAR TO 'w%'

Rejected with: Expected eof, found SIMILAR

LIKE and the regular-expression-free MATCH cover the same ground for the data sizes this engine targets.

SELECT region FROM rows ORDER BY region COLLATE "en"

Rejected with: Expected eof, found COLLATE

String comparison is one documented collation; a per-query collation would change index order too.

SELECT JSON_ARRAYAGG(region) AS regions FROM rows

Rejected with: Unsupported function: JSON_ARRAYAGG

Aggregating into a document needs the same ordered accumulator LISTAGG does; JSON_ARRAY builds one from scalars today.

SELECT ARRAY[1, 2] AS pair

Rejected with: Unsupported SQL character: [

Array and multiset types would need an encoding and a comparison order the columnar format would carry forever; a JSON document in a text column covers the same shape.

SELECT TIME '12:00:00' AS at

Rejected with: Expected eof

The engine has four logical types and stores every datetime as an instant in UTC; a bare time of day has no instant. LOCALTIME reads as an 'HH:MM:SS' string instead.

CREATE SEQUENCE order_ids

Rejected with: Expected TABLE, found SEQUENCE

A sequence is a second kind of catalog object with its own durability and contention story; the key column's autoincrement default covers the common use.