numbering the result set rows for insertion into another table

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?

Many thanks.

-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: numbering the result set rows for insertion into another table

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: numbering the result set rows for insertion into another table

On Mon, Sep 20, 2010 at 7:36 AM, Shawn Green (MySQL)
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 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 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=3Dgcdmg-mysql-2@m.gmane.o rg