Skip to content

RonSQL Common Table Expressions and Subqueries#

A common table expression (CTE) is a named intermediate result defined with a WITH clause:

WITH name AS (SELECT ...)
SELECT ... FROM ... JOIN name ON ...;

RonSQL materializes each CTE result inside the data nodes. The main query, and later CTEs, can then scan the CTE result or join it like a table. The whole statement, including the CTE materialization, the joins and the final aggregation, executes inside the data nodes; intermediate rows never leave the data nodes. This makes CTEs the RonSQL way to express two-level aggregations, such as aggregating over per-group aggregates, or comparing rows against a computed watermark value.

RonSQL supports four classes of CTE bodies:

  • GROUP BY bodies produce one row per group. Every output column must be either an aggregate function or a GROUP BY column, and the GROUP BY columns become the CTE’s key columns.

  • Scalar bodies have no GROUP BY and only aggregate outputs. They produce exactly one row.

  • Single-row key-lookup bodies have no aggregation at all: a single-table SELECT of plain columns whose WHERE clause binds the full primary key by equality. They produce at most one row — the looked-up row itself. See the single-row key-lookup section below.

  • Last-N bodies have no aggregation either: a single-table SELECT of plain columns with ORDER BY and LIMIT, typically selecting the N most recent rows of an entity. The main query can pass the rows through, aggregate them or join them. See the section on the last N rows below.

Any other body that outputs plain columns without aggregation is rejected. For example, a body with an aggregate plus a column that is not in GROUP BY fails with:

CTE 's' has non-aggregate output columns and must contain GROUP BY.

and a body with no aggregate function that does not qualify as a single-row key lookup fails with “CTE 's' must contain at least one aggregate function.” or with the single-row requirement message shown in the single-row section below.

The examples in this chapter use the following schema:

CREATE TABLE customer (
  c_id INT NOT NULL,
  c_name VARCHAR(20) NOT NULL,
  c_region INT NOT NULL,
  c_credit INT NOT NULL,
  PRIMARY KEY (c_id)
) ENGINE=NDB;

CREATE TABLE orders (
  o_id INT NOT NULL,
  o_custkey INT NOT NULL,
  o_total INT NOT NULL,
  o_priority INT NOT NULL,
  PRIMARY KEY (o_id)
) ENGINE=NDB;
CREATE INDEX ix_o_custkey ON orders (o_custkey);

CREATE TABLE lineitem (
  l_id INT NOT NULL,
  l_orderkey INT NOT NULL,
  l_qty INT NOT NULL,
  PRIMARY KEY (l_id)
) ENGINE=NDB;
CREATE INDEX ix_l_orderkey ON lineitem (l_orderkey);

A worked example#

WITH sums AS (
  SELECT o_custkey AS k, SUM(o_total) AS t
  FROM orders GROUP BY o_custkey)
SELECT c.c_region, SUM(sums.t)
FROM customer AS c
JOIN sums ON sums.k = c.c_id
GROUP BY c.c_region;

This computes the total order value per region, where the inner level first sums per customer. Execution proceeds in two phases, both inside the data nodes. First, all data nodes scan their partitions of orders in parallel and aggregate per o_custkey; the resulting groups are distributed across the data nodes by key and held in memory as the materialized sums result. Second, the main query scans customer, looks up each customer’s group in sums by key, and aggregates per region. Only the final per-region rows are returned to the client.

Give every aggregate output in a CTE body an alias with AS; the alias is the name the main query (and later CTEs) use to reference the output. Column outputs keep their column name if not aliased.

Multiple CTEs are defined by separating them with commas: WITH a AS (...), b AS (...) SELECT .... Each CTE name must be unique within the statement (“Duplicate CTE name 'x'.” otherwise). The column-list form WITH name (a, b) AS (...) is not supported; name the outputs with AS inside the body. A statement can define fewer than 64 CTEs, and the whole statement, including all CTE bodies and the main query, is limited to 32 table operations inside the data nodes.

CTE body rules#

FROM and joins inside the body#

A CTE body must have a FROM clause. It can read a single table or a join chain of several tables, using the same join support as main queries (inner joins written as plain JOIN, and LEFT JOIN; see Join queries in RonSQL):

WITH revenue AS (
  SELECT o.o_custkey AS k, SUM(l.l_qty) AS r
  FROM orders AS o
  JOIN lineitem AS l ON l.l_orderkey = o.o_id
  WHERE o.o_total > 100
  GROUP BY o.o_custkey)
SELECT revenue.k, SUM(revenue.r)
FROM customer AS c2
JOIN revenue ON revenue.k = c2.c_id
GROUP BY revenue.k;

A CTE body can also read an earlier CTE; see the section on chained CTEs below.

WHERE inside the body#

The body’s WHERE clause filters rows before they are aggregated. It supports the comparison operators =, !=, <, <=, >, >= against constants (numeric and string), AND, OR (including OR of ranges and three-way OR), IS NULL and IS NOT NULL, IN value lists, LIKE on string columns, and comparisons of GREATEST / LEAST expressions against constants.

Range and equality conditions on indexed columns of the body’s root table are automatically served by ordered index scans, including multi-column bounds on composite indexes; remaining conditions are evaluated as pushed filters during the scan. MySQL-style FORCE INDEX / USE INDEX / IGNORE INDEX hints are accepted on the root table of a query or CTE body; hints on a joined table are rejected.

A comparison between two columns in a CTE body WHERE clause is supported when both are columns of the body’s first FROM table with identical type, precision, length, scale and character set — for example two INT columns, two CHAR(8) columns, two DATE columns or two DECIMAL columns of the same precision and scale (BLOB / TEXT are excluded). All six comparison operators work, alone or inside OR conditions, and a NULL in either column makes the comparison unknown and rejects the row, as in MySQL. A pair of different types is rejected:

Column-vs-column comparison in a CTE body WHERE clause requires
columns of identical type, precision, length, scale and
character set (BLOB/TEXT excluded) — cast one side or compare
against a constant.

as is a pair whose columns do not both belong to the body’s first FROM table:

Column-vs-column comparison in a CTE body WHERE clause is only
supported between two stored columns of the body's first FROM
table. Compare a column to a constant instead.

Aggregate functions and data types#

A CTE body supports COUNT(*), COUNT(col) (which skips NULL values), SUM, MIN, MAX and AVG. The argument of SUM, MIN, MAX and AVG must be a plain column; expressions such as SUM(a * b) or SUM(CASE ...) inside a CTE body are rejected. COUNT(DISTINCT) is not supported inside CTE bodies. GROUP BY may list one or several columns, including VARCHAR and DATE columns; GROUP BY on an expression is not supported. HAVING is not supported in CTE bodies and is rejected with HAVING in a CTE body is not supported.; filter the CTE’s output in the main query’s WHERE clause instead. Give every aggregate output an alias, since the unaliased output name (such as COUNT(*)) cannot be referenced from the main query.

Supported argument types:

  • SUM over all integer types (exact 64-bit accumulation), over FLOAT / DOUBLE (double-precision accumulation, so the last digits can differ from MySQL since addition order differs), and over DECIMAL. A scale-zero DECIMAL is summed exactly as a 64-bit integer; a DECIMAL with fractional digits is summed in double precision and displayed with the declared scale.

  • AVG over all integer types, FLOAT / DOUBLE, and DECIMAL (string and temporal arguments are rejected). The average is carried through the distributed aggregation as a sum and a hidden count — both merge across threads and nodes — and divided exactly once when the CTE result is finalized, so every consumer (joins, WHERE filters, watermark comparisons, re-aggregation) sees an ordinary DOUBLE value. AVG over an empty or all-NULL input is NULL. Averages of exact-type arguments are displayed with four extra fraction digits like MySQL (DECIMAL(p,s) shows scale s+4) when the source fits the double-exact range; the value is a double-precision division, so the last displayed digit can in rare cases differ from MySQL’s exact decimal arithmetic.

  • MIN / MAX over all integer types, FLOAT / DOUBLE, DECIMAL, CHAR / VARCHAR (using the column’s collation), and the temporal types DATE, YEAR, DATETIME, TIME and TIMESTAMP, including fractional seconds. TIMESTAMP results are displayed in UTC.

DECIMAL aggregates must fit the 64-bit integer range: MIN, MAX and SUM over a scale-zero DECIMAL are rejected when the declared precision exceeds 18 (signed) or 19 (unsigned):

MIN/MAX over scale-zero DECIMAL wider than the 64-bit integer
range is not yet supported.  Full DECIMAL precision preservation
requires a wider aggregate-result representation.

ORDER BY and LIMIT in bodies#

A grouped CTE body may end with ORDER BY ... LIMIT n. The pair selects which groups the CTE materializes — the top n under the ORDER BY key. As in MySQL, this decides the set of rows only: a derived table has no guaranteed delivery order, so ordering the final output remains the main query’s job. The selection runs inside the data nodes — every group is sent to a single node, which determines the kept set and drops the rest before the CTE becomes readable. Dropped groups are simply absent: an inner join drops the row, a LEFT JOIN extends with NULLs.

WITH top AS (
  SELECT o_custkey AS k, SUM(o_total) AS s
  FROM orders GROUP BY o_custkey
  ORDER BY s DESC LIMIT 10)
SELECT c.c_name, top.s
FROM customer AS c JOIN top ON top.k = c.c_id;

