Querying

Vectors inside SQL

Inline vector literals

Any expression can contain an inline vector literal. The syntax mirrors Python list notation:

SELECT id, COSINE_SIM(embedding, [0.1, 0.2, 0.3]) AS score FROM docs
SELECT id, VEC_ADD(v, [0.0, 0.0, 1.0]) AS shifted FROM vecs
SELECT L2_NORM([3.0, 4.0]) AS five  -- FROM is optional for a literal/computed-only SELECT

Vector scalar functions

Usable inside SELECT columns, WHERE predicates, or ORDER BY:

Function	Signature	Returns	Description
NORMALIZE(v)	Vector → Vector	Unit vector	Scales v to L2 norm = 1
L2_NORM(v)	Vector → Float	Euclidean length	√(∑ vᵢ²)
COSINE_SIM(a, b)	Vector, Vector → Float	[-1, 1]	dot(a,b) / (‖a‖·‖b‖)
DOT(a, b)	Vector, Vector → Float	Scalar	Dot product ∑ aᵢbᵢ
DISTANCE(a, b)	Vector, Vector → Float	Euclidean distance	Magnitude-sensitive, unlike COSINE_SIM
VEC_ADD(a, b)	Vector, Vector → Vector	Same dim	Element-wise addition
VEC_SCALE(v, s)	Vector, Float → Vector	Same dim	Multiply all elements by s
MAT_SHAPE(m)	Matrix → String	e.g. "2x2"	Shape as "rows x cols"
MATMUL(a, b)	Matrix, Matrix/Vector → Matrix/Vector	Product	Matrix multiplication
TRANSPOSE(m)	Matrix → Matrix	Swapped dims	Transpose

SELECT id, title, COSINE_SIM(embedding, [0.9, 0.1, 0.0]) AS score
FROM docs
WHERE COSINE_SIM(embedding, [0.9, 0.1, 0.0]) > 0.7
ORDER BY score DESC
LIMIT 10

COSINE_SIM is angle-only, not magnitude-aware: [1, 1, 1] and [1000, 1000, 1000] score a perfect 1.0. That’s correct for pre-normalized semantic embeddings, but the wrong tool when your vector’s components share a physical scale (masses, prices, counts). Use DISTANCE instead when magnitude itself carries the signal.

Complex number functions

Complex (see Data & Resources) is a scalar type, usable anywhere any other scalar is:

Function	Signature	Returns	Description
COMPLEX(re, im)	Float, Float → Complex	re + im·i	Construct a complex value (no dedicated literal syntax)
REAL(z)	Complex → Float	Real part
IMAG(z)	Complex → Float	Imaginary part
ABS(z)	Complex → Float	Magnitude	√(re² + im²), named ABS, not MAGNITUDE, to avoid confusion with the unrelated tensor-DSL MAGNITUDE keyword (FFT spectrum magnitude, a different operator on a different type)
PHASE(z)	Complex → Float	Angle in radians	atan2(im, re)
CONJ(z)	Complex → Complex	re - im·i	Complex conjugate

SELECT COMPLEX(3.0, 4.0) AS z, ABS(COMPLEX(3.0, 4.0)) AS magnitude   -- magnitude = 5.0
SELECT z, REAL(z), IMAG(z), CONJ(z) FROM eigen_results

