Queries

Goal

Reference for supported query forms: CTE, JOIN, 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

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;

Supported are: INNER JOIN, LEFT JOIN.

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;

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
`UNION` / `INTERSECT` / `EXCEPT`	`0A000`
Window functions (`OVER (...)`)	`0A000`
`ORDER BY ... NULLS FIRST\|LAST`	`0A000`
`WITH RECURSIVE`	`0A000`
Data-modifying CTEs	`0A000`

AngaraStat surfaces

AngaraBase provides built-in statistical views.

`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`	`0A000`
Data-modifying CTE	`0A000`
`UNION` / `INTERSECT` / `EXCEPT`	`0A000`
Window functions	`0A000`
`ORDER BY ... NULLS FIRST\|LAST`	`0A000`
`ORDER BY` ordinal out of range	`42P10`
Expression type error	`42883`

AngaraBook