Bug #55423 ROUND can return wrong result if its second argument is NULL
Submitted: 21 Jul 2010 4:31 Modified: 21 Jul 2010 4:59
Reporter: Igor Babaev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[21 Jul 2010 4:31] Igor Babaev
Description:
The function ROUND sometimes returns a wrong result if the second argument is NULL.

How to repeat:
The following example demonstrates the problem:

mysql> CREATE TABLE t1 (p int, a double NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1(p,a) VALUES (0,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1(p,a) VALUES (NULL,0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
+---+------+------------+-----------------+
| a | p    | ROUND(a,p) | ROUND(a,p+NULL) |
+---+------+------------+-----------------+
| 1 |    0 |          1 |               1 |
| 0 | NULL |          0 |            NULL |
+---+------+------------+-----------------+
2 rows in set (0.00 sec)

Suggested fix:
See the fix in the patch for bug LP#607177.
[21 Jul 2010 4:59] Valeriy Kravchuk
Thank you for the bug report. Verified with current 5.1.49 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (p int, a double NOT NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1(p,a) VALUES (0,1);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO t1(p,a) VALUES (NULL,0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
+---+------+------------+-----------------+
| a | p    | ROUND(a,p) | ROUND(a,p+NULL) |
+---+------+------------+-----------------+
| 1 |    0 |          1 |               1 |
| 0 | NULL |          0 |            NULL |
+---+------+------------+-----------------+
2 rows in set (0.02 sec)
[21 Jul 2010 7:20] Philip Stoev
See also MySQL bug #55365