From: mos (mos99fastmail.fm)
Date: Mon Jan 19 2009 - 09:48:52 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]