The rules:

  • ORDER BY keys must be output columns of the body: GROUP BY columns or aggregate outputs (including AVG), referenced by name or alias, each optionally ASC or DESC, at most 8 keys. A column that is not a body output is rejected (“must be one of the body's output columns”), and a string MIN/MAX output cannot be an ORDER BY key yet.

  • LIMIT n only (no OFFSET), with n up to 67108863. LIMIT 0 gives an empty CTE. LIMIT without ORDER BY keeps an arbitrary n groups, as in MySQL.

  • ORDER BY without LIMIT is accepted and has no effect: the materialized set is unchanged, and neither RonDB nor MySQL guarantees a derived table’s iteration order.

  • Ties at the cutoff are resolved arbitrarily, exactly as in MySQL — append a unique tie-breaker column to the ORDER BY list for a deterministic kept set.

  • Scalar aggregate bodies produce exactly one row, so ORDER BY and any LIMIT of at least 1 are accepted as no-ops; LIMIT 0 on a scalar body is rejected. Single-row key-lookup bodies support both, including LIMIT 0 to empty the CTE.

  • A body without aggregates or GROUP BY that has ORDER BY and LIMIT selects the last (or first) N rows of its table; see The last N rows.

Using CTEs in the main query#

Scanning a CTE#

The main query can use a CTE as its FROM table and scan the whole materialized result, either re-aggregating it or projecting its rows. Both of the following work:

WITH sums AS (
  SELECT o_custkey AS k, SUM(o_total) AS t
  FROM orders GROUP BY o_custkey)
SELECT MIN(sums.t), MAX(sums.t), COUNT(*)
FROM sums;

WITH sums AS (
  SELECT o_custkey AS k, SUM(o_total) AS t
  FROM orders GROUP BY o_custkey)
SELECT k, t
FROM sums
WHERE t > 1000;

Re-aggregation supports COUNT, SUM, MIN and MAX over the CTE’s outputs, with or without a main-level GROUP BY on the CTE’s key columns.

Joining a CTE#

A CTE joins into the main query with JOIN cte ON .... Each ON condition must be an equality between qualified column names, written with the newly joined table’s column first: JOIN sums ON sums.k = c.c_id. Tables must be joined in order, each ON condition referencing a previously listed table.

For a GROUP BY CTE, the ON conditions of the join must bind every key column of the CTE, where the key columns are exactly the body’s GROUP BY columns. The key columns may be bound in any order. (Single-row key-lookup CTEs are more flexible — any subset of their outputs may be bound; see the single-row section below.) A CTE whose body groups by two columns therefore needs two ON equalities:

WITH pairs AS (
  SELECT o_custkey AS k, o_priority AS p, COUNT(*) AS cnt
  FROM orders GROUP BY o_custkey, o_priority)
SELECT c.c_id, SUM(pairs.cnt)
FROM customer AS c
JOIN pairs ON pairs.k = c.c_id AND pairs.p = c.c_region
GROUP BY c.c_id;

The key columns of one CTE join may come from different previously joined tables, as long as those tables lie on a single chain in the join tree from the root down to the CTE’s join point. Keys sourced from unrelated join branches are rejected: “ON conditions reference tables from unrelated join branches. Multi-table ON conditions are supported only when the referenced tables lie on a single ancestor chain.”

Joining on a CTE output that is not a key column (that is, an aggregate output) is rejected: “CTE lookup key references a CTE output column that is not part of the virtual primary key. The virtual CTE primary key matches the CTE body's GROUP BY column list.” (The error messages refer to the CTE’s key columns as the “virtual CTE primary key”.)

As for joins between stored tables, the two columns of each ON equality must have identical types, also when one side is a CTE output. A GROUP BY column of a CTE keeps the type of its source column, while the outputs of COUNT, SUM, MIN and MAX are 64-bit values (BIGINT or DOUBLE). A mismatch is rejected with Join column type mismatch., naming both columns and their types.

The CTE can also be the query’s first FROM table, with stored tables joined onto it by their primary key or a unique key, i.e. as one lookup per CTE row. An index scan or table scan below a scanned CTE is not supported, so a stored table joined onto a CTE on a non-unique column is rejected with an error beginning A scan below a CTE scan is not supported; make the stored table the first table and join the CTE onto it instead.

Joins that bind only part of the key#

A join that binds only some of a multi-column CTE key is handled in one specific case: an inner join from the query’s first FROM table to the CTE, where the bound columns of the first table form its primary key or a unique key. RonSQL then automatically restructures the query so that the CTE result is scanned as the root and the original root table is looked up for each CTE row; the query executes and returns the same result MySQL would. This works for CTEs found anywhere in the join list, in longer join chains, and alongside LEFT JOINs elsewhere in the chain. When the bound columns of the first table are not unique, the restructured query would need a scan below the CTE scan, and the query is rejected with a Partial CTE lookup key not supported. message that explains why.

All other partial-key joins are rejected — in particular a LEFT JOIN on the partially keyed CTE itself, and a partial-key join whose parent is not the first FROM table:

Partial CTE lookup key not supported.  The virtual CTE primary
key matches the CTE body's GROUP BY column list and the join
must bind every key column.  Workaround: place the multi-key CTE
on the joined root and join the smaller table to it.

LEFT JOIN with CTEs#

A CTE can be the right side of a LEFT JOIN. Rows of the left table with no matching group get NULL for all CTE columns, both in projection-only queries and under main-level aggregation (SUM ignores the injected NULLs while COUNT(*) still counts the row). A CTE used as the first FROM table can also be the left side of a LEFT JOIN to a real table.

The anti-join pattern works: LEFT JOIN a CTE and keep only unmatched rows with WHERE cte.col IS NULL, where col is a key column of the CTE or a SUM or COUNT output. IS NULL on other outputs of a left-joined CTE, such as MIN and MAX outputs, is not supported.

WITH sums AS (
  SELECT o_custkey AS k, SUM(o_total) AS t
  FROM orders GROUP BY o_custkey)
SELECT COUNT(*)                -- customers without orders
FROM customer AS c
LEFT JOIN sums ON sums.k = c.c_id
WHERE sums.k IS NULL;

Scalar CTEs#

A scalar CTE (aggregates only, no GROUP BY) produces exactly one row. COUNT over an empty input produces 0; MIN, MAX and SUM over an empty input produce NULL. Since it always has exactly one row, a scalar CTE is joined with a comma (a cross join that multiplies each row by the single scalar row):

WITH s AS (SELECT MAX(o_id) AS m FROM orders)
SELECT c.c_id
FROM customer AS c, s
WHERE c.c_id > s.m;

This “watermark” comparison of a real column against a scalar CTE output in WHERE supports all six comparison operators. If the scalar value is NULL, every comparison is unknown and no rows are returned, matching MySQL. The operands must be integer, FLOAT, DOUBLE or DATE, or a string pair of the same string type and character set (CHAR with CHAR, VARCHAR with VARCHAR, compared with the column collation); DECIMAL and other temporal comparisons against a scalar CTE output are rejected with a clear message, as are CHAR-vs-VARCHAR mixes and differing character sets. The compared real column must belong to the first FROM table or to a table on the join chain before the scalar CTE; a column from an unrelated join branch is rejected.

Several scalar CTEs can be cross-joined into one query, and GREATEST / LEAST can combine their outputs in the SELECT list:

WITH max_v AS (SELECT MAX(o_total) AS hi FROM orders),
     min_v AS (SELECT MIN(o_total) AS lo FROM orders)
SELECT GREATEST(max_v.hi, min_v.lo) AS biggest
FROM max_v, min_v;

As a RonSQL extension (not accepted by MySQL), the FROM clause can be omitted entirely when every referenced column is qualified with a scalar CTE name; RonSQL synthesizes the cross join automatically. Referencing a grouped or single-row CTE this way is not supported; for a single-row CTE the error is:

Column qualifier 'r' refers to a single-row CTE.  SELECT without
an explicit FROM clause supports only scalar (aggregate-only) CTEs;
add an explicit comma cross join instead (FROM t, r).

Top-level GREATEST / LEAST in the SELECT list are only supported over scalar CTE outputs. Over ordinary table columns they are rejected; use an explicit aggregate such as MAX(GREATEST(col, 1)) instead. The comma cross-join syntax is restricted to scalar and single-row CTEs: comma joins of real tables or of grouped CTEs are rejected.

Scalar CTEs also work in aggregate main queries, and the main query can filter on a scalar CTE’s output directly, e.g. WITH s AS (...) SELECT m FROM s WHERE m > 100.

Single-row key-lookup CTEs#

The one CTE body class with no aggregation is the single-row key lookup: a single-table SELECT of plain columns whose WHERE clause binds every primary key column of the source table by equality with a constant. Such a body reads at most one row, and RonSQL materializes that row directly inside the data nodes — no aggregate functions and no GROUP BY are needed:

WITH conf AS (
  SELECT c_region, c_credit FROM customer WHERE c_id = 7)
SELECT o.o_id
FROM orders AS o, conf
WHERE o.o_total > conf.c_credit;

This is the natural way to look up one configuration, watermark or reference row and use its columns throughout the query, without rewriting each column as MAX(col) in a scalar CTE.

The body must satisfy all of the following; each violation is rejected with a clear error:

  • Exactly one stored table in FROM (no joins, and not an earlier CTE).

  • Every output is a plain column — no expressions, no duplicate columns.

  • The WHERE clause binds every primary key column of the table by equality with a constant. Additional WHERE conjuncts are allowed and filter the row further. Binding a key to another column or to an expression containing one does not count — the single-row guarantee would be lost.

  • No GROUP BY or HAVING. ORDER BY and LIMIT are allowed: on a body of at most one row ORDER BY and LIMIT 1 are no-ops, and LIMIT 0 empties the CTE.

A body without full key coverage fails with:

CTE 'r' has no aggregate functions and no GROUP BY, so it is
only supported as a single-row key lookup: its WHERE clause
must bind every primary key column of table 't' by equality
with a constant.  Otherwise add GROUP BY and aggregate
functions to the CTE body.

Consumption is more flexible than for GROUP BY CTEs, because the result is a single known row rather than a keyed group set:

  • Joins may bind any subset of the outputs, in any order — there is no complete-key requirement, and no restructuring is needed. JOIN conf ON conf.c_region = t.x works even though the body also outputs c_credit.

  • Comma cross joins with no ON condition work, as in the example above: the single row multiplies each row of the other table. If the looked-up row does not exist, the CTE is empty and the query returns no rows — exactly MySQL’s cross-join-with-empty semantics.

  • LEFT JOIN produces NULL-extended rows when the looked-up row is absent or the ON condition does not match, like any CTE.

  • Watermark comparisons in WHERE against the CTE’s outputs work as for scalar CTEs, with the same operand restriction (integer, FLOAT, DOUBLE, DATE, or same-type same-character-set string pairs; DECIMAL and other temporal comparison operands are rejected). Equality joins on the CTE’s outputs are not restricted this way — joining on a DECIMAL output works.

  • The CTE can be the query’s first FROM table (SELECT c_region FROM conf WHERE c_credit > 0), and later CTE bodies can join it.

The difference from a scalar CTE is the empty case: a scalar CTE always produces exactly one row (aggregates over empty input yield 0 for COUNT and NULL otherwise), while a single-row key-lookup CTE produces zero rows when the key does not match, so inner and comma joins drop and LEFT JOINs NULL-extend — in both cases matching what MySQL returns for the same statement.

EXPLAIN marks these CTEs with [single-row key lookup body] in the CTE definitions section.

Single-group CTEs#

A grouped CTE body whose WHERE clause binds every GROUP BY column to a constant by equality produces at most one group. RonSQL recognizes such single-group bodies and executes them more efficiently: when the CTE is the first table of the main query, it is read with a single key lookup, and a main query that only computes MIN or MAX over the CTE’s outputs is executed as a single-table aggregate query over the CTE body:

WITH cf AS (
  SELECT o_custkey AS k, COUNT(*) AS n, MAX(o_total) AS mx
  FROM orders WHERE o_custkey = 42 GROUP BY o_custkey)
SELECT MAX(cf.mx) FROM cf;

The results are the same as without these optimizations. EXPLAIN marks such CTEs with [single-group body], and shows a note when the main query was executed as a single-table aggregate (CTE 'cf' flattened into a single-table aggregate).

The last N rows#

Aggregating over the most recent events of an entity, such as the average amount of a customer’s last 10 transactions, is a common task, for example for the Hopsworks feature store. In RonSQL it is written with a CTE whose body selects the rows with ORDER BY and LIMIT. The examples in this section use the following table:

CREATE TABLE tx (
  customer_id BIGINT NOT NULL,
  event_time  TIMESTAMP NOT NULL,
  amount      BIGINT,
  category    VARCHAR(100),
  merchant_id INT,
  PRIMARY KEY (customer_id, event_time)
) ENGINE=NDB;

A last-N body reads a single stored table, without joins, aggregates, GROUP BY, HAVING or subqueries, outputs distinct plain columns, and has both ORDER BY and LIMIT (at least 1). Its WHERE clause usually binds the entity; a body whose WHERE clause binds the whole primary key is a single-row key lookup instead. The main query can use such a CTE in three ways, described below.

When the rows are passed through or aggregated, the body is executed as a single-table query. It is most efficient when an ordered index delivers the ORDER BY order after the columns bound by equality, like the primary key of tx does for WHERE customer_id = 42 ORDER BY event_time DESC: the scan then stops after N rows instead of reading the entity’s whole history. Otherwise all matching rows are read and sorted, as described for projection-only queries in Single-table queries. When the rows are joined or grouped, all rows matching the body’s WHERE clause are read, and the N rows are selected inside the data nodes.

Passing the rows through#

When the statement defines exactly one CTE and the main query only selects plain columns of it, with no WHERE, joins, GROUP BY, HAVING, ORDER BY or LIMIT of its own:

WITH t AS (
  SELECT customer_id, event_time, amount
  FROM tx
  WHERE customer_id = 42
  ORDER BY event_time DESC LIMIT 5)
SELECT customer_id, event_time, amount FROM t;

RonSQL executes the statement as the equivalent single-table projection-only query, so the ORDER BY can be served in index order (see Single-table queries). The rows are returned in the order given by the CTE’s ORDER BY. A main query naming a column that the CTE does not output is rejected with Column '...' is not an output of CTE '...'. EXPLAIN shows a note that the CTE was collapsed into the pass-through ORDER BY scan.

Aggregating the rows#

When the statement defines exactly one CTE and the main query only computes COUNT(*), and COUNT, SUM, MIN, MAX or AVG of plain columns of the CTE, with no WHERE, joins, GROUP BY, HAVING, ORDER BY or LIMIT of its own:

WITH t AS (
  SELECT customer_id, event_time, amount
  FROM tx
  WHERE customer_id = 42
  ORDER BY event_time DESC LIMIT 10)
SELECT COUNT(*) AS n, SUM(amount) AS s, AVG(amount) AS av FROM t;

RonSQL executes the CTE body as a single-table scan, as when passing the rows through, and computes the aggregates of the main query over the rows it returns. The aggregates follow the same rules as aggregation inside the data nodes, including the result types, the collation of string MIN and MAX, and the overflow error of integer SUM. As in MySQL, COUNT(*) is 0 and the other aggregates are NULL when the entity has no rows. EXPLAIN shows a note that the CTE was aggregated in RonSQL over the ORDER BY / LIMIT scan of its body.

Joining and grouping the rows#

A last-N CTE can also be used in other statements, as long as it is the first table in the FROM clause of the main query or of a later CTE body, and that query aggregates or joins. This covers, for example, a GROUP BY clause in the main query, joins to other tables, a later CTE that aggregates the rows, and several last-N CTEs in one statement:

WITH t AS (
  SELECT customer_id, event_time, amount, category
  FROM tx
  WHERE customer_id = 42
  ORDER BY event_time DESC LIMIT 10)
SELECT category, COUNT(*) AS n, SUM(amount) AS s
FROM t
GROUP BY category;

WITH t AS (
  SELECT customer_id, event_time, amount, merchant_id
  FROM tx
  WHERE customer_id = 42
  ORDER BY event_time DESC LIMIT 10)
SELECT m.mcc, COUNT(*) AS n, SUM(t.amount) AS s
FROM t JOIN merchants AS m ON m.merchant_id = t.merchant_id
GROUP BY m.mcc;

In this form, RonSQL executes the body as a grouped body with one group per row, and the body’s ORDER BY and LIMIT select the kept rows inside the data nodes, as described in ORDER BY and LIMIT in bodies. The body must therefore select every primary key column of its table, so that each row is its own group, and its ORDER BY columns must be among its outputs. A body without the whole primary key is rejected, naming the missing columns:

CTE 't' has LIMIT but no aggregate functions or GROUP BY.  Such a
body is served as the last N rows when it selects every primary key
column of table 'tx', so that each row is its own group (missing:
`event_time`).

The CTE cannot be the joined table of a JOIN (as in JOIN t ON ...); it must be the first table of the query that reads it. EXPLAIN shows a note that the CTE was served as the last N rows.

Chained CTEs#

A CTE body can read an earlier CTE, either scanning it as its FROM table or joining it with real tables:

WITH a AS (
  SELECT o_custkey AS k, SUM(o_total) AS t
  FROM orders GROUP BY o_custkey),
     b AS (
  SELECT c.c_region AS r, SUM(c.c_credit) AS s
  FROM a
  JOIN customer AS c ON c.c_id = a.k
  GROUP BY c.c_region)
SELECT b.r, SUM(b.s) FROM b GROUP BY b.r;

A later CTE can also simply re-aggregate an earlier one (b AS (SELECT k, MAX(t) AS m FROM a GROUP BY k)), and a chained CTE can be joined with real tables in the main query like any other CTE. Chains of three or more levels work, as do independent (sibling) CTEs in one statement. CTEs materialize in dependency order; independent CTEs materialize in parallel. An empty intermediate result simply propagates: the dependent CTE is also empty.

A CTE can only reference CTEs declared before it in the WITH list. A forward reference fails with “Table 'c1' not found.”

Name resolution#

CTEs are resolved in declaration order. Each CTE publishes only its name and its output column names (the AS aliases); aliases used inside a CTE body are internal to that body and cannot be referenced from the main query or from later CTEs. When resolving a table name in FROM or JOIN, a visible CTE takes precedence over a stored table with the same name. A CTE belongs to no database, so a database-qualified table name that names a visible CTE (such as FROM test.sums) is rejected; refer to the CTE without a qualifier.

An unqualified column name that exists in more than one visible table or CTE is rejected: “Ambiguous column 'k' found in multiple tables or CTEs. Use 'table.column' syntax.” Qualified names (alias.column) disambiguate.

Conditions on CTE outputs in the main query#

The main query’s WHERE clause can filter on CTE outputs, both key columns and aggregate outputs. Supported forms, all evaluated inside the data nodes:

  • Comparisons against constants with =, !=, <, <=, >, >=, including on string (VARCHAR) key columns.

  • IS NULL and IS NOT NULL on key columns and aggregate outputs.

  • IN value lists on key columns.

  • AND of several conditions, and OR in disjunctive normal form: a top-level OR whose branches are conditions or AND-conjunctions, with at most 16 branches. Other nesting such as (a OR b) AND c is rejected with CTE_LOOKUP filter: only top-level OR / DNF is supported. Convert '(A OR B) AND C' to DNF or split into UNION. Rewrite such a condition as (a AND c) OR (b AND c).

  • Comparisons between two outputs of the same CTE (e.g. WHERE cte.mn < cte.mx), for integer-typed outputs including mixed signedness, and for string outputs of the same string type and character set (compared with the column collation; CHAR-vs-VARCHAR mixes and differing character sets are rejected).

  • CASE conditions inside main-level aggregates may reference CTE key columns (e.g. SUM(CASE WHEN sums.k = 100 THEN sums.t ELSE 0 END)), with multi-condition AND / OR in the WHEN clause. A CASE condition on a CTE aggregate output is rejected: “CASE condition referencing a CTE aggregate output is not yet supported; reference a CTE column projection instead, or move the predicate into the CTE body's WHERE clause.”

A WHERE condition on a LEFT JOINed CTE’s output that can never be true for NULL behaves as in MySQL: it effectively converts the LEFT JOIN into an inner join (except for the IS NULL anti-join pattern shown above).

GROUP BY, HAVING, ORDER BY and LIMIT in the main query#

The main query of a CTE statement supports the full aggregate-query surface (see Single-table queries in RonSQL for the general rules):

  • GROUP BY on real-table columns and/or CTE key columns.

  • HAVING on aggregate expressions such as HAVING SUM(sums.t) > 100, composable with ORDER BY and LIMIT. As in all RonSQL queries, HAVING must repeat the aggregate expression rather than use its alias.

  • ORDER BY on GROUP BY columns (bare or qualified, including CTE key columns) and on aggregate output aliases, with ASC / DESC and multiple sort columns of mixed direction. String results sort by collation; DATE and DATETIME aggregate results sort chronologically. The aggregate must be referenced through its alias: ORDER BY SUM(x) is a syntax error, and ORDER BY on a column that is neither grouped nor an alias fails with an error saying the column “is not in the GROUP BY clause”.

  • LIMIT n. LIMIT 0 returns no output. The MySQL LIMIT offset,count form is not supported.

WITH spend AS (
  SELECT o_custkey AS k, SUM(o_total) AS total
  FROM orders GROUP BY o_custkey)
SELECT c.c_name, SUM(spend.total) AS s
FROM customer AS c
JOIN spend ON spend.k = c.c_id
GROUP BY c.c_name
ORDER BY s DESC
LIMIT 10;

Subqueries#

Subqueries do not require a WITH clause. They are supported in the WHERE clause of the main query — of aggregate queries, join queries, CTE queries and plain single-table projection queries alike — and, in a correlated aggregate form, in the SELECT list. Subqueries read stored tables, not CTEs.

Subqueries in the WHERE clause are executed before the main query, each as a separate query, and their results are substituted into the main query as constants. Subqueries in the SELECT list are instead executed as part of the main query, as additional joins inside the data nodes.

Scalar subqueries in WHERE#

An uncorrelated scalar subquery is executed first as its own query, and its single value is then used as a constant in the outer query:

SELECT o_id, o_custkey FROM orders
WHERE o_total > (SELECT MAX(o_total) FROM orders
                 WHERE o_id < 100);

The subquery should produce exactly one value, which is what an aggregate without GROUP BY does; the value must be numeric (integer, DECIMAL and DOUBLE results all work). All six comparison operators are supported, and one statement can contain several scalar subqueries. A NULL subquery result makes the comparison unknown, so no rows match.

A correlated scalar subquery — one whose inner WHERE contains an equality between an inner column and an outer column — is also supported:

SELECT COUNT(*) FROM orders AS o
WHERE o.o_total > (SELECT SUM(l_qty) FROM lineitem
                   WHERE l_orderkey = o.o_id);

The inner query must read a single table and have exactly one such equality correlation, as a top-level AND condition, on numeric columns; further inner conditions comparing against constants are allowed. The inner query is executed once, grouped by the correlation column, and its result may cover at most 1000 distinct correlation key values. Outer rows for which the inner query has no matching rows never satisfy the condition. This matches MySQL for SUM, MIN and MAX, which return NULL for no rows, but not for COUNT: RonSQL does not compare such rows against a count of 0. Use NOT EXISTS to find outer rows without matching inner rows.

IN subqueries#

WHERE col IN (SELECT ...) executes the inner query first and turns the result into a value list:

SELECT o_id FROM orders
WHERE o_custkey IN (SELECT c_id FROM customer
                    WHERE c_region = 3);

The inner query must be a supported RonSQL query producing a single numeric column — a plain single-table projection as above, or an aggregate query. It must be self-contained: an IN subquery cannot reference outer columns (use EXISTS for correlated conditions). The inner result may contain at most 1000 values; NULL values in it are ignored. The negated form x NOT IN (SELECT ...) is not supported, and neither are the ANY, SOME and ALL quantifiers.

EXISTS subqueries#

EXISTS and NOT EXISTS are supported for correlated subqueries:

SELECT COUNT(*) FROM customer AS c
WHERE EXISTS (SELECT o_id FROM orders
              WHERE o_custkey = c.c_id AND o_total > 500);

SELECT COUNT(*) FROM customer AS c
WHERE NOT EXISTS (SELECT o_id FROM orders
                  WHERE o_custkey = c.c_id);

The inner query must read a single table (no inner joins) and its WHERE must contain exactly one correlation predicate of the form inner.col = outer.col; additional non-correlated inner conditions are allowed. The inner SELECT list must name a column: EXISTS (SELECT 1 ...) is a syntax error, since RonSQL does not accept constants in the SELECT list. The EXISTS must appear as a top-level AND conjunct of the outer WHERE; inside OR it is rejected: “EXISTS subquery inside OR is not supported. EXISTS must be a top-level AND conjunct in WHERE.”

RonSQL executes [NOT] EXISTS as an [NOT] IN condition on the outer correlation column, with the distinct values of the inner correlation column (after applying the inner conditions) as the list. Therefore the correlation columns must be numeric, and the inner query may produce at most 1000 distinct correlation values.

Subqueries in the SELECT list#

A correlated aggregate subquery can appear as an output column, producing a per-row aggregate:

SELECT c.c_id,
  (SELECT SUM(o.o_total) FROM orders AS o
   WHERE o.o_custkey = c.c_id) AS total_spend,
  (SELECT COUNT(o2.o_id) FROM orders AS o2
   WHERE o2.o_custkey = c.c_id) AS num_orders
FROM customer AS c;

Each such subquery must read a single table without GROUP BY or HAVING, have exactly one output which is a SUM, COUNT, MIN or MAX of a plain column, or COUNT(*) (AVG is not supported), and have exactly one equality correlation to the outer table. The outer query must not have a GROUP BY of its own. Several subqueries over the same or different tables can be combined in one SELECT list, up to 128, and HAVING on their aliases works. Outer rows with no matching inner rows get NULL (or 0 for COUNT), as in MySQL. Give each subquery an alias, since the unaliased output name is the full subquery text, which is limited to 64 bytes.

RonSQL executes each such subquery as a left outer join from the outer table to the subquery’s table, grouped by the outer correlation column. The join rules of Join queries therefore apply: the inner correlation column needs a primary key, unique index or ordered index, and must have the same type as the outer correlation column. The outer correlation column should be a unique column of the outer table, such as its primary key.

Subquery body restrictions#

Unlike CTE bodies, where ORDER BY ... LIMIT selects the kept set, subqueries never apply them: ORDER BY and LIMIT inside any subquery are rejected at prepare time rather than silently ignored, e.g. “LIMIT inside a scalar subquery is not supported. It is only supported at the main SELECT level and in CTE bodies; accepting it here would silently ignore it, changing results compared to MySQL.” Subqueries are not supported inside CTE bodies; in particular a SELECT-list subquery there is rejected with “Subquery aggregation inside CTE body not yet supported.”

Not supported#

The following are outside the supported subset. Unless noted otherwise, each is rejected with a clear error at prepare time.

  • Recursive CTEs: WITH RECURSIVE is rejected as a syntax error (RECURSIVE is an unimplemented keyword).

  • General non-aggregating CTE bodies: apart from the single-row key-lookup and last-N classes described above, every CTE body output must be an aggregate or a GROUP BY column. A non-aggregating body over more than one table, with neither full primary-key equality in WHERE nor ORDER BY and LIMIT, or with expression or duplicate outputs is rejected.

  • Last-N CTEs as the joined table of a JOIN, and last-N bodies without the whole primary key in statements that join or group their rows (see the section on the last N rows).

  • HAVING inside CTE bodies; filter the CTE’s outputs in the main query instead.

  • Index scans and table scans below a scanned CTE: a stored table joined onto a CTE that is the first table must be joined by its primary key or a unique key.

  • CTE join keys of another type than the column they are joined to.

  • Database-qualified references to CTEs (FROM db.cte_name).

  • ORDER BY / LIMIT inside subqueries (supported at the main SELECT level and in CTE bodies).

  • COUNT(DISTINCT) inside CTE bodies, AVG over string or temporal columns, and SUM, MIN, MAX and AVG over expressions inside CTE bodies (arguments must be plain columns).

  • A column list after the CTE name (WITH name (a, b) AS ...).

  • IS NULL on MIN and MAX outputs of a left-joined CTE; the anti-join pattern works on key columns and SUM and COUNT outputs.

  • JOIN after LEFT JOIN inside a CTE body when the inner join references the left-joined table.

  • Column-vs-column comparisons in a CTE body WHERE clause between columns of different types, or not both on the body’s first FROM table (identical-type pairs on the first FROM table are supported).

  • Partial-key CTE joins other than the automatically restructured case described above (inner join from the first FROM table).

  • DECIMAL aggregates beyond the 64-bit range: scale-zero DECIMAL with precision above 18 (signed) or 19 (unsigned) for MIN / MAX / SUM.

  • Mixed string column-vs-column comparisons on CTE outputs (CHAR vs VARCHAR, or differing character sets — same-type same-charset pairs are supported), and CASE conditions on CTE aggregate outputs.

  • DECIMAL and non-DATE temporal operands when comparing a real column against a scalar CTE output.

  • EXISTS inside OR, EXISTS with inner joins, EXISTS (SELECT 1 ...), and multi-column correlation in EXISTS or correlated scalar subqueries.

  • NOT IN (SELECT ...) and the ANY, SOME and ALL quantifiers.

  • Non-numeric subquery results: scalar and IN subqueries must produce numeric values, and correlation columns must be numeric.

  • More than 1000 values from an IN subquery, a correlated scalar subquery or an EXISTS subquery.

  • Subqueries inside CTE bodies, and AVG in SELECT-list subqueries.

  • BETWEEN is not implemented anywhere in RonSQL; write two comparisons instead.

See Restrictions and MySQL compatibility for general restrictions that apply to all RonSQL queries, and the RonSQL chapter for how to execute queries and interpret errors.