SQL Support

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

Area	Details
Table replication	ReadySet takes an initial snapshot of tables from the upstream MySQL or Postgres database and then uses the database's replication stream to keep the snapshot up-to-date as the tables change. To successfully snapshot and replicate a table, ReadySet must support the data types of the columns, the character set in which the data is encoded, and changes to the table via writes and schema changes.
Query caching	Once ReadySet is replicating tables, ReadySet can cache the results of SQL queries run against those tables. To successfully cache the results of a query, ReadySet must support the SQL features and syntax in the query.
SQL extensions	ReadySet supports custom SQL commands for viewing queries that ReadySet has proxied to the upstream database, caching supported queries, viewing caches, and removing caches.

Tip

ReadySet is continually expanding SQL support. If you need an unsupported feature, let us know on the Discord chat, or open an issue in our GitHub repository.

Table replication

Data types

ReadySet can snapshot and replicate tables containing many MySQL and Postgres data types.

MySQLPostgres

Numeric types

Type	Supported	Notes
INT INT UNSIGNED TINYINT TINYINT UNSIGNED SMALLINT SMALLINT UNSIGNED MEDIUMINT MEDIUMINT UNSIGNED BIGINT BIGINT UNSIGNED SERIAL		ReadySet ignores the optional length field.
DECIMAL NUMERIC
FLOAT DOUBLE REAL
BIT		ReadySet ignores the optional length field.

Data and time types

Type	Supported	Notes
DATE DATETIME TIMESTAMP TIMESTAMPTZ		ReadySet ignores the optional precision field.
TIME
YEAR

String types

Type	Supported	Notes
CHAR VARCHAR		ReadySet ignores the optional length field.
BINARY VARBINARY		ReadySet ignores the optional length field.
BLOB TINYBLOG MEDIUMBLOB LONGBLOB
TEXT TINYTEXT MEDIUMTEXT LONGTEXT
ENUM
SET

JSON types

Type	Supported	Notes
JSON		ReadySet represents this type internally as a normalized string. This can cause different behavior than in MySQL with respect to expressions or sorting.

Spatial types

Type	Supported	Notes
GEOMETRY POINT LINESTRING POLYGON MULTIPOINT MULTILINESTRING MULTIPOLYGON, GEOMETRYCOLLECTION

Numeric types

Type	Supported	Notes
INT SMALLINT BIGINT		ReadySet ignores the optional length field.
DECIMAL NUMERIC		ReadySet does not support DECIMAL/NUMERIC values with a scale greater than 28, i.e., with more than 28 digits to the right of the decimal point.
FLOAT DOUBLE PRECISION REAL
SERIAL SMALLSERIAL BIGSERIAL

Monetary types

Type	Supported	Notes
MONEY

Character types

Type	Supported	Notes
CHAR VARCHAR TEXT		ReadySet ignores the optional length field.
"CHAR"
CITEXT

Binary types

Type	Supported	Notes
BYTEA

Date and time types

Type	Supported	Notes
DATE TIME TIMETZ TIMESTAMP TIMESTAMPTZ		ReadySet ignores the optional precision field.
INTERVAL

Boolean types

Type	Supported	Notes
BOOLEAN

Enumerated types

Type	Supported	Notes
ENUM

Geometric types

Type	Supported	Notes
POINT LINE LSEG BOX PATH POLYGON CIRCLE

Network address types

Type	Supported	Notes
INET		ReadySet represents this type internally as normalized string.
CIDR
MACADDR		ReadySet represents this type internally as a normalized string. This can cause different behavior than in Postgres with respect to expressions or sorting.
MACADDR8

Bit string types

Type	Supported	Notes
BIT BIT VARYING		ReadySet ignores the optional length field.

Text search types

Type	Supported	Notes
tsvector
tsquery

UUID types

Type	Supported	Notes
UUID		ReadySet represents this type internally as a normalized string. This can cause different behavior than in Postgres with respect to expressions or sorting.

XML types

Type	Supported	Notes
XML

JSON types

Type	Supported	Notes
JSON JSONB		ReadySet represents this type internally as a normalized string. This can cause different behavior than in Postgres with respect to expressions or sorting.

Array types

Type	Supported	Notes
ARRAY

Composite data types

Type	Supported	Notes
CREATE TYPE <name> AS

Range types

Type	Supported	Notes
INT4RANGE INT8RANGE NUMRANGE TSRANGE TSTZRANGE DATERANGE

Domain types

Type	Supported	Notes
CREATE TABLE <table> (col <domain>)

Object identifier types

Type	Supported	Notes
OID REGCLASS REGCOLLATION REGCONFIG REGDICTIONARY REGNAMESPACE REGOPER REGOPERATOR REGPROC REGPROCEDURE REGROLE REGTYPE

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.

Tip

After running any of the following schema change commands, be sure to re-cache related queries.

MySQLPostgres

Statement	Command	Notes
ALTER TABLE	ADD COLUMN
ALTER TABLE	ADD KEY
ALTER TABLE	DROP COLUMN
ALTER TABLE	ALTER COLUMN	ReadySet supports only SET DEFAULT [literal] and DROP DEFAULT.
ALTER TABLE	CHANGE COLUMN
ALTER TABLE	MODIFY COLUMN	ReadySet does not support FIRST or AFTER.

Statement	Command	Notes
ALTER TABLE	ADD COLUMN
ALTER TABLE	ADD KEY
ALTER TABLE	DROP COLUMN
ALTER TABLE	ALTER COLUMN	ReadySet supports only SET DEFAULT [literal] and DROP DEFAULT.
ALTER TABLE	CHANGE COLUMN
ALTER TABLE	MODIFY COLUMN	ReadySet does not support FIRST or AFTER.
ALTER TYPE	ADD VALUE	ReadySet removes the caches of queries referencing the type.
ALTER TYPE	RENAME TO	ReadySet removes the caches of queries referencing the type.
ALTER TYPE	RENAME VALUE	ReadySet removes the caches of queries referencing the type.
ALTER TYPE	SET SCHEMA	ReadySet removes the caches of queries referencing the type.

Namespaces

ReadySet supports Postgres schemas (namespaces for tables).

Query caching

Tip

After running a query through ReadySet, you can use the SHOW PROXIED QUERIES command to check if ReadySet supports the query. ReadySet always proxies unsupported queries to the upstream database.

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:
    • ?
    • ?|
    • ?&
    • -
    • ||
    • ->
    • ->>
    • @>
    • <@
    • #>
    • #>>
    • #-
• 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 set

MySQL [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 set

MySQL [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.

Limitations of IN

When the IN clause is used with parameters, queries may not contain the AVG or GROUP_CONCAT aggregate functions. However, this limitations does not apply when the right-hand side of the IN clause does not contain any query parameters.

SQL extensions

ReadySet supports the following custom SQL commands:

Command	Description
SHOW READYSET STATUS	Check ReadySet's overall snapshotting status.
SHOW READYSET TABLES	Check the snapshotting status of each table in the database that ReadySet is connected to.
SHOW PROXIED QUERIES	View the queries that ReadySet has proxied to the upstream database and check if they can be cached in ReadySet.
CREATE CACHE	Cache a query in ReadySet.
SHOW CACHES	Show all queries that have been cached in ReadySet.
DROP CACHE	Remove a cache from ReadySet.
SHOW READYSET VERSION	Prints ReadySet version information.