Skip to content

RonSQL#

RonSQL is a SQL engine that executes a subset of MySQL SELECT queries directly against the RonDB data nodes. Filtering, joins and aggregation are pushed down into the data nodes, so intermediate rows never make a round-trip to a MySQL server. For analytical queries that scan and aggregate many rows, this can give large speedups compared to executing the same query through mysqld. RonSQL is part of the RonDB REST API server (RDRS) and is used through its /ronsql endpoint.

RonSQL supports single-table queries (aggregate queries and projection-only queries), pushed join queries, common table expressions (CTEs) joined into queries, and scalar, IN and EXISTS subqueries. It only supports SELECT (and EXPLAIN SELECT) statements; there is no DDL or DML. Tables are created and loaded through a MySQL server or any other RonDB API, and RonSQL reads them.

RonSQL is designed to either execute a query with MySQL semantics or reject it with an error message: queries outside the supported subset are rejected rather than silently computing something different, and the RonDB test suites continuously compare RonSQL results against MySQL results. There are a few documented differences for accepted queries, mainly in the precision of DECIMAL arithmetic, in time zone handling and in overflow behavior; see Restrictions and MySQL compatibility. For the meaning of functions, operators and other keywords, refer to the MySQL documentation.

RonSQL requires every data node in the cluster to run RonDB 26.10.0 or later; otherwise queries are refused with HTTP status 503.

The RonSQL documentation consists of the following chapters:

RonSQL at a glance#

The following table summarizes which SQL features RonSQL supports. The linked chapters describe each feature and its restrictions in detail.

Feature Support in RonSQL
SELECT Supported; the only statement
EXPLAIN SELECT Supported, text output only
INSERT, UPDATE, DELETE, DDL Not supported
COUNT, SUM, MIN, MAX, AVG Supported
COUNT(DISTINCT ...), STD, VARIANCE, GROUP_CONCAT Not supported
Arithmetic, CASE, GREATEST, LEAST Inside aggregate arguments
Expressions in the select list Not supported (except AVRO())
SELECT * and DISTINCT Not supported
WHERE Supported; comparisons, LIKE, IN, IS NULL
BETWEEN, NOT IN, REGEXP Not supported
GROUP BY Column names only
HAVING Aggregate expressions
ORDER BY Column names and aliases
LIMIT n Supported; no offset
JOIN, LEFT [OUTER] JOIN Equality joins on indexed columns
RIGHT, FULL, CROSS, NATURAL joins Not supported
WITH (CTEs) Aggregating, single-row and last-N forms
WITH RECURSIVE Not supported
Scalar, IN and EXISTS subqueries Supported in WHERE
Correlated aggregate subqueries Supported in the select list
Derived tables (FROM (SELECT ...)) Not supported; use a CTE
UNION, window functions, WITH ROLLUP Not supported
Index hints (FORCE/USE/IGNORE INDEX) On the first table
SQL comments Not supported

Executing queries#

REST API#

In production, RonSQL queries are executed through the RonDB REST API server (RDRS). Queries are sent as HTTP POST requests to the /0.1.0/ronsql or the /0.2.0/ronsql endpoint (default port 4406); both paths behave identically. The request body is a JSON object with the following fields:

  • query: (required) The SQL statement to execute. It must be exactly one statement, terminated by a semicolon.

  • database: (required) The database containing the tables read by the query. All tables of the statement, including joined tables and the tables read by CTEs and subqueries, are read from this database; cross-database queries are not supported. A table name may be qualified with this database (db.table), but a qualifier naming another database is rejected.

  • outputFormat: (optional) One of JSON (the default), JSON_ASCII (JSON using only ASCII characters), TEXT (tab-separated values with a header row, mimicking the mysql command line client) or TEXT_NOHEADER (tab-separated values without a header row).

  • explainMode: (optional) Controls EXPLAIN handling; see below. The default is ALLOW.

  • operationId: (optional) A client-chosen string echoed back in the response, at most Internal.OperationIDMaxSize bytes (default 256). Only allowed with the JSON output formats. Use a simple token of letters, digits and punctuation such as -, _ and .; the value is echoed without JSON escaping.

