Skip to content

RonSQL Single-Table Queries#

This chapter describes the RonSQL query classes that read a single table: aggregate queries, which compute COUNT, SUM, MIN, MAX and AVG with optional GROUP BY and HAVING, and projection-only queries, which return plain column values without aggregation. Both classes support WHERE, ORDER BY and LIMIT, and both push filtering down to the data nodes; aggregate queries also push the aggregation itself down, so only the aggregated result travels back. For how to submit queries and read results, see the RonSQL chapter; for which column types can be used where, see Data types and output; for queries over several tables, see Join queries and CTEs and subqueries.

The examples in this chapter use the following table:

CREATE TABLE orders (
  o_id      INT NOT NULL,
  o_custkey INT NOT NULL,
  o_status  CHAR(1) NOT NULL,
  o_total   DECIMAL(12,2) NOT NULL,
  o_date    DATE NOT NULL,
  o_comment VARCHAR(100),
  PRIMARY KEY (o_id),
  INDEX idx_custkey (o_custkey),
  INDEX idx_status_date (o_status, o_date)
) ENGINE=NDB;

Aggregate queries#

An aggregate query has at least one aggregate function in its SELECT list (or in HAVING). Without GROUP BY it returns exactly one row; with GROUP BY it returns one row per group.

SELECT o_custkey, COUNT(*), SUM(o_total) AS total
FROM orders
WHERE o_status = 'O'
GROUP BY o_custkey;

The scan, the filter and the aggregation all execute inside the data nodes, in parallel across table fragments. Only the per-group results are returned.

The SELECT list#

Each item in the SELECT list of an aggregate query must be one of:

  • A column name, plain (o_custkey) or qualified (orders.o_custkey). Every such column must also appear in the GROUP BY clause; otherwise the query is rejected with Ungrouped column in non-aggregated SELECT expression. Write the column the same way, qualified or unqualified, in the SELECT list and in GROUP BY.

  • An aggregate function call, described below.

Expressions outside aggregate functions are not supported: SELECT SUM(a) + 1, SELECT SUM(a) / SUM(b) and SELECT 1 are syntax errors. Compute such values from the result on the client side.

Any item can be given an alias with AS; the AS keyword is required. Without an alias, the output column is named by the SQL text of the item itself, and that text is limited to 64 bytes -- longer expressions must be aliased. Aliases and identifiers are limited to 64 bytes.

Aggregate functions#

The supported aggregate functions are COUNT, SUM, MIN, MAX and AVG, plus COUNT(*). COUNT(expr) counts rows where the argument is not NULL; COUNT(*) counts all rows. Other MySQL aggregate functions such as STD, STDDEV, VARIANCE, VAR_POP, BIT_AND and GROUP_CONCAT, as well as COUNT(DISTINCT ...), are not implemented and are rejected with an “Unimplemented keyword” error.

SUM and AVG accept numeric arguments: integer types, FLOAT, DOUBLE and DECIMAL. MIN, MAX and COUNT additionally accept CHAR and VARCHAR columns (compared using the column’s collation) and the temporal types DATE, YEAR, DATETIME, TIME and TIMESTAMP. Integer sums are computed exactly in 64 bits, and an overflow fails the query. SUM, AVG, MIN and MAX over FLOAT, DOUBLE and DECIMAL columns with a nonzero scale are computed in double precision, so they can differ from MySQL’s exact DECIMAL arithmetic in the least significant digits. See Data types and output for the result types and formatting of each aggregate function.

For a scalar aggregate query (no GROUP BY) over an empty table or an empty filter result, COUNT returns 0 and the other aggregate functions return NULL, as in MySQL. RonSQL does not support time zones and behaves as if the time zone were UTC; TIMESTAMP values are therefore displayed in UTC.

Aggregate arguments#

