Skip to content

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 accurate 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.

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

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
  • 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 TABLES Check the snapshotting status of tables.
SHOW PROXIED QUERIES View the queries that ReadySet has proxied to the upstream database and identify whether such queries can be cached with 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.