SingleStore Managed Service

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.

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)