The argument of an aggregate function is an arithmetic expression built from:

  • column names (plain or qualified);

  • integer literals (up to 9223372036854775807 in magnitude) -- fractional and string literals are not accepted in arithmetic;

  • unary minus and parentheses;

  • the operators +, -, *, / (floating point division), DIV (integer division) and % (modulo);

  • CASE WHEN condition THEN expr ELSE expr END;

  • GREATEST(expr, expr, ...) and LEAST(expr, expr, ...).

CASE is supported in the searched form with exactly one WHEN branch, and the ELSE branch is mandatory. The THEN and ELSE expressions cannot be NULL, so the common MySQL idiom SUM(CASE WHEN c THEN x END) must be written as SUM(CASE WHEN c THEN x ELSE 0 END) (or use COUNT with a suitable WHERE condition). The condition must be a single comparison (=, !=, <, <=, >, >=) between a column and a literal, or a chain of such comparisons combined only with AND or only with OR; an IN list counts as an OR chain. In the condition, the literal may also be a fractional or string literal, and a column can be compared with another integer or floating point column. IS NULL, LIKE, NOT and mixed AND/OR are not supported in CASE conditions.

GREATEST and LEAST take two or more operands, each of which is a column, an integer literal or a nested GREATEST or LEAST; arithmetic operands are not supported. At least one operand must be a column, and column operands must have integer types.

Constant integer subexpressions are computed when the query is prepared; overflow, and DIV or % by the constant zero, are reported as errors. At runtime, division by zero produces NULL for that row, following MySQL semantics, while integer overflow fails the query (see Data types and output). A common pattern is conditional counting:

SELECT o_status,
       SUM(CASE WHEN o_custkey < 1000 THEN 1 ELSE 0 END) AS low_keys,
       MAX(GREATEST(o_custkey, 500)) AS capped_max
FROM orders
GROUP BY o_status;

GROUP BY#

GROUP BY takes a comma-separated list of column names, plain or qualified, at most 127 columns. Expressions, aliases, column positions and WITH ROLLUP are not accepted in GROUP BY. Grouping is supported on all integer types, FLOAT, DOUBLE, DECIMAL, CHAR, VARCHAR, DATE, DATETIME and TIMESTAMP columns. Grouping on BIT, BINARY/VARBINARY, BLOB/TEXT, TIME and YEAR columns is not supported; such a query fails when the first group is printed. A GROUP BY clause on a query without aggregate functions is rejected. The columns in GROUP BY need not appear in the SELECT list.

HAVING#

HAVING filters groups after aggregation. A HAVING condition is built from aggregate function calls (which need not appear in the SELECT list) and numeric literals, combined with +, -, *, /, the comparison operators =, !=, <>, <, <=, >, >=, the logical operators AND, OR, NOT, and IS NULL / IS NOT NULL on an aggregate result. The conditions are evaluated in double precision.

SELECT o_custkey, SUM(o_total) AS total
FROM orders
GROUP BY o_custkey
HAVING SUM(o_total) > 10000 AND COUNT(*) >= 3;

Note that HAVING must repeat the aggregate expression: referring to the alias of an aggregate output, such as HAVING total > 10000 in the example above, is not supported. The only exception is the alias of a correlated subquery in the SELECT list; see CTEs and subqueries. Plain table columns, including GROUP BY columns, are not supported in HAVING either; filter on them in the WHERE clause instead. Both are rejected with HAVING can only reference aggregate functions. XOR, LIKE, DIV, %, bitwise operators and string literals are not supported in HAVING.

ORDER BY and LIMIT#

On an aggregate query, ORDER BY accepts a list of GROUP BY columns and aliases of outputs, each optionally followed by ASC (the default) or DESC. Ordering by an aggregate expression directly, such as ORDER BY SUM(o_total), is a syntax error -- give the aggregate an alias and order by the alias:

SELECT o_custkey, SUM(o_total) AS total
FROM orders
GROUP BY o_custkey
ORDER BY total DESC, o_custkey
LIMIT 10;

