SELECT

Gnok's SELECT supports the full range of analytical SQL — joins, subqueries, CTEs, window functions, grouping sets, pivots, and time travel. Queries execute in parallel across workers with automatic optimization (predicate pushdown, dynamic filtering, late materialization).

Basic Syntax

SELECT [DISTINCT | DISTINCT ON (expr [, ...])] [TOP n] expression [, ...]
       [* EXCEPT (col [, ...])] [* EXCLUDE (col [, ...])]
FROM table_reference [, ...]
[WHERE condition]
[GROUP BY expression [, ...] | ROLLUP (...) | CUBE (...) | GROUPING SETS (...)]
[HAVING condition]
[ORDER BY expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...] | ORDER BY ALL]
[LIMIT count]
[OFFSET start];

ORDER BY ALL (DuckDB-compatible) sorts by every column in the final SELECT list, in left-to-right order. The ASC / DESC / NULLS FIRST / NULLS LAST modifiers may be applied to the ALL keyword and propagate to every implied key.

`SELECT TOP N` (T-SQL)

SELECT TOP N ... is the T-SQL row-cap idiom. Gnok binds it as LIMIT N at parse time. Earlier the top clause was silently ignored — SELECT TOP 2 * FROM orders returned all rows. Now the cap is honoured; the PERCENT and WITH TIES modifiers raise a directional bind-time error pointing at LIMIT / QUALIFY / PERCENT_RANK workarounds.

SELECT TOP 5 customer_id, amount FROM orders ORDER BY amount DESC;
-- Equivalent to:
SELECT customer_id, amount FROM orders ORDER BY amount DESC LIMIT 5;

`SELECT * EXCEPT (...)` / `* EXCLUDE (...)`

Project all columns from the input except the listed ones — DuckDB and broad cross-engine compatibility. Useful for SELECT * queries that need to drop a small handful of columns without enumerating the rest. Two layers were broken in earlier builds: the dialect didn't enable the EXCEPT / EXCLUDE modifier after *, and the binder's wildcard handler ignored the column list. Now both work, and unknown column names raise a clean error instead of silently no-op-ing.

-- Drop noisy bookkeeping columns from SELECT *
SELECT * EXCEPT (created_at, updated_at) FROM orders;

-- DuckDB and some dialects spell it EXCLUDE
SELECT * EXCLUDE (created_at, updated_at) FROM orders;

`DISTINCT ON (...)`

SELECT DISTINCT ON (key1, key2, ...) ... keeps one row per distinct combination of the listed keys, matching PostgreSQL. Internally this is rewritten to a ROW_NUMBER() OVER (PARTITION BY <keys>) = 1 filter, so the choice of which row survives within each group is implementation-defined unless you also supply an explicit top-level ORDER BY over the same keys.

-- One row per customer, latest order chosen by the engine
SELECT DISTINCT ON (customer_id) customer_id, order_id, amount
FROM orders;

-- Arbitrary expressions are accepted as keys
SELECT DISTINCT ON (DATE_TRUNC('day', ts)) ts, value FROM metrics;

Earlier builds rejected this shape with a directional message pointing at a manual ROW_NUMBER() rewrite; the rewrite is now inlined at bind time.

Multi-part qualified column references

Column references may be qualified with up to four name parts:

Form	Example
1-part	`col`
2-part (table.col)	`orders.amount`
3-part (schema.table.col)	`sales.orders.amount`
4-part (catalog.schema.table.col)	`prod.sales.orders.amount`

