Queries

Reference for supported query forms: CTE, JOIN, subqueries, set operations (UNION/INTERSECT/EXCEPT), window functions, aggregation, sorting, and statistical surfaces.

Non-recursive WITH (CTE)

AngaraBase supports limited (non-recursive) CTEs as a derived source in FROM:

WITH recent AS (
 SELECT id, v FROM t WHERE v > 100
)
SELECT r.id, r.v
FROM recent r
ORDER BY r.id;

Supported CTE projection forms

WITH c AS (SELECT id, v FROM t)
SELECT * FROM c WHERE id < 10;

Explicitly unsupported

WITH RECURSIVE — 0A000 feature_not_supported
Data-modifying CTEs (WITH ... INSERT/UPDATE/DELETE) — 0A000 feature_not_supported

JOINs

All five kinds are supported: INNER, LEFT [OUTER], RIGHT [OUTER], FULL [OUTER], CROSS.

SELECT a.id, b.name
FROM orders a
INNER JOIN customers b ON a.customer_id = b.id;

SELECT a.id, b.name
FROM orders a
LEFT JOIN customers b ON a.customer_id = b.id;

SELECT a.id, b.name
FROM orders a
FULL OUTER JOIN customers b ON a.customer_id = b.id;

The join condition is given only via ON <expr> (a CROSS JOIN needs none). USING (...) and NATURAL JOIN are not supported — 0A000.

CROSS JOIN is bounded by the row budget sql.join.max_cross_join_rows (default 1,000,000; env ANGARABASE_MAX_CROSS_JOIN_ROWS); overflow returns 54000 program_limit_exceeded.

ORDER BY

SELECT * FROM t ORDER BY v;
SELECT * FROM t ORDER BY v ASC;
SELECT * FROM t ORDER BY v DESC;

ORDER BY <expr> supports limited scalar expressions already supported by the engine.

Support for aliases

ORDER BY can reference aliases defined in the SELECT list using the AS keyword. This works for both regular columns and aggregation results:

-- Alias for an expression
SELECT x * 2 AS doubled FROM t ORDER BY doubled;

-- Alias for aggregation
SELECT grp, sum(amount) AS total 
FROM t 
GROUP BY grp 
ORDER BY total DESC;

Column ordinals

Sorting by the ordinal number of a column in the SELECT list (1-based) is supported, according to the SQL:2011 standard:

-- Sort by the first column (a)
SELECT a, b FROM t ORDER BY 1;

-- Sort by the second column (b) in descending order
SELECT a, b FROM t ORDER BY 2 DESC;

If the specified ordinal is out of range for the number of columns in the query (or equals 0), a 42P10 (invalid_column_reference) error is returned.

NULL ordering

In ASC order, NULL is treated as the largest value (appears last).
In DESC order, NULL appears first.
Explicit NULLS FIRST / NULLS LAST is not supported — 0A000.

GROUP BY / HAVING

SELECT v, COUNT(*) AS cnt
FROM t
GROUP BY v
HAVING COUNT(*) > 1;

DISTINCT is supported; DISTINCT ON (...) → 0A000.

Subqueries

IN (...), EXISTS / NOT EXISTS, and scalar subqueries in the SELECT list are supported (including correlated):

SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM items i WHERE i.order_id = o.id);
SELECT id, (SELECT COUNT(*) FROM items i WHERE i.order_id = o.id) AS n FROM orders o;
SELECT * FROM t WHERE id IN (SELECT order_id FROM items);

Set operations (UNION / INTERSECT / EXCEPT)

SELECT id FROM a
UNION       -- with dedup
SELECT id FROM b;

SELECT id FROM a UNION ALL  SELECT id FROM b;   -- no dedup
SELECT id FROM a INTERSECT  SELECT id FROM b;
SELECT id FROM a EXCEPT     SELECT id FROM b;

Window functions (`OVER (...)`)

Supported: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE and the window aggregates SUM/COUNT/AVG/MIN/MAX with PARTITION BY / ORDER BY:

SELECT id, grp,
       ROW_NUMBER() OVER (PARTITION BY grp ORDER BY id) AS rn,
       SUM(amount)  OVER (PARTITION BY grp)             AS grp_total
FROM t;

Not supported (0A000): NTILE, named windows (WINDOW clause).

LIMIT / OFFSET

SELECT * FROM t ORDER BY id LIMIT 10;
SELECT * FROM t ORDER BY id LIMIT 10 OFFSET 20;

When incompatible types are used in arithmetic expressions (for example, attempting to add a string to a number 'string' + 1), AngaraBase returns a 42883 (undefined_function / operator not found) error.

Note: string literals containing numbers (e.g., '123') may be automatically cast to a numeric type depending on the context.

Unsupported query forms

Form	SQLSTATE
`WITH RECURSIVE`	`0A000`
Data-modifying CTEs (`WITH … INSERT/UPDATE/DELETE`)	`0A000`
`DISTINCT ON (...)`	`0A000`
`NTILE`, named windows (`WINDOW` clause)	`0A000`
`JOIN … USING (...)` / `NATURAL JOIN`	`0A000`
`ORDER BY ... NULLS FIRST\|LAST`, `COLLATE`	`0A000`

AngaraStat surfaces

Built-in statistical views — what the planner relies on for cardinality estimation. Useful to see which columns have statistics (histogram/MCV/HLL) and how fresh they are (*_epoch); empty histogram/MCV means Level 2 has not been collected for that column yet.

`sys.table_stats`

Column	Description
`stats_level_max`	Max collection level configured
`last_committed_rowid`	Last committed row ID
`last_insert_epoch`	Epoch of last insert
`last_mutation_epoch`	Epoch of last mutation

`sys.column_stats`

Column	Description
`ndv_approx`	Approximate number of distinct values (HLL)
`col_min`	Column minimum (typed `Value`)
`col_max`	Column maximum (typed `Value`)
`null_count`	Number of NULLs
`stats_epoch`	Stats collection epoch
`hll_enabled`	Whether HLL tracking is active
`histogram_bounds`	Equi-depth histogram boundaries (Level 2)
`mcv_values`	Most common values (Level 2)
`mcv_frequencies`	MCV frequencies (Level 2)
`reservoir_size`	Reservoir sample size (Level 2)
`reservoir_epoch`	Reservoir epoch (Level 2)
`reservoir_drift_count`	Drift count since last reservoir refresh (Level 2)

SELECT * FROM sys.table_stats WHERE table_name = 'events';
SELECT * FROM sys.column_stats WHERE table_name = 'events' AND column_name = 'ts';

Expected SQLSTATE

Situation	SQLSTATE
`WITH RECURSIVE` / data-modifying CTE	`0A000`
`DISTINCT ON`, `NTILE`, named windows	`0A000`
`ORDER BY ... NULLS FIRST\|LAST` / `COLLATE`	`0A000`
`JOIN ... USING (...)` / `NATURAL JOIN`	`0A000`
`CROSS JOIN` over the row budget	`54000`
`ORDER BY` ordinal out of range	`42P10`
Expression type error	`42883`

AngaraBook