Limitations and best practices

This page summarizes supported features, limitations, and best practices.

Quick reference

Feature	Supported	Notes
SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT	Yes
Column aliases (`AS`)	Yes
Expressions (CASE, CAST, LIKE, BETWEEN, IN, arithmetic)	Yes	Full expression support
EXPLAIN	Yes	Returns execution plan as text or JSON
173 scalar functions	Yes	Math, string, datetime, regex, crypto, array, map, struct, JSON
33 aggregate functions	Yes	Basic, approximate, statistical, bitwise, boolean, positional
Approximate aggregates	Yes	`approx_distinct`, `approx_median`, `approx_percentile_cont`, `approx_top_k`
Struct / Array / Map column types	Yes	Bracket notation, `get_field()`, array functions, map functions
CTEs (`WITH ... AS`)	Yes	Can reference different tables and include JOINs
JOINs (INNER, LEFT, RIGHT, FULL OUTER, CROSS)	Yes	All standard join types
Implicit joins (comma FROM)	Yes
Subqueries (`IN`, `NOT IN`)	Yes	`NOT IN` not supported on nullable columns — use `NOT EXISTS` instead
Subqueries (`EXISTS`, `NOT EXISTS`)	Yes	semi-join and anti-join patterns
Scalar subqueries	Yes	In SELECT, WHERE, HAVING
Derived tables (FROM subqueries)	Yes	Can be nested and joined. `LATERAL` derived tables not supported.
Self-joins	Yes	Same table with different aliases
Window functions (`OVER`)	No
`SELECT DISTINCT`	No	Use `approx_distinct`
`OFFSET`	No
`UNION` / `INTERSECT` / `EXCEPT`	No
`INSERT` / `UPDATE` / `DELETE`	No	Read-only
`CREATE` / `DROP` / `ALTER`	No	Read-only

For the full SQL syntax, refer to the SQL reference.

Feature	Error
`SELECT DISTINCT`	`unsupported feature: SELECT DISTINCT is not supported`
`OFFSET`	`unsupported feature: OFFSET clause is not supported`
`UNION` / `INTERSECT` / `EXCEPT`	Set operations not supported
Window functions (`OVER`)	`unsupported feature: window functions (OVER clause)`
`INSERT` / `UPDATE` / `DELETE`	`only read-only queries are allowed`
`CREATE` / `DROP` / `ALTER`	`only read-only queries are allowed`
`UNNEST` / `PIVOT` / `UNPIVOT`	Not supported
Wildcard modifiers (`ILIKE`, `EXCLUDE`, `EXCEPT`, `REPLACE`, `RENAME` on `*`)	Not supported
Nested (parenthesized) joins	Not supported
`LATERAL` derived tables	Not supported
`LATERAL VIEW` / `QUALIFY`	Not supported

Pattern	Alternative
`func(DISTINCT ...)` on any aggregate	Use `approx_distinct` for counting
`PERCENTILE_CONT` / `PERCENTILE_DISC`	Use `approx_percentile_cont`
`MEDIAN`	Use `approx_median`
`ARRAY_AGG`	No alternative (unsupported for memory safety)
`STRING_AGG`	No alternative (unsupported for memory safety)
`NOT IN` subquery on nullable columns	Use `NOT EXISTS` with a correlated subquery instead

Constraint	Details
Multi-table queries	JOINs, subqueries (IN, EXISTS, scalar, derived tables), and multi-table CTEs are supported. Performance depends on intermediate result size; use WHERE filters to manage join selectivity.
Partitioned and unpartitioned tables	Both partitioned and unpartitioned Iceberg tables are supported.
Parquet format only	No CSV, JSON, or other formats.
Read-only	R2 SQL is a query engine, not a database. No writes.
`now()` / `current_time()` precision	Quantized to 10ms boundaries and forced to UTC.

Code	Meaning
40003	Invalid SQL syntax
40004	Invalid query (unsupported feature, unknown column, type mismatch)
80001	Edge service connection failure (retry)

Include time-range filters in WHERE to limit data scanned.
Use specific column names instead of SELECT * for better performance.
Use LIMIT to control result set size.
Use approximate aggregation functions (approx_distinct, approx_median, approx_percentile_cont) instead of exact alternatives on large datasets.
Enable compaction in R2 Data Catalog to reduce the number of files scanned per query.
Use EXPLAIN to inspect the execution plan and verify predicate pushdown.
Use WHERE filters with multi-way joins to reduce intermediate result sizes. Joining three or more large tables without filters can exceed resource limits.
Join large fact tables through dimension tables rather than directly joining two large fact tables. For example, join http_requests to firewall_events through a shared zones dimension rather than cross-joining both fact tables.
Be cautious with COUNT(DISTINCT) across multi-way joins. This combination can produce very large intermediate results. Consider using approx_distinct() or breaking the query into smaller steps.
Use explicit JOIN syntax instead of implicit joins (comma-separated FROM) for readability and to ensure the optimizer can choose optimal join ordering.