On Mon, Jan 19, 2009 at 4:14 AM, b <my...@logi.ca> wrote: > mos wrote: >> >> I'm using MySQL 5.1.30 (WinXP) and user defined variables don't seem to be >> working properly if the Select statement is sorted. >> >> Here is a simple example: >> >> CREATE TABLE `tmp` ( >> `Purch_Date` date DEFAULT NULL, >> `Product` char(10) DEFAULT NULL >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 >> >> Here are the values that I entered in this order: >> Purch_Date Product >> 2007-01-01 A >> 2007-01-04 B >> 2008-05-04 B >> 2008-12-04 B >> 2001-01-04 D >> 2001-01-04 C >> >> Now here is the Select statement that is failing: >> >> set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date >> >> OUTPUT: >> @num:=...@num+1 Purch_Date >> 5 2001-01-04 >> 1 2007-01-01 >> 2 2007-01-04 >> 3 2008-05-04 >> 4 2008-12-04 >> >> The earliest date "2001-01-04" should have 1 and not 5 as the first column >> in the results. >> Why isn't the column "@num:=...@num+1" sorted in ascending order since it >> was grouped by purch_date? Even if I explicitly tell it to sort by >> purch_date: >> >> set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date >> order by purch_date; >> >> I still get the same results. This has worked fine in MySQL 5.0.1 but is >> giving me these strange results in MySQL 5.1.30. >> >> Am I doing something wrong here? >> >> TIA >> Mike >> >> > > I suspect that what's happening is MySQL isn't selecting the rows in order > but displaying them ordered by purch_date. That is, the other column value > is being assigned to its row *before* the ordering occurs. > > That's just a guess though, really. I might be way off here. > > You could probably test this by selecting and ordering purch_date in a > sub-query and assigning your other column to the results of that.
That's exactly the problem and that's one of the solutions, yes. Mike: it seems this upgrade has bitten you pretty hard. For what it's worth, I generally recommend logging all queries the app does, getting a test set of data, playing the queries against the data and verifying that they all return the same results. You can do this with mysql's own test framework and with mk-table-checksum of the data after playing the queries. Do this once on the old version and once on the new version and compare. I usually suggest using a Percona release to log all queries with the slow log, but Proxy or packet sniffing or just in-application logging will do as well. Upgrades across a major version are not to be taken lightly, and you especially shouldn't change both the .cnf file and the server version at the same time. What other changes have you not found yet, but which will cause troubles for your application? Baron -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
