From SQL

The SQL you write by habit, read as the FenecQL it means — and refused, saying what to write, where it has no exact meaning here.

FenecQL is not SQL, but the everyday spellings of SQL are FenecQL too. The parser reads CREATE TABLE, INSERT INTO … VALUES, UPDATE … SET a = …, JOIN, IN (…), EXISTS, DISTINCT, LIKE and the rest of the tables below into the very statements FenecQL writes, so a text in either reaches the engine the same way — over HTTP, through MCP, in the fenec shell, in the page and in an app. There is no SQL mode and no second parser: the two spellings mix in one statement. What would answer otherwise than SQL does — a RIGHT JOIN, an aggregate over two tables' rows, a DEFAULT, a transaction left open — is refused with the FenecQL that does it, so an agent writing SQL by habit learns the language from its first error.

Every SQL text on this page is run by a test (crates/fenec-core/tests/sql_docs.rs): the session below statement by statement, each -- → x the answer it checks, and each line of the tables held to the FenecQL beside it — the two parse to the same statement — or refused.

A session written as SQL

CREATE TABLE users (id serial PRIMARY KEY, name text, email citext NOT NULL UNIQUE, age integer, city text, visits integer)
CREATE INDEX users_age ON users (age)
INSERT INTO users (name, email, age, city) VALUES ('Ada', 'ada@example.com', 36, 'London'), ('Bob', 'bob@example.com', 25, 'Leeds'), ('Cem', 'cem@example.com', 52, 'İzmir') RETURNING name  -- → "Ada"
SELECT name FROM users WHERE age BETWEEN 25 AND 36 ORDER BY age DESC LIMIT 10  -- → "Ada"
SELECT name FROM users WHERE city IN ('Leeds', 'İzmir') AND name LIKE 'C%'  -- → "Cem"
SELECT count(*) AS n FROM users WHERE email ILIKE '%@EXAMPLE.COM'  -- → 3
UPDATE users SET visits = coalesce(visits, 0) + 1 WHERE name = 'Ada' RETURNING visits  -- → 1
INSERT INTO users (email, visits) VALUES ('ADA@example.com', 1) ON CONFLICT DO UPDATE SET visits = users.visits + excluded.visits RETURNING visits  -- → 2
INSERT INTO users (email) VALUES ('bob@example.com') ON CONFLICT DO NOTHING  -- → 0
INSERT INTO users (email, visits) VALUES ('bob@example.com', 1) ON CONFLICT (email) DO UPDATE SET visits = excluded.visits RETURNING visits  -- → 1
SELECT name || ' <' || email || '>' AS who FROM users WHERE id IN (SELECT id FROM users WHERE age > 30) ORDER BY id  -- → "Ada <ada@example.com>"
SELECT name FROM users WHERE visits IS DISTINCT FROM 2  -- → "Bob"
SELECT city, count(*) AS n FROM users GROUP BY 1 ORDER BY 2 DESC, 1  -- → "Leeds"
CREATE TABLE orders (id serial PRIMARY KEY, user_id integer NOT NULL, total integer, status text)
CREATE INDEX ON orders USING hash (user_id)
INSERT INTO orders (user_id, total, status) VALUES (1, 1200, 'paid'), (1, 800, 'open'), (2, 450, 'paid')  -- → 3
SELECT u.name, o.total FROM orders o JOIN users u ON u.id = o.user_id WHERE o.status = 'paid' ORDER BY o.total DESC  -- → "Ada"
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id ORDER BY u.id DESC  -- → "Cem"
SELECT count(*) AS n FROM orders o JOIN users u ON u.id = o.user_id WHERE u.city = 'London'  -- → 2
SELECT DISTINCT u.city FROM users u JOIN orders o ON o.user_id = u.id ORDER BY u.city  -- → "Leeds"
SELECT name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)  -- → "Cem"
SELECT name FROM users WHERE age = (SELECT max(age) FROM users)  -- → "Cem"
DELETE FROM users WHERE age NOT BETWEEN 20 AND 40  -- → 1
CREATE TABLE docs (id serial PRIMARY KEY, body text, emb vector(3))
CREATE INDEX ON docs USING hnsw (emb vector_cosine_ops)
INSERT INTO docs (body, emb) VALUES ('north', [0, 1, 0]), ('east', [1, 0, 0]), ('up', [0, 0, 1])  -- → 3
SELECT body FROM docs ORDER BY emb <=> '[0.9, 0.1, 0]' LIMIT 1  -- → "east"