3- and 4-part references resolve correctly when the trailing table.col matches a FROM-clause alias (or the table's bare name). Earlier builds rejected these with a misleading Column <catalog> not found error. Use multi-part qualification to disambiguate columns that exist in multiple schemas or catalogs joined in the same query.

WHERE Clause

Supported operators:

Operator	Example
Comparison	`=`, `<>`, `<`, `>`, `<=`, `>=`
Logical	`AND`, `OR`, `NOT`
Range	`BETWEEN 10 AND 20`
Set membership	`IN (1, 2, 3)`
Pattern matching	`LIKE 'abc%'`, `ILIKE 'abc%'` (case-insensitive)
Regex	`regexp_match(col, pattern)`
Null checks	`IS NULL`, `IS NOT NULL`
Boolean checks	`IS TRUE`, `IS FALSE`
Quantified	`> ANY (subquery)`, `= ALL (subquery)`

Joins

Gnok supports all standard join types. The optimizer automatically selects the best algorithm based on table sizes, available statistics, and sort order.

-- Inner join
SELECT o.*, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- Left outer join
SELECT o.*, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

-- Cross join
SELECT *
FROM products
CROSS JOIN regions;

Supported join types: INNER, LEFT, RIGHT, FULL OUTER, CROSS, SEMI, ANTI, NATURAL.

`NATURAL JOIN`

NATURAL JOIN joins on every column that shares a name on both sides. The shared columns appear once in the output (collapsed to a single column per name, matching standard SQL / Postgres / DuckDB). Internally it lowers to JOIN ... USING (<shared cols>).

-- Implicit join on every shared column
SELECT * FROM orders NATURAL JOIN customers;

-- Equivalent to (if customer_id is the only shared column):
SELECT * FROM orders JOIN customers USING (customer_id);

If the two sides share no column names, the result is a Cartesian product (matching standard SQL — NATURAL JOIN degenerates to CROSS JOIN). Use explicit INNER JOIN ... ON ... when you need the strict-equality, no-shared-column-collapse form.

FULL OUTER JOIN against a structurally-empty right side (an Iceberg table with no data batches) now correctly emits each left row paired with NULL build columns. Earlier this path panicked with index out of bounds: the len is 2 but the index is 2 — the probe columns shifted leftward in the output batch and a parent Project over-read past the column count. The mirror case (EmptyTable FULL JOIN NonEmpty) was already correct.

LATERAL joins

LATERAL lets the subquery on the right of a comma-join (or after an explicit JOIN) reference columns from the left side. The common shape is:

SELECT o.*, x.*
FROM orders o, LATERAL (
    SELECT COUNT(*) AS line_count, SUM(qty) AS total_qty
    FROM order_lines l WHERE l.order_id = o.id
) AS x;

Implicit join type. The comma-join form FROM a, LATERAL (...) is bound as:

LEFT JOIN ... ON TRUE when the inner subquery's SELECT list contains a scalar aggregate (no GROUP BY). This preserves one output row per outer row, with NULL → 0 propagation for integer aggregates via COALESCE(agg, 0) so the result is shaped like an outer-join with a never-NULL count.
INNER JOIN ... ON TRUE in every other case. An empty inner result drops the outer row, matching Postgres / DuckDB.

To force an outer semantic explicitly (preserve outer rows even when the inner subquery has no scalar aggregate), use LEFT JOIN LATERAL (...) ON TRUE.

Outer-expression correlation. Equality correlations between outer columns and inner columns (WHERE l.order_id = o.id, including the aggregate-COALESCE form above) are decorrelated into joins and execute as a single MPP plan. Non-equality correlations that mix an outer expression with an inner column on the same side of an inequality — e.g. WHERE inner.x > outer.a * 5 — are rejected at bind time with a directional message pointing at the precompute-into-derived-table workaround. Earlier this surfaced as a misleading Column 'id' not found in schema with fields: ['column0'] at the physical converter (the alias rename AS a(id) isn't visible at that stage). Bare-column correlation (WHERE inner.k = outer.k) and equality-on-arithmetic on the inner side already decorrelate cleanly.

-- Reject:
SELECT * FROM a, LATERAL (SELECT * FROM b WHERE b.x > a.id * 5);

-- Rewrite: precompute the outer expression
SELECT * FROM (SELECT id, id * 5 AS thresh FROM a) a,
              LATERAL (SELECT * FROM b WHERE b.x > a.thresh);

-- Or the aggregate-LATERAL form:
SELECT o.id, x.total
FROM (SELECT id, threshold * 5 AS bound FROM orders) o
JOIN LATERAL (
    SELECT SUM(qty) AS total FROM order_lines l
    WHERE l.order_id = o.id AND l.qty > o.bound
) x ON TRUE;

Join Algorithms

Gnok selects the optimal join algorithm based on cost estimation:

Algorithm	Selected When
Broadcast Join	One side is small enough to fit in memory (default for tables ≤ 10K rows or ≤ 10 MB)
Shuffle Hash Join	Both sides are large; rows hash-partitioned by join key to colocated workers
Sort-Merge Join	Either side is already sorted on the join key, or both sides have > 1M rows
Range Join	Non-equi join with 2+ inequality conditions (e.g., `a.x >= b.lo AND a.x <= b.hi`)
Nested Loop Join	Cross joins or non-equi joins where no side fits broadcast

Dynamic Filtering

During hash/broadcast joins, Gnok builds bloom filters from the build side and pushes them down to the probe-side scan. This dynamically prunes Parquet row groups and files that cannot contain matching join keys, significantly reducing I/O for large joins.

Subqueries

Subqueries can appear in SELECT lists (scalar), WHERE clauses (IN, EXISTS, ANY/ALL), and FROM clauses (derived tables). Correlated subqueries are decorrelated into joins by the optimizer when possible.

-- Scalar subquery
SELECT *, (SELECT MAX(amount) FROM orders) AS max_amount
FROM orders;

-- IN subquery
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- EXISTS
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Correlated subquery
SELECT * FROM orders o
WHERE amount > (SELECT AVG(amount) FROM orders WHERE region = o.region);

-- ANY / ALL
SELECT * FROM orders
WHERE amount > ANY (SELECT amount FROM orders WHERE region = 'EU');

Row-valued IN subqueries — `(a, b) IN (SELECT x, y FROM …)`

Multi-column IN accepts a row constructor on the left and a multi-column subquery on the right. The shape matches against every column at once — a row qualifies only when every left-side column equals the corresponding right-side column in some subquery row:

-- "is this (customer, day) combo in the staged set?"
SELECT * FROM orders o
WHERE (o.customer_id, o.order_date) IN (
    SELECT customer_id, order_date FROM staged_refunds
);

-- Same shape with NOT IN
SELECT * FROM orders o
WHERE (o.customer_id, o.region) NOT IN (
    SELECT customer_id, region FROM active_pairs
);

The binder rewrites this to an EXISTS over the subquery with the per-column equality predicate AND-ed in, so it composes with the rest of the decorrelator (joins, aggregates, etc. inside the subquery all work). When the subquery is a literal VALUES list, the rewrite collapses further to a flat AND of OR chains ((o.a = lit AND o.b = lit) OR …) for cheap probe evaluation on the outer side.

NULL semantics follow the SQL standard: a row containing NULL on either side is "unknown" rather than "no match", so NOT IN with a NULL in the subquery returns UNKNOWN (and the row is dropped by the surrounding WHERE).

Correlated subquery limitations

Correlated outer-column references inside a subquery are type- inferred against the enclosing query's scope. Arithmetic involving an outer column (e.g. outer.v * 10) is typed correctly and no longer surfaces a misleading Cannot apply * between Utf8 and Int64 error.

The decorrelator supports equality predicates between outer and inner columns, and correlated inequality predicates inside EXISTS / IN / NOT IN subqueries that reference an outer column on one side of an inequality:

-- Equi-correlation
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM shipments s WHERE s.order_id = o.id);

-- Non-equi correlation inside IN — decorrelated cleanly
SELECT * FROM left_t l
WHERE l.id IN (
    SELECT r.id FROM right_t r WHERE r.w > l.v
);

-- Same for NOT IN
SELECT * FROM left_t l
WHERE l.id NOT IN (
    SELECT r.id FROM right_t r WHERE r.w > l.v
);

Equality between an inner column and an arithmetic outer expression (inner.x = outer.v * 10) also decorrelates — the outer expression is precomputed into the build side of the join, and the equi-join runs against the materialised value:

-- Correlated equality with arithmetic on the outer side
SELECT * FROM outer_t o
WHERE EXISTS (
    SELECT 1 FROM inner_t i WHERE i.x = o.v * 10
);

-- Same shape inside an IN-subquery
SELECT * FROM outer_t o
WHERE o.id IN (
    SELECT i.id FROM inner_t i WHERE i.x = o.v * 10
);

Earlier this form errored with correlated subquery uses an expression of outer columns as the equi-join key, requiring a manual rewrite into a derived table that exposed the arithmetic result as a bare column.

Scalar subquery cardinality

A scalar subquery used as an expression must produce at most one row. A multi-row result raises an explicit error (matching Postgres and the SQL standard) rather than silently returning the first row.

A zero-row scalar subquery returns NULL typed to the subquery's projected column type — e.g. (SELECT id FROM t WHERE false) returns an Int32 NULL, not the untyped empty-string fallback that earlier builds produced. This matters for nested SELECT (SELECT (SELECT ...)) chains and for downstream type inference (the outer projection now reports the correct type).

Correlated scalar subquery in the projection list

A correlated scalar subquery used as a projection expression must either be inner-aggregated (produce ≤ 1 row per outer key on its own) or be rewritten as a LEFT JOIN. Plain SELECT a.id, (SELECT w FROM b WHERE b.id = a.id) FROM a is rejected at bind time with a directional message — earlier the decorrelator's fallback materialised the subquery as a global join that aggregated across outer rows, producing a cross-product or the Scalar subquery returned N rows, expected at most 1 error at runtime.

-- Reject:
SELECT a.id, (SELECT w FROM b WHERE b.id = a.id) FROM a;

-- Rewrite #1: LEFT JOIN (preserve every outer row even if no match)
SELECT a.id, b.w FROM a LEFT JOIN b ON b.id = a.id;

-- Rewrite #2: wrap in MAX/MIN/ANY_VALUE so the inner is single-row
SELECT a.id, (SELECT MAX(w) FROM b WHERE b.id = a.id) FROM a;

Inner-aggregated forms (SELECT MAX(c) FROM b WHERE b.k = a.k) remain a valid scalar subquery in the projection — they always produce one row per correlated key.

Common Table Expressions (CTEs)

CTEs define named temporary result sets for use within a single query. They improve readability and allow the optimizer to share computation when a CTE is referenced multiple times.

WITH top_customers AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 10000
)
SELECT c.name, t.total
FROM top_customers t
JOIN customers c ON t.customer_id = c.id;

