Skip to content

RonSQL Restrictions and MySQL Compatibility#

RonSQL deliberately implements a subset of MySQL. The design rule is that a query is either executed with MySQL-compatible semantics or rejected with an error message; unsupported constructs are not silently ignored or approximated. This chapter is the consolidated reference for what is not supported, for the restricted forms of the supported clauses, and for the ways in which RonSQL behaves differently from MySQL even for accepted queries.

The supported functionality is described in the RonSQL overview, Single-table queries in RonSQL, Join queries in RonSQL, CTEs and subqueries in RonSQL and Data types and output. Remember that RonSQL reads ordinary RonDB tables: any query that RonSQL rejects can still be executed through a MySQL server against the same tables, with full MySQL semantics.

Statement-level restrictions#

  • SELECT is the only statement. The full statement shape is [EXPLAIN] [FRAGS_PER_WORKER = n] [WITH ...] SELECT ... ;

  • The statement must be terminated by a semicolon, and exactly one statement is allowed per request. A missing semicolon is reported as unexpected end of input, and anything other than whitespace following the semicolon is an error. Multi-statement requests are not supported.

  • No data modification: INSERT, UPDATE, DELETE and REPLACE are not supported.

  • No DDL: CREATE, ALTER, DROP and TRUNCATE are not supported. Tables are created and altered through a MySQL server.

  • No SET, SHOW, USE or DESCRIBE. The database is selected per request (the database field in the REST API, or the -D option of ronsql_cli), and all tables of a query are read from that database. A table name may be qualified with that database; a qualifier naming another database is rejected with Cross-database table references are not supported.

  • No transaction control: BEGIN, START TRANSACTION, COMMIT and ROLLBACK are not supported. Every query executes independently as a committed read (see the semantic differences section below).

  • No prepared statements or parameter placeholders, and no user or session variables; the ? and @ characters are not valid anywhere in a RonSQL statement outside string literals.

  • No stored procedures or stored functions.

  • Views are not visible to RonSQL. A view exists only in the MySQL server layer, so querying one fails with a table-not-found error. The same applies to tables in storage engines other than RonDB: RonSQL can only read ENGINE=NDB tables.

  • WITH RECURSIVE is not supported; the WITH clause only accepts the CTE forms described in the CTE chapter.

  • EXPLAIN output is only available in the text output formats, not in the JSON output formats.

No SQL comments#

RonSQL does not support SQL comments in any form. This regularly surprises users, so it deserves emphasis: -- ... line comments, # ... line comments and /* ... */ block comments (including /*! ... */ version comments and optimizer hint comments) are all not recognized. Only whitespace is skipped between tokens.

A # character is reported as an illegal token, and /* causes a syntax error, so those comment forms fail loudly. The -- form is more dangerous: RonSQL reads it as two minus signs, which in a numeric context parses as a double negation. In most places this leads to an error, but in HAVING clauses and aggregate arguments, text that MySQL would ignore as a comment can silently change the meaning of the query. For example:

SELECT a, COUNT(*) FROM t GROUP BY a HAVING COUNT(*) > 1 -- 2
;

MySQL treats -- 2 as a comment and evaluates COUNT(*) > 1; RonSQL evaluates COUNT(*) > 1 - (-2), i.e. COUNT(*) > 3. Always strip comments from queries before sending them to RonSQL.

Lexical restrictions#

Identifiers and keywords#

  • Keywords are case insensitive, as in MySQL.

  • Identifiers (column, table, CTE and alias names) are case sensitive and must be written exactly as the name is stored. Note that MySQL treats column names case insensitively, so this is a difference for accepted queries as well. Index names in index hints are case insensitive.

  • Identifiers can be unquoted or quoted with backticks. A literal backtick inside a quoted identifier is written as two backticks. Double quotes are not supported for identifiers (or strings), so the ANSI_QUOTES SQL mode has no RonSQL equivalent.

  • An unquoted identifier may not coincide with a MySQL keyword, reserved or not, whether or not RonSQL implements it, nor with the RonSQL keyword FRAGS_PER_WORKER. For example, unquoted columns named status, name, date, value or comment are rejected with an error suggesting backtick quotation. Keywords that RonSQL implements, such as year, key or count, give a plain syntax error when used as unquoted identifiers. MySQL only restricts reserved keywords, so many identifiers that are legal unquoted in MySQL require backticks in RonSQL.

  • Identifiers are limited to 64 bytes. Exceeding the limit is an error; RonSQL never truncates identifiers. (The MySQL documentation states these limits in characters, but the actual limit is in bytes; with UTF-8 these differ whenever an identifier contains a character above U+007F.)

  • Aliases after AS are identifiers, so they are also limited to 64 bytes (MySQL allows 256 for column aliases), and a string literal cannot be used as an alias. The AS keyword is required, both for output aliases and for table aliases. Without an alias, the output column name is the expression text itself and is subject to the same 64-byte limit, so a select expression longer than 64 bytes requires a shorter alias via AS.

  • Unquoted identifiers may contain ASCII letters, digits, $ and _, plus characters U+0080--U+FFFF. Quoted identifiers may contain any character in the Basic Multilingual Plane except NUL. Supplementary characters (U+10000 and higher, such as most emoji) are not permitted in identifiers, though they are permitted in string literals.

  • Outside string literals and quoted identifiers, the characters ", #, :, ?, @, [, ], {, }, ~ and \ are illegal. Whitespace is limited to space, tab, carriage return and newline.