Field names and the values of outputFormat and explainMode are case sensitive. Unknown fields are rejected, except that fields whose names begin with # are ignored and can be used as comments.

Example using curl:

curl -X POST -H "Content-Type: application/json" \
  http://localhost:4406/0.1.0/ronsql \
  -d '{"query": "SELECT o_custkey, SUM(o_total) FROM orders GROUP BY o_custkey;",
       "database": "test"}'

Note that a JSON string cannot contain raw line breaks, so the query must be written on a single line or with \n escape sequences.

With the JSON output formats, the response body is a JSON object with a data member holding one object per result row, keyed by output column name:

{"data":
[{"o_custkey":100,"SUM(o_total)":800.00}
,{"o_custkey":200,"SUM(o_total)":900.00}
,{"o_custkey":300,"SUM(o_total)":400.00}
]
}

When an operationId is given, it is returned as the first member of the response object. An empty result set is returned as an empty array.

With outputFormat set to TEXT, the same result is returned as tab-separated values with a header row:

o_custkey   SUM(o_total)
100 800.00
200 900.00
300 400.00

For an empty result set, text output is empty; the header row is omitted, as in the mysql client. NULL values are printed as null in JSON and as NULL in text output. The response content types are:

  • JSON: application/json; charset=utf-8

  • JSON_ASCII: application/json; charset=US-ASCII

  • TEXT: text/tab-separated-values; charset=utf-8; header=present

  • TEXT_NOHEADER: text/tab-separated-values; charset=utf-8; header=absent

  • EXPLAIN output: text/plain; charset=utf-8

The response is built completely before it is sent; it is never truncated. A result larger than Internal.MaxRespSize fails with an error (see Configuration). The order of the result rows is unspecified unless the query has an ORDER BY clause.

Data types and output describes in detail how each data type is formatted in each output format.

If API key authentication is enabled in RDRS, the request must carry the API key in the X-API-KEY header, and the key must authorize access to the database, and to every table and column that the query reads, including the tables read through joins, CTEs and subqueries. See the RonDB REST API documentation for the API key mechanism.

Both the input SQL and the query output use UTF-8 encoding.

Error responses#

A failed query returns a plain-text response body (for both API versions). Errors detected by RonSQL itself end with a line of the form

[<class>] Caught exception: <message>

preceded by diagnostic lines that describe the problem in more detail, often pointing at the offending position in the statement. The diagnostic lines are intended for humans and their format may change. The error class is also returned in the X-RonSQL-Error-Class response header, and when the error was raised by the data nodes, the NDB error code is returned in the X-RonSQL-NDB-Error header. For example:

Failed to get column `sint16_altered`.
Note that column names are case sensitive.
Error handling: RMS->RPE
[semantic] Caught exception: Could not find column (column names are case sensitive).

The error classes and their HTTP status codes are:

Class Meaning HTTP status
syntax The statement does not parse 400
semantic Unknown tables or columns, mistyped operands 400
unsupported Valid SQL that RonSQL does not execute 400
limit A RonSQL or configured limit is exceeded 400 or 413
resource The server ran out of a resource 503
internal An internal error; please report it 500

A limit error returns 413 when the result was too large (for example the sort buffer of a projection-only ORDER BY), and 400 otherwise. Other status codes returned by the endpoint are:

  • 400 for an invalid request body, for example an unknown field, an invalid database, outputFormat, explainMode or operationId, or a missing or malformed API key.

  • 401 when the API key is unknown, expired, or not authorized for the database, table or columns.

  • 413 when the request body exceeds Internal.maxReqSize.

  • 429 when a user rate limit is exceeded (see Rate limits and quotas). The client should back off and retry.

  • 500 when the result exceeds Internal.MaxRespSize.

  • 503 when all RonSQL workers are busy and the request queue is full, when the server is shutting down, or when a temporary error persisted through all internal retries (see Error handling and retries). These are worth retrying later. A 503 whose body contains Caught exception: is a permanent resource error, such as running out of memory for the query, and is likely to fail again.

Query phase statistics#

A client can ask the /ronsql endpoint for per-request diagnostics by sending the request header x-ronsql-phases: 1 (any value other than 0 or false works). A successful response to such a request, including an EXPLAIN response, then carries an x-ronsql-phases response header, for example:

parse=12,analyze=3,load=40,plan=8,compile=5,prepare=9,subquery=0,
ndbprep=15,send=21,firstbatch=310,drain=95,print=11,execute=520,
rows=3,attempts=1,fetched=3,queue=2,loop=5,worker=3,close=4

(The header is a single line; it is wrapped here for readability.) The fields from parse to execute are the time in microseconds spent in each execution phase of the last attempt. rows is the number of result rows, attempts the number of execution attempts, fetched the number of rows received from the data nodes, queue the time in microseconds the request waited for a RonSQL worker, loop and worker identify the REST thread and the RonSQL worker thread (0 when the query ran on the REST thread), and close is the time in microseconds spent closing scans and the transaction before the response was sent (part of execute). This is useful for understanding where time is spent in a slow query without any extra instrumentation. New fields are only ever added at the end.

Without the request header, no timings are collected and no x-ronsql-phases header is returned.

RonDB CLI#

The RonDB CLI (rondb) sends RonSQL queries to the REST API server with the RONSQL command:

rondb> RONSQL SET DATABASE test
rondb> RONSQL SELECT o_custkey, COUNT(*) FROM orders GROUP BY o_custkey;
rondb> RONSQL EXPLAIN SELECT COUNT(*) FROM orders WHERE o_custkey = 100;

A statement can span several lines and ends with a semicolon. The commands .ronsql_database, .ronsql_format and .ronsql_explain show or set the database (default test), the output format (default TEXT) and the explain mode (default ALLOW). The SQL USE statement does not change the RonSQL database. The CLI connects to the REST API server given by --rdrs-host and --rdrs-port, and sends the API key given by --rdrs-api-key.

ronsql_cli#

The command line tool ronsql_cli executes a single RonSQL statement directly against the cluster. It is intended for testing and scripting only: it connects to the cluster at every invocation, which adds a latency overhead of more than a second, whereas the REST API server keeps an open connection. It does not support AVRO(), API keys or rate limits. Its options are:

  • --connect-string string: the NDB connection string. Without it, no connection is made, and only EXPLAIN (with explain mode REQUIRE or FORCE) is possible, with limited output.

  • -D, --database name: the database; required together with --connect-string.

  • -e, --execute query: the statement to execute. --execute-file file reads it from a file; without either option, it is read from standard input.

  • --output-format format: JSON, JSON_ASCII, TEXT or TEXT_NOHEADER. The default is JSON when standard input and output are both terminals, and TEXT otherwise. -s selects a text format.

  • --explain-mode mode: ALLOW (default), FORBID, REQUIRE, REMOVE or FORCE.

  • -T, --debug-info: print timing information at exit.

The output is the same as the REST API output, without the {"data": ...} wrapper. The exit code is 0 on success, 1 on a permanent error and 3 when a temporary error persisted through three attempts.

ronsql_cli --connect-string mgmhost:1186 -D test \
  -e "SELECT o_custkey, COUNT(*) FROM orders GROUP BY o_custkey;"

EXPLAIN#

Prefixing a query with the EXPLAIN keyword returns a text description of the execution plan instead of executing the query. This shows how RonSQL will access each table (primary key lookup, index scan or table scan), the join structure, and which conditions are pushed down to the data nodes.

Over the REST API, the explainMode field controls how the EXPLAIN keyword is treated:

  • ALLOW: (default) Execute SELECT queries, explain EXPLAIN SELECT queries.

  • FORBID: Return an error for EXPLAIN queries.

  • REQUIRE: Return an error for queries without EXPLAIN.

  • REMOVE: Execute the query even if it has an EXPLAIN prefix.

  • FORCE: Explain the query even if it has no EXPLAIN prefix.

Explain output is only available in the text output formats, so requests that produce explain output must set outputFormat to TEXT or TEXT_NOHEADER; with a JSON output format, an explain request fails with an unsupported error. EXPLAIN still loads the table definitions, so unknown tables and columns are reported as errors.

EXPLAIN output#