CTE names follow the standard SQL identifier-case rules — unquoted names are normalised to lower case and compared case-insensitively against references in the body:

-- All three resolve to the same CTE
WITH Numbers AS (SELECT 1 AS n)
SELECT * FROM Numbers
UNION ALL SELECT * FROM numbers
UNION ALL SELECT * FROM NUMBERS;

Duplicate CTE names within a single WITH clause are detected case-insensitively and raise a bind-time error. Use double-quoted identifiers ("Numbers") to preserve case and create distinct CTEs.

Recursive CTEs

WITH RECURSIVE org_tree AS (
    -- Anchor: top-level managers
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: employees under each manager
    SELECT e.id, e.name, e.manager_id, t.depth + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;

The recursive term must use UNION or UNION ALL. UNION deduplicates across iterations.

Aggregates and DISTINCT in the recursive term are rejected at bind time. The fixed-point iteration cannot bound the working set when each step collapses prior rows — every iteration would re-aggregate over the growing accumulator, so the recursion has no finite termination shape. Run the aggregate (or DISTINCT) in the anchor term, or wrap the entire recursive CTE in an outer query that aggregates the converged result:

-- Reject: aggregate in the recursive term
WITH RECURSIVE r AS (
    SELECT 1 AS n
    UNION ALL
    SELECT SUM(n) + 1 FROM r WHERE n < 5    -- rejected
)
SELECT * FROM r;

-- OK: aggregate over the converged CTE
WITH RECURSIVE r AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM r WHERE n < 5
)
SELECT SUM(n) FROM r;

