Limitations and best practices

R2 SQL is designed for querying partitioned Apache Iceberg tables in your R2 data catalog. This document outlines the supported features, limitations, and best practices of R2 SQL.

Quick Reference

Feature	Supported	Notes
Basic SELECT	Yes	Columns, *
Aggregation functions	Yes	COUNT(*), SUM, AVG, MIN, MAX
Single table FROM	Yes	Note, aliasing not supported
WHERE clause	Yes	Filters, comparisons, equality, etc
JOINs	No	No table joins
Array filtering	No	No array type support
JSON filtering	No	No nested object queries
Simple LIMIT	Yes	1-10,000 range, no pagination support
ORDER BY	Yes	Partition key or with GROUP BY columns
GROUP BY	Yes	Supported
HAVING	Yes	Supported

Supported SQL Clauses

R2 SQL supports: DESCRIBE, SHOW, SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT. New features will be released in the future, keep an eye on this page for the latest.

SELECT Clause

Supported Features

Individual columns: SELECT column1, column2
All columns: SELECT *

Limitations

No JSON field querying: Cannot query individual fields from JSON objects
Limited aggregation functions: See Aggregation Functions section below for details
No synthetic data: Cannot create synthetic columns like SELECT 1 AS what, "hello" AS greeting
No field aliasing: SELECT field AS another_name (applies to both regular columns and aggregations)

Examples

-- Valid
SELECT timestamp, user_id, status FROM my_table;
SELECT * FROM my_table;

-- Invalid
SELECT user_id AS uid, timestamp AS ts FROM my_table;
SELECT COUNT(*) FROM events FROM FROM my_table;
SELECT json_field.property FROM my_table;
SELECT 1 AS synthetic_column FROM my_table;

Aggregation Functions

Supported Features

COUNT(*): Count total rows note: only * is supported
SUM(column): Sum numeric values
AVG(column): Calculate average of numeric values
MIN(column): Find minimum value
MAX(column): Find maximum value
With GROUP BY: All aggregations work with GROUP BY

Limitations

No aliases: AS keyword not supported (SELECT COUNT(*) AS total fails)
COUNT(*) only: COUNT(column_name) or COUNT(DISTINCT column) is not supported

Examples

-- Valid
SELECT department, COUNT(*) FROM sales GROUP BY department;
SELECT region, AVG(amount) FROM sales GROUP BY region;
SELECT category, MIN(price), MAX(price) FROM products GROUP BY category;
SELECT SUM(quantity) FROM sales GROUP BY department ORDER BY SUM(amount) DESC;

-- Invalid
SELECT COUNT(*) AS total FROM sales GROUP BY department; -- No aliases
SELECT COUNT(department) FROM sales; -- Must use COUNT(*)
SELECT COUNT(DISTINCT region) FROM sales; -- No DISTINCT support

GROUP BY Clause

Supported Features

Single column grouping: GROUP BY column
Multiple column grouping: GROUP BY column1, column2
With WHERE: Filter before grouping
With LIMIT: Limit grouped results

Limitations

No expressions: Cannot use expressions in GROUP BY (e.g., GROUP BY YEAR(date))

Examples

SELECT region, COUNT(*) FROM sales GROUP BY region;
SELECT dept, category, COUNT(*) FROM sales GROUP BY dept, category;
SELECT region, COUNT(*) FROM sales WHERE status = 'completed' GROUP BY region;
SELECT dept, COUNT(*) FROM sales GROUP BY dept ORDER BY COUNT(*) DESC LIMIT 10;
SELECT is_active, SUM(amount) FROM sales GROUP BY is_active;
SELECT dept, SUM(amount) FROM sales GROUP BY dept ORDER BY SUM(amount) DESC;

HAVING Clause

Supported Features

With COUNT(*): Filter groups by count
Comparison operators: >, >=, =, <, <=, !=, BETWEEN, AND, IS NOT NULL
With GROUP BY: Must be used with GROUP BY

Examples

SELECT region, COUNT(*) FROM sales GROUP BY region HAVING COUNT(*) > 1000;
SELECT dept, SUM(amount) FROM sales GROUP BY dept HAVING SUM(amount) > 100000; -- HAVING with SUM
SELECT region, COUNT(*) FROM sales GROUP BY region HAVING COUNT(*) > 100 AND COUNT(*) < 1000;

FROM Clause

Supported Features

Single table queries: SELECT * FROM table_name