An ORDER BY column that is not in the GROUP BY clause is rejected, which includes any column in ORDER BY of a scalar aggregate query (which has no GROUP BY). Sorting follows MySQL semantics: NULL values sort first in ascending order and last in descending order, and string columns sort according to their collation. The sort is performed by RonSQL after the data nodes have returned the groups.

LIMIT takes a single non-negative integer and is applied after HAVING and ORDER BY. LIMIT without ORDER BY is allowed and returns an arbitrary subset of the groups. LIMIT 0 produces an empty result -- in text output not even the header row, matching the mysql client. The MySQL forms LIMIT offset, count and OFFSET are not supported.

Projection-only queries#

A projection-only query selects plain column values without aggregation:

SELECT o_id, o_date, o_total, o_status
FROM orders
WHERE o_custkey = 100;

Every item in the SELECT list must be a plain or qualified column name or an AVRO(column) call (see Data types and output), optionally aliased with AS. Expressions in the SELECT list, such as SELECT o_total + 1, are a syntax error, and SELECT * is not supported. GROUP BY and HAVING are not allowed on projection-only queries, and a single-table projection may not carry a WITH clause that it does not use.

Columns of all integer types, FLOAT, DOUBLE, DECIMAL, CHAR, VARCHAR, BINARY, VARBINARY, DATE, YEAR, DATETIME, TIME and TIMESTAMP can be projected. Binary values are returned base64 encoded in the JSON output formats. Projecting a BIT or BLOB/TEXT column fails with the error Unsupported column type (N) in pass-through result., where N is an internal type code.

How the query executes#

RonSQL automatically chooses an access method, visible in EXPLAIN:

  • Primary key lookup. When the WHERE clause covers every primary key column with equality conditions (in any order, including composite and VARCHAR keys), a projection-only query executes as a single-row lookup. Any additional conditions ride along as a filter evaluated on the data node, so a row that exists but fails the filter yields an empty result. Contradictory conditions such as o_id = 77 AND o_id = 78 correctly yield an empty result. A very large residual filter makes the query fall back to a scan.

  • Several primary key lookups. When one of the primary key columns is given by an IN list and the others by equality conditions, the query executes as one primary key lookup per distinct value, all in one round trip, for up to 4095 values. This also applies to aggregate queries, which then aggregate the rows read by the lookups:

    SELECT o_id, o_total FROM orders WHERE o_id IN (12, 4, 30, 7);
SELECT COUNT(*), SUM(o_total) FROM orders WHERE o_id IN (12, 4, 30, 7);

  • Ordered index scan. When the WHERE clause matches an ordered index -- equality conditions on a prefix of the index columns, optionally followed by a range condition on the next column -- the query scans only the matching index range. An IN list on an index column becomes a scan of several ranges, one per distinct value, for up to 4095 values. Conditions not expressible as index bounds are pushed down as filters on the scanned rows. Among several usable indexes, RonSQL prefers the one with the most equality bounds.

  • Table scan. Otherwise the whole table is scanned, with all WHERE conditions pushed down as filters, so non-matching rows are discarded inside the data nodes.

  • Row count statistics. A query whose only outputs are COUNT(*), with no WHERE, GROUP BY or HAVING clause, reads the row counts that the data nodes maintain for each table fragment instead of scanning the table, like a MySQL server does for the same query.

EXPLAIN prints Execute as primary key lookup., Execute as N primary key lookups (IN list on ...), Execute as index scan. (with the chosen index and, for each condition, whether it became an index bound or a filter), Execute as table scan. or Execute as fragment-stats COUNT(*) (no row scan). The same access-method selection applies to the scans of aggregate queries, except that an aggregate query with equality conditions on the whole primary key executes as a scan restricted to that key.

ORDER BY and LIMIT on projections#

LIMIT on a projection-only query streams rows and stops as soon as the limit is reached, closing the scan early -- an effective way to sample a large table. LIMIT 0 produces an empty result. Without ORDER BY, which rows are returned by a truncating LIMIT is not deterministic.

