The UNION and UNION ALL operators may order the rows in the final result set in an unspecified order. For example, it may place rows from the second result set before/after the row from the first result set.
I have encountered a situation where a query with UNION and UNION ALL resulted in a stable different row order...
UNION is often used for something like this:
SELECT * FROM (
SELECT id FROM t WHERE name = 'user_input'
UNION
SELECT id FROM t WHERE name = 'default_value'
)
LIMIT 1
It is important that if a record with 'user_input' is found, it should be the first record in the result.
In such a case, add an ORDER BY of this form to the query:
ORDER BY name = 'default_value'