Bug #63126 cannot perform a precise calculation with decimal data
Submitted: 7 Nov 2011 2:16 Modified: 7 Dec 2011 4:55
Reporter: jess Balint Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.5.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: decimal, precision

[7 Nov 2011 2:16] jess Balint
Description:
when given an expression containing decimal fields, i expect it would retain the precision spec of the decimal field when performing the calculation. the behaviour indicates that this is not the case and the calculation is performed with a lower precision.

I want to insert the value 1.03 then, using sql, insert the value 1/1.03, which is about 0.97087378640776699029126213592233. however, when using the query "insert ... 1/x" (where x = 1.03) i get wind up with the value 0.9709. if i insert with a literal, eg "insert ... 1/1.03" I get a closer value, 0.970873786, but still less precision than possible.

in addition, there is some odd behaviour in 5.1.41 where if there is only one row, the expected result is obtained.

How to repeat:
here is the result from 5.5.16:
(@localhost) [test]> drop table if exists x;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(@localhost) [test]> create table x (c decimal (40,30));
Query OK, 0 rows affected (0.34 sec)

(@localhost) [test]> insert into x values (1.03);
Query OK, 1 row affected (0.02 sec)

(@localhost) [test]> insert into x values (1.03);
Query OK, 1 row affected (0.01 sec)

(@localhost) [test]> insert into x select 1/c from x;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

(@localhost) [test]> select * from x;
+----------------------------------+
| c                                |
+----------------------------------+
| 1.030000000000000000000000000000 |
| 1.030000000000000000000000000000 |
| 0.970900000000000000000000000000 |
| 0.970900000000000000000000000000 |
+----------------------------------+
4 rows in set (0.00 sec)

(@localhost) [test]>
(@localhost) [test]> drop table if exists x;
Query OK, 0 rows affected (0.06 sec)

(@localhost) [test]> create table x (c decimal (40,30));
Query OK, 0 rows affected (0.08 sec)

(@localhost) [test]> insert into x values (1.03);
Query OK, 1 row affected (0.00 sec)

(@localhost) [test]> insert into x select 1/c from x;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

(@localhost) [test]> select * from x;
+----------------------------------+
| c                                |
+----------------------------------+
| 1.030000000000000000000000000000 |
| 0.970900000000000000000000000000 |
+----------------------------------+
2 rows in set (0.00 sec)

(@localhost) [test]> select version();
+-----------+
| version() |
+-----------+
| 5.5.16    |
+-----------+
1 row in set (0.00 sec)

---------------------------------------------------------
---------------------------------------------------------
---------------------------------------------------------
and 5.1.41:
mysql> drop table if exists x;
Query OK, 0 rows affected, 1 warning (0.00 sec

mysql> create table x (c decimal (40,30));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into x values (1.03);
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values (1.03);
Query OK, 1 row affected (0.00 sec)

mysql> insert into x select 1/c from x;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from x;
+----------------------------------+
| c                                |
+----------------------------------+
| 1.030000000000000000000000000000 |
| 1.030000000000000000000000000000 |
| 0.970900000000000000000000000000 |
| 0.970900000000000000000000000000 |
+----------------------------------+
4 rows in set (0.00 sec)

mysql>
mysql> drop table if exists x;
Query OK, 0 rows affected (0.00 sec)

mysql> create table x (c decimal (40,30));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into x values (1.03);
Query OK, 1 row affected (0.00 sec)

mysql> insert into x select 1/c from x;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from x;
+----------------------------------+
| c                                |
+----------------------------------+
| 1.030000000000000000000000000000 |
| 0.970873786407766990291262135922 |
+----------------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.41    |
+-----------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
perform decimal calculations as accurately as possible, and/or give a warning when precision is lost
[7 Nov 2011 4:55] Valeriy Kravchuk
That's probably related to the fact that MySQL consider numeric constants as DOUBLE (not DECIMAL(M,N)). Look:

macbook-pro:5.5 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.5.17-debug Source distribution

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> select 1/1.03;
+--------+
| 1/1.03 |
+--------+
| 0.9709 |
+--------+
1 row in set (0.00 sec)

mysql> select cast(1/1.03 as decimal(30,20));
+--------------------------------+
| cast(1/1.03 as decimal(30,20)) |
+--------------------------------+
|         0.97087378600000000000 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select cast(1/1.03 as decimal(40,30));
+----------------------------------+
| cast(1/1.03 as decimal(40,30))   |
+----------------------------------+
| 0.970873786000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select cast(1 as decimal(40,30))/cast(1.03 as decimal(40,30));
+--------------------------------------------------------+
| cast(1 as decimal(40,30))/cast(1.03 as decimal(40,30)) |
+--------------------------------------------------------+
|                       0.970873786407766990291262135922 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

So, this is probably not a bug, but just a (sad) result of this documented "feature".
[7 Dec 2011 7:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".