MySQL Bugs: #63126: cannot perform a precise calculation with decimal data

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

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

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".

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".