Unsupported MySQL Features

Unsupported Feature List

• warn_level Engine Variable - how SingleStore behaves when it encounters unsupported functionality is controlled via the warn_level engine variable. 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.

  The warn_level variable is set just like other global engine variables: SET GLOBAL warn_level= "errors";.

• getProcedures() and getProcedureColumns() MySQL built-ins

• User-Defined Variables. 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 LIKE does not support the ESCAPE clause. However, you can use the backslash character \ as an escape character in LIKE patterns.

• get_lock() and related functions (release_lock(), etc.).

Behavior Differences

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.

  SQL

  Copy

  SELECT * FROM messages;

  +----+-------+
  | id | msg   |
  +----+-------+
  |  2 | ohai! |
  |  3 | kthx  |
  |  1 | yo    |
  +----+-------+
  3 rows in set (0.00 sec)

  SQL

  Copy

  SELECT * FROM messages ORDER BY id;

  +----+-------+
  | id | msg   |
  +----+-------+
  |  1 | yo    |
  |  2 | ohai! |
  |  3 | kthx  |
  +----+-------+
  3 rows in set (0.00 sec)

• UNION ALL with ORDER BY behavior 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.

  SQL

  Copy

  SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY table2.col1