MySQL Bugs: #6468: ROUND: returns incorrect result if 2nd argument is negative

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:	Windows (Windows XP)
Assigned to:	Paul DuBois	CPU Architecture:	Any

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.

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>

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>

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

The description in the manual is incorrect.

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.