Functions

Gnok provides a comprehensive set of built-in functions for data transformation, analysis, and computation. Functions are categorized as scalar (operate on individual values), aggregate (summarize groups of rows), and window (compute over sliding frames).

All functions handle NULL inputs following SQL standard rules: most scalar functions return NULL if any argument is NULL, unless documented otherwise (e.g., coalesce, concat). NULL-handling forms (COALESCE, IFNULL, NVL) and CASE are evaluated per-row short-circuit — a later argument or branch is evaluated only on the rows that still need it, so defensive idioms like COALESCE(x, 1/0) do not crash on the dead branch.

Scalar Functions

String Functions

String manipulation and pattern matching. All string functions use 1-based indexing.

Function	Description
length(s) / len(s) / char_length(s)	Character length
octet_length(s) / byte_length(s)	Byte length (BYTE_LENGTH is the Trino spelling)
upper(s) / lower(s)	Case conversion
initcap(s)	Title-case each word
trim(s) / ltrim(s) / rtrim(s) / btrim(s)	Whitespace trimming
lpad(s, len [, pad]) / rpad(s, len [, pad])	Pad to length
substring(s, start [, len])	Extract substring (1-indexed)
left(s, n) / right(s, n)	First/last n characters
concat(s1, s2, ...)	Concatenate strings, skipping NULL arguments (PG-style NULL-safe). When every argument is NULL the result is '' (the empty string), not NULL — this preserves the NULL-skip identity.
concat_ws(sep, s1, s2, ...)	Concatenate with separator, skipping NULLs
replace(s, from, to)	Replace all occurrences
translate(s, from, to)	Character-by-character substitution
reverse(s)	Reverse string
repeat(s, n) / replicate(s, n)	Repeat string n times (REPLICATE is the T-SQL spelling)
position(sub IN s) / position(sub, s) / position(sub, s, start) / strpos(s, sub) / instr(s [, sub] [, start]) / instrb(s [, sub] [, start]) / charindex(sub, s [, start])	Position of substring (1-indexed). The 2-arg call form POSITION(substring, string) takes the substring first. The 3-arg form takes a 1-based character start offset (multi-byte safe); start < 1 raises a bind error. CHARINDEX(needle, haystack [, start]) is the MSSQL spelling with reversed argument order vs STRPOS / INSTR — it lowers to INSTR(haystack, needle [, start]) so the optional start offset is honoured. INSTRB is an Oracle-compatible alias for INSTR.
elt(n, s1, s2, ...) / field(needle, s1, s2, ...)	MySQL string-index idioms. ELT(n, ...) returns the n-th argument (1-indexed) or NULL if n is out of range; FIELD(needle, ...) returns the 1-based position of the first argument equal to needle, or 0 if none match. Both are rewritten to CASE at bind time so they work uniformly across distributed scans.
starts_with(s, prefix) / ends_with(s, suffix)	Prefix/suffix test
contains(s, substr)	Substring test
split_part(s, delimiter, n)	Nth part after splitting
string_split(s, delimiter)	Split into array
ascii(s) / codepoint(s) / chr(code) / nchar(code)	First-character codepoint / character (CODEPOINT is the Trino / Presto spelling; NCHAR is the MSSQL Unicode-codepoint-to-char spelling and is an alias for CHR)
quote_literal(s) / quote_ident(s)	PG SQL-safe quoting. QUOTE_LITERAL wraps s in single quotes and doubles any internal '; QUOTE_IDENT does the same with double quotes. NULL input → NULL. Useful for dynamically constructing SQL.
regexp_match(s, pattern) / regexp_like(s, pattern [, flags])	Regex match (boolean). The optional 3rd flags argument follows the PG / Oracle convention — 'i' for case-insensitive, 's' for dot-matches-newline, 'm' for multi-line. Flags are spliced onto the pattern as an inline (?...) mode prefix.
s ~ pattern / s ~* pattern / s !~ pattern / s !~* pattern	PG POSIX regex operators. ~ matches; ~* matches case-insensitively; !~ / !~* are the negated forms. Lowered to REGEXP_LIKE(s, pattern) (with the 'i' flag for the * variants), wrapped in NOT for the bang variants.
regexp_extract(s, pattern [, group])	Extract regex capture group
regexp_extract_all(s, pattern) / regexp_match_all(s, pattern)	Extract all matches as ARRAY<VARCHAR> (empty array if none)
regexp_instr(s, pattern [, position])	1-based byte offset of the first regex match, or 0 if there is no match. Optional position (1-based, default 1) is the byte offset to start searching from. Returns BIGINT.
regexp_replace(s, pattern, replacement)	Regex replace. $N / ${N} / ${name} in replacement reference capture groups (Spark / Hive / PG ≥ 12). To emit a literal $, double it as $$.
sprintf(fmt, ...args) / printf(fmt, ...args) / format_string(fmt, ...args) / format(fmt, ...args)	C / Trino / PG-style printf formatter. Supports %s / %d / %f / %x / %X / %o / %%, plus width, precision, and left-align flags. FORMAT(fmt, ...) disambiguates by first-arg type — a string fmt routes here, a numeric first arg routes to FORMAT_NUMBER.
regexp_split_to_array(s, pattern)	Split on regex
levenshtein(s1, s2) / editdistance(s1, s2)	Edit distance. NULL inputs (including a bare NULL literal) propagate row-wise — the row's result is NULL rather than a hard error. EDITDISTANCE is an alias.
jaro_winkler_similarity(s1, s2)	Jaro-Winkler similarity (0–1)
jaccard(s1, s2)	Jaccard similarity
url_encode(s) / url_decode(s)	URL percent-encoding/decoding

-- Extract domain from email using regex capture group
SELECT regexp_extract(email, '@(.+)$', 1) AS domain FROM users;
-- 'gmail.com'

-- Split a path and get the filename
SELECT split_part('/data/warehouse/orders.parquet', '/', 4);
-- 'orders.parquet'

-- Fuzzy matching
SELECT name, levenshtein(name, 'Jhon Smith') AS distance
FROM customers
WHERE levenshtein(name, 'Jhon Smith') <= 2;

Numeric Functions

Arithmetic, rounding, trigonometry, and bitwise operations.

Function	Description
abs(x)	Absolute value
sign(x)	Sign (-1, 0, 1) — the output type matches the input: sign(INTEGER) returns INTEGER, sign(DOUBLE) returns DOUBLE, sign(DECIMAL(p, s)) returns the same DECIMAL(p, s)
ceil(x) / floor(x)	Rounding up/down
round(x [, d])	Round to d decimal places
trunc(x [, d])	Truncate toward zero
power(x, y)	Exponentiation
sqrt(x) / cbrt(x)	Square root / cube root
exp(x)	e raised to the power x
ln(x) / log10(x) / log2(x)	Natural / base-10 / base-2 logarithm
log(base, x)	Logarithm of x with the given base
mod(x, y)	Modulo
greatest(a, b, ...) / least(a, b, ...) / greatest_ignore_nulls(a, b, ...) / least_ignore_nulls(a, b, ...)	Maximum / minimum of arguments. The _IGNORE_NULLS variants skip NULL arguments — they return NULL only when every argument is NULL. The bare forms propagate NULL on any NULL argument.
width_bucket(value, low, high, n_buckets)	Equiwidth histogram bucket index for value over the half-open interval [low, high) divided into n_buckets equal-width buckets. Returns BIGINT: 0 for values below low, 1..n_buckets for in-range values, n_buckets + 1 for values at or above high. Descending bounds (low > high) are supported and reverse the bucket direction.
gcd(a, b) / lcm(a, b)	Greatest common divisor / least common multiple
factorial(n)	Factorial
pi()	Returns π
sin(x) / cos(x) / tan(x)	Trigonometric functions
asin(x) / acos(x) / atan(x) / atan2(y, x)	Inverse trigonometric
radians(x) / degrees(x)	Angle conversion
isnan(x) / isinf(x) / isfinite(x)	Special value checks
bit_count(x) / popcount(x) / get_bit(x, pos) / bit_get(x, pos) / set_bit(x, pos, val)	Bit operations (POPCOUNT is the Trino / Presto / DuckDB spelling; BIT_GET is the Spark spelling — alias for GET_BIT).
bitwise_and(x, y) / bitwise_or(x, y) / bitwise_xor(x, y) / bitwise_not(x) / bitshiftleft(x, n) / bitshiftright(x, n)	Bitwise operators in function form. The infix operators & / | work for AND / OR. x ^ y (MySQL / Spark) and x # y (Postgres) lower to bitwise_xor. Note: << / >> infix shifts are accepted by the dialect for tokenisation but are not wired as binary operators today — use BITSHIFTLEFT(x, n) / BITSHIFTRIGHT(x, n) instead.

Domain errors

SQRT, LN, LOG2, LOG10, and LOG(base, x) raise an explicit domain error on invalid inputs rather than returning silent NaN or -Infinity (which previously poisoned downstream SUM / AVG and comparisons). Matches Postgres semantics.

Function	Rejected input
SQRT(x)	x < 0
LN(x) / LOG10(x) / LOG2(x)	x <= 0
LOG(base, x)	x <= 0, base <= 0, or base = 1

