From: Hank
Date: September 20 2010 1:12pm
Subject: Re: numbering the result set rows for insertion into another table
List-Archive: http://lists.mysql.com/mysql/223051
Message-Id: <AANLkTimcaPmmOJShu7yt5i9wMG9HhC7B03Z4L+ASuUmc@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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:=3D0;
>> insert ignore into dest_table
>> =A0 =A0 =A0 select t1.field1, =A0t1.field2, =A0t1.field3, =A0t2.field1,
>> t1.field3, t2.ts, @cnt:=3D@cnt+1
>> =A0 =A0 =A0 from table1 as t1 left join table2 as t2 using (field1, fiel=
d2)
>> =A0 =A0 =A0 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 processi=
ng.
> 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. =A0This is completely in line with h=
ow
> the SQL Standard says a query should operate. =A0What if you wanted to OR=
DER
> 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 t=
he
> way you want, you need to create a temporary table with the results of yo=
ur
> 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. =A0We =
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 le=
ast
> 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:=3D0;
update  dest_table set hcnt=3D@cnt:=3D@cnt+1 <where clause> order by ts;

This works for both mysql 4.x and 5.1.15.

-Hank