Skip to content

RonSQL Data Types and Output#

This chapter describes which column data types RonSQL supports in each part of a query, how literals are compared with columns, which types and precision aggregate results have, and how values are formatted in each output format. It also describes the AVRO() function, which decodes Avro-encoded binary columns. For the query forms themselves, see Single-table queries, Join queries and CTEs and subqueries.

Data types by role#

The following table shows where columns of each data type can be used: as a plain output column of a projection-only query, as a GROUP BY column, as the argument of SUM and AVG, as the argument of MIN, MAX and COUNT, and in a WHERE comparison with a literal (which includes index bounds and primary key lookups).

Type Output GROUP BY SUM, AVG MIN, MAX, COUNT WHERE
Integer types Yes Yes Yes Yes Yes
FLOAT, DOUBLE Yes Yes Yes Yes Yes
DECIMAL Yes Yes Yes Yes Yes
CHAR, VARCHAR Yes Yes No Yes Yes
BINARY, VARBINARY Yes No No No No
DATE Yes Yes No Yes Yes
DATETIME, TIMESTAMP Yes Yes No Yes Yes
TIME, YEAR Yes No No Yes No
BIT No No No No No
BLOB, TEXT, JSON No No No No No

Notes:

  • The integer types are TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT, signed and unsigned. The temporal types include fractional seconds.

  • Nullable columns are supported in all roles. NULL values are skipped by aggregate functions, form their own group in GROUP BY, and can be tested with IS NULL and IS NOT NULL for columns of any supported type.

  • COUNT(*) can always be used. COUNT(col) accepts the same column types as MIN and MAX.

  • MIN and MAX over CHAR and VARCHAR columns compare values with the column’s collation. The result of one group, including all its string results, must fit in 16 KB.

  • ORDER BY on an aggregate query names GROUP BY columns and output aliases, so the GROUP BY column restrictions apply. In projection-only queries, ORDER BY can use columns of all types except BLOB and TEXT.

  • LIKE can be used on CHAR and VARCHAR columns.

  • Join columns can be of any type except BLOB and TEXT, but the two columns of a join condition must have identical type, length, precision, scale and character set; see Join queries.

  • BINARY and VARBINARY columns can also be decoded with the AVRO() function.

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

  • JSON and spatial (GEOMETRY) columns are stored as BLOBs in RonDB and share the BLOB restrictions. Other columns of a table containing such columns can be used normally.

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

Using a column of an unsupported type is reported as an error. Some cases are only detected while the result is produced: a GROUP BY column of an unsupported type fails with RonSQL feature not implemented: Print GROUP BY column of type ... when the first group is printed, and outputting a BIT, BLOB or TEXT column fails with Unsupported column type (N) in pass-through result., where N is the internal type code. An empty result does not trigger these errors.

Comparing columns with literals#

RonSQL performs no implicit type conversion in WHERE comparisons: the literal must match the type of the column it is compared with. The same rules apply to index bounds and primary key lookups, and to the values of an IN list.

  • Integer columns can only be compared with integer literals, and the literal must be within the range of the column’s type. For example, comparing a TINYINT column with 200 is rejected with Integer type column compared to an integer literal out of range., and comparing an INT column with 1.5 or '5' is rejected with Integer type column compared to an incompatible value. Only integer literals are supported. MySQL would evaluate these comparisons. Literals compared with a BIGINT UNSIGNED column can be at most 9223372036854775807.

  • FLOAT and DOUBLE columns can be compared with integer and fractional literals.

  • DECIMAL columns can be compared with integer and fractional literals. A fractional literal is converted exactly from its text, not through floating point, and must be representable in the column’s precision and scale.

  • CHAR and VARCHAR columns can only be compared with string literals. The literal may not be longer, in bytes, than the column can hold. Comparisons use the column’s collation, so for example a case-insensitive collation compares case-insensitively. String literals are compared as UTF-8 bytes; comparing literals containing non-ASCII characters with columns that use other character sets (such as latin1) is not supported.

  • DATE, DATETIME and TIMESTAMP columns are compared with string literals in the MySQL date and time formats, or with DATE_ADD and DATE_SUB expressions over such literals. The literal must match the type: a date without a time part for a DATE column, and a date with a time part for DATETIME and TIMESTAMP columns. So d >= '2025-01-01' works for a DATE column, while a DATETIME column needs dt >= '2025-01-01 00:00:00'. Fractional seconds beyond the column’s precision are truncated. TIMESTAMP literals are interpreted as UTC and must lie between '1970-01-01 00:00:01' and '2038-01-19 03:14:07'.

  • Other types, including TIME, YEAR, BIT, BINARY, VARBINARY, BLOB and TEXT, cannot be compared. The error message is Unsupported column type in comparison condition. ...