String literals#

  • Strings use single quotes only. Double-quoted strings are not supported.

  • A literal apostrophe is written as ''. The MySQL backslash escapes are supported: \0, \', \", \b, \n, \r, \t, \Z and \\. As in MySQL, \% and \_ keep their backslash (for use in LIKE patterns), and a backslash before any other character is dropped. Adjacent string literals separated by whitespace are concatenated, as in MySQL.

  • Character set introducers (such as _utf8mb4'...') and the COLLATE clause are not supported.

  • Hexadecimal literals (0x..., x'...'), bit literals (b'...'), typed literals such as DATE '2024-01-01', and TRUE and FALSE are not supported.

Numeric literals#

  • Integer literals must fit in a signed 64-bit integer (at most 9223372036854775807). Larger literals are an error; MySQL would instead promote them to unsigned or DECIMAL. Negative numbers are written with the unary minus operator, so the smallest 64-bit integer, -9223372036854775808, cannot be written as a literal.

  • Fractional literals must be written with digits on both sides of the decimal point (0.5, not .5 or 5.). They are parsed as double-precision floating point rather than as DECIMAL as in MySQL, except when compared with a DECIMAL column, where the literal text is converted exactly.

  • Scientific notation is not supported; 1e5 is read as an identifier, which typically results in an unknown-column error.

Character encoding#

  • RonSQL requires UTF-8 for the input SQL and produces UTF-8 output. The input is strictly validated: overlong encodings, surrogate code points, code points above U+10FFFF and stray continuation bytes are all rejected with specific errors.

  • NUL bytes are not allowed anywhere in the input. A NUL character inside a string value can be written with the \0 escape sequence.

  • No Unicode normalization is performed. Identifiers must match the stored names byte for byte.

Unsupported clauses and expressions#

Select list#

  • SELECT * is not supported, nor is t.*. Every output column must be written explicitly.

  • A select expression can only be: a (possibly qualified) column name; an aggregate function COUNT, SUM, MIN, MAX or AVG over an arithmetic expression; COUNT(*); AVRO(column) in projection-only queries; a correlated aggregate subquery; or GREATEST/LEAST over scalar CTE outputs. Each may be aliased with AS.

  • Arbitrary expressions are not allowed in the select list: SELECT a + 1, SELECT 1, SELECT UPPER(name) and expressions over aggregate results such as SELECT SUM(a) / SUM(b) are all rejected. Arithmetic inside an aggregate argument is fine: SUM(a * b + 1) is supported.

Features with no support at all#

Each of the following is absent from the RonSQL grammar and produces an error (for most keywords, an “unimplemented keyword” error) if used:

  • DISTINCT, including COUNT(DISTINCT ...).

  • The aggregate functions STD, STDDEV, VARIANCE, VAR_POP, BIT_AND, GROUP_CONCAT and similar.

  • UNION, INTERSECT and EXCEPT.

  • BETWEEN; write x >= a AND x <= b instead.

  • The infix NOT forms x NOT IN (...) and x NOT LIKE ... are syntax errors; prefix NOT works, e.g. NOT (x IN (1, 2)). The ESCAPE clause of LIKE is not supported.

  • REGEXP / RLIKE and SOUNDS LIKE.

  • IS TRUE, IS FALSE and IS UNKNOWN.

  • CAST and CONVERT.

  • Window functions (OVER, ROW_NUMBER, RANK and so on).

  • GROUP BY ... WITH ROLLUP.

  • The null-safe equality operator <=>. Also, the NULL keyword can only appear in IS NULL / IS NOT NULL; a comparison like x = NULL is a syntax error.

  • Derived tables: FROM (SELECT ...) is not supported. Use a WITH clause where the supported CTE forms suffice.

  • INNER JOIN (write JOIN), RIGHT JOIN, FULL JOIN, the CROSS JOIN keyword, NATURAL joins, STRAIGHT_JOIN, LATERAL and JOIN ... USING.

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

  • Nearly all of MySQL’s built-in functions: string functions (CONCAT, SUBSTRING, UPPER, ...), mathematical functions (ABS, ROUND, FLOOR, ...), control-flow functions (IF, IFNULL, COALESCE, NULLIF), and date and time functions (NOW, CURDATE, DATEDIFF, EXTRACT, ...). The only scalar functions RonSQL supports are GREATEST and LEAST, DATE_ADD and DATE_SUB with constant arguments in the WHERE clause, and AVRO in the SELECT list of projection-only queries. A function call with one column argument in the SELECT list gives the error “Unknown function. The only non-aggregate function RonSQL supports in the SELECT list is AVRO(column).”; other function calls give a syntax error or an unimplemented keyword error.

