Supported Queries

This page describes the specific query structures that are cacheable in Readyset.

Query Structure

Readyset supports the following clauses in SQL SELECT queries:

• SELECT with a list of select expressions, all of which must be supported expressions (see “Expressions”)
  • Readyset does not support scalar subqueries in the SELECT clause
• DISTINCT, modifying the select clause
• OVER([PARTITION BY ..] [ORDER BY ..]) also known as Window Functions
• FROM, with a list of tables (which may be implicitly joined), and (uncorrelated) subqueries
• JOIN (see "Joins")
• WHERE
  • The WHERE clause must have a single condition expression
• GROUP BY, with a list of column references
  • Readyset does not support expressions or field positions in the GROUP BY clause
• HAVING
• ORDER BY, with a list of expression, an optional ASC or DESC specifier, and an optional NULLS FIRST or NULLS LAST
• LIMIT
• OFFSET

There are specific top-level clauses and other query conditions that Readyset does not yet support.

Notably, Readyset does not support any of the following (this is not an exhaustive list):

• UNION, INTERSECT, or EXCEPT as operators to combine multiple SELECT statements

Joins

Readyset supports the below forms of the SQL JOIN clause:

• LEFT and INNER joins are supported
• the right-hand side of a join may be a subquery, and if preceded with LATERAL, might be correlated with the preceding items.

The primary limitation is on the condition of a JOIN. If using the ON clause with a join condition expression, the condition may only be either a single equality comparison between a column on a table appearing outside the join (or a function call with such column as an argument) and the join table (or subquery), or multiple such expressions combined using AND. For example, the following queries are supported:

select * from t1
join t2 on t1.id = t2.t1_id
select * from t1
join t2 on lower(t1.id) = lower(t2.t1_id)
select * from t1
join t2 on t1.x = t2.x AND t1.y = t2.y
select * from t1
join t2 on t1.x = t2.x
join t3 on t1.y = t3.y

But the following queries are not supported:

-- This query doesn't compare a column in one table to a column in another table
select * from t1 join t2 on t1.x = t1.y
-- This query doesn't compare using equality
select * from t1 join t2 on t1.x > t2.x
-- This query doesn't combine its equality join keys with AND
select * from t1 join t2 on t1.x = t2.x OR t1.y = t2.y

In addition, multiple tables specified in the FROM clause can be implicitly joined, but only if there is a condition in the WHERE clause that follows the above requirements when expressed in conjunctive normal form. For example, the following query is supported:

select * from t1, t2 where t1.x = t2.x

but the following query is not:

select * from t1, t2 where t1.x = t1.y

Expressions

ReadySet supports most standard SQL expressions. We are continuously expanding coverage to ensure compatibility with the broadest range of MySQL and PostgreSQL workloads.

Parameters

Readyset uses the parameters in a prepared statement, specified either positionally (using ?) or numbered (using $1, $2, etc.), as the key that enables storing only certain result sets for each query. Readyset will automatically turn literal values in certain positions in queries into parameters, but only supports certain positions for user-specified parameters in queries:

• Parameters can only appear in the WHERE clause of the outermost SELECT statement in a query (e.g., not in any subqueries).
  • Parameters are only supported in the WHERE clause of a query if, when expressed in conjunctive normal form, all conjunctive subexpressions of the expression in the WHERE clause either contain no parameters, or can be expressed as a single equality comparison between a column and a parameter, or are an IN expression where the right-hand side consists of a list of only parameters (Readyset does not support mixing parameters and other types of expressions on the right-hand side of an IN expression).
  • Readyset contains experimental support for conditions that consist of an inequality comparison between a parameter and a column (>, >=, < and <=)
• Parameters can also appear as the value of the LIMIT or OFFSET clause of a query.

Limitations of IN

When the IN clause is used with parameters, queries may not contain the following elements:

• Some aggregate functions (AVG, or GROUP_CONCAT)

These limitations do not apply when the right-hand side of the IN clause does not contain any query parameters.

Schema

Check out the Supported Data Types page for more information on the data types that Readyset supports.

Miscellaneous schema support

Readyset does not support schemas (opens in a new tab) (namespaces for tables).

Character sets & collations

Readyset stores all strings internally as UTF-8. We currently support the following list of collations:

Loading supported collations...