GROUP BY

Group rows sharing common values and compute aggregates per group. Gnok supports basic grouping, ROLLUP (hierarchical subtotals), CUBE (all combinations), and GROUPING SETS (explicit subtotal groups).

Basic Grouping

SELECT region, COUNT(*), SUM(amount)
FROM orders
GROUP BY region
HAVING SUM(amount) > 10000;

ROLLUP / CUBE / GROUPING SETS

-- ROLLUP: hierarchical subtotals
-- Generates: (region, city), (region), ()
SELECT region, city, SUM(amount) AS total
FROM orders
GROUP BY ROLLUP(region, city);

-- CUBE: all combinations of subtotals
-- Generates: (region, city), (region), (city), ()
SELECT region, city, SUM(amount) AS total
FROM orders
GROUP BY CUBE(region, city);

-- GROUPING SETS: explicit subtotal groups
SELECT region, city, SUM(amount) AS total
FROM orders
GROUP BY GROUPING SETS((region, city), (region), ());

Empty input

When the source has zero rows, ROLLUP, CUBE, and any GROUPING SETS list that contains the empty set () still emit a single grand-total row. Grouping columns are NULL, COUNT and COUNT(*) are 0, and other aggregates (SUM, AVG, MAX, …) are NULL. This matches Postgres / DuckDB and the SQL standard, and ensures dashboards backed by ROLLUP queries always render at least the total row.

Set Operations

Combine result sets from multiple queries. All operands must have the same number of columns with compatible types.

-- Union (deduplicates)
SELECT region FROM customers
UNION
SELECT region FROM suppliers;

-- Union all (keeps duplicates)
SELECT order_id, amount FROM orders_2024
UNION ALL
SELECT order_id, amount FROM orders_2025;

-- Intersect
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM returns;

-- Except
SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM blocked_customers;

Branch type widening

UNION / INTERSECT / EXCEPT wrap each branch in a projection that casts to the coerced column type before deduplication. Earlier builds compared the raw branch outputs at the dedupe step, which silently produced wrong answers when the branches disagreed on type — for example SELECT 1 INTERSECT SELECT 1.4 returned 1 row instead of 0, because the integer 1 and the float 1.4 hashed identically in their raw forms.