Restricted forms of supported clauses#

The following clauses are supported, but in a more restricted form than in MySQL. The linked chapters describe the supported forms in detail; this section summarizes the restrictions.

  • WHERE: Every term must be explicitly boolean; MySQL’s implicit truth test of a bare column (WHERE flag_col) is rejected. Every comparison must have a column as one operand, so constant comparisons like WHERE 1 = 1 are rejected. Arithmetic is not supported in conditions, neither on columns (WHERE a + 1 > b) nor between constants (WHERE a > 1 + 2); only a unary minus on a literal and the DATE_ADD and DATE_SUB functions are evaluated. The operator ! is not supported in WHERE; use NOT. A literal must have the type of the column it is compared with; see Data types and output. A WHERE clause may have at most 64 top-level AND conjuncts. Comparisons between columns of the same table require identical column types. Comparisons between columns of different tables are only supported in aggregate queries, or when they can be used as index bounds of a join. CASE cannot be used as a WHERE predicate. EXISTS subqueries must be top-level AND conjuncts. GREATEST/LEAST in WHERE can only be compared to a constant with a range operator (<, <=, >, >=) and only over column and integer arguments.

  • LIKE: The left operand must be a stored-table column and the right operand a string literal pattern.

  • DATE_ADD / DATE_SUB: The first argument must be a string literal (or a nested DATE_ADD / DATE_SUB) and the INTERVAL amount a constant; these functions cannot be applied to column values, and they are only available in the WHERE clause. The supported INTERVAL units are MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER and YEAR; the composite units such as DAY_HOUR are not supported.

  • Aggregate arguments: Only integer literals can be used in arithmetic. CASE must have exactly one WHEN and an ELSE branch, the THEN and ELSE values cannot be NULL, and the condition must be a comparison or a chain of comparisons combined only with AND or only with OR. GREATEST and LEAST operands must be columns of integer types, integer literals or nested GREATEST and LEAST, with at least one column.

  • GROUP BY: Column names only (bare or table-qualified). Expressions, aliases and ordinal positions are not supported.

  • ORDER BY: Column names and output aliases only, each optionally with ASC or DESC. Expressions and ordinal positions are not supported; ORDER BY SUM(x) is a syntax error, so alias the aggregate and order by the alias.

  • LIMIT: Only the single-argument form LIMIT n. No OFFSET and no LIMIT offset, count.

  • HAVING: Conditions may combine aggregate function calls and numeric literals using +, -, *, /, comparisons, IS [NOT] NULL, AND, OR and NOT. Aliases of aggregate outputs are not supported (except aliases of SELECT-list subqueries): repeat the aggregate expression instead. Plain table columns are not supported in HAVING; put conditions on them in WHERE. The conditions are evaluated in double precision.

  • Joins: Only JOIN ... ON (inner) and LEFT [OUTER] JOIN ... ON. The ON clause must be a conjunction of plain column equalities (a.col = b.col) linking the joined table to an earlier table, and the joined table must have a primary key, unique index or ordered index on the join columns; any other condition belongs in WHERE. A comma cross-join is only supported for scalar and single-row key-lookup CTEs. In aggregate join queries, aggregates and GROUP BY columns can only use the last joined table and the tables on its path to the first table. WHERE conditions on a left-joined stored table must reject NULL. A pushed join may contain at most 32 internal operations (a unique-index lookup counts as two).

  • Index hints: FORCE / USE / IGNORE INDEX are only honored on the root table of a query or CTE body; a hint on a joined table is an error. Only FORCE INDEX reports index names that do not exist.

  • CTE bodies and subqueries: Bodies must be aggregate queries, single-row key lookups (a single-table SELECT of plain columns whose WHERE binds the full primary key by equality), or last-N bodies (a single-table SELECT of plain columns with ORDER BY and LIMIT); any other non-aggregating CTE body is rejected. A last-N CTE must be the first table of the query that reads it, and when that query joins or groups the rows, the body must select the whole primary key of its table. Stored tables joined onto a CTE that is the first table must be joined by their primary key or a unique key, and join keys must have the same type on both sides, also for CTE outputs. HAVING is not supported in CTE bodies. ORDER BY ... LIMIT inside a CTE body selects the kept group set (top-N; keys must be body outputs, at most 8, no string MIN/MAX keys, no OFFSET); inside a subquery both are rejected rather than silently ignored. The argument of a SUM / MIN / MAX / AVG CTE output must be a plain column (AVG additionally rejects string and temporal arguments). Column-vs-column comparisons in a CTE body’s WHERE clause require two identical-type columns of the body’s first FROM table. Subquery results and correlation columns must be numeric. See the CTE chapter for details.