email is citext, a nocase field, so its =, its @unique and its LIKE compare by the case fold, and 'ADA@example.com' is Ada's row. Each join reads as the lookup it is, or as one table's rows filtered by the other's (below); the last statement is near, by the cosine distance the index measures.

Read as written

Each line is the SQL and the FenecQL it is — the same statement, to the last expression.

Joins

A JOIN on a key is a lookup from each row of the first table to its matches in the next, answered as SQL answers it: flat rows, a row a pair — and, for a LEFT JOIN, a row of nulls where nothing matched — the columns the list names under SQL's names. Where only one table's columns are answered and each of its rows is answered once — the other table found by its id, or DISTINCT — it is that table's get, the other's rows a filter. Each line below is the FenecQL it parses to; a lookup's line answers flat, which no FenecQL text asks for.

What a join is read as, and what it cannot be:

• The key. ON pairs a column of the joined table with one of the table joined just before it — several, joined by AND, are a key over several fields. Where the answer is the joined rows beside each row — read as a lookup — the joined table is read by its column, which must be its id, a field with @hash or the first fields of an index over several, as a lookup's child key must be; anything else is refused naming the index to declare. Where only one table's columns are answered and the other is a filter, key in (get …), no index is needed, and a small joined table is answered through the first table's index — that list is held to 100 000 values, as any in (get …) is. A join that finds the joined table by its id looks each row's match up by that id past them, at any size, whichever table comes first and with the joined table filtered, where the other key is an id, an int, a float or a timestamp; an aggregate, DISTINCT, EXISTS or LEFT JOIN … IS NULL over a larger joined table is refused, never cut. Its other conditions read one table's columns. A comma between tables is a join whose key is in the WHERE, and USING (k) pairs k of each.
• The names. A column is named by its table's name or alias, o.total; a name with no table is the first table's — the parser reads no schema, and SQL would have refused one two tables hold — and one the first table has no field of is refused, saying to name it with its table. A column no table has is refused, as SQL refuses it, wherever the statement names it — an ON term too — and so is a table no database holds. A column answers under its name, total, or its AS; two that would answer under one name — every table's id under SELECT * — are refused, since a row holds a name once: SELECT o.*, u.name AS who.
• The order and the page are the first table's, whose rows the others are found for: ORDER BY names its columns, and LIMIT counts the flat rows, the first table read as far as the page could need. Ordering by a joined table's column would put each row's matches in order rather than the rows: refused, saying to name that table first.
• Aggregates and DISTINCT read one table's columns, the other's rows a filter, where each row of it is counted once: folded over pairs, a row matching three times would be counted three times. Grouping one table's rows with another's column beside each group is a lookup after the groups: get orders select user_id, count(*) as n group user_id lookup users on id = user_id select name.
• A LEFT JOIN's table in the WHERE reads the rows it kept with nulls: o.id IS NULL is the rows with none, and anything else is refused — a condition on its rows goes in ON, and one that drops the rows kept with nulls makes it an inner join, written JOIN. A condition in a LEFT JOIN's ON on a table before it keeps that table's rows and drops only their matches, which no lookup says: refused. An inner join after a LEFT JOIN, a RIGHT, FULL or CROSS JOIN and a condition between two tables' columns other than the key are refused.

Refused, and what to write

Each is refused with the FenecQL in its error.

Not read, with no error of its own: WITH, window functions, CAST and ::, LIMIT x, y, UNION, INSERT OR IGNORE and REPLACE, MySQL's ON DUPLICATE KEY, ? placeholders ($1 is a parameter).

Where the meaning is not SQL's

Nulls: two values, not three

SQL's logic has three values: a comparison with a null is unknown, and WHERE keeps only what is true. FenecQL's has two. A null is equal to a null and to nothing else, so = and != take it as a value — they are SQL's IS NOT DISTINCT FROM and IS DISTINCT FROM — while <, <=, >, >=, LIKE and BETWEEN are false with a null on either side, and NOT makes a false true. Where SQL's unknown leaves a row out, FenecQL may take it in:

Add AND x IS NOT NULL to leave out what SQL leaves out. Arithmetic and || with a null are null, as in SQL; aggregates skip nulls, as in SQL — count(x) counts the rows where x is not null — and sum over none is null. A null sorts first ascending and last descending, as in SQLite and MySQL — PostgreSQL's default is the other way round — so NULLS FIRST is read after ASC and NULLS LAST after DESC, and either other way is refused. x LIKE 'a%' in a select list is null for a null.

<> is !=, null and all. One could read <> as SQL's and keep != FenecQL's, but not exactly: SQL's unknown runs through every NOT above it, which no rewrite of the one comparison carries, so the two would part somewhere else instead. Two operators that differ on null alone would be the subtler trap, and FenecQL read <> as != before it read any other SQL, so a text that used it would change its answer. One meaning, then, written here.

Double quotes are text

In SQL "name" is a column; in FenecQL it is text, as 'name' is. Where only a name can stand — a collection, the fields of an INSERT, a field SET assigns, RETURNING, an index's fields, a facet — "name" is the name, as in SQL. Where a value can stand it is text, as it always was: where title = "Rust". Where SQL would read a column and text would make the answer the same for every row, it is refused rather than answer all the rows or none:

• alone on the left of a test against values: "name" = 'Ada', "age" > $1, "x" IS NULL, "x" IN (1, 2), "x" LIKE 'a%', "x" BETWEEN 1 AND 2;
• on the right of a test against a literal: 'Ada' = "name";
• an item of a select list, a key of ORDER BY or GROUP BY, an aggregate's argument: SELECT "name", max("at").

A parameter tested against double-quoted text stays a test: a policy's $jwt.role = "admin". Text on the right of a field stays text: UPDATE t SET a = "b" sets a to the text b, where SQL would copy the column b.

~ is contains

In PostgreSQL ~ matches a regular expression; in FenecQL it finds the text as written, case-insensitively: email ~ "@example.org". Written as a pattern, it would run and match nothing — and an agent that ran email ~ '@example\.org$' told its user there were no such rows. So a text that reads as a regular expression — anchored, ^a or a$, or holding .* or .+ — is refused, pointing at LIKE: like 'abc%' for a start, like '%abc' for an end, like 'a%b' for a gap. A parameter is taken as the text it is.

LIKE and ILIKE

% stands for any run of characters and _ for one character — of however many bytes. There is no escape character unless ESCAPE names one, as in standard SQL and SQLite; PostgreSQL's default is \, and a FenecQL string reads \ as an escape of its own, so '50\%' is the text 50%: write LIKE '50!%' ESCAPE '!'. A pattern that ends in its escape is refused.

A nocase field compares by its case fold wherever it is compared, LIKE too, as PostgreSQL's citext does. ILIKE compares the case folds — Unicode's full fold, the one nocase uses — so 'Straße' ILIKE 'STRASSE', and _ stands for one character of the fold. Its escape takes no character with a case. A collated field's LIKE compares its bytes, as its = does. ~, FenecQL's own, is a case-insensitive contains.

LIKE reads every row the rest of the filter leaves: no index answers it. A prefix over a @sorted field walks its index written as a range, name >= 'abc' and name < 'abd'.

An insert names its fields

INSERT INTO t VALUES (…) without its fields is refused. The parser reads no schema, and a collection's fields have no order a statement could lean on: id is the database's and no field's, and a field added goes last. Each row of VALUES has as many values as there are fields named. An id among them names the row's id; one that is taken is refused, as a key is, unless ON CONFLICT says otherwise.

ON CONFLICT

FenecQL's upsert is held by any unique key — the id, or a @unique value, a field's or several fields' — and ON CONFLICT is read with no target, as SQLite reads it: DO NOTHING passes a row over where any of them is held, and DO UPDATE SET sets the row holding its id, or else the first @unique value it holds, in the order the fields are declared. A target, ON CONFLICT (email), is taken where it names the one unique key the collection's rows are found by — its @unique field, the fields of a unique index over several, or id where there is no other — and refused, naming the keys, where the collection has another or a row names an id beside it: the target and the upsert, held by any, would part there. In DO UPDATE SET, excluded.f is the row offered (new.f) and t.f, or f, the row held; a WHERE is refused.

Text, ||, numbers