ORDER BY accepts table columns (plain or qualified, not necessarily in the SELECT list) and output aliases, each with optional ASC/DESC. It executes in one of two ways:

  • Index order (streaming). When the ordered index chosen for the scan also delivers the requested order -- the ORDER BY list equals the index columns that follow any leading equality conditions, with one direction across all keys -- the per-fragment ordered scans are merged into one globally ordered stream. Combined with LIMIT, this returns the top N rows while reading only as much of the index as needed. An index can be chosen for its ordering alone, even without a usable WHERE bound. A scan of several ranges from an IN list does not deliver index order.

  • Client-side sort (buffered). Otherwise all matching rows are buffered and sorted before printing, and LIMIT is applied after the sort. The buffer is capped at 1,000,000 rows or 256 MB; exceeding the cap fails with an error suggesting to tighten the WHERE clause or use an aggregate query.

SELECT o_id, o_date
FROM orders
WHERE o_status = 'O'
ORDER BY o_date DESC
LIMIT 5;

This example streams in index order via idx_status_date: the equality on o_status binds the first index column and the index delivers o_date order. EXPLAIN reports the strategy as ORDER BY: index order ... or ORDER BY: client-side sort .... Mixed ASC/DESC directions always use the client-side sort. NULL values sort first in ascending and last in descending order.

The WHERE clause#

Both query classes share the same WHERE support. A condition is a combination of predicates using AND (&&), OR (||), XOR, NOT and parentheses. Note that || is logical OR, never string concatenation. The supported predicate forms are:

  • column <cmp> literal and literal <cmp> column, where <cmp> is =, !=, <>, <, <=, > or >=;

  • column <cmp> column, comparing two columns of the table that have identical types;

  • column LIKE 'pattern' with the MySQL wildcards % and _ (the pattern must be a string literal);

  • column IS NULL and column IS NOT NULL;

  • column IN (literal, literal, ...), equivalent to a chain of OR-ed equalities;

  • GREATEST(...) or LEAST(...) compared against a constant with <, <=, > or >=; the arguments must be columns or integer literals, including at least one column;

  • scalar subqueries, IN subqueries and EXISTS subqueries -- see CTEs and subqueries.

Every predicate must compare a column: WHERE 1 = 1, a bare column used as a truth value (WHERE flag), and arithmetic on columns (WHERE a + 1 > 5) are rejected. Arithmetic between constants is not computed either, so WHERE a > 1 + 2 is rejected; write the computed value. The operator ! is not supported in WHERE; use NOT. BETWEEN, x NOT IN (...) and x NOT LIKE ... are not supported; write x >= a AND x <= b, NOT (x IN (...)) and NOT (x LIKE ...) instead. A WHERE clause can have at most 64 conditions combined with AND at the top level.

Literals are integer literals (up to 9223372036854775807 in magnitude, optionally negated), fractional literals written with a decimal point and digits on both sides (e.g. 123.45; scientific notation is not supported), and single-quoted string literals with MySQL backslash escapes. Adjacent string literals are concatenated; double-quoted strings are not supported. Date and time values are written as string literals, e.g. o_date >= '1997-06-01'. The type of a literal must match the type of the column it is compared with; for example, an integer column can only be compared with integer literals, and a DATETIME column only with literals that include a time part. See Data types and output for the complete rules.

LIKE patterns are matched in the data nodes using the column’s collation. The escape character is always the backslash, and the ESCAPE clause is not supported. Trailing spaces of CHAR values are ignored.

For temporal comparisons, DATE_ADD and DATE_SUB are supported as constant expressions: the first argument must be a string literal (or a nested DATE_ADD/DATE_SUB call) and the second an INTERVAL n unit literal, where the amount is an integer or a string holding an integer (negative allowed) and the unit is one of MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR. The whole expression is folded to a constant when the query is prepared:

