| Bug #63668 | ROUND(x,d) doesn't round a value to "d" digits when "d" is passed as column | ||
|---|---|---|---|
| Submitted: | 8 Dec 2011 13:21 | Modified: | 11 Feb 2018 22:48 |
| Reporter: | Jacek J. | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.5.13, 5.5.17 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | decimal, round | ||
[9 Dec 2011 7:19]
Valeriy Kravchuk
Thank you for the problem report. Verified just as described with 5.5.17 also:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 --column-
type-info test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TEMPORARY TABLE test1 (
-> id INTEGER UNSIGNED,
-> value DECIMAL (11,10),
-> PRIMARY KEY (id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.16 sec)
mysql> INSERT INTO test1(id,value) VALUES(1,2.9123845673), (2,2.1325480691);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE TEMPORARY TABLE test2 (
-> id INTEGER UNSIGNED,
-> dec_part TINYINT,
-> PRIMARY KEY (id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test2(id,dec_part) VALUES(1,3), (2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT ROUND(T1.value, T2.dec_part)
-> FROM test1 T1 INNER JOIN test2 T2 ON T1.id = T2.id;
Field 1: `ROUND(T1.value, T2.dec_part)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: NEWDECIMAL
Collation: binary (63)
Length: 28
Max_length: 12
Decimals: 10
Flags: BINARY NUM
+------------------------------+
| ROUND(T1.value, T2.dec_part) |
+------------------------------+
| 2.9120000000 |
| 2.1300000000 |
+------------------------------+
2 rows in set (0.02 sec)
mysql> SELECT ROUND(T1.value, 3)
-> FROM test1 T1;
Field 1: `ROUND(T1.value, 3)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: NEWDECIMAL
Collation: binary (63)
Length: 7
Max_length: 5
Decimals: 3
Flags: BINARY NUM
+--------------------+
| ROUND(T1.value, 3) |
+--------------------+
| 2.912 |
| 2.133 |
+--------------------+
2 rows in set (0.00 sec)
Note that the problem is, probably, related to wrong metadata in case of column used, namely
Max_length: 12
Decimals: 10
that are just taken over from test1.value column "as is", unlike in case of constant used.
[15 Jan 2015 22:54]
Dan Markley
I believe I found a simpler test case: select roundTo, round(26.7891, roundTo ) as rounded, round(26.7891, 2 ) as roundedStatic from ( select 2 as roundTo ) a; yields: +---------+---------+---------------+ | roundTo | rounded | roundedStatic | +---------+---------+---------------+ | 2 | 26.7900 | 26.79 | +---------+---------+---------------+
[14 Oct 2015 13:00]
Attila Varga
Still unsolved in mysql 5.6.25 Another thing to mention: Dividing 2 values that are passed by columns (the type of the first column is float!) and then rounding the result where the "D" is also passed by a column then it works properely. But if the type of the first column is an integer it still doesn't work. What is common?
[11 Feb 2018 22:48]
Roy Lyseng
Posted by developer: This is not a bug. First, the decimal part is not cut, it is rounded correctly according to the supplied number of decimals. This would be evident if different data values had been provided. Second, metadata of SQL expressions are determined when expressions are resolved, prior to execution. When the number of decimals is provided during execution, MySQL must perform an educated guess of what should be the appropriate precision and scale. The scale (number of decimals) is copied from the first argument, since a scale less than this can easily be filled with zeros and a scale bigger than this would have to be filled with zeros anyway.
Description: When using ROUND(x,d) function and passing "d" parameter as a column value not straightly as a number the result is not rounded to "d" digits but the decimal part is cut to "d" digits and filled up with zeros on the right side and whole decimal parts has as many digits as "x" had. How to repeat: mysql> CREATE TEMPORARY TABLE test1 -> ( -> id INTEGER UNSIGNED, -> value DECIMAL (11,10), -> PRIMARY KEY (id) -> ) ENGINE = MEMORY; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO test1(id,value) -> VALUES(1,2.9123845673), -> (2,2.1325480691); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> CREATE TEMPORARY TABLE test2 -> ( -> id INTEGER UNSIGNED, -> dec_part TINYINT, -> PRIMARY KEY (id) -> ) ENGINE = MEMORY; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO test2(id,dec_part) -> VALUES(1,3), -> (2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT -> ROUND(T1.value, T2.dec_part) -> FROM -> test1 T1 -> INNER JOIN test2 T2 ON T1.id = T2.id; +------------------------------+ | ROUND(T1.value, T2.dec_part) | +------------------------------+ | 2.9120000000 | | 2.1300000000 | +------------------------------+ 2 rows in set (0.00 sec) but: mysql> SELECT ROUND(T1.value,(SELECT COUNT(id) FROM test2)) -> FROM test1 T1; +-----------------------------------------------+ | ROUND(T1.value,(SELECT COUNT(id) FROM test2)) | +-----------------------------------------------+ | 2.91 | | 2.13 | +-----------------------------------------------+ 2 rows in set (0.00 sec) and also bug can be seen here: mysql> SELECT ROUND(1.23454556346572,(@a:=FLOOR(0 + RAND() * 5))),@a; +-----------------------------------------------------+------+ | ROUND(1.23454556346572,(@a:=FLOOR(0 + RAND() * 5))) | @a | +-----------------------------------------------------+------+ | 1.23450000000000 | 4 | +-----------------------------------------------------+------+ 1 row in set (0.00 sec)