Bug #6468 ROUND: returns incorrect result if 2nd argument is negative
Submitted: 5 Nov 2004 17:43 Modified: 7 Mar 2005 19:00
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.0-alpha-max-debug OS:Microsoft Windows (Windows XP)
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[5 Nov 2004 17:43] Trudy Pelzer
Description:
If ROUND is used with two arguments, and the second argument
is a negative number, the result should be that the function returns 
<argument 1> with the integer part of the number zeroed out to the
number of digits specified by <argument 2>. This is not happening
correctly in all cases.

Note: The problem already exists in version 4.1, where I tested
it with 4.1.4-gamma.

How to repeat:
mysql> select round(900500,-1) ;
+------------------+
| round(900500,-1) |
+------------------+
|           900500     |
+------------------+
-- Correct result, the last digit rounded to zero

mysql> select round(-900500,-1) ;
+-------------------+
| round(-900500,-1) |
+-------------------+
|           -900500     |
+-------------------+
-- Correct result, just the negative of the previous example

mysql> select round(900500,-3) ;
+------------------+
| round(900500,-3) |
+------------------+
|           900000     |
+------------------+
-- Correct result, the last 3 digits rounded to zero

mysql> select round(-900500,-3) ;
+-------------------+
| round(-900500,-3) |
+-------------------+
|           -901000     |
+-------------------+
-- Incorrect result. As with example #2, this should just return the
negative of the previous example; that is, the correct result is
-900000

mysql> select round(900500.555,-3) ;
+----------------------+
| round(900500.555,-3) |
+----------------------+
|                   901000  |
+----------------------+
-- Incorrect result. The correct result is 900000.

mysql> select round(-900500.555,-3) ;
+----------------------+
| round(900500.555,-3) |
+----------------------+
|                  -901000  |
+----------------------+
-- Incorrect result. The correct result is -900000.
[5 Nov 2004 18:41] Miguel Solorzano
Verfied on Windows with source from latest BK tree:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.2-alpha-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select round(900500,-1) ;
+------------------+
| round(900500,-1) |
+------------------+
|           900500 |
+------------------+
1 row in set (0.07 sec)

mysql> select round(-900500,-1) ;
+-------------------+
| round(-900500,-1) |
+-------------------+
|           -900500 |
+-------------------+
1 row in set (0.00 sec)

mysql> select round(900500,-3) ;
+------------------+
| round(900500,-3) |
+------------------+
|           900000 |
+------------------+
1 row in set (0.00 sec)

mysql> select round(-900500,-3) ;
+-------------------+
| round(-900500,-3) |
+-------------------+
|           -901000 |
+-------------------+
1 row in set (0.00 sec)

mysql> select round(900500.555,-3) ;
+----------------------+
| round(900500.555,-3) |
+----------------------+
|               901000 |
+----------------------+
1 row in set (0.00 sec)

mysql>
[5 Nov 2004 18:54] Miguel Solorzano
Tested also on Linux Slackware and showed partially incorrect
results:

miguel@hegel:~$ cd dbs/5.0/
miguel@hegel:~/dbs/5.0$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.2-alpha-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select round(900500,-1) ;
+------------------+
| round(900500,-1) |
+------------------+
|           900500 |
+------------------+
1 row in set (0.10 sec)

mysql> select round(-900500,-1) ;
+-------------------+
| round(-900500,-1) |
+-------------------+
|           -900500 |
+-------------------+
1 row in set (0.00 sec)

mysql> select round(900500,-3) ;
+------------------+
| round(900500,-3) |
+------------------+
|           900000 |
+------------------+
1 row in set (0.00 sec)

mysql> select round(-900500,-3) ;
+-------------------+
| round(-900500,-3) |
+-------------------+
|           -900000 |
+-------------------+
1 row in set (0.00 sec)

mysql>  select round(900500.555,-3) ;
+----------------------+
| round(900500.555,-3) |
+----------------------+
|               901000 |
+----------------------+
1 row in set (0.02 sec)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
mysql>
[4 Mar 2005 12:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22657
[7 Mar 2005 17:58] Sergei Golubchik
The description in the manual is incorrect.
[7 Mar 2005 19:00] Paul Dubois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Update manual for ROUND(), and also for TRUNCATE(),
which has a similar behavior with a negative second argument.

For TRUNCATE, it's:
D can be negative to truncate (make zero) D digits left of the decimal
point of the value X.

For ROUND, it's:
D can be negative to round off D digits left of the decimal point of
the value X.