• || is +. It joins two texts, and a text and a number are refused rather than made text: 'n' || 1 is an error where PostgreSQL gives n1. MySQL's ||, an OR, is not read.
• Text compares by its bytes, as COLLATE "C" does. A field declared collate tr or und orders by that collation, and its = and LIKE compare bytes — see Collation.
• Division between integers is whole, toward zero: 7 / 2 is 3, as in PostgreSQL and SQLite (MySQL's is 3.5); an integer and a float make a float.
• Every row has an id, an int the database makes and no field declares; /_schema?as=fenecql and the MCP schema tool say so in a comment. rowid and _id are refused naming it.
• Errors name what to write. contains(…), starts_with(…) and ends_with(…) are answered with LIKE; users.email — a column named with its table — with the field written bare.

DISTINCT, EXISTS and a query's one value

SELECT DISTINCT a, b is a group of its items, which answers each set of their values once, as does a GROUP BY with no aggregate; ORDER BY then names the list's columns, as SQL requires of DISTINCT. Over an aggregating list whose keys it lists it changes nothing, and DISTINCT ON is refused: first(name by signup) is the row of each key it asks for. ORDER BY 2 and GROUP BY 1 name the list's items by their places — written with BY, as SQL writes them: in FenecQL's order 1 and group 1 the number is a key, as it always was. Over *, whose places the parser reading no schema cannot count, a number is refused, saying to name the columns; one naming an aggregate as a group's key is refused as SQL refuses it.

EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND …) takes the one equality between a column of its query's table and one of the row around it as the list an in (get …) holds, and the rest of its WHERE as that query's: u.id in (get orders select user_id where …). A row whose key is null has no match — so NOT EXISTS keeps it, as in SQL — and a name with no table is the query's own, as SQL reads it first. Correlated any other way, it is refused. = (SELECT …) is the same list where its query answers one row — an aggregate with no group, or LIMIT 1 — and refused where it may answer several, which SQL would refuse at the second; where it answers none, SQL's comparison is unknown and FenecQL's in false, the same under NOT as NOT IN.

CREATE TABLE

Each column's type is the one fenec import gives a column of it — one table for both, PostgreSQL's names and then SQLite's affinity rules for any other word:

• The row's id is an int the database makes: a column id of an integer type that is the primary key — serial, generated … as identity, AUTOINCREMENT — is it, and is no field. An id of another type, or that is no primary key, is refused.
• A primary key on other columns is its two rules, @unique and required, over several columns an index over them — beside the row's id, which every row still has. ON CONFLICT (k) finds a row by it.
• NOT NULL is required: a write must give the field a value, and a null is none. UNIQUE is @unique, COLLATE NOCASE nocase, COLLATE "C" the bytes' order.
• A type's own rules are not held, as SQLite holds none: varchar(80) takes a longer text, and char(n) is not padded with spaces; a uuid is text as written, its case kept, where PostgreSQL writes it lowercase; a date is a timestamp, which keeps a time written with it; smallint and integer take any 64-bit int, past their 16 and 32 bits; real is a 64-bit float, more precise than its 32 bits.
• What fenecdb has no rule of is refused rather than dropped, which would take writes SQL refuses: DEFAULT, REFERENCES and FOREIGN KEY, CHECK, a computed column, a sequence on another column. A key a join reads is a field with @hash — CREATE INDEX … USING hash.
• CREATE INDEX keeps no name. A btree is @sorted (UNIQUE, @unique), over several columns an index over them; USING hash @hash; USING hnsw @hnsw by its operator class — vector_l2_ops, pgvector's default, vector_cosine_ops, vector_ip_ops — with WITH (m, ef_construction), held to pgvector's bounds; USING gin (to_tsvector(…, body)) @text. A partial index, an expression's and ivfflat, gist or brin have no counterpart.

pgvector's distances

ORDER BY emb <-> $1 LIMIT 10 is near emb $1 limit 10, nearest first, where the field's index measures the operator's distance: <-> L2 over @hnsw(l2), <=> cosine over @hnsw(cosine), <#> the negative inner product over @hnsw(dot) or a sparse field's @inverted. Another index's order would be another, and is refused naming the operator to write. The vector is a parameter, a list, or pgvector's text '[1, 2, 3]'. The search walks the graph, as pgvector's HNSW index scan does: the nearest are found approximately, and near … exact measures every row.

Each row's distance is in its _score, turned to a similarity: for <-> it is the L2 distance itself, for <=> 1 minus the cosine distance — the cosine similarity — and for <#> the inner product, minus pgvector's value. The operator in the list or the WHERE, DESC and a second key are refused: near ranks the rows, and the distance is the score.