List:General Discussion« Previous MessageNext Message »
From:Hank Date:September 20 2010 1:12pm
Subject:Re: numbering the result set rows for insertion into another table
View as plain text  
On Mon, Sep 20, 2010 at 7:36 AM, Shawn Green (MySQL)
<shawn.l.green@stripped> 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 hcnt=@cnt:=@cnt+1 <where clause> order by ts;

This works for both mysql 4.x and 5.1.15.

-Hank
Thread
numbering the result set rows for insertion into another tableHank19 Sep
  • Re: numbering the result set rows for insertion into another tableMySQL)20 Sep
    • Re: numbering the result set rows for insertion into another tableHank20 Sep