List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:September 20 2010 11:36am
Subject:Re: numbering the result set rows for insertion into another table
View as plain text  
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 

When executing an SQL statement, there are several stages to the 
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 

Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
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