velaria-sql-capabilities

name	velaria-sql-capabilities
description	Velaria SQL v1 syntax, functions, and boundaries. What SQL works in the Velaria local analytics engine, what does not, and how to write queries.

Velaria SQL Capabilities

Velaria uses PostgreSQL-compatible parsing (libpg_query frontend) for familiar SQL syntax, but execution is limited to the Velaria local analytics subset.

Key rule: libpg_query parse success does not mean Velaria support. Unsupported features are rejected with structured unsupported_sql_feature diagnostics.

SQL Frontend Modes

Frontend	Env	Description
legacy	default	Hand-written parser
pg_query	VELARIA_SQL_FRONTEND=pg_query	libpg_query-based, PG syntax compatible
dual	VELARIA_SQL_FRONTEND=dual	Dual-run: legacy executes, pg_query compares

Supported SELECT Syntax

SELECT [col | func(col) AS alias | *]
FROM table [alias]
[WHERE pred [AND|OR pred]]
[GROUP BY col [, col]]
[ORDER BY col [ASC|DESC]]
[LIMIT n]

Predicates: =, !=, <>, <, <=, >, >=, AND, OR, NOT

Aggregate Functions

COUNT(*), COUNT(col), SUM, MIN, MAX, AVG

Scalar Functions

String: LOWER, UPPER, TRIM, LTRIM, RTRIM, LENGTH/LEN/CHAR_LENGTH, REVERSE, CONCAT, CONCAT_WS, LEFT, RIGHT, SUBSTR/SUBSTRING, POSITION, REPLACE

Numeric: ABS, CEIL, FLOOR, ROUND

DateTime: YEAR, MONTH, DAY, ISO_YEAR, ISO_WEEK, WEEK, YEARWEEK, NOW, TODAY, CURRENT_TIMESTAMP, UNIX_TIMESTAMP

Cast: CAST(expr AS type) where type is STRING, INT, DOUBLE, BOOL

Functions can be nested: SUBSTR(CAST(col AS STRING), 1, 6)

DDL / DML (legacy frontend only)

CREATE TABLE t (col1 STRING, col2 INT);
CREATE SOURCE TABLE src USING csv OPTIONS(path:'/data.csv');
CREATE SINK TABLE sink (col1 STRING, col2 INT);
INSERT INTO t VALUES ('a', 1);
INSERT INTO t SELECT col1, col2 FROM other;

Constraints: SOURCE TABLE is read-only. SINK TABLE cannot be queried as input.

Stream SQL

INSERT INTO output_sink
SELECT region, SUM(amount) AS total
FROM input_stream
WHERE amount > 0
GROUP BY region;

Only INSERT INTO <sink> SELECT ... via session.startStreamSql().

Unsupported PostgreSQL Features

Feature	Status	Workaround
CTE / WITH	Rejected	Materialize as temp view, query separately
Subquery	Rejected	Same as CTE
Window functions (batch)	Rejected	Use stream SQL WINDOW BY
UNION / INTERSECT / EXCEPT	Rejected	Merge in client
DISTINCT ON	Rejected	Use GROUP BY
DML/DDL via pg_query	Rejected	Use legacy frontend
Multi-statement	Rejected	Execute separately
LATERAL JOIN	Rejected	Redesign query

Identifier Rules

• Quoted identifiers ("col"): exact match only
• Unquoted: exact → case-insensitive → normalized match
• Unicode column names: normalize via velaria_dataset_normalize first

Agent Quick Reference

Parse SQL: session.sql(sql_str) Explain plan: session.explainSql(sql_str) Check frontend: session.sqlFrontendName()

Error types from structured diagnostics: parse_error, unsupported_sql_feature, bind_error, execution_error

Always call velaria_sql_capabilities or velaria_sql_function_search for up-to-date function catalog rather than guessing.