SQL dialect

SQLite is the default. The dialect determines the result where the two interpret a statement differently. For example, SELECT 7/2 returns 3 in SQLite and 3.5000 in MySQL.

Where the dialect is set

There are three places to set it. The most specific one wins.

The names sqlite and sqlite3 both mean SQLite, and mysql and mariadb both mean MySQL, in any case. SET sql_dialect also takes the numbers 0 (MySQL) and 1 (SQLite). SELECT @@sql_dialect returns the dialect in force, as sqlite or mysql.

A worked example

Set the app's dialect to MySQL.

curl -s -X POST $SUBSTRIO_URL/v1/_settings -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" -d '{"key":"sql.dialect","value":"mysql"}'

Statements using the app default now run in MySQL. This statement returns 3.5000.

curl -s -X POST $SUBSTRIO_URL/v1/_sql -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" -d '{"sql":"SELECT 7/2"}'

An individual call can still request SQLite. This statement returns 3.

curl -s -X POST $SUBSTRIO_URL/v1/_sql -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" -d '{"sql":"SELECT 7/2","dialect":"sqlite"}'

The next call without a dialect runs in MySQL again. A batch takes dialect once, beside statements, and every statement of the batch runs in it.

A next_cursor belongs to the dialect used for its first page. Send the same dialect with the cursor, or omit it if the first page did not specify one.

Text comparison and LIKE

A column created in SQLite uses BINARY collation unless another is specified. BINARY compares bytes, so 'a' and 'A' are different values and trailing spaces count. COLLATE NOCASE folds ASCII letters, and COLLATE RTRIM ignores trailing spaces. Any other collation name is refused with "no such collation sequence". A column keeps its collation regardless of the dialect used to read it later. SHOW CREATE TABLE prints a BINARY column with no collation, because BINARY is the default in SQLite and MySQL reads COLLATE BINARY as its binary character set. A NOCASE or RTRIM column is printed as, for example, varchar(40) COLLATE NOCASE, with no character set, and MySQL accepts those two names as SQLite writes them. The printed text makes the same column again in either dialect, and the column is still text. Run in MySQL, a BINARY column made this way takes MySQL's default collation.

Two text values without a specified collation also compare as BINARY in SQLite, so SELECT 'a' = 'A' returns 0. An explicit COLLATE determines the comparison, with the left operand taking precedence. Computed text, such as lower(name) or a || b, is sorted, grouped, deduplicated by DISTINCT, and ranked by MIN and MAX as BINARY too, unless it names a collation of its own. In MySQL, text compares case-insensitively with trailing spaces ignored, as MariaDB's default collation does.

LIKE in SQLite folds ASCII letters whatever the column's collation is. PRAGMA case_sensitive_like = ON turns the folding off. A pattern with no ESCAPE clause has no escape character, so a backslash matches itself. In MySQL a backslash escapes the next character unless the session sets NO_BACKSLASH_ESCAPES.

Comparing text and numbers

In SQLite, text and numbers are different kinds of value. SELECT '1' = 1 returns 0, and a number always sorts before text, so SELECT 1 < 'a' returns 1. A blob sorts after text.

A column changes the other side of a comparison to suit its type, as SQLite does. A TEXT column turns a number into text, so t = 10 finds the text '10', while t = 10.0 looks for the text '10.0'. An INTEGER, REAL, or NUMERIC column turns text that holds a whole number into that number, so i = ' 10 ' finds 10. Text that is not a number stays text, so i < 'abc' is true for every number. IN uses the type of its left side alone, so 10 IN (t) compares 10 with the text in t and answers 0.

The same rules hold between two columns and for expressions. A TEXT column compared with an INTEGER column is read as a number. A TEXT column compared with arithmetic turns the result into text, so t = abs(-10.0) looks for the text '10.0'. A function or a literal has no type of its own to give, so upper(t) = 10 compares text with a number and answers 0. BETWEEN and row values apply the rules to each pair they compare.

In MySQL, a string compared with a number is read as a number, so '1' = 1 returns 1.

Functions that share a name

A few functions have the same name in SQLite and MariaDB but return different results. Each statement uses the function for its dialect.

trim(x, y), ltrim(x, y), and rtrim(x, y) remove every character of y from the ends of x, as SQLite does, in both dialects.

Bit operators

In SQLite, &, |, <<, >>, and ~ work on signed 64-bit integers. ~5 is -6, and -8 >> 1 is -4. A negative shift count shifts the other way, and a count of 64 or more leaves 0, or -1 for a negative value shifted right. Each operand is read as an integer the way SQLite reads one, so '12abc' & 15 is 12 and 7.9 | 0 is 7. In MySQL the same operators work on unsigned 64-bit integers, so ~5 is 18446744073709551610.

Error texts

A failed SQLite statement returns SQLite's error text with the same error code used in MySQL.

Statements that SQLite takes

Several statements that MariaDB refuses run in SQLite and return SQLite results.

