On Mon, Sep 20, 2010 at 7:36 AM, Shawn Green (MySQL)
> 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,
>> t1.field3, t2.ts, @cnt:=@cnt+1
>> from table1 as t1 left join table2 as t2 using (field1,
>> 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
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:
update dest_table set hcnt=@cnt:=@cnt+1 <where clause> order by ts;
This works for both mysql 4.x and 5.1.15.