I use MySQL 5.7 and the following query (derived from drupal 7):
select distinct node.nid, node.title, node.changed, users_node.name from node left join users users_node on node.uid = users_node.uid order by node.nid desc limit 4;
In some servers the results look like this which seems correct:
+-----+----------------------------+------------+------+ | nid | title | changed | name | +-----+----------------------------+------------+------+ | 521 | ... | 1501674634 | xx | | 520 | ... | 1501674634 | xx | | 519 | ... | 1501674634 | xx | | 517 | ... | 1501674634 | xx | +-----+----------------------------+------------+------+
But in some other servers I get the opposite nid
order from what I expect:
+-----+----------------------------+------------+------+ | nid | title | changed | name | +-----+----------------------------+------------+------+ | 1 | ... | 1501674634 | xx | | 2 | ... | 1501674634 | xx | | 3 | ... | 1501674634 | xx | | 4 | ... | 1501674634 | xx | +-----+----------------------------+------------+------+
How can this be explained? Is it the case that the final order of the results cannot be guaranteed when using the distinct keyword?