Explain output is a plain-text report. Depending on the query class it contains, in order:

  • The FRAGS_PER_WORKER setting, if given (see Configuration).

  • Notes about CTE rewrites, for example that a CTE was collapsed into the pass-through ORDER BY scan, flattened into a single-table aggregate, aggregated in RonSQL over the ORDER BY / LIMIT scan of its body, or served as the last N rows (see CTEs and subqueries).

  • CTE definitions: (CTE queries only) — one entry per CTE, showing its name, source table, output columns, GROUP BY columns, any ORDER BY and LIMIT, markers such as [single-row key lookup body] and [single-group body], and how its body is read (Body root:).

  • Join plan (N operations): (join and CTE queries) — the join tree, one line per operation. The first operation is marked [ROOT]; joined operations are marked [INNER], [LEFT JOIN], [SEMI] or [ANTI] and show the operation they are joined to after an arrow (<- p). Each operation names its access method (TABLE_SCAN, INDEX_SCAN, PK_LOOKUP, UNIQUE_LOOKUP, or CTE_LOOKUP and CTE_SCAN for reading a CTE result), followed by details such as the chosen index, the join key equalities (Key:), index bounds (Bounds:, Ranges:), whether a filter is pushed to the operation (Residual filter: yes), and for aggregate queries the operation where aggregation is applied (** Aggregation leaf **).

  • Query parse tree: — the query as RonSQL understood it, with the output columns, FROM, WHERE, GROUP BY, ORDER BY and LIMIT clauses in normalized form. Outputs computed after the data nodes return their results are marked CLIENT-SIDE CALCULATION (for AVG) or CLIENT-SIDE AVRO DECODING.

  • Aggregation program (N instructions): (aggregate queries only) — the compiled aggregation program that the data nodes execute for each row.

  • Access method details for single-table queries, one of:

    • Execute as primary key lookup., with each condition labeled KEY[n]: (used as primary key column n) or FILTER: (evaluated against the fetched row);

    • Execute as N primary key lookups (IN list on ...) or Execute as N aggregating primary key lookups (IN list on ...), for an IN list on the primary key;

    • Execute as index scan., followed by the chosen index on the Index: line, any Ranges: from an IN list, and each condition labeled INDEX[n]: (used as a range bound on index column n) or FILTER: (pushed filter evaluated during the scan);

    • Execute as table scan., followed by the pushed FILTERS: or No filters.;

    • Execute as fragment-stats COUNT(*) (no row scan)., for a COUNT(*) over a whole table.

  • ORDER BY: strategy (projection-only queries with ORDER BY) — index order (rows are streamed in index order, no sort needed), client-side sort (rows are buffered and sorted, and LIMIT is applied after the sort), or no sort for a single-row primary key lookup.

  • Result post-processing: the result ordering and limit applied after the data nodes return their results, the output format, and the size of the post-processing program.

For a single-table aggregate query:

EXPLAIN SELECT cchar AS c, MAX(sint16)
FROM api_scan GROUP BY cchar ORDER BY cchar;

the explain output looks like this:

Query parse tree:
SELECT
  Out_0:`c`
   = C0:`cchar`
  Out_1:`max(sint16)`
   = A0:Max(`sint16`)
FROM api_scan
GROUP BY
  C0:`cchar`
ORDER BY
  C0:`cchar` ASC

Aggregation program (2 instructions):
Instr. DEST SRC DESCRIPTION
Load   r00  C01 r00 = C01:`sint16`
Max    A00  r00 A00:MAX <- r00:`sint16`

Execute as table scan.
No filters.

Result sorted by `cchar` ASC.
Output in mysql-style tab separated format.
The program for post-processing and output has 8 instructions.

For a query joining a CTE:

EXPLAIN
WITH agg2 AS (
  SELECT grp AS g, COUNT(*) AS cnt, SUM(val) AS s
  FROM pt2 GROUP BY grp)
SELECT p.b, COUNT(*), SUM(agg2.cnt), SUM(agg2.s)
FROM pt1 AS p JOIN agg2 ON agg2.g = p.b
GROUP BY p.b;

the explain output additionally shows the CTE definitions and the join plan:

