* Doug Thompson
> On Wed, 27 Feb 2002 13:53:15 +0100, Roger Baklund wrote:
>
> >* Doug Thompson
>
> >> I found using variables to work as expected with 3.23.43 and
> >> Win98.
> >>
> >> I was unable to duplicate your changing outputs.
> >
> >I was using a 3.23.30-gamma win2k.
> >
> >> mysql> select @a:=digits,number,round(number,@a) from testme;
> >> +------------+----------+------------------+
> >> | @a:=digits | number | round(number,@a) |
> >> +------------+----------+------------------+
> >> | 3 | 100.4235 | 100.4230 |
> >> | 1 | 85.4000 | 85.4000 |
> >> +------------+----------+------------------+
> >> 2 rows in set (0.00 sec)
> >
> >Wouldn't you expect the output to be something like:
> >
> > +------------+----------+------------------+
> > | @a:=digits | number | round(number,@a) |
> > +------------+----------+------------------+
> > | 3 | 100.4235 | 100.423 |
> > | 1 | 85.4000 | 85.4 |
> > +------------+----------+------------------+
>
>
> No, I would expect the output to inherit the field type of
> "number" and display the same way.
ok... may I ask why? If the function involved two columns (like the original
did, or like "round(number1+number2,3)") which of the two would this column
inherit from?
> >
> >also.... 100.423... should it not be 100.424?
>
> As you know, some systems/packages round down 0.xx50 while other
> round up from that value. I agree that intuitively
I am aware of the problems involved in using floating point numbers in
general, there is also a note in the doc. about this. However, it was my
impression that most implementations of a round() function did not suffer
from this... It makes the round()-function more or less useless, doesn't it?
(I must admit I almost never use it, I prefer working with integers &
strings ;))
> it should round up, but it is only a matter about which we need
> to be aware unless the application is critical --
> control systems come to mind -- and then you compensate in the
> software design if it's needed. It's a function of how
> the floating point hardware performs storage and manipulation of
> values in response to the instructions and how the
> software processes the calls. It is reasonable to expect
> consistent behavior within a given processor family, but
> that's about all we can hope for.
>
> For example, I'm running an AMD K6/2-500 processor. Your system
> likely has an Intel cpu. Then there are still
> different results from a Sun system. Your results brought the
> issues to light. I wonder if there's a reasonable fix.
One obvious workaround is to avoid floating point numbers... or in mysql
terms, use the decimal/numeric data type instead of float/double... but that
does not solve the problem in this case, I guess floating point is used
internally in the round() function...?
With different numbers in the test table, I discovered that my version also
did the rounding using the id field, but it seems the decimals parameter of
the round() function is evaluated only once, I can't get it to output a
different number of decimals for each row:
mysql> select *,round(num1,id) from tab4;
+------+----------+----------------+
| id | num1 | round(num1,id) |
+------+----------+----------------+
| 1 | 1.2345 | 1.2000 |
| 2 | 12.3456 | 12.3500 |
| 3 | 100.4235 | 100.4230 |
+------+----------+----------------+
3 rows in set (0.01 sec)
Note that the number of digits after the decimal sign is always four, but
the rightmost digits are just padding zeroes... the number of digits
actually coming from the num1 field is determined by the id field, and the
round() function "works", if you can accept .4235 -> .423 on _some_
platforms... a little substring magic, and we got the desired result:
mysql> select *,@a:=round(num1,id) as tmp,left(@a,locate('.',@a)+id)+0 as
result from tab4;
+------+----------+----------+---------+
| id | num1 | tmp | result |
+------+----------+----------+---------+
| 1 | 1.2345 | 1.2000 | 1.2 |
| 2 | 12.3456 | 12.3500 | 12.35 |
| 3 | 100.4235 | 100.4230 | 100.423 |
+------+----------+----------+---------+
3 rows in set (0.00 sec)
Putting some time & effort in it, I was able to work around the round()
rounding error, but I must warn you, it is rather messy... ;o)
mysql> select *,@a:=mid(number,1,floor(log10(number))+3+digits) as tmp,
-> left(if(right(@a,1)+0>4,@a+pow(10,-(floor(log10(number))+1)),@a),
-> length(@a)-1)+0 as result from testme;
+----------+--------+----------+---------+
| number | digits | tmp | result |
+----------+--------+----------+---------+
| 100.4235 | 3 | 100.4235 | 100.424 |
| 85.4000 | 1 | 85.40 | 85.4 |
+----------+--------+----------+---------+
2 rows in set (0.00 sec)
mysql> select *,@a:=mid(num1,1,floor(log10(num1))+3+id) as tmp,
-> left(if(right(@a,1)+0>4,@a+pow(10,-(floor(log10(num1))+1)),@a),
-> length(@a)-1)+0 as result from tab4;
+------+----------+----------+---------+
| id | num1 | tmp | result |
+------+----------+----------+---------+
| 1 | 1.2345 | 1.23 | 1.2 |
| 2 | 12.3456 | 12.345 | 12.35 |
| 3 | 100.4235 | 100.4235 | 100.424 |
+------+----------+----------+---------+
3 rows in set (0.00 sec)
It's not a pretty sight, but it seems to do the work.
--
Roger
query
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