SELECT 1 INTERSECT SELECT 1.4;        -- 0 rows (was 1)
SELECT 1.0 UNION   SELECT 1;          -- 1 row, type DOUBLE

The coerced output type is the standard SQL numeric / string / temporal promotion of the per-column types across all branches. Branches with incompatible types (e.g. VARCHAR vs DATE) still raise a bind-time error.

Ordering set-op results

A trailing ORDER BY applies to the combined result of the set operation, not to the last branch. Arbitrary expressions over the projected columns — not just bare column references or output ordinals — are supported and produce a single globally-sorted output:

SELECT id FROM a
UNION ALL
SELECT id FROM b
ORDER BY id + 10;          -- sorts the union globally

UNNEST

Expand array columns and array literals into rows — one output row per array element. UNNEST may appear as a top-level table source in the FROM clause, joined against another table, or in the SELECT list.

-- Correlated UNNEST: expand an array column from a sibling table.
-- The comma-join and CROSS JOIN forms are equivalent (both lateral).
SELECT o.id, item
FROM orders o, UNNEST(o.items) AS u(item);

SELECT o.id, item
FROM orders o CROSS JOIN UNNEST(o.items) AS u(item);

-- UNNEST an array literal directly in FROM with a named column
SELECT v FROM UNNEST(ARRAY[10, 20, 30]) AS t(v);
-- 10
-- 20
-- 30

-- UNNEST in the SELECT list
SELECT id, UNNEST(tags) AS tag
FROM products;

-- With ordinal position (1-based ORDINALITY or 0-based OFFSET),
-- works in both the standalone and correlated/lateral positions
SELECT o.id, item, ord
FROM orders o, UNNEST(o.items) WITH ORDINALITY AS u(item, ord);

SELECT o.id, item, pos
FROM orders o, UNNEST(o.items) WITH OFFSET AS pos;

The output column inherits the array's element type — e.g. UNNEST(ARRAY[10, 20, 30]) emits a single BIGINT column with three rows. A correlated UNNEST(t.col) replicates the outer table's columns across each expanded element row.

Known limitations

UNNEST of a non-column array expression that references an outer table (e.g. UNNEST(SPLIT(t.s, ','))) is not yet supported in the FROM clause — wrap the expression in a derived table first (FROM (SELECT SPLIT(s, ',') AS parts FROM t) d, UNNEST(d.parts)).
An array literal inside a VALUES node (e.g. VALUES (1, ARRAY[10,20])) is not yet supported; build the array via a SELECT (SELECT 1 AS id, ARRAY[10,20] AS arr) instead.

QUALIFY

Filter rows based on window function results (evaluated after window functions but before ORDER BY/LIMIT):

-- Top 5 orders per region
SELECT region, order_id, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM orders
QUALIFY rn <= 5;

-- Deduplicate: keep latest row per customer
SELECT *
FROM customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

QUALIFY eliminates the need for a subquery wrapper when filtering on window functions.

PIVOT

Rotate rows into columns:

SELECT *
FROM sales
PIVOT (
    SUM(amount)
    FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);

Supported aggregates in PIVOT: SUM, AVG, COUNT, MIN, MAX.

UNPIVOT

Rotate columns into rows (inverse of PIVOT):

SELECT item_id, metric, val
FROM item
UNPIVOT (val FOR metric IN (i_current_price, i_wholesale_cost));

Each column listed in the IN clause becomes a row with the column name in metric and the value in val.

`EXCLUDE NULLS` / `INCLUDE NULLS`

UNPIVOT defaults to EXCLUDE NULLS per SQL standard — rows where the unpivoted value is NULL are dropped. Earlier builds always behaved like INCLUDE NULLS, leaking sparse-column NULLs through every row.

-- Drop NULL values (standard, default)
SELECT item_id, metric, val
FROM item
UNPIVOT EXCLUDE NULLS (val FOR metric IN (i_current_price, i_wholesale_cost));

-- Keep NULL values explicitly
SELECT item_id, metric, val
FROM item
UNPIVOT INCLUDE NULLS (val FOR metric IN (i_current_price, i_wholesale_cost));

LATERAL FLATTEN

Expand arrays and objects into rows:

-- Flatten an array column
SELECT t.id, f.VALUE
FROM test_table t, LATERAL FLATTEN(input => t.tags) f;

-- Flatten with sequence number
SELECT t.id, f.SEQ, f.INDEX, f.VALUE
FROM test_table t, LATERAL FLATTEN(input => t.items) f;