Two columns of the same table can be compared with each other when they have identical types (for example WHERE l_quantity < l_price with two INT columns); the rules for comparing columns of different tables are described in Join queries.

Arithmetic and aggregate results#

Arithmetic in aggregate arguments#

Aggregate arguments are evaluated inside the data nodes using 64-bit integers and double-precision floating point:

  • Integer columns and DECIMAL columns with scale 0 are loaded as 64-bit integers. FLOAT, DOUBLE and DECIMAL columns with a nonzero scale are loaded as double-precision floating point values.

  • +, - and * produce a floating point result if either operand is floating point, and a 64-bit integer otherwise (unsigned if either operand is unsigned).

  • / always produces a floating point result, whereas MySQL computes an exact DECIMAL result. Integer operands of / whose magnitude exceeds 2 to the power of 53 (about 9 * 10^15) cause an overflow error.

  • DIV performs integer division, truncating toward zero. % takes the sign of the dividend, as in MySQL.

  • Division by zero (/, DIV or %) produces NULL, as in MySQL. Any NULL operand makes the result NULL.

  • Integer overflow never wraps around. An overflow, including a negative result of an unsigned subtraction, fails the query with NDB error 1860 (arithmetic operation results overflow), returned with HTTP status 400 and the header X-RonSQL-NDB-Error: 1860.

Constant subexpressions using +, -, *, DIV and % are computed when the query is prepared; an overflow or a DIV or % by the constant zero in this step is reported as an error.

Aggregate result types#

  • COUNT returns a 64-bit integer. It is 0 for an empty input or when all values are NULL.

  • SUM over integer columns and scale-0 DECIMAL columns is computed exactly in 64 bits. A sum that overflows the 64-bit range fails the query with NDB error 1860, whereas MySQL would return a wider DECIMAL. Since partial sums are computed on many fragments in parallel, an overflow can be detected even if the final sum would fit.

  • SUM over FLOAT, DOUBLE, and DECIMAL columns with a nonzero scale, as well as over expressions involving them, is computed in double precision.

  • MIN and MAX return a value of the argument’s type, computed like SUM for numeric arguments. String results are compared with the column’s collation, and temporal results chronologically.

  • AVG is computed as the SUM divided by the COUNT in double precision, after all partial results are combined.

  • SUM, MIN, MAX and AVG return NULL for an empty input or when all values are NULL.

  • A query with aggregates and no GROUP BY returns exactly one row, also for an empty input. A query with GROUP BY returns no rows for an empty input.

The use of double precision means that SUM, AVG, MIN and MAX over DECIMAL columns with a nonzero scale, and arithmetic with /, can differ from MySQL’s exact DECIMAL arithmetic in the least significant digits, in particular for values with more than about 15 significant digits. Also, since floating point addition is not associative and the order in which partial results are combined varies, the last digits of floating point sums can differ between two executions of the same query.

Output formatting#

Values#

Values are formatted as follows. Where MySQL formatting is mentioned, the output is the same as that of the mysql command line client.

  • Integers are printed as decimal numbers.

  • FLOAT values are printed as MySQL prints them.

  • DOUBLE values, and all aggregate results computed in double precision, are printed with the shortest number of digits that represents the value exactly, in fixed-point notation (never with an exponent). This can differ from MySQL’s formatting, e.g. 26777.986559999998 where MySQL prints the exact DECIMAL result 26777.986560. Infinity and NaN are printed as NULL.

  • DECIMAL column values are printed exactly, with the column’s scale, e.g. 12.50. SUM, MIN and MAX directly over a DECIMAL column with a nonzero scale and a precision of at most 15 are printed with the column’s scale, as in MySQL; for larger precisions the double result is printed as described above.

  • AVG results are printed like in MySQL: with 4 decimals when the argument is an integer column or an expression, with the column’s scale plus 4 decimals (at most 30) for a DECIMAL column, and as a double for FLOAT and DOUBLE columns, e.g. 2.8700 for the average of an INT column.

  • CHAR and VARCHAR values are converted to UTF-8 from the column’s character set. Trailing spaces are removed from CHAR values, as in MySQL, but kept in VARCHAR values.

  • BINARY and VARBINARY values are printed as base64 strings (RFC 4648, with padding and without line breaks) in the JSON formats, the same convention as the other RonDB REST API endpoints, and as raw bytes in the text formats. A BINARY(n) value is printed with its full padded length.

  • Temporal values are printed as YYYY-MM-DD (DATE), YYYY-MM-DD HH:MM:SS (DATETIME and TIMESTAMP), HH:MM:SS (TIME, with a sign for negative values) and YYYY (YEAR). Values with fractional seconds are printed with as many fractional digits as the column declares. TIMESTAMP values are printed in UTC.

  • NULL is printed as null in the JSON formats and as NULL in the text formats.