Use CASE WHEN x > 0 THEN LN(x) END (or a similar guard) if you specifically want NULL on out-of-domain values.

ROUND(decimal, n) narrows the declared scale

ROUND(x, n) over a DECIMAL(p, s) input now returns a value whose declared scale is n, matching DuckDB / Postgres. Earlier the source's scale s was preserved, so the rounded value was right but trailing zeros leaked into CAST(... AS VARCHAR) and into the projected schema. Negative n (round to the nearest 10^|n|) narrows scale to 0:

SELECT ROUND(CAST(1.2345 AS DECIMAL(10, 4)), 2);            -- 1.23   (scale 2)
SELECT CAST(ROUND(CAST(1.2345 AS DECIMAL(10, 4)), 2)
            AS VARCHAR);                                    -- '1.23' (no trailing zeros)
SELECT ROUND(CAST(1234.5678 AS DECIMAL(10, 4)), -2);        -- 1200   (scale 0)

DECIMAL input accepted by numeric / formatter / stats / spatial UDFs

Because unquoted decimal literals (1.23, 100.00, 0.5) type as Decimal128(p, s) rather than Float64 (see Numeric Literal Typing), every UDF that takes a numeric argument accepts Decimal128 alongside Int* / Float*. The internal scale is unwound before the function sees the value, so callers don't need a defensive CAST(... AS DOUBLE) to invoke any of these on a decimal column or literal:

• Formatters: FORMAT_NUMBER(x, n), TO_CHAR(x, fmt), SPRINTF / PRINTF / FORMAT(fmt, ...) with %f / %g, TO_VARCHAR, TO_STRING.
• Stats UDFs: NORMAL_CDF(x), NORMAL_INV(p), T_TEST, T_TEST_P, CHI_SQUARE_TEST, CHI_SQUARE_TEST_P, MANN_WHITNEY, MANN_WHITNEY_P.
• Spatial: H3_LATLNG_TO_CELL(lat, lng, res), S2_LATLNG_TO_CELL(lat, lng, level), ST_POINT(x, y) — all accept lat / lng / x / y as Decimal128 (e.g. 40.75, -73.98) without explicit cast.
• Array constructors / set-ops: ARRAY_APPEND(arr, v) / ARRAY_PREPEND(v, arr) onto a List<Decimal128(p, s)> rescales an integer or wider-scale v to the list's (p, s); LIST_DISTINCT / ARRAY_DISTINCT / ARRAY_EXCEPT / ARRAY_INTERSECT / ARRAY_UNION over Decimal128 element lists preserve the inner type.
• Time conversion: TO_TIMESTAMP(<decimal seconds>) / FROM_UNIXTIME(<decimal seconds>) interpret the fractional part as sub-second precision — TO_TIMESTAMP(1234567890.5) returns 2009-02-13 23:31:30.5 (earlier the Decimal128 literal was silently misread as microseconds-since-epoch and landed near 1970).

Where an aggregate that previously projected Float64 now sees a Decimal128 input, the output type follows the existing precision rules — see AVG(Decimal) / SUM(Decimal) widening.

Date/Time Functions

Timestamp arithmetic, extraction, formatting, and bucketing. Timestamps are stored internally as microseconds since epoch (UTC).

Function	Description
now() / current_timestamp / sysdate / sysdate()	Current timestamp. Bare SYSDATE (no parens) is the Oracle spelling and lowers to CURRENT_TIMESTAMP at bind time.
current_date / today()	Current date
current_time	Current time
extract(unit FROM ts) / date_part(unit, ts)	Extract field from timestamp
date_trunc(unit, ts)	Truncate to unit
date_add(ts, interval) / date_sub(ts, interval)	Add/subtract interval
date_diff(unit, ts1, ts2)	Difference between timestamps
make_date(y, m, d)	Construct date
make_time(h, m, s)	Construct TIME from hour / minute / second. s may be Float64 / Float32 / DECIMAL for fractional seconds. Out-of-range hour / minute / second raises an explicit error (matches MAKE_TIMESTAMP) rather than silently returning NULL.
make_timestamp(y, m, d, h, min, s)	Construct timestamp
dayofweekiso(date)	ISO day-of-week (Mon = 1 … Sun = 7). Binder rewrite to EXTRACT(ISODOW FROM date).
strftime(format, ts)	Format timestamp to string
strptime(s, format)	Parse string to timestamp
date_format(ts, fmt)	MySQL / Spark spelling — uses MySQL %-style codes (%Y year, %m month, %d day, %H 24-hour, %i minutes, %s seconds, %p AM/PM, %T = %H:%i:%s), not the YYYY-MM-DD masks TO_CHAR uses. DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s') → '2024-03-10 12:34:56'. NULL date or NULL format → NULL. For the PG YYYY-MM-DD mask dialect use TO_CHAR instead.
format_date(fmt, date)	A spelling with arguments reversed vs STRFTIME — FORMAT_DATE('%Y-%m-%d', d) rewrites to STRFTIME('%Y-%m-%d', d) (strftime %-codes, not PG masks). Naive routing through TO_CHAR would silently return the literal %Y-%m-%d mask.
parse_date(fmt, s) / parse_timestamp(fmt, s) / parse_datetime(fmt, s)	Date / timestamp parsers — arguments are reversed vs STRPTIME(s, fmt). The binder swaps the args, routes to STRPTIME, and casts to Date32 (PARSE_DATE) or Timestamp (PARSE_TIMESTAMP / PARSE_DATETIME). Format uses strftime %-codes.
to_date(s [, fmt]) / to_timestamp(s [, fmt])	Parse using a PG-style format mask. TO_TIMESTAMP(numeric) is treated as epoch seconds and routes through FROM_UNIXTIME (not via CAST(<int> AS TIMESTAMP), which is rejected — see Rejected casts).
try_to_date(s [, fmt]) / try_to_timestamp(s [, fmt])	Like to_date / to_timestamp but returns NULL on parse failure
from_unixtime(seconds)	Convert Unix epoch seconds (BIGINT / DOUBLE) to TIMESTAMP. Use this (or TO_TIMESTAMP(seconds)) instead of CAST(<int> AS TIMESTAMP) — the cast is rejected to avoid the silent microseconds-vs-seconds unit confusion. FROM_UNIXTIME(NULL) propagates NULL (no longer errors "argument must be an integer or float").
epoch(ts) / unix_timestamp(ts)	Seconds since Unix epoch. UNIX_TIMESTAMP(NULL) / EPOCH(NULL) propagate NULL. An unparseable string argument (e.g. UNIX_TIMESTAMP('garbage')) errors directionally rather than silently returning NULL — wrap with TRY_TO_TIMESTAMP(...) then EPOCH(...) if soft-failure is wanted.
epoch_ms(millis) / epoch_ns(nanos)	Milliseconds/nanoseconds to timestamp
time_bucket(interval, ts)	Truncate to time bucket (e.g., '5 minutes')
last_day(date)	Last day of the month

-- Extract year and month
SELECT extract(YEAR FROM order_date) AS yr, extract(MONTH FROM order_date) AS mo
FROM orders;

-- Difference in days between two dates
SELECT date_diff('day', created_at, shipped_at) AS days_to_ship FROM orders;

-- `day` counts calendar-day boundary crossings, not 24h chunks of
-- elapsed time. This matches MSSQL and common SQL behavior.
SELECT date_diff('day', TIMESTAMP '2025-01-01 23:00',
                        TIMESTAMP '2025-01-02 01:00');  -- 1
-- Likewise `month` / `year` / `quarter` / `week` count boundary
-- crossings of the named unit. Use `hour` / `minute` / `second` /
-- `millisecond` / `microsecond` for true elapsed time.

-- 5-minute time buckets for metrics aggregation
SELECT time_bucket('5 minutes', event_time) AS bucket, COUNT(*)
FROM events
GROUP BY 1 ORDER BY 1;

-- Format for display
SELECT strftime('%Y-%m-%d %H:%M', order_date) AS formatted FROM orders;

-- Parse with a PG-style format mask (no chrono % codes required)
SELECT to_date('2025-03-14', 'YYYY-MM-DD');
SELECT to_timestamp('2025-03-14 09:30:15.123456', 'YYYY-MM-DD HH24:MI:SS.FF');

-- NULL inputs propagate cleanly (no panic)
SELECT to_date(NULL, 'YYYY-MM-DD');  -- NULL

EXTRACT return types

EXTRACT (and the equivalent date_part) returns:

Unit	Return Type	Notes
EPOCH	DOUBLE	Fractional seconds since Unix epoch — preserves microsecond precision
SECOND	DOUBLE	Fractional seconds within the minute (e.g., 12.345678)
MILLISECOND / MICROSECOND	BIGINT	Whole-unit count
YEAR / QUARTER / MONTH / WEEK / DAY / DOW / DOY / HOUR / MINUTE	BIGINT	Whole-unit count

EPOCH and SECOND return DOUBLE so that downstream arithmetic preserves sub-second precision. Other units return BIGINT. If you were previously casting EXTRACT(EPOCH FROM ts) to a wider type defensively, you can drop the cast.

DATE_TRUNC units