Limitations

No multiple tables: Cannot specify multiple tables in FROM clause
No subqueries: SELECT ... FROM (SELECT ...) is not supported
No JOINs: No INNER, LEFT, RIGHT, or FULL JOINs
No SQL functions: Cannot use functions like read_parquet()
No synthetic tables: Cannot create tables from values
No schema evolution: Schema cannot be altered (no ALTER TABLE, migrations)
Immutable datasets: No UPDATE or DELETE operations allowed
Fully defined schema: Dynamic or union-type fields are not supported
No table aliasing: SELECT * FROM table_name AS alias

Examples

--Valid
SELECT * FROM http_requests;

--Invalid
SELECT * FROM table1, table2;
SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
SELECT * FROM (SELECT * FROM events WHERE status = 200);

WHERE Clause

Supported Features

Simple type filtering: Supports string, boolean, number types, and timestamps expressed as RFC3339
Boolean logic: Supports AND, OR, NOT operators
Comparison operators: >, >=, =, <, <=, !=
Grouped conditions: WHERE col_a="hello" AND (col_b>5 OR col_c != 3)
Pattern matching: WHERE col_a LIKE ‘hello w%’ (prefix matching only)
NULL Handling : WHERE col_a IS NOT NULL (IS/IS NOT)

Limitations

No column-to-column comparisons: Cannot use WHERE col_a = col_b
No array filtering: Cannot filter on array types (array[number], array[string], array[boolean])
No JSON/object filtering: Cannot filter on fields inside nested objects or JSON
No SQL functions: No function calls in WHERE clause
No arithmetic operators: Cannot use +, -, *, / in conditions

Examples

--Valid
SELECT * FROM events WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-02';
SELECT * FROM logs WHERE status = 200 AND user_type = 'premium';
SELECT * FROM requests WHERE (method = 'GET' OR method = 'POST') AND response_time < 1000;

--Invalid
SELECT * FROM logs WHERE tags[0] = 'error'; -- Array filtering
SELECT * FROM requests WHERE metadata.user_id = '123'; -- JSON field filtering
SELECT * FROM events WHERE col_a = col_b; -- Column comparison
SELECT * FROM logs WHERE response_time + latency > 5000; -- Arithmetic

ORDER BY Clause

Supported Features

ASC: Ascending order
DESC: Descending order (Default, on full partition key)
With partition key: Order by partition key columns
With GROUP BY: Can order by all aggregation columns

Limitations

Non-partition keys not supported: ORDER BY on columns other than the partition key is not supported (except with aggregations)

Examples

-- Valid
SELECT * FROM table_name WHERE ... ORDER BY partitionKey;
SELECT * FROM table_name WHERE ... ORDER BY partitionKey DESC;
SELECT dept, COUNT(*) FROM table_name GROUP BY dept ORDER BY COUNT(*) DESC;

-- Invalid
SELECT * FROM table_name GROUP BY dept ORDER BY nonPartitionKey DESC --ORDER BY a non-grouped column

LIMIT Clause

Supported Features

Simple limits: LIMIT number
Range: Minimum 1, maximum 10,000

Limitations

No pagination: LIMIT offset, count syntax not supported
No SQL functions: Cannot use functions to determine limit
No arithmetic: Cannot use expressions like LIMIT 10 * 50

Examples

-- Valid
SELECT * FROM events LIMIT 100
SELECT * FROM logs WHERE ... LIMIT 10000

-- Invalid
SELECT * FROM events LIMIT 100, 50; -- Pagination
SELECT * FROM logs LIMIT COUNT(*); / 2 -- Functions
SELECT * FROM events LIMIT 10 * 10; -- Arithmetic

Unsupported SQL Clauses

The following SQL clauses are not supported:

UNION/INTERSECT/EXCEPT
WITH (Common Table Expressions)
WINDOW functions
INSERT/UPDATE/DELETE
CREATE/ALTER/DROP

Best Practices

Always include time filters in your WHERE clause to ensure efficient queries.
Use specific column selection instead of SELECT * when possible for better performance.
Flatten your data to avoid nested JSON objects if you need to filter on those fields.
Use COUNT(*) exclusively - avoid COUNT(column_name) or COUNT(DISTINCT column).
Enable compaction in R2 Data Catalog to reduce the number of data files needed to be scanned.

Was this helpful?

Community
X
Discord
YouTube
GitHub