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
Scalar functions	Yes	Math, string, datetime, regex, crypto, array, map, struct, JSON
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`)	Yes	Inline `OVER (...)` only — named `WINDOW` clause not supported
`QUALIFY`	Yes	Filter on a window function result
`SELECT DISTINCT` / `DISTINCT ON`	Yes
`func(DISTINCT ...)`	Yes	`COUNT`, `SUM`, `AVG`, and other aggregates
Set operations (`UNION`, `UNION ALL`, `INTERSECT`, `EXCEPT`)	Yes
`GROUPING SETS` / `ROLLUP` / `CUBE`	Yes
`OFFSET`	No
`INSERT` / `UPDATE` / `DELETE`	No	Read-only
`CREATE` / `DROP` / `ALTER`	No	Read-only

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

Unsupported SQL features

Feature	Error
`OFFSET`	`unsupported feature: OFFSET clause is not supported`
Named `WINDOW` clause	`unsupported feature: WINDOW clause is not supported`
`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 / `LATERAL VIEW`	Not supported
`PERCENTILE_DISC`	Not supported — use `PERCENTILE_CONT`

Unsupported expression patterns

Pattern	Alternative
`NOT IN` subquery on nullable columns	Use `NOT EXISTS` with a correlated subquery instead

Exact aggregates such as COUNT(DISTINCT ...), MEDIAN, PERCENTILE_CONT, ARRAY_AGG, and STRING_AGG are supported. On large datasets, prefer the approximate alternatives (approx_distinct, approx_median, approx_percentile_cont) for lower memory and compute. Refer to Aggregate functions.

Runtime constraints

Constraint	Details
Resource-intensive queries	During open beta, queries that require high memory or compute may time out. This includes multi-way joins (three or more large tables), `COUNT(DISTINCT)` and other `func(DISTINCT ...)` across joins or high-cardinality columns, `ARRAY_AGG` / `STRING_AGG`, set operations that deduplicate large inputs, window functions over large partitions, and large sorts or high-cardinality `GROUP BY`. Add `WHERE` filters and `LIMIT`, and prefer `approx_*` aggregates to reduce the chance of a timeout.
Budget-gated functions	`MEDIAN`, `PERCENTILE_CONT`, `ARRAY_AGG`, `STRING_AGG`, `NTH_VALUE` used as an aggregate, any aggregate with `DISTINCT`, and window functions (including those used through `QUALIFY`) are budget-gated up front. R2 SQL estimates the memory required before running the query and rejects it with a `400` error if too much data would be scanned. Add a `GROUP BY` or `WHERE` filters to reduce the rows processed.
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.

Common error codes

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

Best practices

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.