SQL language reference

Varc accepts exactly one SELECT, UPDATE, EXPLAIN SELECT, or EXPLAIN UPDATE statement over one logical table:

redis(key, type, redis_type, ttl_ms, value)

Each row represents one Redis key. A hash with one field and a hash with one million fields both occupy one SQL row; collections are never expanded into rows.

Columns

Column	SQL shape	Meaning
key	Binary, never NULL	The exact binary-safe Redis key.
type	Text, never NULL	Varc’s normalised type: string, hash, list, set, zset, stream, json, or unknown.
redis_type	Text, never NULL	The raw server type, such as ReJSON-RL for a RedisJSON document.
ttl_ms	Integer or NULL	Remaining lifetime in milliseconds. NULL means the key is persistent.
value	Lazy Redis value	A bounded string preview or an opaque reference to a collection or JSON value.

Use SELECT * to project all five columns. You can qualify a column with redis, or assign a table alias:

SELECT r.key, r.type
FROM redis AS r
WHERE r.ttl_ms IS NOT NULL
LIMIT 100;

Aliases may name projected expressions and can be used by ORDER BY. A table alias cannot rename the table’s columns.

UPDATE

UPDATE selects key rows from the same logical table and commits each matched key immediately:

UPDATE redis
SET value = 'enabled', ttl_ms = 60000
WHERE key LIKE 'feature:%'
RETURNING key, type, ttl_ms, value
LIMIT 100;

The supported shape is:

UPDATE redis
SET assignment [, assignment ...]
[WHERE predicate]
[RETURNING expression [, expression ...]]
[LIMIT non_negative_integer];

key, value, and ttl_ms are mutable. type and redis_type are derived. A target column cannot be assigned twice, and tuple assignments are rejected. Every right-hand side evaluates from the original row.

• key requires a text or binary scalar expression.
• value accepts a non-NULL Boolean, integer, finite real, text, or binary scalar expression, or one top-level collection mutation function.
• ttl_ms accepts an integer or NULL. Positive values set a relative millisecond expiry, NULL persists the key, and zero or negative values expire it immediately.

WHERE is optional. LIMIT counts selected update attempts in traversal order; it is not a returned-row limit. RETURNING uses the normal projection language and reads the committed value at the final key name. Without RETURNING, the results pane reports the affected-key count.

There is no implicit predicate or limit. The first failed row stops execution and returns the number of earlier committed keys; those earlier writes are not rolled back. See Update keys with SQL for execution and Cluster behavior.

Supported syntax

Area	Supported forms
Projection	Expressions, *, AS aliases, scalar DISTINCT
Predicates	WHERE, parentheses, AND, OR, NOT
Comparison	=, <>, <, <=, >, >=, BETWEEN, NOT BETWEEN
Arithmetic	+, -, *, /, integer %, unary +/-
Concat	Binary-safe ||
Conditional	Searched and simple CASE with optional ELSE
Matching	LIKE, NOT LIKE, optional ESCAPE, IN, NOT IN
NULL	IS NULL, IS NOT NULL, SQL three-valued logic
Conversion	CAST and TRY_CAST to text, integer, real, or binary
Aggregation	COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING
Ordering	ORDER BY, ASC, DESC, NULLS FIRST, NULLS LAST
Bounds	A non-negative integer literal in LIMIT
Mutation	UPDATE redis SET ... [WHERE ...] [RETURNING ...] [LIMIT]
Explain	Plain EXPLAIN SELECT or EXPLAIN UPDATE

Keywords, table names, column names, and function names are case-insensitive. String and binary comparisons remain byte-exact.

Literals and binary data

Varc supports text, binary, integer, real, Boolean, and NULL literals:

SELECT key
FROM redis
WHERE key IN ('user:1', 'user:2', X'00FF')
  AND ttl_ms >= 1000
LIMIT 20;

• 'text' is encoded as UTF-8 bytes.
• X'00FF' represents arbitrary bytes. Hex must contain an even number of digits.
• Integers are signed 64-bit values. Reals must be finite.
• TRUE, FALSE, and NULL use their SQL meanings.
• Unary + and - apply to numeric expressions. Overflow and non-finite results fail with SQL_ARITHMETIC.
• An IN list contains 1 to 1,000 constant values.

Exact key = ... and key IN (...) predicates can use direct key lookups. They do not require you to change the SQL you write.

LIKE and ESCAPE

LIKE operates over bytes: % matches any byte sequence and _ matches one byte. Use a one-byte text literal with ESCAPE when a wildcard should be literal:

SELECT key
FROM redis
WHERE key LIKE 'invoice!_%' ESCAPE '!'
LIMIT 100;

That pattern requires the key to begin with the literal bytes invoice_. A pattern cannot end with an unmatched escape byte. Varc safely translates eligible key patterns for Redis scanning without giving Redis glob characters accidental meaning.

Values, comparisons, and NULL

Redis strings participate directly in equality, ordering, LIKE, and casts. A collection or JSON document is an opaque value; use a type-aware SQL function to inspect it.

SQL three-valued logic applies. Comparisons with NULL produce unknown, so they do not pass WHERE. Use IS NULL or IS NOT NULL when the absence of a value matters.

ttl_ms IS NULL means the key is persistent. Missing function values also commonly return NULL. A value that cannot be evaluated safely because it changed or exceeded a transfer boundary is indeterminate, which Varc counts separately instead of treating it as a definitive false match.

CAST and TRY_CAST

Supported target names are:

• text: TEXT or VARCHAR;
• integer: INTEGER, INT, or BIGINT;
• real: REAL, DOUBLE, or FLOAT; and
• binary: BINARY, VARBINARY, BLOB.