Data type restrictions#

Data types and output describes in detail which data types can be used where. In summary:

  • BLOB and TEXT columns (all sizes) cannot be used anywhere in a RonSQL query: not as join columns, not in the select list, not in GROUP BY, not as aggregate arguments and not in WHERE comparisons. JSON and spatial (GEOMETRY) columns are stored as BLOBs in RonDB and share these restrictions. Other columns of a table containing such columns can be used normally.

  • BIT columns are not supported in any position.

  • BINARY and VARBINARY columns can be selected in projection-only queries (base64 encoded in JSON output) and decoded with AVRO(), but they cannot be aggregated, grouped or compared in WHERE.

  • ENUM and SET columns are stored by the MySQL server in an internal binary encoding that RonSQL does not decode, so values would not be returned as their labels. Do not use ENUM or SET columns in RonSQL queries.

  • DECIMAL: Aggregates over DECIMAL columns with a nonzero scale are computed in double precision (see the next section). Aggregates over scale-zero DECIMAL columns are computed with 64-bit integers, so values outside the 64-bit range are not supported; inside CTE bodies, aggregating such a column with a declared precision above 18 (signed) or 19 (unsigned) is rejected.

  • Temporal types: DATE, YEAR, DATETIME, TIME and TIMESTAMP columns can be selected and used with MIN / MAX, but SUM and AVG over them are rejected. WHERE comparisons against string literals are supported for DATE, DATETIME and TIMESTAMP columns, but not for TIME or YEAR columns, which also cannot be grouped on.

  • CHAR and VARCHAR columns support MIN and MAX, but not SUM or AVG. Comparing non-ASCII string literals with columns using character sets other than UTF-8 is not supported.

  • Tables using the pre-MySQL-5.6 storage formats for temporal or DECIMAL columns are not supported.

Semantic differences from MySQL#

Even for accepted queries, the following behaviors differ from a MySQL server with default settings.

  • Time zone: RonSQL has no time zone support and operates as if the time zone were UTC. In particular, TIMESTAMP values are displayed in UTC, and TIMESTAMP literals are interpreted as UTC. To get identical results from a MySQL server, run it with SET time_zone = '+00:00'.

  • No implicit type conversion: A literal compared with a column must have the column’s type; for example, an integer column cannot be compared with 1.5 or '1', a DATETIME column cannot be compared with a date without a time part, and an integer literal outside the range of the column’s type is an error rather than a comparison that is always true or false.

  • Integer overflow: SUM over integer columns is computed in 64 bits, and arithmetic in aggregate arguments never wraps around. An overflow fails the query with NDB error 1860, whereas MySQL returns a wider DECIMAL result.

  • Floating-point aggregation order: SUM and AVG over FLOAT or DOUBLE columns are computed as partial sums on many table fragments in parallel and then combined. Floating-point addition is not associative, so the result can differ from MySQL in the last digits, and can even differ slightly between two runs of the same query.

  • Division: The / operator performs floating-point division, whereas MySQL performs exact DECIMAL division. Integer operands of / whose magnitude exceeds 2 to the power of 53 (about 9 * 10^15) produce an overflow error. DIV performs integer division as in MySQL.

  • DECIMAL aggregation: SUM, MIN, MAX and AVG over DECIMAL columns with a nonzero scale, and arithmetic involving such columns, are computed in double precision and can therefore differ from MySQL’s exact decimal arithmetic in the last digits, in particular for values with more than about 15 significant digits. SUM, MIN and MAX directly over a column are displayed with the column’s scale when its precision is at most 15; other results are displayed as double values, e.g. 26777.986559999998 where MySQL displays 26777.986560.

  • AVG: AVG is computed as a double-precision division of SUM by COUNT, and displayed with the same number of decimals as in MySQL. The displayed value matches MySQL except for sums beyond the exact range of double precision.

  • HAVING arithmetic: HAVING conditions are evaluated in double precision, so comparisons involving 64-bit integer aggregate values beyond 2 to the power of 53 can round.

  • Floating-point display: FLOAT values are displayed as in MySQL. DOUBLE values and aggregate results computed in double precision are displayed in the shortest form that represents the value, in fixed-point notation without an exponent, which can differ from MySQL’s formatting. Infinities and NaN are displayed as NULL.

  • Row order: Without ORDER BY, the order of the result rows is unspecified, and it often differs from the order MySQL returns. In particular, groups are not returned in GROUP BY order.

  • Correlated COUNT subqueries: In a WHERE condition comparing a column with a correlated scalar subquery, outer rows without matching inner rows never satisfy the condition, even when the subquery is a COUNT that MySQL evaluates to 0.

  • Identifier length: An over-long identifier is an error; MySQL truncates some identifiers instead.

  • Index hints: USE INDEX and IGNORE INDEX with names of indexes that do not exist are accepted, whereas MySQL reports an error.

  • Committed reads over multiple batches: RonSQL reads committed data without taking locks, matching RonDB’s read committed isolation level and the way a MySQL server reads RonDB tables. A query that scans a large table fetches rows in many batches, so if other transactions commit changes while the query runs, the result can reflect a mix of states that never coexisted at a single point in time. This is the expected behavior at the read committed isolation level.