TABLESAMPLE

Sample a subset of rows:

-- Bernoulli (row-level) sampling — 10% of rows
SELECT * FROM orders TABLESAMPLE BERNOULLI (10);

-- System (block-level) sampling — 10% of blocks
SELECT * FROM orders TABLESAMPLE SYSTEM (10);

-- Fixed row count
SELECT * FROM orders TABLESAMPLE BERNOULLI (1000 ROWS);

-- Repeatable seed for deterministic results
SELECT * FROM orders TABLESAMPLE BERNOULLI (5) REPEATABLE (42);

-- Works on CTE / subquery / derived-table sources
WITH big AS (SELECT * FROM events WHERE day = CURRENT_DATE)
SELECT COUNT(*) FROM big TABLESAMPLE BERNOULLI (1);

BERNOULLI flips an independent coin per row, so the output row count is approximately the requested percentage with binomial variance. SYSTEM decides per-batch, which is much faster but gives a coarser sample and may skip whole blocks if the table is clustered. The percentage is validated at bind time: a value outside [0, 100] is rejected with TABLESAMPLE percentage must be between 0 and 100, got <value>.

Table-valued functions

Functions that produce rows can be used in the FROM clause like a table.

-- A range of integers, inclusive of both bounds. Defaults step = 1
-- (or -1 if stop < start, so reverse ranges work without a step).
SELECT n FROM generate_series(1, 5) AS s(n);
-- 1, 2, 3, 4, 5

-- Negative or fractional step requires the 3-arg form
SELECT n FROM generate_series(10, 1, -2) AS s(n);
-- 10, 8, 6, 4, 2

-- Only integer arguments are accepted; passing Float / Decimal is
-- rejected at bind time with a CAST hint so truncation is opt-in:
--   GENERATE_SERIES start argument must be an integer; got Float64
SELECT n FROM generate_series(CAST(1.5 AS BIGINT), 5) AS s(n);

GENERATE_SERIES caps the result at 10 million rows; an estimate beyond that is rejected before any output is produced.

SEQUENCE(start, stop [, step]) is a Spark-compatible alias for GENERATE_SERIES and returns List(Int64) — the projection-schema return type is reported as List(Int64), so Flight SQL / PG-wire clients decode the array natively rather than as a stringified list. (Earlier builds reported Utf8 for the output column, which caused some clients to render the result as '[1, 2, 3]' text.)

-- Spark shape (returns List(Int64))
SELECT sequence(1, 5);            -- [1, 2, 3, 4, 5]
SELECT sequence(10, 1, -2);       -- [10, 8, 6, 4, 2]

Window Functions

Window functions compute a value for each row based on a set of related rows (the "window"), without collapsing groups like GROUP BY. Use PARTITION BY to define the window and ORDER BY to control row ordering within it.

SELECT
    order_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM orders;

Window Frame Specifications

-- Default: entire partition
SUM(amount) OVER (PARTITION BY region)

-- Rows between
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- Range between (value-based)
SUM(amount) OVER (ORDER BY amount RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING)

