Page 1 of 1

sort by `value` problem?

Posted: Sun Jul 14, 2002 10:43 am
by mays
the following sql query doesnt work as i expect. i want the records to be sorted order by pprice (i want it treated as integer) but it sorts them as if they were char values.

SELECT price,
@ham:=(price & 0x0FFFFFFF),
@cur:=(price >> 28 ),
if((@cur=1),(@ham),(if((@cur=2),(@ham*$dolr_val/100),(@ham*$euro_val/100)))) as pprice
from tbl1 order by pprice;

price is a 32bit INT field. first 4 bits used for currency info and the rest 28bits used for price. pprice is produced by multiplication of cur and ham

thanks for your interests.

Posted: Sun Jul 14, 2002 3:58 pm
by josa
I tried to reproduce your problem and succeeded a couple of times and then all of a sudden it started sorting correctly. I cut, pasted and ran your query without changes. I know this isn't a solution but I just wanted to point out that there might be a bug in MySQL causing this. I use MySQL 3.23.49 on a Windows XP machine.

/josa

Posted: Wed Jul 17, 2002 5:28 am
by mays
so
anyone can help me?

or any suggest, who i can ask about this problem?

Posted: Wed Jul 17, 2002 12:14 pm
by josa
Okay, I've delved a bit deeper into the problem and found that MySQL sorts the result as a string only the first time you do the select. The second time it sorts correctly. Since this query don't have any keys to order by, the sorting is performed by creating a temporary file and dump the data to that file and then read it back sorted (it's a bit more complex, but the manual explains it better than I do). I tried to force MySQL to treat pprice as a numeric value and succeeded (on my machine anyway) by adding a zero to the result, like this:

SELECT price,
@ham:=(price & 0x0FFFFFFF),
@cur:=(price >> 28 ),
if((@cur=1),(@ham),(if((@cur=2),(@ham*$dolr_val/100),(@ham*$euro_val/100)))) + 0 as pprice
from tbl1 order by pprice;

/josa