DATE_TRUNC(unit, ts) accepts the usual calendar units ('year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second') plus sub-second units 'millisecond' and 'microsecond' on TIMESTAMP inputs. The sub-second units floor the timestamp's microsecond field to the requested resolution (earlier builds silently returned the unmodified timestamp). Pre-1970 timestamps floor toward the past (div_euclid), matching Postgres.

SELECT DATE_TRUNC('millisecond', TIMESTAMP '2024-01-15 10:00:00.123456');
-- 2024-01-15 10:00:00.123000

SELECT DATE_TRUNC('microsecond', TIMESTAMP '2024-01-15 10:00:00.123456');
-- 2024-01-15 10:00:00.123456 (no-op at microsecond resolution)

TO_DATE / TO_TIMESTAMP format masks

TO_DATE and TO_TIMESTAMP accept Postgres-style format masks and translate them to the underlying chrono format internally:

Mask	Meaning
YYYY / YY	4- / 2-digit year
MM	Zero-padded month
MON / MONTH	Abbreviated / full month name
DD	Zero-padded day of month
HH24 / HH12	Hour (24- or 12-hour)
MI	Minute
SS	Second
FF / FF1–FF9	Fractional seconds (1–9 digits)
AM / PM	AM/PM marker
TZH / TZM	Timezone hour / minute offset

TRY_TO_DATE / TRY_TO_TIMESTAMP return NULL on parse failure instead of raising. Both forms also handle NULL inputs cleanly — TO_DATE(NULL, 'YYYY-MM-DD') returns NULL.

TO_CHAR format masks

TO_CHAR(ts, fmt) formats a DATE / TIMESTAMP to a VARCHAR using the same Postgres-style mask alphabet as TO_DATE / TO_TIMESTAMP. The fractional-second and timezone tokens below were corrected in Wave 97 — earlier builds either double-printed a leading . (e.g. ..123 for SS.FF3) or passed the token through as a literal.

Mask	Meaning
FF / FF1–FF9	Fractional seconds — n unprefixed digits. No leading . is emitted; include the dot in your mask explicitly (e.g. 'SS.FF3' → '12.123'). The default FF is 6 digits (microseconds, matching the storage resolution).
MS	3-digit milliseconds, unprefixed (no leading .).
US	6-digit microseconds, unprefixed (no leading .).
TZ	Timezone abbreviation. Renders empty for naive TIMESTAMP values, since the type carries no zone — cast to TIMESTAMPTZ first if you need a zone abbreviation.
OF / TZH:TZM / TZH	Numeric UTC offset +HH:MM (e.g. +05:30, -08:00). TZH alone is rendered the same way — chrono has no native hours-only form.
Q	Quarter digit (1–4). Wired via a sentinel-substitution post-pass — chrono has no native Q directive but the substitution runs cleanly inside larger format masks.

-- Correct fractional-second formatting (user controls the dot)
SELECT TO_CHAR(TIMESTAMP '2024-01-15 10:30:45.123456',
               'YYYY-MM-DD HH24:MI:SS.FF3');
-- '2024-01-15 10:30:45.123'

SELECT TO_CHAR(TIMESTAMP '2024-01-15 10:30:45.123456',
               'YYYY-MM-DD"T"HH24:MI:SS.US');
-- '2024-01-15T10:30:45.123456'  (US = 6 digits, no leading dot)

Known limitations

• Case-folded day / month names (day / Day / DAY / mon / Mon) and double-quoted literal escapes (e.g. "T") are deferred — they need a post-format substitution pass that the current chrono-backed implementation doesn't run.

AT TIME ZONE

ts AT TIME ZONE 'tz' shifts a timestamp between zone-naive and zone-aware representations. The right side must be a literal string naming an IANA zone ('America/New_York', 'UTC', 'Asia/Tokyo', 'Europe/Berlin', …); a column or expression on the right side is rejected at bind time.

There are two semantics depending on the input type:

Input	Behaviour	Output type
Naive TIMESTAMP	Interpret the wall-clock as local in tz, convert to UTC	Timestamp(Microsecond, Some("UTC"))
Tz-aware TIMESTAMPTZ / Timestamp(_, Some(_))	Preserve the instant; relabel for display in tz	Timestamp(Microsecond, Some("UTC"))

-- Naive → tz-aware: "noon in New York" → the corresponding UTC instant
SELECT TIMESTAMP '2024-06-15 12:00:00' AT TIME ZONE 'America/New_York';
-- 2024-06-15T16:00:00Z   (NYC noon in summer = UTC 16:00)

SELECT typeof(TIMESTAMP '2024-06-15 12:00:00' AT TIME ZONE 'America/New_York');
-- 'Timestamp(Microsecond, Some("UTC"))'

-- Tz-aware → tz-aware: the underlying instant is preserved
SELECT (TIMESTAMP '2024-06-15 12:00:00' AT TIME ZONE 'America/New_York')
        AT TIME ZONE 'Asia/Tokyo';