In the JSON formats, numbers (including DECIMAL values) are JSON numbers, while strings, binary values and temporal values (including YEAR) are JSON strings. Note that very large BIGINT and DECIMAL values may lose precision in JSON parsers that represent all numbers as double-precision floating point.

String escaping#

In the JSON format, strings are escaped as JSON requires: ", \ and the control characters are escaped, using \b, \f, \n, \r, \t and \u00XX, and all other characters are written as UTF-8. The characters U+007F to U+009F are also written as \u00XX escapes. The JSON_ASCII format additionally escapes every character from U+00A0 upwards as \uXXXX (with surrogate pairs for characters above U+FFFF), so that the output consists of ASCII characters only.

In the text formats, values are escaped like the mysql client does in batch mode: NUL, tab, newline and backslash are written as \0, \t, \n and \\; all other bytes are written as they are.

Invalid byte sequences in string columns are replaced by the Unicode replacement character U+FFFD.

Output column names#

Each output column is named by:

  • its alias, if it has one (SUM(x) AS total);

  • otherwise, for a plain column, the column name without any table qualifier (o.o_id is named o_id);

  • otherwise, the text of the select expression exactly as written in the query, including case, spaces and backticks, for example max(sint16) or COUNT(*).

Output names are limited to 64 bytes; a longer unaliased expression must be given an alias. Duplicate output names are not renamed, so give each output a unique alias when the result is consumed as JSON.

Row order and empty results#

The order of the result rows is unspecified unless the query has an ORDER BY clause. In particular, the groups of an aggregate query are not returned in GROUP BY order, and numeric groups are not necessarily returned in numeric order.

An empty result, including one produced by LIMIT 0, is an empty array [] in the JSON formats, and completely empty output (without a header row) in the text formats.

The AVRO() function#

Hopsworks stores complex features, such as arrays and structs, Avro-encoded in BINARY and VARBINARY columns. Selecting such a column directly returns the raw bytes (base64 encoded in JSON). AVRO(col) instead returns the decoded value as JSON — the same value that the feature store endpoints of the REST API server return:

SELECT id1, AVRO(`array`) AS a, AVRO(`struct`) AS s
FROM sample_complex_type_1
WHERE id1 = 6;

returns

{"data":
[{"id1":6,"a":[91,65],"s":{"int1":36,"int2":17}}]
}

(The column names array and struct are MySQL keywords and must therefore be quoted with backticks.)

The rules for AVRO() are:

  • AVRO(col) or AVRO(t.col) can be used as an item of the SELECT list of a projection-only query, optionally with an alias. It cannot be used in aggregate queries, in WHERE, GROUP BY or ORDER BY, inside CTE bodies or subqueries, or on the outputs of a CTE. The function name is case insensitive, and avro can still be used as a column name.

  • The argument must be a BINARY or VARBINARY column. It can belong to any table of a join, including a table joined with LEFT JOIN; a NULL value, including one produced by a LEFT JOIN without a match, returns null.

  • The Avro schema is taken from the Hopsworks feature store metadata that the REST API server caches. The database of the request is the feature store, the table is the feature group table (<feature group>_<version>), and the column is the feature. The feature group must be served by a feature view. AVRO() is therefore only available through the REST API server, not through ronsql_cli.

  • In the JSON format, the decoded value is embedded as a JSON value (an array, object, number, string or null), not as a string. In the JSON_ASCII format, non-ASCII characters are escaped. In the text formats, the JSON text of the value is printed, with the escaping described above.

  • Without an alias, the output column name is the expression text, e.g. AVRO(`struct`).

  • EXPLAIN shows AVRO() outputs as CLIENT-SIDE AVRO DECODING.

The following errors can occur:

  • AVRO() is only supported in queries without aggregation.

  • AVRO() is only supported in the SELECT list of the outer query, not in a CTE body or subquery.

  • AVRO() of a CTE output is not supported.

  • AVRO() requires a BINARY or VARBINARY column.

  • AVRO() is only supported by the RDRS /ronsql endpoint.

  • A message stating that the cached feature store metadata has no Avro schema for the column, when no feature view serves the feature.

  • A message stating that a value is not Avro data of the feature’s schema, when a stored value cannot be decoded.

Any other function call in the SELECT list, such as UPPER(name), is rejected with Unknown function. The only non-aggregate function RonSQL supports in the SELECT list is AVRO(column).