SELECT key, TRY_CAST(value AS INTEGER) AS numeric_value
FROM redis
WHERE type = 'string'
  AND TRY_CAST(value AS INTEGER) >= 100
LIMIT 100;

CAST fails the query when a value cannot be converted. TRY_CAST returns NULL. Casting bytes to text requires valid UTF-8; real values must remain finite.

Arithmetic, concatenation, BETWEEN, and CASE

Integer arithmetic is checked 64-bit arithmetic. Division truncates toward zero. A Real operand promotes the operation to a finite Real. Overflow, division by zero, MIN / -1, and non-finite results fail with SQL_ARITHMETIC. % requires Integer or NULL operands. NULL and indeterminate values propagate.

|| concatenates complete reads under the transfer ceiling. Text plus text stays text. Binary or Redis-string participation returns binary. Opaque collections return NULL.

BETWEEN and NOT BETWEEN are inclusive. The subject is evaluated once and compared with existing three-valued logic.

CASE supports searched (CASE WHEN … THEN …) and simple (CASE expr WHEN … THEN …) forms. The operand and selected result are evaluated once. An evaluated indeterminate WHEN condition propagates. Missing ELSE returns NULL. Compatible branches unify: NULL is a wildcard, Integer plus Real becomes Real, and Text plus Binary becomes Binary.

Operator precedence follows SQL: parentheses, unary plus/minus, * / %, + - ||, comparisons and BETWEEN, NOT, AND, then OR. CASE is an expression.

GROUP BY, HAVING, and aggregates

Execution order is WHERE → grouping/aggregation → HAVING → projection → ORDER BY → LIMIT.

GROUP BY accepts at most 32 explicit scalar, non-opaque expressions. Aliases, ordinals, ALL, ROLLUP, CUBE, and grouping sets are rejected. Every non-aggregate expression in the SELECT list, HAVING, or ORDER BY must be constant or structurally identical to a grouping expression. Projection aliases remain valid only in ORDER BY.

LIMIT is applied after grouping. Aggregate queries always traverse the complete candidate source; LIMIT never stops discovery early.

EXPLAIN

Plain EXPLAIN SELECT and EXPLAIN UPDATE return sanitized semantic plans. They perform no Redis I/O, capability probing, or query-run allocation. A SELECT plan describes access, reads, local work, traversal, grouping, sorting, and qualitative impact. An UPDATE plan also identifies same-slot requirements and row-at-a-time atomic execution. Neither form includes Redis command names, arguments, literals, endpoints, topology identifiers, implementation names, or expression trees.

EXPLAIN ANALYZE, VERBOSE, QUERY PLAN, ESTIMATE, format clauses, options, DESCRIBE, nested EXPLAIN, and table-only EXPLAIN are rejected.

ORDER BY, DISTINCT, and LIMIT

ORDER BY accepts up to 32 expressions. It can use a projected alias and can place NULL values explicitly:

SELECT key, JSON_VALUE(value, '$.age') AS age
FROM redis
WHERE type = 'json'
ORDER BY age DESC NULLS LAST
LIMIT 50;

Without an explicit NULL modifier, descending order puts NULL first and ascending order puts NULL last.

DISTINCT removes duplicate projected scalar rows. ORDER BY and DISTINCT must see the complete candidate source before Varc can call the result globally ordered or distinct. Neither may operate directly on opaque collection or JSON values; first project a scalar function result.

LIMIT must be a non-negative integer literal. Varc stops early when semantics permit. Numeric OFFSET is not supported because resumable result cursors, rather than unstable offsets, page a running keyspace query.

Unsupported SQL

The following are rejected with source-ranged diagnostics:

• INSERT, DELETE, DDL, and every mutating statement other than the documented UPDATE form;
• UPDATE hints, FROM, OUTPUT, conflict clauses, ORDER BY, tuple assignments, duplicate targets, table aliases, and assignments to type or redis_type;
• SET value = NULL, opaque value assignments, mutation functions outside the top level of SET value = ..., and invalid mutation signatures;
• multiple statements in one editor run;
• joins, subqueries, common table expressions, unions, and other set operations;
• named windows, window functions, and QUALIFY;
• aggregate DISTINCT, FILTER, ordered or named aggregate arguments, OVER, and WITHIN GROUP;
• GROUP BY ALL, aliases or ordinals in GROUP BY, ROLLUP, CUBE, and grouping sets;
• scalar SELECT DISTINCT mixed with aggregates;
• numeric OFFSET, FETCH, locks, query settings, output-format, and pipe clauses;
• TOP, INTO, PREWHERE, lateral views, CONNECT BY, CLUSTER BY, DISTRIBUTE BY, and SORT BY;
• table-valued functions, collection expansion, ordinality, hints, index hints, partitions, paths, samples, and table versions;
• table-alias column lists, wildcard modifiers, EXCLUDE, select modifiers, optimiser hints, and value-table modes;
• DISTINCT ON, ORDER BY ALL, ORDER BY ... WITH FILL, and LIMIT BY;
• LIKE ANY, non-constant IN values, an empty IN list, or more than 1,000 IN values;
• EXISTS, array, interval, and every other expression form not listed as supported above;
• EXPLAIN ANALYZE and every EXPLAIN option other than a single SELECT or UPDATE;
• ODBC function syntax, function parameters, FILTER, null treatment, OVER, WITHIN GROUP, named function arguments, and ordered or DISTINCT function arguments;
• cast arrays, cast formats, or cast targets other than the documented scalar types;
• unknown tables, columns, functions, operators, cast types, or invalid function signatures; and
• more than 256 projected expressions, more than 32 order expressions, or more than 32 grouping expressions.

RediSearch tables, inferred namespace tables, write SQL other than UPDATE, export, collection expansion, and Redis module types other than RedisJSON are not part of this SQL dialect.