Arithmetic (+/-/*//) promotes any operand pairing that touches Complex to Complex (the same promote-and-never-demote convention Float64 uses, but checked first since Complex is strictly wider). SUM/AVG (plain and windowed) work correctly on Complex columns; MIN/MAX/ORDER BY error loudly instead of returning a silently-wrong answer, since Complex has equality but no total order.

Vector aggregate functions

Element-wise statistics across all rows in a group:

Function	Description
AVG_VEC(col)	Element-wise average (the centroid of all vectors in the group)
SUM_VEC(col)	Element-wise sum across all vectors in the group

-- Per-category centroids
SELECT category, AVG_VEC(embedding) AS centroid FROM docs GROUP BY category

SELECT

SELECT region, AVG(score)
FROM diagnostics
WHERE id > 100
GROUP BY region
HAVING AVG(score) > 0.5
LIMIT 10

• Aggregates: SUM, AVG, COUNT, MIN, MAX, AVG_VEC, SUM_VEC. An aggregate with no GROUP BY computes one global row (SELECT COUNT(*) FROM t).
• HAVING resolves an aggregate by alias too: ... AVG(score) AS avg_score ... HAVING avg_score > 0.5 works the same as HAVING AVG(score) > 0.5.
• WHERE/FILTER are interchangeable.
• DISTINCT: SELECT DISTINCT <cols> FROM ... removes duplicate rows.
• LIMIT/OFFSET: OFFSET skips rows before LIMIT is applied; use together or independently.
• FROM is optional for a SELECT list of only literals/computed expressions (no column/aggregate/window reference): SELECT L2_NORM([3.0, 4.0]) AS five evaluates once. Any real column reference still requires FROM.

Predicates (in WHERE/FILTER/HAVING):

SELECT * FROM items WHERE category IN ('a', 'b', 'c')
SELECT * FROM items WHERE price BETWEEN 5 AND 25
SELECT * FROM items WHERE tag IS NULL
SELECT * FROM items WHERE tag IS NOT NULL
SELECT * FROM items LIMIT 10 OFFSET 20

Automatic partition pruning: a col <op> literal predicate (either operand order) or col BETWEEN low AND high, against a column with no index at all, is optimized automatically once the dataset spans more than one internal 1024-row partition. Each partition tracks its own column min/max, and a partition that can’t satisfy the predicate is skipped without reading its rows. No CREATE INDEX, no special syntax; results are identical with or without it.

Subqueries in FROM

SELECT * FROM (SELECT id, price FROM items WHERE price > 5) AS cheap

FROM (<SELECT>) AS <alias> runs the inner query first and treats its result as the outer query’s source, referenced by <alias>.

INSERT / UPDATE / DELETE

-- Positional, in column-declaration order
INSERT INTO users VALUES (1, "alice", 30, true)

-- Named, any order, only the columns you specify
INSERT INTO users (id = 1, name = "alice", active = true)

-- Vector / Matrix literals work in either form
INSERT INTO docs VALUES (1, [0.1, 0.2, 0.3])
INSERT INTO grids (id = 1, m = [[1, 0], [0, 1]])

UPDATE users SET active = false, name = "bob" WHERE id = 1

DELETE FROM users WHERE active = false

UPDATE ... SET takes one or more comma-separated col = expr assignments and an optional predicate (omitting it updates/deletes every row).

JOIN

SELECT o.id, u.name FROM orders o JOIN users u ON o.user_id = u.uid

SELECT * FROM a LEFT JOIN b ON a.key = b.key
SELECT * FROM a RIGHT JOIN b ON a.key = b.key
SELECT * FROM a FULL JOIN b ON a.key = b.key

-- Index-accelerated similarity join: joins on cosine similarity instead of
-- equality, using a vector index on the right side when one exists
SELECT aid, bid FROM a JOIN b ON COSINE_SIM(a.v, b.v) > 0.8

• Kinds: [INNER] JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN. Multiple JOINs may be chained on one SELECT.
• ON supports scalar equality, or (for two Vector columns) COSINE_SIM(<left>, <right>) > <threshold> (no other comparison operator is supported for the similarity form yet).
• An alias (FROM orders o / FROM orders AS o) works anywhere a column is referenced, but the qualifier doesn’t disambiguate: only the bare column name resolves, so column names must stay unique across the joined datasets.
• An unaliased expression in the SELECT list gets an auto-generated name (__cmp_0, …); give it an explicit AS name if you need a predictable one.

CTEs & UNION

WITH recent AS (SELECT * FROM events WHERE ts > 100) SELECT * FROM recent WHERE user_id = 1

WITH cte_a AS (SELECT * FROM t1), cte_b AS (SELECT * FROM t2) SELECT * FROM cte_a

SELECT id FROM users_a UNION SELECT id FROM users_b       -- deduplicates
SELECT id FROM users_a UNION ALL SELECT id FROM users_b   -- keeps duplicates

WITH <name> AS (<SELECT>), ... materializes each CTE as a temporary dataset before the main query runs, removed once the statement completes. Avoid reusing a real dataset’s name. UNION/UNION ALL chain freely (A UNION B UNION C).

Keep the whole WITH ... SELECT ... on one line in .lnl files or the REPL: a WITH clause’s trailing SELECT has to be part of the same statement, but splitting it across lines (WITH recent AS (\n ...\n)\nSELECT ...) doesn’t work: the file runner’s line joiner only tracks paren balance, and the WITH clause’s own parens close before reaching the trailing SELECT, so it’s treated as two separate (and the first, invalid) statements. Verified directly: this is a real gap in the CLI’s statement-joining, not a language limitation; the same multi-line form works fine sent as one string over /execute.

Window functions

SELECT id, price, ROW_NUMBER() OVER (ORDER BY price DESC) AS rn FROM items

SELECT id, price, category,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rk
FROM items

SELECT id, price, LAG(price) OVER (ORDER BY id) AS prev_price FROM items
SELECT id, price, LEAD(price, 2) OVER (ORDER BY id) AS next2_price FROM items

-- Combine freely in one SELECT
SELECT id, price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS rn,
       SUM(price) OVER (PARTITION BY category ORDER BY id) AS running_total
FROM items

• Ranking: ROW_NUMBER(), RANK(), DENSE_RANK() (no arguments).
• Offset: LAG(col [, offset]), LEAD(col [, offset]) (offset defaults to 1).
• Aggregate-as-window: SUM/AVG/COUNT/MIN/MAX/SUM_VEC/AVG_VEC followed by OVER (...) computes a running aggregate instead of collapsing to one row.
• OVER (...) takes an optional PARTITION BY col [, ...] and ORDER BY col [ASC|DESC] [, ...]. ORDER BY on a Vector/Matrix column inside OVER (...) is rejected: those types have no defined ordering.
• Default column names are easy to get wrong (row_number, rank, lag, sum(expr)_over, …). Always give an explicit AS alias.

CASE, COALESCE, NULLIF, CAST

SELECT id, CASE WHEN score > 90 THEN "A" WHEN score > 80 THEN "B" ELSE "C" END AS grade FROM students
SELECT id, CASE status WHEN 1 THEN "active" WHEN 0 THEN "inactive" ELSE "unknown" END AS label FROM accounts

SELECT id, COALESCE(nickname, name, "anonymous") AS display_name FROM users
SELECT id, NULLIF(score, 0) AS score_or_null FROM results   -- NULL if score = 0
SELECT id, CAST(price AS INT) AS price_int FROM items

-- Reshape a Vector/Matrix column inline
SELECT id, CAST(flat_embedding AS MATRIX(2, 2)) AS as_matrix FROM t
SELECT id, FLATTEN(grid) AS flattened FROM t

• CASE [operand] WHEN <cond> THEN <expr> ... [ELSE <expr>] END: with an operand, each WHEN is compared for equality; without one, each WHEN is a standalone boolean condition.
• COALESCE(a, b, ...) returns the first non-NULL argument. NULLIF(a, b) (alias IFNULL) returns NULL if a = b, else a.
• CAST(expr AS <type>) supports these scalar targets: INT/INTEGER, FLOAT/FLOAT32, DOUBLE/FLOAT64, TEXT/STRING/VARCHAR, BOOL/BOOLEAN.
• CAST(expr AS VECTOR(n)) / CAST(expr AS MATRIX(r, c)) reshapes row-major; source and target must have the same total element count, or the result is NULL (never a resize or error). This is how you reshape to an arbitrary shape inside a query. The standalone RESHAPE keyword only works on tensor variables outside SELECT.
• FLATTEN(expr) also works inside SELECT: flattens a Matrix row-major into a Vector, or is a no-op on an already-flat Vector.

String functions

Function	Signature	Description
UPPER(s)	String → String	Uppercase
LOWER(s)	String → String	Lowercase
LENGTH(s)	String → Int	Character count
TRIM(s)	String → String	Strip leading/trailing whitespace
CONCAT(a, b, ...)	String... → String	Concatenate 2+ strings
SUBSTR(s, start [, len])	String, Int, Int? → String	1-based substring; omit len for the rest of the string

SELECT SUBSTR(name, 1, 3) AS prefix, UPPER(TRIM(email)) AS clean_email FROM users