mos wrote:
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
Glad to help. The sub-query idea was only a suggestion for testing my
idea, though. There's likely a better solution for production. Maybe you
could create a view around the purch_date select, for instance. I'd
think there'd be a few alternate methods for getting the results you
want, some more efficient than others.
As for whether it's a bug or not, I'm really not so sure I'd call it
that. The behaviour seems to me to be logical because ordering always
comes after row selection. It would take far too long to collect a set
of results, otherwise. Like many things SQL, it might not be necessarily
what most people would want but, in the end, something most of us can
grudgingly agree is the best course. I know much more about PG than
MysSQL and i'm pretty sure PG would do the same. I'd like to run some
tests, actually.
b
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