SELECT COUNT(*), SUM(o_total)
FROM orders
WHERE o_date >= DATE_SUB('1998-12-01', INTERVAL 90 DAY);

DATE_ADD and DATE_SUB cannot be applied to columns and cannot be used in the SELECT list. The composite interval units (such as DAY_HOUR or YEAR_MONTH) and the EXTRACT function are not supported.

Comparisons are supported on columns of all integer types, FLOAT, DOUBLE, DECIMAL, CHAR, VARCHAR, DATE, DATETIME and TIMESTAMP. String comparisons use the column’s collation. TIME, YEAR, binary and BLOB/TEXT columns cannot be used in comparisons, except with IS NULL and IS NOT NULL.

Index hints#

Since RonSQL has no cost-based optimizer, index selection can be steered with MySQL-style hints placed after the table name (KEY is a synonym for INDEX):

SELECT o_custkey, SUM(o_total) AS total
FROM orders FORCE INDEX (idx_custkey)
WHERE o_custkey = 100
GROUP BY o_custkey;

SELECT o_id, o_total FROM orders USE INDEX () WHERE o_total > 500;

  • FORCE INDEX (name, ...) requires one of the named indexes to be used. If no listed index exists or is usable -- no WHERE condition matches its leading column and it does not serve the ORDER BY -- the query is rejected rather than silently falling back to a table scan.

  • USE INDEX (name, ...) restricts the choice to the named indexes but allows a table scan if none is usable. USE INDEX () with an empty list forces a table scan.

  • IGNORE INDEX (name, ...) removes the named indexes from consideration.

Only ordered indexes can be used for scans, so hints name ordered indexes; the ordered index of the primary key is called PRIMARY (it does not exist when the primary key is defined with USING HASH). Index names in hints are case insensitive. Unlike MySQL, USE INDEX and IGNORE INDEX do not report index names that do not exist. Hints are honored only on the scanned root table of a query (or of a CTE body); a hint on a joined table is rejected -- see Join queries. On projection-only queries, hints also influence the ORDER BY strategy: forcing an index that delivers the requested order enables index-ordered streaming, and ignoring it forces the client-side sort.

Not supported#

The following are not supported on single-table queries. See Restrictions and MySQL compatibility for the complete list of RonSQL restrictions.

  • Expressions in the SELECT list (syntax error), except aggregate functions and AVRO(column); function calls other than these (“Unknown function”). GREATEST/LEAST as a SELECT item is only accepted over scalar CTE outputs, see CTEs and subqueries.

  • SELECT *, DISTINCT, BETWEEN, OFFSET, UNION, CAST and all other unimplemented MySQL keywords: “Unimplemented keyword. If this was intended as an identifier, use backtick quotation.”

  • The aggregate functions STD, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, GROUP_CONCAT and COUNT(DISTINCT ...).

  • CASE without ELSE, with several WHEN branches, in the simple form CASE x WHEN ..., or outside an aggregate argument.

  • GROUP BY on expressions, aliases or column positions; GROUP BY without aggregation; WITH ROLLUP.

  • Aliases and plain columns in HAVING.

  • ORDER BY on expressions, aggregate calls or column positions; on aggregate queries, ORDER BY on a column not in GROUP BY.

  • LIMIT offset, count, negative or non-integer LIMIT values, and LIMIT placed before ORDER BY (syntax errors).

  • Constant arithmetic, !, EXTRACT and composite interval units in WHERE.

  • SQL comments (--, #, /* */) and double-quoted strings anywhere in the statement.

  • Columns of unsupported types in the various parts of a query; see Data types and output.

  • Keywords as unquoted identifiers: MySQL keywords, whether implemented in RonSQL or not, must be backtick-quoted to be used as identifiers.

Note also that every statement must end with a semicolon, and that a non-aggregate query shape outside the supported classes is rejected with a message beginning “This query has no aggregate expression, so it is not an aggregate query”, listing the supported projection-only shapes.