-- Groups between (peer-group-based)
SUM(amount) OVER (ORDER BY region GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Unbounded
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Per-row running tail (frame extends from the current row to the end)
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

All three frame unit types are supported: ROWS, RANGE, and GROUPS.

Supported frame bounds

Both the start and end of a BETWEEN frame may use any combination of the bounds below — including a UNBOUNDED FOLLOWING tail with any preceding start bound:

Bound	Meaning
`UNBOUNDED PRECEDING`	Beginning of the partition
`<n> PRECEDING`	`n` rows / range units / peer groups before the current row
`CURRENT ROW`	The current row (or its peer group, for `RANGE` / `GROUPS`)
`<n> FOLLOWING`	`n` rows / range units / peer groups after the current row
`UNBOUNDED FOLLOWING`	End of the partition

A frame whose tail is UNBOUNDED FOLLOWING is computed per row — for example, ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING yields the suffix sum at each row. The frame collapses to a single per-partition aggregate only when both bounds are unbounded (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).

Supported Window Functions

Function	Description
`ROW_NUMBER()`	Sequential row number
`RANK()`	Rank with gaps
`DENSE_RANK()`	Rank without gaps
`NTILE(k)`	Divide into `k` buckets; remainder distributes to early buckets
`PERCENT_RANK()`	Relative rank (0 to 1)
`CUME_DIST()`	Cumulative distribution
`LAG(expr [, offset [, default]])`	Value from `offset` rows before; `default` must be type-compatible
`LEAD(expr [, offset [, default]])`	Value from `offset` rows after; `default` must be type-compatible
`FIRST_VALUE(expr)`	First value in the frame (not the partition)
`LAST_VALUE(expr)`	Last value in the frame (not the partition)
`NTH_VALUE(expr, n)`	Nth value in the frame; `n` must be ≥ 1

All aggregate functions (SUM, AVG, COUNT, etc.) can also be used as window functions with OVER.

Restricted positions

Window functions are only valid in two places: the SELECT projection and the QUALIFY clause. Anywhere else, the engine rejects them at bind time with a directional message. Earlier builds let some of these shapes through to distributed serialization and crashed with an opaque h2 protocol error.

Position	Status	Workaround
`SELECT` list	OK	—
`QUALIFY`	OK	—
`GROUP BY` (direct, e.g. `GROUP BY ROW_NUMBER() OVER (...)`)	Bind error	Alias the window in the SELECT list, then `GROUP BY` the alias.
`GROUP BY` (via ordinal indirection, e.g. `GROUP BY 2` where projection 2 is a window)	Bind error	Same — alias and reference by name.
`GROUP BY` (nested in `CASE` / arithmetic)	Bind error	Same.
Top-level `ORDER BY` (e.g. `ORDER BY ROW_NUMBER() OVER (...)`)	Bind error	Alias the window in the SELECT list, then `ORDER BY` the alias.
`WHERE` / `HAVING`	Bind error (pre-existing)	Use `QUALIFY`.

-- Reject: window directly in ORDER BY
SELECT v FROM t ORDER BY ROW_NUMBER() OVER (ORDER BY v);

-- OK: alias-then-order
SELECT v, ROW_NUMBER() OVER (ORDER BY v) AS rn FROM t ORDER BY rn;

Frame-aware positional functions

FIRST_VALUE, LAST_VALUE, and NTH_VALUE index relative to the window frame, matching Postgres / SQL-standard semantics. With the default frame (ROWS UNBOUNDED PRECEDING TO CURRENT ROW), LAST_VALUE returns the current row. To get partition-end semantics, use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. See the Functions reference for full details on NTILE distribution, LAG/LEAD default type-checking, and the FILTER clause restriction on non-aggregate window functions.

Time Travel

Query data as it existed at a previous point in time. Gnok leverages Iceberg's snapshot history — each DML operation creates an immutable snapshot that can be queried later.

-- Query by snapshot ID (Trino-flavoured)
SELECT * FROM orders FOR VERSION AS OF 1234567890;
SELECT * FROM orders FOR SYSTEM_VERSION AS OF 1234567890;  -- synonym

-- Query by wall-clock timestamp
SELECT * FROM orders FOR TIMESTAMP AS OF '2025-01-01 12:00:00';
SELECT * FROM orders AS OF TIMESTAMP '2025-01-01 12:00:00';       -- Oracle-style

-- AT() function-style syntax (Iceberg-flavoured)
SELECT * FROM orders AT(SNAPSHOT => 1234567890);
SELECT * FROM orders AT(TIMESTAMP => '2025-01-01 12:00:00');

-- FOR SYSTEM_TIME AS OF accepts function calls and typed-string forms
SELECT * FROM orders FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP;
SELECT * FROM orders FOR SYSTEM_TIME AS OF NOW();
SELECT * FROM orders FOR SYSTEM_TIME AS OF TIMESTAMP '2025-01-01 12:00:00';

The FOR SYSTEM_TIME AS OF expression accepts:

CURRENT_TIMESTAMP / NOW() / LOCALTIMESTAMP / CURRENT_DATE / TODAY() — captured as current wall-clock milliseconds.
A typed-string literal — e.g. TIMESTAMP '2025-01-01 12:00:00', parsed as an ISO-8601 timestamp and resolved against the snapshot log.

Earlier the expression arm rejected function calls with Unsupported time travel expression — only bare integer / typed-string forms worked.

Both syntaxes resolve through the same path — the timestamp form selects the newest snapshot whose committed_at <= <ts>. Small numeric arguments to AT() are interpreted as snapshot IDs; large values (> 1 trillion) are interpreted as millisecond timestamps.

Time-travel reads work on every supported scan code path — single-file, multi-file, and split-task partitioned scans — so a historical snapshot returns the same rows regardless of the table's size.

For the full snapshot story — inspecting history with SHOW SNAPSHOTS / <table>$snapshots, rolling the live head with ALTER TABLE … SET SNAPSHOT n / ROLLBACK TABLE, and the relationship between time-travel reads and rollback — see Snapshots, Time Travel, and Rollback.

Metadata Columns

Iceberg metadata columns are excluded from SELECT * but can be requested explicitly:

SELECT _file, _pos, _spec_id, order_id, amount
FROM orders
LIMIT 10;

Column	Type	Iceberg Version	Description
`_file`	VARCHAR	v1+	Source data file path
`_pos`	BIGINT	v1+	Row position within the file
`_spec_id`	INT	v1+	Partition spec ID
`_partition`	STRUCT	v1+	Partition values
`_file_size`	BIGINT	v1+	Data file size in bytes
`_row_id`	BIGINT	v3	Globally unique row identifier (`first_row_id + _pos`)
`_last_updated_sequence_number`	BIGINT	v3	Sequence number of the commit that last modified the row

note

_row_id and _last_updated_sequence_number are only available on tables using Iceberg format version 3. They enable row lineage tracking across compaction and schema evolution.

Change Data Capture

Read incremental changes between two snapshots (Iceberg v2+):

READ CHANGES FROM orders FROM 123456 TO 789012;

Returns rows annotated with change type indicators for CDC pipelines.

EXPLAIN

Inspect the query plan:

-- Show logical/physical plan
EXPLAIN SELECT * FROM orders WHERE region = 'US';

-- Execute and show actual metrics (rows, bytes, timing)
EXPLAIN ANALYZE SELECT * FROM orders WHERE region = 'US';

-- Show estimated cost across warehouse sizes
EXPLAIN COST SELECT COUNT(*) FROM orders WHERE region = 'US';

EXPLAIN ANALYZE output includes per-operator metrics: input/output rows, estimated vs actual row counts, elapsed time, memory usage, files scanned vs pruned, row groups pruned, and dynamic filter statistics.

EXPLAIN COST shows estimated compute units (CUs), estimated execution time, and cost across warehouse sizes (XS through 4XL) to help with capacity planning.

EXPLAIN works on tableless queries (no FROM clause, or a constant VALUES) — these run through the tableless short-circuit path and previously failed with No catalog context available because the short-circuit didn't recognise the EXPLAIN wrapper:

EXPLAIN SELECT 1;
EXPLAIN SELECT 1 + 2 AS x;
EXPLAIN VALUES (1, 2), (3, 4);

All three now return the plan text rather than executing the inner query.

EXPLAIN AI

EXPLAIN AI SELECT region, SUM(amount)
FROM orders
WHERE order_date > DATE '2024-01-01'
GROUP BY region;

EXPLAIN AI returns a natural-language explanation of the query plan instead of the raw operator tree — it describes, in plain English, how the engine will execute the query (scan strategy, joins, aggregation, pruning, and shuffles) and highlights anything noteworthy (e.g. a missing filter, a large shuffle, or an opportunity to add a predicate). It is useful for understanding or reviewing a plan without reading EXPLAIN output directly. See AI Scalar Functions for the underlying LLM configuration.

Scan Optimizations Shown in EXPLAIN

Optimization	Description
`files_pruned`	Files skipped via partition and column statistics pruning
`row_groups_pruned`	Parquet row groups skipped via min/max statistics
`limit_pushdown`	LIMIT pushed into the scan for early termination
`dynamic_filters`	Runtime bloom/min-max filters from join build side
`late_materialization`	Wide columns decoded only for rows passing filters

Basic Syntax​

SELECT TOP N (T-SQL)​

SELECT * EXCEPT (...) / * EXCLUDE (...)​

DISTINCT ON (...)​

Multi-part qualified column references​

WHERE Clause​

Joins​

NATURAL JOIN​

LATERAL joins​

Join Algorithms​

Dynamic Filtering​

Subqueries​

Row-valued IN subqueries — (a, b) IN (SELECT x, y FROM …)​

Correlated subquery limitations​

Scalar subquery cardinality​

Correlated scalar subquery in the projection list​

Common Table Expressions (CTEs)​

Recursive CTEs​

GROUP BY​

Basic Grouping​

ROLLUP / CUBE / GROUPING SETS​

Empty input​

Set Operations​

Branch type widening​

Ordering set-op results​

UNNEST​

QUALIFY​

PIVOT​

UNPIVOT​

EXCLUDE NULLS / INCLUDE NULLS​

LATERAL FLATTEN​

TABLESAMPLE​

Table-valued functions​

Window Functions​

Window Frame Specifications​

Supported frame bounds​

Supported Window Functions​

Restricted positions​

Time Travel​

Metadata Columns​

Change Data Capture​

EXPLAIN​

EXPLAIN AI​

Scan Optimizations Shown in EXPLAIN​