Skip to Content
New EngineQuery DataExpressions

Scalar expressions are available in projection, WHERE, HAVING, and some other clauses, subject to the clause-specific limits in the SELECT reference.

Supported expression forms:

FormExample
Column referenceuser_id, events.user_id
Literal42, 3.14, 'text', true, NULL
Arithmetica + b, a - b, a * b, a / b
Comparisona = b, a <> b, a > b, a <= b
Boolean logica AND b, a OR b, NOT a
CastCAST(a AS BIGINT)
Null testa IS NULL, a IS NOT NULL
Betweenscore BETWEEN 0 AND 1
Pattern matchname LIKE 'spql.%', name ILIKE 'spiraldb.%'
Simple searched caseCASE WHEN ok THEN value ELSE 0 END
Function calladd(left => a, right => b) or spql.add(a, b) when present in the engine callable catalog

Current limitations:

  • Unary minus for general expressions is not supported.
  • IS TRUE, IS FALSE, IS UNKNOWN, and IS DISTINCT FROM are not supported.
  • TRY_CAST is not supported.
  • LIKE ANY, ILIKE ANY, LIKE ... ESCAPE, ILIKE ... ESCAPE, SIMILAR TO, and RLIKE are not supported.
  • IN, subquery expressions, tuples, and arrays are not yet supported by the binder.
  • CASE supports one searched WHEN branch and requires ELSE.
  • Function calls support positional and named arguments. Function parameters, DISTINCT, FILTER, call-level ORDER BY, and OVER are not supported.

Cast targets

Supported cast target names include:

  • BOOLEAN
  • TINYINT, INT8, I8
  • SMALLINT, INT16, I16
  • INT, INTEGER, INT32, I32
  • BIGINT, INT64, I64
  • UINT8, U8
  • UINT16, U16
  • UINT32, U32
  • UINT64, U64
  • REAL, FLOAT, FLOAT32, F32
  • DOUBLE, FLOAT64, F64
  • VARCHAR, TEXT, STRING, UTF8
  • DECIMAL(p[, s]), NUMERIC(p[, s])

All cast targets currently bind as nullable output types.

Registered extension dtype factories are also valid cast targets. The default core session installs spiral-datetime, which registers temporal factories:

SELECT
  CAST(day_number AS date(days)) AS day,
  CAST(ts_ms AS timestamp(ms, 'UTC')) AS event_time
FROM events;

Plugin factories such as blob(), jpeg(), jpeg_bytes(), exif(), and image_view() are available when the corresponding default CLI plugins are registered.

Temporal functions

The default core session also registers named datetime functions under spql.dt. Use these names directly; standard SQL temporal syntax such as DATE '2026-06-29', EXTRACT, AT TIME ZONE, and INTERVAL is still a planned compatibility layer.

SELECT
  spql.dt.to_date('2026-06-29') AS day,
  spql.dt.year(spql.dt.to_timestamp('2026-06-29T12:34:56')) AS year,
  spql.dt.duration_total(spql.dt.to_duration('2h'), 's') AS seconds;

Datetime generators are table functions and emit a top-level value column:

SELECT value
FROM spql.dt.bdate_range('2026-06-29', '2026-07-03');

Implemented generators include spql.dt.date_range, spql.dt.timestamp_range, spql.dt.time_range, spql.dt.timedelta_range, spql.dt.period_range, spql.dt.bdate_range, and spql.dt.generate_series.

Last updated on
SELECTFunctions