* Benjamin Pflugmann [me about round(2.55,1) -> 2.5 or 2.6 on different platforms.] > I would argue whether that is a bug... it is inconsistent on different > platforms, but it is consistent with the behaviour of other programs > on the same platform. You always break the one or the other thing.
well... I think I understand now... thanks! :) ...but as long as 0.55-0.5 != 0.05 I will call it a bug. It may be well known and hard to fix, but that does not make the expression right. :) > Since there is another way to get consistent behaviour across > platforms (FLOOR((2.55*10)+0.5)/10), but I know of none to get it > consistent with the platform, I prefer the current behaviour. This formula is pretty straight forward... why is it so difficult to implement it in the round() function? I know, read on... ;) > [...] > > While investigating this issue, I came across what I think is a > third bug: > > > > solaris, 3.23.39-log: > > > > mysql> select round(0.5,0),round(0.55,1),round(0.555,2); > > +--------------+---------------+----------------+ > > | round(0.5,0) | round(0.55,1) | round(0.555,2) | > > +--------------+---------------+----------------+ > > | 0 | 0.6 | 0.56 | > > +--------------+---------------+----------------+ > > 1 row in set (0.00 sec) > > > > If 0.5 is rounded down to 0, why is 0.55 and 0.555 rounded up to 0.6 and > > 0.56? > > > > I would have expected 0/0.5/0.55 or 1/0.6/0.56 > > Well, if at all, I don't think that this is a new bug, but a > reincarnation of the "bug" above: it's the behaviour of your > underlying system lib. > > But, in fact, I think it is only a misunderstanding. Note that only a > limited amount of decimal numbers can represented in the internal > floating point format. Others will be represented by one of its > neighbours, e.g. > > mysql> SELECT 0.55000000000000000000000; > +---------------------------+ > | 0.55000000000000000000000 | > +---------------------------+ > | 0.55000000000000004440892 | > +---------------------------+ > 1 row in set (0.00 sec) yes... yes... yes! Thank you very mutch, it all makes sense now. :o) > If you look at this representation, it is obvious, that this number > will be rounded up to 0.6. The same goes for 0.555. Yes, it is obvious, I just did not think that far... :) With this inconsistency in mind, is floating point really usefull for anything? I can't think of a project where I would use it... when you have very big and very small values in the same column, and accuracy is not important...? This is not a flame, I really wonder what people use it for! :) More important: this is a trap for novice users, maybe mysql would have been better of without floating point numbers? Keep the decimal/numeric type, but not using floating point as an internal representation, and also not using floating point for decimal constants... just a thought. I suppose a replication setup with master/slave on different platforms could also suffer from this... a statement involving floating point operations may give a different result on the slave... I guess this could mean trouble in some applications. -- 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