CTE definitions:
  CTE[0] agg2 — source: pt2
    Outputs: g, cnt, s
    GROUP BY: col_0
    Body root: TABLE_SCAN

Join plan (2 operations):
├─ 0: [ROOT] TABLE_SCAN pt1 AS p
╰─ 1: [INNER] CTE_LOOKUP CTE:agg2 AS agg2  <- p
     CTE outputs: g, cnt, s
     Key: g = p.b
     ** Aggregation leaf **

Query parse tree:
SELECT
  Out_0:`b`
   = C2:`b`
  Out_1:`COUNT(*)`
   = A0:Count(1)
  Out_2:`SUM(agg2.cnt)`
   = A1:Sum(`cnt`)
  Out_3:`SUM(agg2.s)`
   = A2:Sum(`s`)
FROM pt1
GROUP BY
  C2:`b`

Aggregation program (6 instructions):
Instr. DEST SRC DESCRIPTION
LoadI  r00  I00 r00 = I00:1
Count  A00  r00 A00:COUNT <- r00:1
Load   r00  C03 r00 = C03:`cnt`
Sum    A01  r00 A01:SUM <- r00:`cnt`
Load   r00  C04 r00 = C04:`s`
Sum    A02  r00 A02:SUM <- r00:`s`


Output in mysql-style tab separated format.
The program for post-processing and output has 14 instructions.

The explain output is intended for human consumption and its exact format may change; do not parse it programmatically.

Supported query classes#

RonSQL supports two families of queries: aggregate queries, which compute COUNT, SUM, MIN, MAX and AVG (including COUNT(*)) with optional GROUP BY and HAVING, and projection-only queries, which return rows of plain column values without aggregation. Both families support WHERE, ORDER BY and LIMIT. The sections below give an overview; the linked chapters describe each query class in detail.

Single-table queries#

Queries over a single table can be aggregate queries or projection-only queries. RonSQL automatically chooses between primary key lookups (also for IN lists on the primary key), an ordered index scan (with several ranges for an IN list on an indexed column) and a table scan, and pushes WHERE conditions down to the data nodes.

SELECT o_custkey, COUNT(*), SUM(o_total)
FROM orders
WHERE o_total > 100
GROUP BY o_custkey;

SELECT o_id, o_total
FROM orders
WHERE o_custkey = 100
ORDER BY o_total DESC
LIMIT 10;

See Single-table queries in RonSQL for details.

Join queries#

Inner joins and left outer joins are executed as pushed joins: the whole join tree is evaluated inside the data nodes. Aggregates can be computed over the join result, also inside the data nodes, so that only the (typically small) aggregated result is returned. Join queries can likewise be projection-only.

SELECT c.c_region, SUM(l.l_quantity)
FROM customer AS c
JOIN orders AS o ON o.o_custkey = c.c_id
JOIN lineitem AS l ON l.l_orderkey = o.o_id
WHERE c.c_active = 1
GROUP BY c.c_region;

See Join queries in RonSQL for details.

CTEs and subqueries#

A WITH clause can define common table expressions (CTEs), whose results can then be joined into the main query like tables or scanned as its first table. A CTE body is an aggregating query (with GROUP BY or with scalar aggregates only), a single-row primary-key lookup, or a selection of the last N rows of an entity with ORDER BY and LIMIT. This allows two-level aggregations, such as aggregating over per-group aggregates, entirely inside the data nodes, looking up a single reference row once and using its columns throughout the query, and aggregating over the most recent events of an entity. Scalar, IN and EXISTS subqueries are supported in the WHERE clause, and correlated aggregate subqueries are supported in the SELECT list.

WITH agg AS
  (SELECT b AS g, COUNT(*) AS cnt FROM t1 GROUP BY b)
SELECT MAX(agg.cnt), COUNT(*)
FROM t1 AS p
JOIN agg ON agg.g = p.b;

SELECT l_orderkey, SUM(l_quantity)
FROM lineitem
WHERE l_quantity > (SELECT MIN(l_quantity) FROM lineitem)
GROUP BY l_orderkey;

See CTEs and subqueries in RonSQL for details.

Restrictions and MySQL compatibility#

