At 03:14 AM 1/19/2009, b wrote:
mos wrote:
I'm using MySQL 5.1.30 (WinXP) and user defined variables don't seem to
be working properly if the Select statement is sorted.
Here is a simple example:
CREATE TABLE `tmp` (
`Purch_Date` date DEFAULT NULL,
`Product` char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Here are the values that I entered in this order:
Purch_Date Product
2007-01-01 A
2007-01-04 B
2008-05-04 B
2008-12-04 B
2001-01-04 D
2001-01-04 C
Now here is the Select statement that is failing:
set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date
OUTPUT:
@num:=...@num+1 Purch_Date
5 2001-01-04
1 2007-01-01
2 2007-01-04
3 2008-05-04
4 2008-12-04
The earliest date "2001-01-04" should have 1 and not 5 as the first
column in the results.
Why isn't the column "@num:=...@num+1" sorted in ascending order since it
was grouped by purch_date? Even if I explicitly tell it to sort by purch_date:
set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date
order by purch_date;
I still get the same results. This has worked fine in MySQL 5.0.1 but is
giving me these strange results in MySQL 5.1.30.
Am I doing something wrong here?
TIA
Mike
I suspect that what's happening is MySQL isn't selecting the rows in order
but displaying them ordered by purch_date. That is, the other column value
is being assigned to its row *before* the ordering occurs.
That's just a guess though, really. I might be way off here.
You could probably test this by selecting and ordering purch_date in a
sub-query and assigning your other column to the results of that.
(I think)
b,
I replaced it with:
set @num:=0;select * from (select @num:=...@num+1, purch_date from tmp order
by purch_date) as t1;
and that produces the correct sort order.
@num:=...@num+1 purch_date
1 2001-01-04
2 2001-01-04
3 2007-01-01
4 2007-01-04
5 2008-05-04
6 2008-12-04
This means anytime a user defined variable like "@num := @num + 1" and is
used in a query that is sorted, it is going to have to be changed to a
subquery. For example, if someone was using it to display detail line item
numbers on an invoice, it now has to be turned into a subselect. This
breaks a lot of code. Yes your solution works (thanks for that), but I
still think this is a bug.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
