On Mon, Sep 20, 2010 at 7:36 AM, Shawn Green (MySQL) <shawn.l.gr...@oracle.com> wrote: > Hello Hank, > > On 9/18/2010 9:35 PM, Hank wrote: >> >> I have the following pseudo code running on mysql 4.x: >> >> set @cnt:=0; >> insert ignore into dest_table >> select t1.field1, t1.field2, t1.field3, t2.field1, >> t1.field3, t2.ts, @cnt:=...@cnt+1 >> from table1 as t1 left join table2 as t2 using (field1, field2) >> order by t2.ts; >> >> This works perfectly to sequentially number the result set rows >> inserted into dest_table in order of t2.ts (a timestamp field). >> >> In my upgrade to mysql 5.1.14-community, the numbers returned by @cnt >> are not in order... they trend upward from 0 to the number of records >> inserted, but they're far from "in order"... so somehow mysql is >> inserting the rows in some strange order. >> >> How can I fix this so it works in both mysql 4.x and 5.x? >> > > I am not sure you can fix this to work properly in a single statement for > 5.1.14. The order of operations appears out of sequence to what you need. > > When executing an SQL statement, there are several stages to the processing. > 1)gather rows and filter on matches (FROM ... and JOIN ...) > 2)filter the results of 1 (WHERE) > 3)apply any GROUP BY > 4)filter the results of 3 (HAVING) > 5)sort the results (ORDER BY) > 6)window the results (LIMIT) > > It appears that computation of your @cnt variable is performed BEFORE the > ORDER BY and not after the ORDER BY. This is completely in line with how > the SQL Standard says a query should operate. What if you wanted to ORDER > BY on the @cnt column and we did not compute it until after that stage of > processing? That would break standards compatibility. To make this work the > way you want, you need to create a temporary table with the results of your > query sorted the way you want them. Then, query that temporary table and add > your column of sequential numbers to the first results. > > > There may possibly be a saving grace for you, though. 5.1.14 was a very > early release in the 5.1 series. It is possible that someone else noticed > the same problem and a later version may be operating as you want. We are > currently releasing 5.1.50 which contains 34 rounds of bugfixes above and > beyond your current 5.1.14. I suggest you upgrade and try again. Even if > this does not fix the behavior to act as you want, the upgrade will at least > remove your exposure to hundreds of identified bugs. > > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. > Office: Blountville, TN >
Hello Shawn, Many thanks for your detailed reply. This is a test/dev box which I do plan to upgrade to the newest mysql version (5.1.x or maybe 5.5.x) in a couple of weeks. But I found a solution to my problem... I'm not setting the @cnt value in the <insert...select> statement, but I added a second statement right after it to do this, which works as I intended: set @cnt:=0; update dest_table set hc...@cnt:=...@cnt+1 <where clause> order by ts; This works for both mysql 4.x and 5.1.15. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