RonSQL deliberately implements a subset of MySQL. Queries outside the subset are rejected with an error message describing what is unsupported. Notable restrictions include: only SELECT statements; no expressions in the select list outside aggregate functions; GROUP BY only on column names; ORDER BY only on column names and aliases; joins only on equality of indexed columns; and ORDER BY / LIMIT are not supported inside subqueries. There are also syntax differences from MySQL, for example that SQL comments are not supported, that unquoted identifiers may not coincide with keywords, and that double-quoted strings are not supported. See Restrictions and MySQL compatibility for the complete list.

Configuration#

The following REST API server configuration parameters affect RonSQL requests:

  • Internal.maxReqSize: The maximum HTTP request body size in bytes (default 4 MiB, minimum 256). Larger request bodies are refused with HTTP status 413. There is no separate limit on the length of the query. This parameter also sizes the per-thread JSON parse buffers, so REST.NumThreads * Internal.maxReqSize bytes are pre-allocated.

  • Internal.MaxRespSize: The maximum HTTP response body size the server will build for a RonSQL query, in bytes (default 64 MiB; 0 means unlimited; otherwise at least 64 KiB). A query whose result exceeds the cap fails with HTTP status 500 and an error naming the cap, suggesting to narrow the query or add LIMIT, instead of exhausting server memory. The query still runs to completion in the data nodes.

  • RonSQL.NumThreads: The number of RonSQL worker threads (default 16). A request is parsed and authorized on the REST thread that received it and then executed by a RonSQL worker, so a long-running query does not delay other requests. This is also the maximum number of RonSQL queries the server executes at once. 0 executes queries on the REST threads.

  • RonSQL.MaxQueuedRequests: The maximum number of requests waiting for a RonSQL worker (default 1024). A request arriving when the queue is full fails at once with HTTP status 503.

  • Internal.SchemaCacheTTLSecs: How long, in seconds, the REST API server caches the list of indexes of a table (default 10; 0 means until the table definition changes). A newly created index is used by RonSQL within this time. Other schema changes, such as ALTER TABLE changing columns or dropping an index, are detected when a query fails, and the query is retried automatically.

  • Internal.OperationIDMaxSize: The maximum length of the operationId request field (default 256 bytes).

  • RateLimit.Enable: Tags RonSQL transactions with the API key, so that the data nodes can enforce user rate limits (see Rate limits and quotas).

There is no query timeout in RonSQL; the transaction timeouts of the data nodes still apply.

On the data nodes, the configuration parameter CompiledInterpreter controls whether the interpreted programs that RonSQL pushes down (scan filters and aggregation programs) are compiled to machine code. The value OFF (the default) uses the interpreter only; AUTO compiles every eligible program. AUTO is only accepted on x86_64 and aarch64 data nodes. The parameter can be changed at runtime with the management client, e.g. ALL SET CompiledInterpreter AUTO, and applies to programs compiled after the change. RonSQL marks its aggregation programs as reusable, so that the data nodes can cache the compiled programs of repeated queries. The use of compiled programs is not visible in EXPLAIN.

For advanced performance tuning of aggregate join and CTE queries, a statement can start with FRAGS_PER_WORKER = n (after any EXPLAIN prefix), which bundles n fragments of the first table per worker inside the data nodes. The value must be a positive integer and is normalized to a power of two, at most 8; the default is one fragment per worker. The setting is ignored by single-table queries, projection-only queries, and queries that scan a CTE result. Most users should not need this option.

Error handling and retries#

RonSQL distinguishes permanent errors from temporary errors. Permanent errors, such as syntax errors, unsupported constructs, references to missing tables or columns, or arithmetic overflow, fail immediately with a message describing the problem and one of the error classes listed under Error responses.

Temporary errors reflect transient conditions, for example temporary overload of the cluster, a lock wait timeout, or a change of a table definition since the query was planned (such as an ALTER TABLE). The REST API server automatically retries such queries, up to 10 attempts in total, with delays growing from 10 milliseconds to one second, which adds up to a few seconds. A query is not retried once it has started producing result rows. A query that still fails after all attempts returns HTTP status 503 with error class resource, indicating that the same query may succeed if resubmitted later. Rate limit errors (HTTP status 429) are never retried by the server.