Special Statements

This page covers KQL syntax elements that don't fit neatly into a single function category — including inline data tables, duration literals, dynamic values, verbatim strings, pipe composition, datetime/timespan arithmetic, and a few preprocessing transformations the compiler applies automatically before parsing.

Legend: ✅ Supported · ⚠️ Approximated · ❌ Not Supported · 🔄 Rewritten

Feature	SQLite	MySQL	ClickHouse	PostgreSQL	DuckDB	Notes
datatable	✅	✅	✅	✅	✅	CREATE TEMP TABLE + INSERT INTO
Bare duration literals (e.g. 1d)	⚠️	✅	✅	✅	✅	SQLite has no INTERVAL type — rendered as a seconds number; others use INTERVAL
dynamic({}) / dynamic([])	✅	✅	✅	✅	✅	Rendered as JSON string constant; nested containers rewritten before parsing
Verbatim strings @"..."	✅	✅	✅	✅	✅	Unescaped string literals
Pipe chaining (|)	✅	✅	✅	✅	✅	Each pipe stage wraps as subquery when needed
datetime(bare-date) preprocessing	✅	✅	✅	✅	✅	Auto-quoted before parsing
between(datetime() .. datetime()) preprocessing	✅	✅	✅	✅	✅	Rewritten to >= / <= before parsing
Datetime/timespan arithmetic (+ - * / %)	✅	✅	✅	✅	✅	Timespan operands lowered to a seconds number; SQLite uses datetime() modifiers, others use INTERVAL

Dialect Summary

The table below gives a high-level view of which SQL capabilities are available per dialect, which shapes what KQL features are achievable.

Capability	SQLite	MySQL	ClickHouse	PostgreSQL	DuckDB
ILIKE operator	❌	❌	✅	✅	✅
RIGHT JOIN	❌	✅	✅	✅	✅
FULL OUTER JOIN	❌	❌	✅	✅	✅
Native regex	❌	✅	✅	✅	✅
extractAll / regex capture groups	❌	❌	✅	❌	✅
Temp tables	✅	✅	✅	✅	✅
CTEs (WITH)	✅	✅	✅	✅	✅
Array unnesting	❌	❌	✅	✅	✅
String concat style	||	concat()	concat()	||	concat()
Date arithmetic style	modifier string	DATE_ADD	subtract* functions	INTERVAL expression	INTERVAL expression
Regex match style	none	REGEXP_LIKE	match()	~ tilde operator	regexp_matches()