• A window with no ORDER BY ranks every row of its partition as one group of peers, so rank() OVER () is 1 on every row.
• A RANGE frame with an offset over a text key, a blob key, or a NULL key frames the current row's peers. A numeric key measures the offset exactly, with no rounding.
• A frame offset may be any constant expression, such as 1+1 or '2'. A ROWS or GROUPS offset must be a non-negative integer, and 1.0 counts as one. A RANGE offset must be a non-negative number. A column or a function call in an offset is refused with "frame starting offset must be a non-negative integer", as SQLite refuses it.
• A join may be spelled with every form SQLite knows, such as NATURAL INNER JOIN and NATURAL LEFT OUTER JOIN. A NATURAL join with ON or USING is refused with "a NATURAL join may not have an ON or USING clause".
• A table or an alias may be named twice in one FROM clause. A column that becomes ambiguous is refused where it is named.
• A UNION, INTERSECT, or EXCEPT with no ORDER BY answers its rows in key order, as SQLite does. Each column is ascending, with NULL first, then numbers, text, and blobs. A UNION ALL after them appends its rows at the end.
• The compound operators run left to right, and INTERSECT has no higher precedence than UNION. So a UNION b INTERSECT c is (a UNION b) INTERSECT c.
• The ORDER BY of a UNION, INTERSECT, or EXCEPT may name a column with its table, such as ORDER BY a.x. The rows are ordered by the output column of that name. A name that matches no output column is refused with "1st ORDER BY term does not match any column in the result set".
• Every integer type holds 64 bits. A SMALLINT column stores 100000, and an INT column stores 9223372036854775807.
• A table's one primary key column declared exactly INTEGER is its row number, as SQLite's rowid alias is. An INSERT that leaves it out, or gives it NULL, is given the next number, so CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT) takes INSERT INTO t (name) VALUES ('ada'). The numbers run on after a DELETE and are never given twice, as they are with SQLite's AUTOINCREMENT. INT PRIMARY KEY, BIGINT PRIMARY KEY, and a key of two columns are not row numbers.
• A generated number, from a row number or a MySQL AUTO_INCREMENT column, is always above every stored value of the column. That holds after an UPDATE that raises a value, after an explicit value given as text, and after a restart. The table's stored values are read once, by the first generated number after the server starts. Every insert after that costs the same, however many rows the table holds.
• UPDATE t SET a = b, b = a reads every value from the row as it was before the update, so the two columns trade values.
• LIMIT takes any expression. A negative limit means there is no limit, so LIMIT -1 OFFSET 1 skips one row and returns the rest.
• A trigger body may hold a bare SELECT. It runs, and its rows are dropped.
• RAISE(ABORT, 'message'), RAISE(FAIL, 'message'), and RAISE(ROLLBACK, 'message') in a trigger body fail the statement with that message. Each one undoes the statement's changes, as ABORT does. The guard SELECT RAISE(ABORT, 'n too big') WHERE NEW.n > 10 works as it does in SQLite. RAISE outside a trigger is refused with "RAISE() may only be used within a trigger-program". RAISE(IGNORE) is refused for now.
• INSERT stores each value by its column's affinity. '12' in an INTEGER column is stored as the integer 12, 'abc' stays text, 3 in a REAL column is stored as 3.0, and 12 in a TEXT column is stored as the text '12'.

Foreign keys

A session in SQLite starts with foreign keys off, as SQLite does. Rows are stored even when their parent row does not exist. PRAGMA foreign_keys = ON turns the checks on for the session, and PRAGMA foreign_keys reads the setting back. A session in MySQL starts with the checks on, as MariaDB does, and SET foreign_key_checks = 0 turns them off.

In SQLite, a foreign key is checked when the statement ends, as SQLite checks it. A row may name a parent row that a later row of the same statement adds, so INSERT INTO t VALUES (2, 1), (1, NULL) works on a table that references itself. A key declared DEFERRABLE INITIALLY DEFERRED is checked at COMMIT instead. PRAGMA defer_foreign_keys = ON defers every key until the transaction ends, and the pragma then turns itself off. A COMMIT that leaves a deferred key broken fails with "FOREIGN KEY constraint failed", and the transaction stays open, so you can fix the rows or roll back. Outside a transaction, a deferred key is checked when the statement ends. ON DELETE RESTRICT and ON UPDATE RESTRICT still refuse at the row, as SQLite does.

Setting the dialect with SET sql_dialect starts the new dialect's default, so a session that moves to MySQL has its checks on. The dialect field of one call changes nothing here, and the call runs under the session's setting.

Functions and the MySQL wire

A function's sql() call runs in the app's dialect. A SET sql_dialect statement inside the function moves it for the statements after it.

A MySQL wire connection starts in the app's dialect. A client that expects MySQL's answers sends SET sql_dialect = 'mysql' once after it connects. A standalone substrio_mysql_daemon starts its connections in the dialect that SUBSTRIO_MYSQL_DIALECT names, sqlite or mysql. Any other value stops the daemon at start.

The daemon builds one default session when it starts, in that dialect. Every connection runs in the default session until it changes a setting of its own. A SET sql_dialect on one connection moves that connection alone, and every other connection keeps the dialect it had.

The daemon listens on 127.0.0.1, so only clients on the same machine reach it. SUBSTRIO_MYSQL_BIND names another IPv4 address to listen on, such as 0.0.0.0 for every interface. A value that is not an IPv4 address stops the daemon at start, before it opens the database.

What it costs

The dialect is read when a statement is compiled. There is no per-row or per-operator dialect cost at run time. Each dialect has a separate cached plan for a statement, so callers using different dialects can both use the cache. Changing the app setting recompiles each cached statement once, on its next run.

Refusals