* 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