-- 2024-06-16T01:00:00Z   (same instant, displayed in Tokyo's frame)

AT TIME ZONE is the bridge between naive TIMESTAMP columns (timestamps without a zone) and TIMESTAMPTZ arithmetic. If your incoming column stores wall-clock values that are really "local in some zone", apply AT TIME ZONE '<their_zone>' before joining against a UTC-anchored series.

DATE + sub-day INTERVAL promotion

Adding an INTERVAL with any sub-day component (hours, minutes, seconds) to a DATE automatically promotes the result to TIMESTAMP, matching Postgres. Pure-day intervals stay on the DATE path.

-- Result is TIMESTAMP '2024-01-15 01:30:00' (DATE was promoted)
SELECT DATE '2024-01-15' + INTERVAL '90 minutes';

-- Result stays DATE '2024-01-25' (no promotion)
SELECT DATE '2024-01-15' + INTERVAL '10 days';

Conditional Functions

Control flow expressions for handling NULLs and branching logic.

Function	Description
CASE WHEN ... THEN ... ELSE ... END	Conditional expression (per-row short-circuit; THEN / ELSE branches are evaluated only on rows whose WHEN selected them)
coalesce(a, b, ...)	First non-null value (per-row short-circuit)
nullif(a, b)	Returns NULL if a = b
ifnull(a, b) / nvl(a, b)	Returns b if a is NULL (per-row short-circuit, same semantics as coalesce(a, b))
is_null(x)	Function-call form of the x IS NULL predicate (SQLite spelling). Returns BOOLEAN. The two-arg form ISNULL(x, y) is lowered to COALESCE(x, y) (T-SQL).
equal_null(a, b) / is_distinct_from(a, b)	NULL-safe equality / inequality. EQUAL_NULL(a, b) lowers to a IS NOT DISTINCT FROM b — returns TRUE when both are NULL, FALSE when exactly one is NULL, and the usual a = b otherwise. IS_DISTINCT_FROM(a, b) is its negation (a IS DISTINCT FROM b).
booland(a, b) / boolor(a, b) / boolxor(a, b) / boolnot(x)	Boolean function-call form of the logical operators. BOOLAND / BOOLOR / BOOLNOT map directly to AND / OR / NOT. BOOLXOR is decomposed as (a OR b) AND NOT (a AND b) so NULL propagates the standard way.

COALESCE, IFNULL, and NVL short-circuit per row: each subsequent argument is evaluated only on rows where every prior argument was NULL. This matches PostgreSQL / DuckDB / MySQL and lets you write the standard divide-by-zero defensive idiom:

-- Safe: the second argument is only evaluated on rows where qty IS NULL,
-- so the divide-by-zero on the dead branch never executes.
SELECT COALESCE(qty, 100 / 0) FROM orders;

COALESCE, GREATEST, LEAST, NVL, IFNULL, and NULLIF reject zero-argument calls at bind time. COALESCE / GREATEST / LEAST also promote Date + Timestamp mixes to Timestamp.

-- Categorize with CASE
SELECT order_id,
    CASE
        WHEN amount >= 1000 THEN 'high'
        WHEN amount >= 100  THEN 'medium'
        ELSE 'low'
    END AS tier
FROM orders;

-- Default to 0 if NULL
SELECT coalesce(discount, 0) AS discount FROM orders;

-- Avoid division by zero (nullif returns NULL when denominator is 0)
SELECT total / nullif(count, 0) AS avg_per_item FROM summary;

Type Functions

Function	Description
CAST(expr AS type) / TRY_CAST(expr AS type)	Convert to type. CAST errors on failure; TRY_CAST returns NULL on failure. Both forms trim surrounding whitespace for numeric / temporal targets — TRY_CAST(' 42 ' AS INT) returns 42, matching CAST (earlier TRY_CAST returned NULL for whitespace-padded numerics, so defensive CAST→TRY_CAST swaps on CSV-style inputs silently nuked rows).
to_string(expr) / to_varchar(expr)	Alias for CAST(expr AS VARCHAR). The 2-arg form TO_VARCHAR(date_or_ts, fmt) routes to TO_CHAR(date_or_ts, fmt).
typeof(expr)	Return the underlying Arrow type name as a string

TYPEOF(expr) returns the runtime Arrow type — useful for debugging implicit-cast and type-inference behaviour. It mirrors DuckDB's typeof.

SELECT typeof(1);                           -- 'Int64'
SELECT typeof(1.5);                         -- 'Decimal128(2, 1)'  (unquoted decimal literals are Decimal128)
SELECT typeof(1e5);                         -- 'Float64'           (scientific notation stays float)
SELECT typeof('hello');                     -- 'Utf8'
SELECT typeof(DATE '2025-01-15');           -- 'Date32'
SELECT typeof(TIMESTAMP '2025-01-15 10:00');-- 'Timestamp(Microsecond, None)'
SELECT typeof(CAST(1.5 AS DECIMAL(10, 2))); -- 'Decimal128(10, 2)'
SELECT typeof(ARRAY[1, 2, 3]);              -- 'List(Int64)'
SELECT typeof(INTERVAL '1 day');            -- 'Interval(MonthDayNano)'

-- Decimal / Decimal stays in Decimal128 — the result scale is
-- max(6, left.scale), precision is widened to 38 to leave room for
-- the quotient. The reported `typeof` matches the actual column
-- type produced by the divide.
SELECT typeof(CAST(10 AS DECIMAL(18, 2)) / CAST(3 AS DECIMAL(18, 2)));
-- 'Decimal128(38, 6)'

-- Decimal / Int also produces Decimal128 — the integer is promoted
-- into Decimal first, the divide preserves the decimal track, and
-- `typeof` reports the actual runtime column type (earlier the
-- column was Decimal128 but TYPEOF returned 'Float64', confusing
-- downstream clients that switched code paths on the report).
SELECT typeof(CAST(10 AS DECIMAL(18, 2)) / CAST(3 AS INT));
-- 'Decimal128(38, 6)'

CASE-branch unification preserves decimal precision: a CASE expression whose branches are a mix of Decimal128 values unifies to a Decimal128 wide enough to hold every branch, rather than silently collapsing to Float64.

Hash and Encoding Functions

Function	Description
md5(s)	MD5 hash (32-char hex)
sha256(s)	SHA-256 hash (64-char hex)
base64(s) / to_base64(s) / base64_encode(s) / encode_base64(s)	Base64 encode — returns VARCHAR. The portable _ENCODE / ENCODE_* spellings are aliases for the bare form.
from_base64(s) / base64_decode(s) / decode_base64(s)	Base64 decode — returns BINARY (wire layer renders raw bytes as \xHH… hex, consistent with UNHEX). The portable _DECODE / DECODE_* spellings are aliases.
hex(s) / to_hex(s) / hex_encode(s)	Hex encode — returns VARCHAR. HEX_ENCODE is the portable spelling.
unhex(s) / from_hex(s) / hex_decode(s)	Hex decode — returns BINARY. HEX_DECODE is the portable spelling.
encode(bytes, fmt) / decode(string, fmt)	PG-style codec dispatch. fmt is one of 'base64', 'hex', or 'escape' (escape only on DECODE); the binder rewrites to the named encoder / decoder. ENCODE(b, 'hex') ≡ HEX(b); DECODE(s, 'base64') ≡ FROM_BASE64(s).
to_binary(s [, fmt]) / try_to_binary(s [, fmt])	String-to-BINARY conversion. fmt is one of 'HEX' (default), 'BASE64', or 'UTF-8'. TO_BINARY errors on a malformed input; TRY_TO_BINARY returns NULL. Wire-layer rendering matches FROM_BASE64 / UNHEX — raw bytes as \xHH… over PG-wire, hex via the HTTP API.
try_to_double(s) / try_to_float(s) / try_to_real(s)	NULL-on-failure floating-point parsers. Lowered to TRY_CAST(s AS DOUBLE) (the FLOAT / REAL spellings widen to Float64 to match TRY_CAST(... AS FLOAT)).
gen_random_uuid() / uuid_string() / random_uuid() / uuid() / uuidv4()	Generate random UUID v4 (H2 / DuckDB / PG aliases)

JSON Functions

Extract and inspect values from JSON-encoded VARCHAR columns. For native semi-structured data, see Variant Functions.

Function	Description
json_extract(json, path) / get_json_object(json, path) / get_path(variant, dotted_key)	Extract value as JSON string (GET_JSON_OBJECT is the Hive / Spark spelling). GET_PATH(v, 'a.b') is a VARIANT dot-notation alias — the binder prepends '$.' to the second argument if missing so GET_PATH(v, 'a.b') ≡ JSON_EXTRACT(v, '$.a.b').
json_extract_string(json, path) / json_extract_scalar(json, path) / json_extract_path_text(json, path)	Extract value as plain string (Trino / PG aliases)
json_exists(json, path)	Path-existence test (SQL/JSON). Binder rewrites to JSON_EXTRACT(json, path) IS NOT NULL. Returns BOOLEAN.
json_array_length(json) / json_length(json)	Length of JSON array
json_keys(json) / object_keys(json)	Keys of JSON object (OBJECT_KEYS is an alias)
json_type(json)	JSON type as a lowercase string: 'object', 'array', 'number', 'string', 'boolean', or 'null' (matches PG / DuckDB)
is_object(v) / is_array(v)	VARIANT-introspection scalars: true if the JSON value at v is an object / array. Binder rewrite to JSON_TYPE(v) = 'object' / JSON_TYPE(v) = 'array'. NULL input propagates (because JSON_TYPE(NULL) is NULL and NULL = '…' is NULL).
json_valid(json)	True if valid JSON
json_object(k1, v1, k2, v2, ...)	Build a JSON object literal from key/value pairs (zero-arg form returns '{}')
json_array(v1, v2, ...)	Build a JSON array literal (zero-arg form returns '[]')
json_quote(s)	Quote and escape a value as a JSON string literal

-- Extract nested values (path uses $ notation)
SELECT json_extract(payload, '$.user.name') AS user_name,
       json_extract_string(payload, '$.status') AS status
FROM events;

-- Iterate over a JSON array
SELECT json_array_length(json_extract(data, '$.items')) AS item_count
FROM orders;

Supported path syntax

Gnok's JSON path is a strict subset of JSONPath: dot-notation field access ($.user.name) and bracketed numeric index access ($.items[0].name). Negative bracket indices are accepted and count from the end ($[-1] is the last element, matching DuckDB / MySQL).

Quoted member keys — for keys that contain ., [, or other path-syntax characters, both forms are accepted:

-- Dot-then-quoted
SELECT json_extract('{"a.b": 1}', '$."a.b"');     -- 1

-- Bracket-then-quoted (single or double quotes)
SELECT json_extract('{"a.b": 1}', '$["a.b"]');    -- 1
SELECT json_extract('{"a.b": 1}', E'$[\'a.b\']'); -- 1

The following constructs are not supported and raise an explicit error so users aren't silently handed NULL:

Construct	Example	Status
Wildcard array index	$.users[*].name	Error
Recursive descent	$..name	Error
Filter expression	$.users[?(@.age > 30)]	Error

Use UNNEST over the JSON array (after a typed extract) for the wildcard / filter use cases.

Array Functions

Operations on ARRAY (Iceberg list) columns. Use UNNEST to expand arrays into rows.

Function	Description
array_length(arr)	Array length
array_contains(arr, val)	Test membership
array_concat(arr1, arr2)	Concatenate arrays
array_append(arr, val) / array_prepend(val, arr)	Append / prepend a single element
array_position(arr, val) / array_index_of(arr, val) / index_of(arr, val)	1-based index of val, or NULL if not found (Trino / Hive aliases)
array_sort(arr)	Sort elements
array_distinct(arr)	Remove duplicates
array_slice(arr, begin, end)	Slice array with inclusive begin / end indices (PG / DuckDB semantics).
slice(arr, start, length)	Spark (start, length) slice. Binder rewrite to ARRAY_SLICE(arr, start, start + length - 1). Negative start (offset-from-end) and length-overflow truncation are preserved through the underlying ARRAY_SLICE path.
arrays_overlap(a, b) / array_overlap(a, b)	Spark "do these arrays share any element" boolean. Binder rewrite to ARRAY_LENGTH(ARRAY_INTERSECT(a, b)) > 0.
flatten(arr)	Flatten nested array one level
generate_series(start, stop [, step]) / sequence(start, stop [, step])	Generate integer series. SEQUENCE is the Spark alias and returns List(Int64) (the projection-schema return type is reported as List(Int64), not Utf8 — so Flight SQL / PG-wire clients decode the array natively rather than as a stringified list).
cardinality(arr)	Size of array or map

|| operator on arrays

The || operator concatenates arrays and now also handles array/scalar pairs without falling back to string-concatenation:

Form	Semantics
`ARRAY
`ARRAY
`scalar

SELECT ARRAY[1, 2, 3] || 4;       -- [1, 2, 3, 4]
SELECT 0 || ARRAY[1, 2, 3];       -- [0, 1, 2, 3]
SELECT ARRAY[1, 2] || ARRAY[3];   -- [1, 2, 3]

array_position not-found semantics

array_position(arr, x) returns NULL when x is not in arr, matching Postgres / DuckDB. Earlier builds returned 0 for not-found — if you have queries that test array_position(...) = 0, switch to array_position(...) IS NULL.

-- Generate a date series (useful for filling gaps in time-series data)
SELECT d::DATE AS day
FROM generate_series(1, 30) AS t(d);

-- Filter rows where the tags array contains 'urgent'
SELECT * FROM tickets WHERE array_contains(tags, 'urgent');

-- Slice first 3 elements (1-based, inclusive)
SELECT array_slice(items, 1, 3) FROM orders;

-- Spark (start, length) slice — rewritten to
-- ARRAY_SLICE(items, 2, 4)  (inclusive end = start + length - 1).
SELECT slice(items, 2, 3) FROM orders;

-- Do two arrays share any element?
SELECT arrays_overlap(ARRAY[1, 2, 3], ARRAY[3, 4, 5]);  -- true

Map Functions

Operations on MAP (Iceberg map) columns — key-value collections with typed keys and values.

Function	Description
map_extract(map, key)	Extract value by key
map_keys(map) / map_values(map)	Keys / values as arrays
map_contains(map, key)	Test key presence
map_entries(map)	Key-value pairs as list of structs
map_from_entries(list)	Construct map from entries

Struct Functions

Operations on STRUCT (Iceberg struct) columns — named field collections.

Function	Description
struct_extract(s, field)	Extract named field
struct_keys(s)	Field names as array
struct_pack(v1, v2, ...) / struct(v1, v2, ...) / row(v1, v2, ...)	Construct struct from positional values. STRUCT(v1, v2, ...) is the Spark alias and ROW(v1, v2, ...) is the standard-SQL spelling; both route to STRUCT_PACK. The named form STRUCT(v AS k) is not supported (sqlparser rejects it) — use STRUCT_PACK plus an outer AS rename, or NAMED_STRUCT / OBJECT_CONSTRUCT to build a JSON object instead.
named_struct(k1, v1, k2, v2, ...)	Spark / Hive named-struct constructor. Routes to the same JSON-object dispatch as OBJECT_CONSTRUCT / JSON_OBJECT — keys must be string literals; values may be any expression. Returns a JSON object string.

Vector Distance Functions

Compute distances between fixed-length float arrays for similarity search and ML applications. See Vector Operations for HNSW index support.

Function	Description
l2_distance(v1, v2)	Euclidean (L2) distance
l1_distance(v1, v2)	Manhattan (L1) distance
cosine_distance(v1, v2)	Cosine distance (1 − similarity). COSINE_DISTANCE(v, v) is clamped to exactly 0.0 (pgvector / Faiss parity) so identical vectors compare equal and stable KNN ORDER BY doesn't break on a positive ULP.
inner_product(v1, v2) / inner_product_distance(v1, v2) / vector_inner_product_distance(v1, v2)	Dot product (negative, for distance metric)
vector_norm(v)	L2 norm of a single vector
hamming_distance(v1, v2)	Hamming distance for binary embeddings
vector_avg(v)	Element-wise average aggregate

All vector-distance scalars project as Float64 in the result schema, so ORDER BY <distance>(...) sorts numerically and downstream arithmetic (distance + 1.0, distance * weight) type-checks cleanly. Earlier builds shipped INNER_PRODUCT_DISTANCE, L1_DISTANCE, VECTOR_NORM, and VECTOR_INNER_PRODUCT_DISTANCE with a Utf8 projection-schema type — runtime produced Float64 but the wire layer string-formatted each row, so KNN ORDER BY ... LIMIT sorted lexicographically ('-10.0' < '-1.0' < '-0.0' < '5.0' as strings) and rankings were silently wrong.

pgvector distance operators

Gnok also accepts the pgvector infix operators for similarity search, including against ARRAY[...] literals on either operand (earlier the operator rewriter only recognised (...) brackets, so an ARRAY literal on the left silently truncated the rewrite or left the right side as a bare identifier):

Operator	Lowers to	Notes
<->	l2_distance	Euclidean distance
<=>	cosine_distance	Cosine distance
<#>	inner_product (negated)	Negative inner product
<~>	hamming_distance	Hamming distance for binary embeddings

-- ARRAY literal on either side now lowers cleanly
SELECT id FROM docs ORDER BY embedding <=> ARRAY[0.1, 0.2, ...]::FLOAT[]
LIMIT 10;

AI/ML Functions

Scalar UDFs for in-query inference, embedding generation, and feature lookup. See AI/ML overview for the underlying primitives.

Function	Signature	Description
ML_PREDICT(model_name, ...args)	(VARCHAR, …) -> <model return type>	Invoke the ACTIVE version of a registered model. The first argument MUST be a string literal; remaining args are passed positionally to the model's input tensor. Works against ONNX / PyTorch / sklearn / native (KMeans, DNN, …) models uniformly. See ML_PREDICT.
EMBED(text)	(VARCHAR) -> FixedSizeList<Float32, N>	Generate an embedding via the configured embedding provider (managed by Gnok). Off until an organization administrator enables AI in Studio (Operations → AI Governance); counts against the organization's monthly token budget. The return type is FixedSizeList<Float32, N> where N is the provider's dimension (default 1536). Values render as JSON arrays through /api/query, as native list cells through Flight SQL, and feed directly into VECTOR(N) columns, COSINE_DISTANCE / L2_DISTANCE / DOT_PRODUCT, and the vector ANN index.
FEATURE_LOOKUP(group, key, feature)	(VARCHAR, BIGINT, VARCHAR) -> FLOAT64	Look up the named feature for key from the live snapshot of group. O(1) hash-indexed.
FEATURE_LOOKUP_AS_OF(group, key, feature, as_of_ms)	(VARCHAR, BIGINT, VARCHAR, BIGINT) -> FLOAT64	Point-in-time variant. Consults the in-memory snapshot ring first; falls through to the offline Iceberg table via time-travel on miss.

-- Inline inference + feature enrichment
SELECT t.transaction_id,
       ML_PREDICT('fraud_detector',
                  t.amount,
                  FEATURE_LOOKUP('user_features', t.user_id, 'avg_amount'))
              AS fraud_score
FROM transactions t
WHERE t.created_at > now() - INTERVAL '5 minutes';

-- Semantic search with inline embedding
SELECT id, title
FROM documents
ORDER BY COSINE_DISTANCE(embedding, EMBED('refund policy'))
LIMIT 10;

AI scalar LLM functions

A comprehensive family of LLM-backed scalar UDFs that operate on text columns inline in any SELECT, WHERE, or JOIN expression. They share a single dispatch path with input deduplication, bounded parallel concurrency, per-call timeout, and exponential-backoff retry. See the dedicated AI Scalar Functions page for service configuration, failure semantics, and cost guidance.

Function	Signature	Description
AI_COMPLETE(prompt) / AI_COMPLETE(prompt, max_tokens)	(VARCHAR [, BIGINT]) -> VARCHAR	Free-form completion. The 2-arg form sets the requested response-token limit per call, subject to service policy and your organization's AI Governance policy.
AI_GENERATE(prompt)	(VARCHAR) -> VARCHAR	Vendor-neutral alias of AI_COMPLETE(prompt).
AI_SUMMARIZE(text)	(VARCHAR) -> VARCHAR	Terse 1–3 sentence summary.
AI_CLASSIFY_TEXT(text, labels)	(VARCHAR, ARRAY<VARCHAR>) -> VARCHAR	Pick exactly one label from the supplied array.
AI_FILTER(prompt, text)	(VARCHAR, VARCHAR) -> BOOLEAN	Boolean LLM predicate — TRUE when text satisfies the natural-language prompt (arg 0 must be a string literal). Useful inline in WHERE / HAVING.
AI_TRANSLATE(text, target_language)	(VARCHAR, VARCHAR) -> VARCHAR	Translate text into the named target language.
AI_EXTRACT_ANSWER(text, question)	(VARCHAR, VARCHAR) -> VARCHAR	Extract the answer to question from text. Returns SQL NULL when the source doesn't contain the answer.
AI_SENTIMENT(text)	(VARCHAR) -> VARCHAR	Sentiment label: positive, negative, or neutral.
AI_FIX_GRAMMAR(text)	(VARCHAR) -> VARCHAR	Grammar/spelling/punctuation correction, meaning preserved.
AI_MASK(text, pii_types)	(VARCHAR, ARRAY<VARCHAR>) -> VARCHAR	Redact the listed PII entity types (empty array = all common PII). AI_REDACT is an alias.
AI_EXTRACT(text, json_schema)	(VARCHAR, VARCHAR) -> VARCHAR	Structured extraction into a JSON object matching json_schema.
AI_SQL(request)	(VARCHAR) -> VARCHAR	Natural-language → a single SQL statement (schema-agnostic; use ASK for grounded NL2SQL).
AI_VALIDATE(text, criteria)	(VARCHAR, VARCHAR) -> VARCHAR	LLM data-quality check; returns {"valid": <bool>, "reason": <text>} JSON.
AI_RERANK(query, candidates [, k])	(VARCHAR, ARRAY<VARCHAR> [, BIGINT]) -> ARRAY<VARCHAR>	Listwise LLM rerank: reorder candidates by relevance to query and return the top k (all of them if k omitted). Returns SQL NULL for a NULL query or empty candidate array.
AI_DEDUPE(items, threshold)	(ARRAY<VARCHAR>, DOUBLE) -> ARRAY<VARCHAR>	Fuzzy entity resolution: LLM groups near-duplicate entries and returns one canonical value per group. threshold (0–1) tunes merge strictness. Call over ARRAY_AGG(col).
AI_FORECAST(series, horizon [, season])	(ARRAY<DOUBLE>, BIGINT [, BIGINT]) -> ARRAY<DOUBLE>	Time-series forecast via native ARIMA/SARIMA — horizon future points. No LLM/API key required (always-on). Order the series with ARRAY_AGG(v ORDER BY ts).
AI_RAG(question, table, embed_col, k)	(VARCHAR, table, column, INT) -> VARCHAR	One-shot retrieval-augmented generation: embed question, vector-search the top k rows of table by embed_col, answer from them via AI_COMPLETE. embed_col may be a vector column (searched directly) or a text column (embedded on the fly). See AI Scalar Functions.
AI_SIMILARITY(a, b)	(VARCHAR, VARCHAR) -> DOUBLE	Cosine similarity of the two inputs' embeddings (1.0 = identical). Composes EMBED + COSINE_DISTANCE; needs the embedding provider configured.
AI_EMBED(text [, model])	(VARCHAR [, VARCHAR]) -> FixedSizeList<Float32, N>	Alias of EMBED.

Every LLM-backed scalar also has a TRY_-prefixed twin (TRY_AI_COMPLETE, TRY_AI_SENTIMENT, TRY_AI_RERANK, TRY_AI_DEDUPE, …) that returns SQL NULL for any cell whose LLM call fails instead of failing the query. (AI_FORECAST is the exception — it runs a classical ARIMA/SARIMA model, not an LLM, so it needs no API key and has no TRY_ twin.) Full configuration, error-handling, and cost guidance: AI Scalar Functions.

-- Classify support tickets in flight
SELECT ticket_id,
       AI_CLASSIFY_TEXT(message,
         ARRAY['billing', 'technical', 'feature_request', 'spam']) AS category,
       AI_SUMMARIZE(message) AS tldr,
       AI_SENTIMENT(message) AS mood
FROM support_tickets
WHERE created_at > now() - INTERVAL '1 hour';

Variant Functions (v3)

These functions operate on the VARIANT type, available on Iceberg format version 3 tables.

Function	Description
PARSE_JSON(string)	Parse a JSON string into VARIANT
TO_VARIANT(expr)	Convert a scalar value to VARIANT
VARIANT_GET(v, path, type)	Extract a typed value at a dot-path (e.g., VARIANT_GET(v, 'user.name', 'VARCHAR'))
VARIANT_EXTRACT_STRING(v, path)	Extract value as a plain string
VARIANT_TYPE(v)	Return the runtime type name ('object', 'string', 'int64', etc.)
VARIANT_AS_BOOL(v)	Cast VARIANT to BOOLEAN
VARIANT_AS_INT(v)	Cast VARIANT to BIGINT
VARIANT_AS_DOUBLE(v)	Cast VARIANT to DOUBLE
VARIANT_AS_STRING(v)	Cast VARIANT to VARCHAR
IS_VARIANT_NULL(v)	True if the VARIANT value is null

SELECT
    VARIANT_GET(payload, 'user', 'VARCHAR') AS user_name,
    VARIANT_GET(payload, 'metrics.latency_ms', 'DOUBLE') AS latency
FROM events
WHERE VARIANT_TYPE(payload) = 'object';

Spatial Functions (v3)

Spatial functions for GEOMETRY and GEOGRAPHY types, available on Iceberg format version 3 tables. Follows PostGIS naming conventions.

Function	Description
ST_POINT(x, y)	Construct a point geometry. x / y are auto-coerced to Float64 from any signed-integer or Float32 input — ST_POINT(0, 0) and ST_POINT(CAST(0 AS REAL), 0) work without an explicit cast (matches PG / DuckDB).
ST_DISTANCE(g1, g2)	Euclidean distance (GEOMETRY) or haversine distance (GEOGRAPHY)
ST_WITHIN(g1, g2)	True if g1 is entirely within g2
ST_CONTAINS(g1, g2)	True if g1 contains g2
ST_INTERSECTS(g1, g2)	True if geometries share any point
ST_AREA(g)	Area of a polygon
ST_LENGTH(g)	Length of a linestring
ST_ASTEXT(g)	Convert to Well-Known Text (WKT)
ST_GEOMFROMTEXT(wkt)	Parse WKT string to GEOMETRY
ST_GEOGFROMTEXT(wkt)	Parse WKT string to GEOGRAPHY
ST_X(g) / ST_Y(g)	Extract X/Y coordinates from a point
ST_SRID(g)	Get the spatial reference identifier
ST_TRANSFORM(g, target_srid)	Reproject to a different coordinate reference system
ST_BUFFER(g, distance)	Compute a buffer polygon around the geometry
ST_DWITHIN(g1, g2, distance)	True if distance between geometries ≤ threshold
ST_ENVELOPE(g)	Compute the bounding box as a polygon
ST_CENTROID(g)	Centroid (POINT) of any geometry
ST_CONVEX_HULL(g)	Convex hull polygon over a multi-point / multi-geometry input
ST_BOUNDARY(g)	Boundary geometry — endpoints for a LINESTRING, ring for a POLYGON, etc.
ST_GEOMETRY_TYPE(g)	Type name as VARCHAR, upper-case: POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION
ST_NUMPOINTS(g)	Total number of coordinate vertices, including across the parts of a Multi* or GeometryCollection

Supported CRS: WGS84 (EPSG:4326), Web Mercator (EPSG:3857), UTM zones (EPSG:326xx/327xx).

-- Find stores within 5km of a location
SELECT name, ST_DISTANCE(location, ST_POINT(-73.98, 40.75)) AS dist_m
FROM stores
WHERE ST_DWITHIN(location, ST_POINT(-73.98, 40.75), 5000)
ORDER BY dist_m;

H3 spatial indexing

Uber H3 hexagonal cell index. Cell IDs are returned as BIGINT so they can be partition keys, join keys, or grouping keys at full engine speed without serialization round-trips.

Function	Description
h3_latlng_to_cell(lat, lng, resolution)	Encode a lat/lng pair at a given resolution (0–15) into an H3 cell ID
h3_cell_to_lat(cell) / h3_cell_to_lng(cell)	Decode the center of a cell back to lat / lng
h3_is_valid(cell)	True if the integer is a well-formed H3 cell ID
h3_get_resolution(cell)	Cell's resolution level
h3_cell_to_parent(cell, resolution)	Parent cell at the requested coarser resolution
h3_cell_to_children(cell, resolution)	Array of child cells at the requested finer resolution
h3_grid_disk(cell, k)	Array of cells within k rings of cell (inclusive)
h3_grid_distance(a, b)	Hex grid distance between two cells (NULL if not in the same resolution / region)

-- "Pings within 1 km of any of these venues" via H3 prefix join
SELECT v.name, COUNT(*) AS hits
FROM venues   v
JOIN pings    p ON p.h3_r9 IN (
    SELECT cell FROM UNNEST(H3_GRID_DISK(v.h3_r9, 1)) AS t(cell)
)
GROUP BY v.name;

S2 spatial indexing

Google S2 spherical cell index. Same shape as the H3 helpers but for S2 levels (0–30).

Function	Description
s2_latlng_to_cell(lat, lng, level)	Encode a lat/lng at a given S2 level into a cell ID
s2_cell_to_lat(cell) / s2_cell_to_lng(cell)	Decode the center back to lat / lng
s2_is_valid(cell)	True if the integer is a well-formed S2 cell ID
s2_cell_level(cell)	S2 level of the cell
s2_cell_to_parent(cell, level)	Parent cell at the requested coarser level

Session Functions

Introspect the current session's identity and authorization context. Useful in row-level security policies and audit queries.

Function	Description
current_user()	Authenticated username
current_roles()	User's roles (array)
has_role(role)	True if user has the specified role
session_id()	Current session ID

Aggregate Functions

Aggregate functions compute a single result from a set of input rows. They can be used in SELECT with GROUP BY, in HAVING clauses, and as window functions with OVER. approx_count_distinct uses HyperLogLog for fast cardinality estimation with ~2% relative error.

Function	Description
count(*) / count(expr)	Row count
count(DISTINCT expr)	Distinct count (COUNT(DISTINCT *) is rejected — see below)
sum(expr)	Sum
avg(expr)	Average
min(expr) / max(expr)	Minimum / maximum
any_value(expr) / arbitrary(expr)	Arbitrary non-NULL value (ARBITRARY is the Trino / Presto spelling)
first(expr) / last(expr)	First / last value
stddev(expr) / stddev_samp(expr) / stddev_pop(expr)	Sample / population standard deviation
variance(expr) / var_samp(expr) / var_pop(expr)	Sample / population variance
string_agg(expr, delimiter) / listagg(expr, delimiter) / list_agg(expr, delimiter) / array_to_string_agg(expr, delimiter)	Concatenate strings with separator (LISTAGG / LIST_AGG are DuckDB / Trino spellings)
array_agg(expr) / arrayagg(expr) / collect_list(expr) / collect_set(expr)	Collect values into array (works under GROUP BY); COLLECT_SET deduplicates. Narrow integer / float input types are widened in the output element type — Int8 / Int16 / Int32 / UInt8 / UInt16 / UInt32 all collect into List(Int64); Float32 collects into List(Float64). The binder, analyzer, and decorrelator now agree on this rule, so a correlated ARRAY_AGG(int_col) reports the same List(Int64) schema as the uncorrelated form.
json_array_agg(expr) / json_agg(expr)	Collect values into a JSON array string
json_object_agg(key, value)	Collect key/value pairs into a JSON object string
percentile_cont(p) WITHIN GROUP (ORDER BY expr)	Continuous percentile
percentile_disc(p) WITHIN GROUP (ORDER BY expr)	Discrete percentile
median(expr)	Median — continuous interpolation (alias for percentile_cont(0.5))
mode(expr)	Most frequent value (returns Float64 for numeric / temporal / boolean inputs; preserves Utf8 / LargeUtf8 for string inputs)
grouping(col [, col, ...]) / grouping_id(col [, col, ...])	Bitmask identifying which GROUP BY columns are aggregated in the current row (ROLLUP / CUBE / GROUPING SETS)
approx_count_distinct(expr)	Approximate distinct count (HyperLogLog)
bool_and(expr) / bool_or(expr)	Boolean aggregate AND / OR
bit_and(expr) / bit_or(expr) / bit_xor(expr)	Bitwise aggregates
corr(y, x)	Pearson correlation coefficient
covar_pop(y, x) / covar_samp(y, x)	Population / sample covariance
regr_slope(y, x) / regr_intercept(y, x)	Linear regression
regr_r2(y, x)	R-squared
regr_count(y, x)	Count of non-NULL pairs
regr_avgx(y, x) / regr_avgy(y, x)	Mean of x / y over rows where both x and y are non-NULL
regr_sxx(y, x) / regr_syy(y, x) / regr_sxy(y, x)	Sum of squared deviations of x / y / cross-product, over pair-non-NULL rows
arg_min(val, key) / arg_max(val, key) / min_by(val, key) / max_by(val, key)	Value at min/max key. MIN_BY / MAX_BY are the Spark spellings. Known limitation: the global (ungrouped) form can return NULL when the query runs in parallel. Use a ROW_NUMBER() OVER (ORDER BY key) = 1 filter instead.
count_if(condition)	Count where condition is true
kurtosis(expr) / skewness(expr)	Distribution shape
entropy(expr)	Shannon entropy in bits
histogram(expr)	Frequency histogram (returns map)
kahan_sum(expr)	Numerically stable sum (Kahan compensated)
product(expr)	Multiplicative aggregate (always returns Float64, regardless of input type)

Approximate aggregates

For high-cardinality columns where an exact result is expensive but a small bounded error is acceptable.

Function	Description
approx_top_k(col [, k])	Top-k most frequent values (Space-Saving sketch). Returns a JSON string of {value, estimated_count} entries. Default k = 10. Works under GROUP BY and combines correctly across partitions (the final phase truncates back to k after merging partials).
approx_percentile(col, p)	Approximate percentile via a t-digest sketch. p ∈ [0, 1]. Faster than percentile_cont for very large inputs and bounded relative error (≈ 1% by default).
hll_sketch(col)	Build a HyperLogLog sketch for distinct-cardinality estimation. Returns a base64-encoded self-describing sketch (GHLL magic header + 16384-bucket payload). Persist as VARCHAR to compose sketches across shards / partitions / time windows.
hll_merge(sketch_col)	Aggregate over an existing column of hll_sketch outputs; pointwise-max combines the buckets. Returns a sketch of the same shape. Use approx_count_distinct(col) for a one-shot cardinality, or call hll_merge and decode externally.

-- Top categories per partition, then a roll-up across partitions
SELECT region, APPROX_TOP_K(product, 5) AS top5
FROM events
GROUP BY region;

-- Pre-aggregated HLL sketches per day, merged at query time
WITH per_day AS (
  SELECT day, HLL_SKETCH(user_id) AS sk FROM events GROUP BY day
)
SELECT HLL_MERGE(sk) AS sketch_for_window
FROM per_day
WHERE day BETWEEN DATE '2026-01-01' AND DATE '2026-01-31';

Statistical tests

Two-sample hypothesis tests over paired numeric columns. Each test ships with a _P sibling that returns the p-value directly; the bare form returns the test statistic.

Function	Description
t_test(a, b) / t_test_p(a, b)	Welch's t-test on two numeric columns (unequal variances). Returns the t-statistic / two-sided p-value.
chi_square_test(a, b) / chi_square_test_p(a, b)	Pearson chi-square test of independence over paired observations. Returns the test statistic / p-value.
mann_whitney(a, b) / mann_whitney_p(a, b)	Mann-Whitney U test (rank-based, non-parametric). Returns the U statistic / two-sided p-value.

-- A/B test: did the new ranker move click-through?
SELECT
    T_TEST(arm_a_ctr, arm_b_ctr) AS t,
    T_TEST_P(arm_a_ctr, arm_b_ctr) AS p_value
FROM ab_results;

-- Comma-separated list of regions per customer
SELECT customer_id, string_agg(region, ', ' ORDER BY region) AS regions
FROM orders GROUP BY customer_id;

-- 95th percentile of response times
SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95
FROM requests;

-- Which product had the highest total revenue?
SELECT arg_max(product_name, total_revenue) AS top_product
FROM (SELECT product_name, SUM(amount) AS total_revenue FROM sales GROUP BY 1);

-- Count orders over $100 without a subquery
SELECT count_if(amount > 100) AS big_orders, COUNT(*) AS total FROM orders;

-- Quick frequency distribution
SELECT histogram(status) FROM orders;
-- {'pending': 150, 'shipped': 820, 'cancelled': 30}

-- Linear regression: price sensitivity
SELECT regr_slope(quantity, price) AS price_sensitivity,
       regr_r2(quantity, price) AS r_squared
FROM products;

-- ARRAY_AGG with GROUP BY collects per-group lists
SELECT region, ARRAY_AGG(customer_id ORDER BY customer_id) AS customers
FROM orders
GROUP BY region;

AISQL aggregates (LLM-backed)

LLM-backed aggregates that fold a group of text rows into one result via a binder rewrite (STRING_AGG over the group, then a scalar AI call). Use them with GROUP BY, or without — an ungrouped query folds the whole result set into a single group. Each call hits the configured AI provider; see AI Scalar Functions for provider availability and cost guidance and the AISQL cookbook for worked examples.

Function	Description
AI_AGG(prompt, text)	Apply the natural-language prompt (arg 0, a string literal) across all text values in the group; returns the LLM's response.
AI_SUMMARIZE_AGG(text)	One summary of all text values in the group.
AI_CLASSIFY_AGG(text, labels)	Pick one label from labels (an ARRAY<VARCHAR>) for the group's combined text.
AI_FILTER_AGG(prompt, text)	Boolean — TRUE when the group's combined text satisfies the natural-language prompt (arg 0 a string literal).

Each of these has a TRY_-prefixed variant (TRY_AI_AGG, TRY_AI_SUMMARIZE_AGG, TRY_AI_CLASSIFY_AGG, TRY_AI_FILTER_AGG) that yields SQL NULL for a group whose LLM call fails instead of failing the whole query.

SELECT region, AI_SUMMARIZE_AGG(review) AS summary
FROM reviews
GROUP BY region;

Aggregate semantics notes

MEDIAN — continuous interpolation

MEDIAN(x) lowers to PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x), matching Postgres / DuckDB / Oracle. Even-cardinality inputs return the linear interpolation of the two middle values, e.g. MEDIAN(1, 2, 3, 4) = 2.5. Use PERCENTILE_DISC(0.5) explicitly if you want the discrete lower-middle (= 2).

PERCENTILE_CONT(p) / PERCENTILE_DISC(p) — p literal type

The percentile argument p may be written as any of: PERCENTILE_CONT(0.5) (unquoted decimal — Decimal128), PERCENTILE_CONT(1) / PERCENTILE_CONT(0) (integer endpoint), or PERCENTILE_CONT(CAST(0.95 AS DOUBLE)) (explicit float). All three are recognised by the bind-time domain check and by the planner's param_value extraction, so the value you write is the percentile the aggregate computes. Earlier the unquoted-decimal form (PERCENTILE_CONT(0.9)) fell through to a 0.5 default at the planner because the Decimal128 shape wasn't recognised — the aggregate then silently returned the median.

STDDEV_SAMP / VAR_SAMP with n=1

Sample standard deviation and sample variance return NULL when the group has exactly one non-NULL row (the n − 1 divisor is undefined). Population variants (STDDEV_POP / VAR_POP) return 0 for a single-row group as before. Matches Postgres.

Regression aggregates — pair-NULL handling

All two-argument regression aggregates (CORR, COVAR_POP, COVAR_SAMP, REGR_SLOPE, REGR_INTERCEPT, REGR_R2, REGR_COUNT, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY) skip rows where either x or y is NULL — every component sums over the same pair-non-NULL row set. This matches the SQL standard and Postgres; e.g. CORR over (1,1), (2,NULL), (3,3) returns 1.0 (the pair (2, NULL) is dropped from every component, not just from REGR_COUNT). Earlier builds let individual components see different row masks, which silently produced wrong correlations and the NULL defence-in- depth fall-back on legitimate inputs.

SUM(DISTINCT expr) / AVG(DISTINCT expr) over an expression

SUM(DISTINCT expr) and AVG(DISTINCT expr) now work for any expression, not just bare columns. Earlier the rewriter that pre-deduplicates (group_by, dedup_expr) left the outer aggregate referencing the original expression while the inner Aggregate's output schema named the synthetic column __group_0, so SUM(DISTINCT qty + 1) either errored (Column 'qty' not found) or silently returned NULL:

-- Now returns the distinct-sum of (qty + 1) per group
SELECT region, SUM(DISTINCT qty + 1) AS s, AVG(DISTINCT qty + 1) AS a
FROM orders
GROUP BY region;

COUNT(DISTINCT expr) and MIN(DISTINCT expr) / MAX(DISTINCT expr) were never affected — COUNT uses a different codepath, and MIN / MAX are dedup-insensitive so the rewriter strips DISTINCT in place.

Multiple DISTINCT aggregates with different arguments

A single GROUP BY scope that mixes SUM/AVG/STDDEV/VAR DISTINCT over different argument expressions, or mixes a duplicate-sensitive DISTINCT aggregate with a duplicate-sensitive non-DISTINCT aggregate, is rejected at bind time:

-- Error: the pre-dedup rewrite needed for SUM(DISTINCT v) would
-- change the row count seen by the plain SUM(v).
SELECT SUM(DISTINCT v), SUM(v) FROM t;

Earlier this silently returned the non-distinct sum for both slots (the runtime hash-aggregate only honours DISTINCT for COUNT). Rewrite as separate scalar subqueries or a UNION ALL of single-bucket aggregations:

SELECT (SELECT SUM(v) FROM (SELECT DISTINCT v FROM t)) AS sum_distinct,
       (SELECT SUM(v) FROM t)                          AS sum_all;

COUNT(DISTINCT a), COUNT(DISTINCT b) is unaffected (the COUNT codepath dedups independently), and a single DISTINCT bucket combined with dedup-insensitive MIN/MAX over the same argument still works.

GROUPING() / GROUPING_ID() argument must be a grouping key

In a plain GROUP BY (no ROLLUP / CUBE / GROUPING SETS), GROUPING(col) is only meaningful for a column that appears in the GROUP BY clause (it always returns 0 there). Calling it on a non-grouping column now errors at bind time instead of silently returning 0 (which falsely implied the column was a grouping key):

-- Error: 'b' is not a grouping expression of this query level
SELECT a, GROUPING(b) FROM t GROUP BY a;

-- OK
SELECT a, GROUPING(a) FROM t GROUP BY a;          -- always 0
SELECT a, GROUPING(a) FROM t GROUP BY ROLLUP(a);  -- 0 or 1 per subtotal

COUNT(DISTINCT *) is rejected

COUNT(DISTINCT *) raises a bind-time error rather than silently counting all rows. The semantics aren't well-defined cross-engine (Postgres rejects it). Rewrite as COUNT(DISTINCT (col1, col2, ...)) (composite distinct) or COUNT(*) if you actually wanted a row count.

ROLLUP / CUBE / GROUPING SETS over empty input

When the input table is empty, queries with GROUP BY ROLLUP(...) / CUBE(...) / GROUPING SETS(... ()) still emit one grand-total row per () subset — matching Postgres / DuckDB. Earlier the empty-input path skipped the Partial-side synthesis, so SELECT SUM(x) FROM empty GROUP BY ROLLUP(a) returned zero rows instead of one grand-total row with NULL group keys.

-- Returns one row: (NULL, 0) — the grand total over zero rows
SELECT a, COUNT(*) FROM (SELECT 1 AS a WHERE FALSE) t
GROUP BY ROLLUP(a);

Correlated ANY_VALUE(int) / MODE(int) preserve input type

The decorrelator's per-aggregate schema-build rule for ANY_VALUE / ARBITRARY / MODE preserves the input numeric type. A correlated (SELECT ANY_VALUE(int_col) FROM t WHERE t.k = a.k) returns Int32 instead of Float64 (matching the uncorrelated form and the binder's schema for the same aggregate).

GROUPING / GROUPING_ID multi-arg form

GROUPING(a, b, …) and the alias GROUPING_ID(a, b, …) accept multiple arguments and return a bitmask where bit n − i − 1 is set if column i is rolled up in the current output row. Useful for distinguishing subtotal rows produced by ROLLUP, CUBE, or GROUPING SETS:

SELECT region, city,
       GROUPING(region, city) AS gid,
       SUM(amount) AS total
FROM orders
GROUP BY ROLLUP(region, city)
ORDER BY gid;
-- gid=0  → (region, city) leaf rows
-- gid=1  → (region) subtotals (city is rolled up)
-- gid=3  → grand total (both rolled up)

FILTER (WHERE …) clause

Aggregate functions support a FILTER (WHERE predicate) clause that limits the rows fed into the aggregate without affecting other aggregates in the same SELECT:

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'shipped') AS shipped,
    SUM(amount) FILTER (WHERE region = 'US') AS us_revenue
FROM orders;

FILTER is also valid on aggregate window functions (e.g. SUM(...) FILTER (...) OVER (...)) but is rejected at bind time on non-aggregate window functions (RANK, ROW_NUMBER, NTILE, LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE, PERCENT_RANK, CUME_DIST). Postgres has the same restriction; the clause was previously silently dropped, hiding user intent.

Window Functions

Window functions compute a value for each row over a sliding frame of related rows, without collapsing groups. All aggregate functions can also be used as window functions with an OVER clause. See SELECT: Window Functions for frame syntax (ROWS, RANGE, GROUPS).

Function	Description
row_number()	Sequential row number
rank()	Rank with gaps
dense_rank()	Rank without gaps
percent_rank()	Relative rank (0.0 to 1.0)
cume_dist()	Cumulative distribution
lag(expr [, offset [, default]])	Value from offset rows before; default must be type-compatible with expr
lead(expr [, offset [, default]])	Value from offset rows after; default must be type-compatible with expr
first_value(expr)	First value in the frame (frame-aware — see notes below)
last_value(expr)	Last value in the frame (frame-aware — see notes below)
nth_value(expr, n)	Nth value in the frame (n must be ≥ 1)
ntile(k)	Divide into k buckets; remainder rows go to the early buckets

Window-function semantics notes

Frame-aware FIRST_VALUE / LAST_VALUE / NTH_VALUE

These three functions honour the window frame, not the partition, matching Postgres and the SQL standard. With the default frame (ROWS UNBOUNDED PRECEDING TO CURRENT ROW):

• FIRST_VALUE(x) OVER (ORDER BY ts) returns the first row's value in the partition (frame starts at UNBOUNDED PRECEDING).
• LAST_VALUE(x) OVER (ORDER BY ts) returns the current row's value (the frame ends at CURRENT ROW).

To get partition-end semantics for LAST_VALUE, set the frame explicitly:

LAST_VALUE(x) OVER (
    ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Earlier builds applied these functions to the full partition regardless of the frame clause, which silently disagreed with Postgres / DuckDB.

NTILE(k) remainder distribution

NTILE(k) distributes the remainder N % k extras to the early buckets, matching Postgres and the SQL standard. For 10 rows split into 3 buckets, sizes are [4, 3, 3] (not [4, 4, 2]).

NTH_VALUE(x, n) requires n ≥ 1

NTH_VALUE(expr, 0) raises a bind-time error (Postgres rejects n ≤ 0). Pass 1 for the first frame row, or use FIRST_VALUE(expr).

LAG / LEAD default-value type check

The optional default argument to LAG / LEAD must be type- compatible with the value expression — numeric defaults for numeric columns, string defaults for string columns, etc. NULL defaults are always accepted and intra-kind widening (e.g., INT default for a BIGINT column) is allowed. Cross-kind defaults (string default for a numeric column) raise a bind error mirroring Postgres' "function lag(integer, integer, unknown) does not exist".

LAG / LEAD default must be a literal

The default argument must be a literal (string, number, boolean, NULL, or a typed literal like DATE '2024-01-01'). A per-row default expression — anything that references a column — is rejected at bind time with:

LAG's default value must be a literal — per-row default
expressions are not supported

Earlier builds silently produced NULL on the boundary row instead of evaluating the per-row default, which masked user intent.

-- OK: literal default
LAG(v, 1, 0) OVER (ORDER BY ts)
LAG(v, 1, NULL) OVER (ORDER BY ts)

-- Error: per-row default expression
LAG(v, 1, v * 10) OVER (ORDER BY ts)

If you need a per-row fallback, materialise the expression via COALESCE after the LAG:

SELECT COALESCE(LAG(v) OVER (ORDER BY ts), v * 10) FROM t;

Sequence Functions

Function	Description
nextval('sequence_name')	Advance the sequence and return the next value
currval('sequence_name')	Return the last value generated in this session (no advance)

See CREATE SEQUENCE for creating and managing sequences.