List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 27 2013 7:38pm
Subject:Re: MySQL Descending ORDER issue
View as plain text  
In the last episode (Dec 27), Russ Lavoie said:
> 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 (credential_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 (credential_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?

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).

-- 
	Dan Nelson
	dnelson@stripped
Thread
MySQL Descending ORDER issueRuss Lavoie27 Dec
  • Re: MySQL Descending ORDER issueDan Nelson27 Dec
RE: MySQL Descending ORDER issueVikas Shukla27 Dec
  • RE: MySQL Descending ORDER issueRuss Lavoie27 Dec