MySQL Descending ORDER issue

Question

I am currently upgrading from mysql 5.1.72 -> mysql 5.6 and the migration and upgrade is sound (In a QA ENV). Queries work etc. However, when I run a query similar to "SELECT table.* FROM table WHERE (some_id IN (13528, 14906, 38845)) ORDER BY date DESC LIMIT 1;" on 5.1.72 and on 5.6.15 I get back different data. The date for all of the data are exactly the same to the second.

However, if I run "SELECT table.* FROM table WHERE (some_id IN (13528, 14906, 38845)) ORDER BY date LIMIT 1;" it comes back with the exact same data that is expected. Is this a bug? Or am I missing here?

Answer 1

It's probably a difference in the optimizer or the query engine between 5.1 and 5.6. Since you only specified "ORDER BY date", mysql is free to return rows with the same date value in any order, even on consecutive runs within the same session if it wants to. If you need a stable sort every time, you'll need to add another column to your ORDER BY clause. id might be a good choice (since it's your primary key for that table), or credential_id (since you're using that in your WHERE clause already).