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=arch...@jab.org
