Supported SQL syntax

This page documents ReadySet's SQL support. There are 3 main areas of support:

Table replication

Data types

ReadySet can snapshot and replicate tables containing many MySQL (opens in a new tab) and Postgres (opens in a new tab) data types.

Character sets

ReadySet supports the UTF-8 character set for strings and compares strings case-sensitively and sorts strings lexicographically. ReadySet does not support other character sets, alternative collations, or comparison methods for strings. However, you can use the CITEXT data type in Postgres to emulate a case-insensitive collation, and you can use the BYTEA data type in Postgres and the BINARY data type in MySQL to store arbitrary binary data.

Writes

All INSERT, UPDATE, and DELETE statements sent to ReadySet are proxied to the upstream database. ReadySet receives new/changed data via the database's replication stream and updates its snapshot and cache automatically.

Schema changes

When ReadySet receives the following schema change commands via the replication stream, ReadySet updates its snapshot of the affected tables and removes the caches of related queries.

Namespaces

ReadySet supports Postgres schemas (opens in a new tab) (namespaces for tables).

Query caching

Clauses

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

• FROM, with a list of tables (which may be implicitly joined)

• JOIN (see "Joins")

• WHERE

• GROUP BY, with a list of column or numeric field references

  • ReadySet does not support expression in the GROUP BY clause.

• HAVING

  • ReadySet does not support parameters in the HAVING clause.

• ORDER BY, with a list of expressions and an optional ASC or DESC specifier

• LIMIT

• OFFSET

• WITH (common table expressions)

  • ReadySet does not support recursive common table expressions (WITH RECURSIVE).

There are specific top-level clauses and other query conditions that ReadySet does not yet support, including:

• UNION, INTERSECT, or EXCEPT as operators to combine multiple SELECT statements
• WINDOW
• ORDER BY with NULLS FIRST or NULLS LAST

Joins

ReadySet supports the following JOIN types:

• [INNER] JOIN: Only the rows from the left and right side that match the condition are returned.
• LEFT [OUTER] JOIN: For every left row where there is no match on the right, NULL values are returned for the columns on the right.

Note that the right side of a JOIN can be a subquery but must not be correlated.

The primary limitation is on the condition of a JOIN. If using the ON clause with a join condition expression, the condition must be either a single equality comparison between a column on a table appearing outside the join 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 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 the following components of the SQL expression language:

• Literal values

  • String literals, quoted according to the SQL dialect being used (single quotes for PostgreSQL, double or single quotes for MySQL)

    • ReadySet does not support string literals with charset or collation specifications

  • Integer literals

  • Float literals

    • ReadySet does not support float literals using scientific (exponential) notation

  • NULL literal

  • Boolean literals TRUE and FALSE

  • Array literals

• Operators

  • AND
  • OR (or || in MySQL)
  • LIKE
  • NOT LIKE
  • ILIKE
  • NOT ILIKE
  • =
  • != or <>
  • >
  • >=
  • <
  • <=
  • IS NULL
  • IS NOT NULL
  • +
  • -
  • *
  • /
  • Unary -
  • Unary NOT
  • BETWEEN
  • EXISTS
  • Postgres JSON operators (opens in a new tab):
    • ?
    • ?|
    • ?&
    • -
    • ||
    • ->
    • ->>
    • @>
    • <@
    • #>
    • #>>
    • #--

• IN and NOT IN with a list of expressions

  • see "Limitations of IN" under “Parameters”

• CAST

• CASE

• Built-in functions

  • ADDTIME()
  • ARRAY_TO_STRING()
  • COALESCE()
  • CONVERT_TZ()
  • DATE_FORMAT()
  • DAYOFWEEK()
  • GREATEST()
  • IFNULL()
  • JSON_DEPTH()
  • JSON_OVERLAPS()
  • JSON_QUOTE()
  • JSON_TYPEOF()
  • JSON_VALID()
  • JSONB_ARRAY_LENGTH()
  • JSONB_EXTRACT_PATH()
  • JSONB_EXTRACT_PATH_TEXT()
  • JSONB_INSERT()
  • JSONB_PRETTY()
  • JSONB_SET()
  • JSONB_SET_LAX()
  • JSONB_STRIP_NULLS()
  • JSONB_TYPEOF()
  • LEAST()
  • MONTH()
  • ROUND()
  • SPLIT_PART()
  • SUBSTR() and SUBSTRING()
  • TIMEDIFF()

• Aggregate functions (see Aggregations)

ReadySet does not support the following components of the SQL expression language (this is not an exhaustive list):

• Literals

  • DATE and TIME specifications for literals
  • Hexadecimal literals
  • Bit-Value literals

• User-defined variables

• Operators: |, &, <<, >>, DIV, MOD, %, ^, <=>, SOUNDS LIKE

• COLLATE specifiers

• Unary +, ~, !

• ROW expressions

• Tuple expressions

• LIKE with an ESCAPE specifier

• INTERVAL

• IN or NOT IN with a subquery

• ANY or SOME subquery expressions

Aggregations

ReadySet supports the following aggregate functions:

• AVG(expr)
• AVG(DISTINCT expr)
• COUNT(expr)
• COUNT(DISTINCT expr)
• COUNT(*)
• SUM(expr)
• SUM(DISTINCT expr)
• MAX(expr)
• MIN(expr)
• GROUP_CONCAT(expr SEPARATOR str_val)
  • ReadySet does not support the ORDER BY clause in the GROUP_CONCAT aggregate function, and requires the specification of a SEPARATOR (unlike MySQL, where the SEPARATOR is optional)

Similar to many SQL databases, ReadySet requires all columns in the SELECT clause or ORDER BY list that aren't in an aggregate function to be explicitly listed in the GROUP BY clause. This corresponds to the MySQL ONLY_FULL_GROUP_BY SQL mode.

If one or more aggregate functions appear in the column list of a subquery which returns no results, ReadySet will consider that subquery to also emit no results. This differs slightly from the handling of aggregates over empty result sets in MySQL. For example, in MySQL:

MySQL [test]> select count(*) from empty_table;+----------+| count(*) |+----------+|        0 |+----------+1 row in setMySQL [test]> select count(*) from (select count(*) from empty_table) as subquery;+----------+| count(*) |+----------+|        1 |+----------+1 row in set

While in ReadySet:

MySQL [test]> select count(*) from empty_table;+----------+| count(*) |+----------+|        0 |+----------+1 row in setMySQL [test]> select count(*) from (select count(*) from empty_table) as subquery;+----------+| count(*) |+----------+|        0 |+----------+1 row in set

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.

SQL extensions

ReadySet supports the following custom SQL commands: