| Bug #10719 | Inconsistent behavior of DECIMAL(M,D) with higher precision input | ||
|---|---|---|---|
| Submitted: | 18 May 2005 18:19 | Modified: | 28 Jul 2005 11:20 |
| Reporter: | Michael Stassen | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.11 | OS: | |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[19 May 2005 18:19]
Michael Stassen
I should add that I get the same results with 4.0.20. I'm guessing that because mysql 4.x stores DECIMALs as strings, it handles the string case with a simple string copy, hence the truncation. Gordon reports on the mailing list <http://lists.mysql.com/mysql/184274>, however, that 5.0.4 always rounds . I'm guessing that since DECIMAL is no longer internally a string as of 5.0.3, a simple string copy is not possible, so the input string is converted to a number first, which means it gets rounded before it is assigned. Finally, in cases where the value is rounded, mysql currently does not issue a warning that the value was rounded. I believe we should warn whenever data is changed.
[28 Jul 2005 11:20]
Alexey Botchkov
Thank you for taking the time to report a problem. Unfortunately you are not using a current version of the product your reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/ If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open". Again, thank you for your continued support of MySQL. Additional info: Sorry, but this bug can't be fixed in 4.x DECIMAL fieldtype works correctly since 5.0.
Description: When a DECIMAL(M,D) column is assigned a value with more than D decimal places, the result depends on how the assignment is made. If the assigned value is a string, the extra digits are simply dropped, whereas if it's a number, it is rounded to the Dth decimal place. How to repeat: mysql> CREATE TABLE vardec (v VARCHAR(10), d DECIMAL(10,2)); Query OK, 0 rows affected (0.01 sec) ## Rounds when inserting numbers mysql> INSERT INTO vardec VALUES -> ('6.22',6.22), ('6.223',6.223), ('6.225',6.225), ('6.227',6.227); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM vardec; +-------+------+ | v | d | +-------+------+ | 6.22 | 6.22 | | 6.223 | 6.22 | | 6.225 | 6.22 | | 6.227 | 6.23 | +-------+------+ 4 rows in set (0.00 sec) ## Truncates when input is a string mysql> UPDATE vardec SET d = v; Query OK, 1 row affected, 3 warnings (0.00 sec) Rows matched: 4 Changed: 1 Warnings: 3 mysql> SHOW WARNINGS; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'd' at row 2 | | Warning | 1265 | Data truncated for column 'd' at row 3 | | Warning | 1265 | Data truncated for column 'd' at row 4 | +---------+------+----------------------------------------+ 3 rows in set (0.01 sec) mysql> SELECT * FROM vardec; +-------+------+ | v | d | +-------+------+ | 6.22 | 6.22 | | 6.223 | 6.22 | | 6.225 | 6.22 | | 6.227 | 6.22 | +-------+------+ 4 rows in set (0.00 sec) ## Rounds if string is converted to a number before the assignment mysql> UPDATE vardec SET d = v+0; Query OK, 1 row affected (0.00 sec) Rows matched: 4 Changed: 1 Warnings: 0 mysql> SELECT * FROM vardec; +-------+------+ | v | d | +-------+------+ | 6.22 | 6.22 | | 6.223 | 6.22 | | 6.225 | 6.22 | | 6.227 | 6.23 | +-------+------+ 4 rows in set (0.00 sec) ## Truncates when converting a string to decimal via ALTER mysql> ALTER TABLE vardec CHANGE v v DECIMAL(6,2); Query OK, 4 rows affected, 3 warnings (0.04 sec) Records: 4 Duplicates: 0 Warnings: 3 mysql> SELECT * FROM vardec; +------+------+ | v | d | +------+------+ | 6.22 | 6.22 | | 6.22 | 6.22 | | 6.22 | 6.22 | | 6.22 | 6.23 | +------+------+ 4 rows in set (0.00 sec) Suggested fix: At the very least, this should be documented, but the preferred solution would be consistent behavior -- either always round or always truncate. Always rounding would be the more mathematically correct option.