Hi, there are a couple of bugs related to the round() function. (See the thread 'Can somebody help me with "round (columna,columnb) "?')
One bug is already well known and documentet, it is related to different C library implementations on different platforms: the result of round(2.55,1) may be 2.5 or 2.6. The other bug is related to using a column name as the decimal parameter to the round() function (select round(col1,col2) from table). The rounding is performed, but the result is padded with zeroes, so that all rows have the same number of decimals. The number of decimals used is the number of decimals in the definition of col1, or if 'col1' is an expression, the highest number of decimals in any of the parameters in the expression. This is wrong, because "1.1000" indicates a higher grade of precision than "1.1", even if it has the same numeric value. 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 win2k, 3.23.30-gamma: 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.5 | 0.56 | +--------------+---------------+----------------+ 1 row in set (0.00 sec) On my win2k box 0.5 and 0.55 is rounded down, but 0.555 is rounded up...? Both my boxes agree on this: mysql> select round(0.55-0.5,1),round(0.05,1); +-------------------+---------------+ | round(0.55-0.5,1) | round(0.05,1) | +-------------------+---------------+ | 0.1 | 0.0 | +-------------------+---------------+ 1 row in set (0.00 sec) mysql> select 0.55-0.5=0.05; +---------------+ | 0.55-0.5=0.05 | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) ...but this is correct on both boxes: mysql> select 0.55-0.05=0.5; +---------------+ | 0.55-0.05=0.5 | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) Another test: solaris: mysql> select round(0.05,1),round(0.15,1),round(0.25,1),round(0.35,1); +---------------+---------------+---------------+---------------+ | round(0.05,1) | round(0.15,1) | round(0.25,1) | round(0.35,1) | +---------------+---------------+---------------+---------------+ | 0.0 | 0.2 | 0.2 | 0.4 | +---------------+---------------+---------------+---------------+ 1 row in set (0.00 sec) Sometimes it rounds up, sometimes it rounds down... win2k: mysql> select round(0.05,1),round(0.15,1),round(0.25,1),round(0.35,1); +---------------+---------------+---------------+---------------+ | round(0.05,1) | round(0.15,1) | round(0.25,1) | round(0.35,1) | +---------------+---------------+---------------+---------------+ | 0.0 | 0.1 | 0.2 | 0.3 | +---------------+---------------+---------------+---------------+ 1 row in set (0.00 sec) Always down, that could be ok, but...: mysql> select round(0.005,2),round(0.015,2),round(0.025,2),round(0.035,2); +----------------+----------------+----------------+----------------+ | round(0.005,2) | round(0.015,2) | round(0.025,2) | round(0.035,2) | +----------------+----------------+----------------+----------------+ | 0.00 | 0.01 | 0.02 | 0.04 | +----------------+----------------+----------------+----------------+ 1 row in set (0.00 sec) Can anyone confirm if this behaviour is also in the newer versions? Please tell me if there is something wrong with my expectations... -- 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
