Unsupported MySQL Features
On this page
SingleStore intentionally does not support all of the features of MySQL.
level Engine Variable - how SingleStore behaves when it encounters unsupported functionality is controlled via the
level The variable has two settings:
Warnings (default) - Permits SQL statements that aren’t supported, but whose unsupported features can safely be ignored.
Warnings will be issued when such queries are used. Use this mode when porting an existing application to SingleStore and to avoid having to change the application to not use unsupported features.
Errors - SQL statements with unsupported features will be rejected as errors.
This is the most strict warn level. Use this level when you are developing a new application on SingleStore and want to only use features fully supported by SingleStore.
warn_variable is set just like other global engine variables:
SET GLOBAL warn_.
For more information on setting engine variables, see the Engine Variables overview.
SingleStore does not fully support user-defined variables. See User-Defined Variables.
Triggers (other than the TIMESTAMP type)
Foreign keys and referential integrity
Prepared statements - SingleStore does not fully support server-side prepared statements.
See Using Prepared Statements.
SingleStore geospatial is incompatible with MySQL geospatial.
The comparison operator
LIKEdoes not support the
However, you can use the backslash character
\as an escape character in
get_and related functions (
SingleStore differs from MySQL query behavior in a few ways, mostly in cases where MySQL behavior is officially undefined, and the observed behavior depends on the details of implementation.
No implicit ordering of results by primary key: MySQL (and other single-machine databases) may return data in order of the primary key of the table, even when the query does not specify an ordering.
In SingleStore, a
SELECT *query without an order clause may return data in any order, and the ordering may even be different between runs of the same query.
This is because partitions stream their results to the aggregator in parallel, and the results are forwarded to the client as they arrive. To retrieve rows in order, you must specify an explicit order clause.SELECT * FROM messages;
+----+-------+ | id | msg | +----+-------+ | 2 | ohai! | | 3 | kthx | | 1 | yo | +----+-------+ 3 rows in set (0.00 sec)SELECT * FROM messages ORDER BY id;
+----+-------+ | id | msg | +----+-------+ | 1 | yo | | 2 | ohai! | | 3 | kthx | +----+-------+ 3 rows in set (0.00 sec)
ORDER BYbehavior inconsistent with MySQL: SingleStore parses UNION ALL followed by ORDER BY commands differently from MySQL.
In SingleStore, the following query is valid. In MySQL, it is invalid.SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY table2.col1
Last modified: January 20, 2023