Operational limits#

  • REST request size: Internal.maxReqSize caps the HTTP request body (default 4 MiB). Larger requests are refused with HTTP status 413.

  • REST response size: Internal.MaxRespSize caps the response body the server will build (default 64 MiB; 0 means unlimited). A query whose result exceeds the cap fails with HTTP status 500 and an error naming the cap and suggesting to narrow the query, add LIMIT, or raise the configured limit.

  • Concurrency: the REST API server executes at most RonSQL.NumThreads queries at once (default 16) and queues at most RonSQL.MaxQueuedRequests more (default 1024); further requests fail with HTTP status 503. There is no query timeout.

  • ORDER BY on a projection-only query that no ordered index can serve buffers the full result for a client-side sort. The sort buffer is capped at 1000000 rows and 256 MB; exceeding the cap is a permanent error suggesting a tighter WHERE clause or an aggregate query.

  • Aggregation: at most 127 GROUP BY columns and 255 aggregate results per query (AVG counts as two), and the aggregate results of one group, including string MIN/MAX values, must fit in 16 KB. The number of groups is limited by the query memory of the data nodes.

  • IN lists: up to 4095 values are executed as primary key lookups or index ranges; longer lists are evaluated as filters.

  • An IN subquery, a correlated scalar subquery and an EXISTS subquery may produce at most 1000 values or rows. At most 128 subqueries can be used in the SELECT list.

  • A WHERE clause is limited to 64 top-level conjuncts, and a condition on CTE outputs to 16 top-level OR branches.

  • A pushed join is limited to 32 internal operations (a unique-index lookup counts as two), including the operations of CTE bodies, with at most 8 key columns per join and at most 16 columns passed from other tables to the aggregating table. A statement can define fewer than 64 CTEs.

  • Identifiers and aliases are limited to 64 bytes; integer literals to the signed 64-bit range.

When a query is rejected#

RonSQL rejects queries with an error describing the unsupported construct, usually pointing at the offending position in the statement, together with an error class and HTTP status as described in the RonSQL overview. Common ways to bring a query into the supported subset:

  • Remove comments, DISTINCT, and expressions from the select list; alias aggregates with AS and reference the aliases in ORDER BY, but repeat the aggregate expressions in HAVING.

  • Rewrite predicates into the supported forms: replace BETWEEN with two comparisons, compute constant arithmetic yourself, move arithmetic off the column side of a comparison, write literals of the column’s type, replace x NOT IN (...) with NOT (x IN (...)), and move non-equality join conditions from ON into WHERE.

  • Backtick-quote identifiers that collide with MySQL keywords.

  • Make sure join conditions are equalities on indexed columns of identical types, and give CTE bodies an aggregate, make them single-row primary key lookups, or select the last N rows with ORDER BY and LIMIT.

  • If the query cannot be expressed in the RonSQL subset, run it through a MySQL server instead: the same tables are fully accessible with complete MySQL semantics, typically at a higher cost for large analytical scans.