Bug #92790 Rounding error for (some) small decimal values
Submitted: 16 Oct 2018 8:41 Modified: 27 Feb 5:07
Reporter: Par Andersson Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:8.0.12 OS:Ubuntu (18)
Assigned to: CPU Architecture:x86
Tags: mysql-connector-python

[16 Oct 2018 8:41] Par Andersson
Description:
The setup is Ubuntu 18, MySQL 8.0.12 (had the same issue with 5.7), mysql-connector-python 8.0.12, python 3.6.5

Using the python Decimal datatype and small decimal values it's sometimes possible to get rounding errors with arithmetic expressions.

The unexpected result is most likely a side effect of decimals being quoted like string by the connector (see below for results supporting this claim).

Note that the issue will not happen if there is no arithmetic involved, for example inserting a new row or just setting the value to the decimal value would not trigger the issue.

The issue can be reproduced with both use_pure=True as well as use_pure=False (same side effects).

How to repeat:
Setup phase using mysql client:

1. Create a dummy table with a column of decimal type
mysql> CREATE TABLE testing ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, value DECIMAL(32,8) NOT NULL);

2. Insert a row with value 100000000000.00000001
mysql> INSERT INTO testing VALUES (NULL, '100000000000.00000001');

3. Ensure we have the expected result
mysql> select * from testing;
+----+-----------------------+
| id | value                 |
+----+-----------------------+
|  1 | 100000000000.00000001 |
+----+-----------------------+

And now to trigger the bug in python:

4.
>>> import mysql.connector
>>> import decimal
>>> d = decimal.Decimal('0.00000101')  <- just an example, using '0.00000001' would also trigger the error (same result).
>>> query = ("UPDATE testing SET value = (value + %s) where id = %s") 
>>> cnx = mysql.connector.connect(... your config here...) <- use_pure=True or False will not impact the result
>>> cur = cnx.cursor()
>>> cur.execute(query, (d, 1)) 

Now if we look in the mysql log we will see that the cur.execute(..) generated the following (note that the decimal is quoted):
  Query     UPDATE testing SET value = (value + '0.00000101') where id = 1

The expected value in this case would be 100000000000.00000001 + 0.00000101 = 100000000000.00000102

5. Inspecting the actual result in mysql client
mysql> select * from testing where id = 1;
+----+-----------------------+
| id | value                 |
+----+-----------------------+
|  1 | 100000000000.00000000 |  <- Does not match the expected value.
+----+-----------------------+

Now to verify the claim from the description that this is a side effect of the decimal being quoted lets first reset the value to the original
mysql> UPDATE testing SET value = '100000000000.00000001' where id = 1;
mysql> select * from testing where id = 1;
+----+-----------------------+
| id | value                 |
+----+-----------------------+
|  1 | 100000000000.00000001 |
+----+-----------------------+

Try doing the same update that the python code did, but from the mysql client and without the quotes around the decimal number

mysql> UPDATE testing SET value = (value + 0.00000101) where id = 1;
mysql> select * from testing where id = 1;
+----+-----------------------+
| id | value                 |
+----+-----------------------+
|  1 | 100000000000.00000102 |
+----+-----------------------+

Suggested fix:
Not quoting the decimal seems to solve the problem as demonstrated in the mysql client.
[22 Oct 2018 5:11] Umesh Shastry
Hello Par Andersson,

Thank you for the report and test case.

regards,
Umesh
[27 Feb 5:07] Par Andersson
In case anyone else end up here and this bug is still not fixed:

Try using mysqlclient instead (https://pypi.org/project/mysqlclient/) version 1.3.14 (or later I guess). Note that mysqlclient had the same bug that I reported here in versions 1.3.13 and earlier so it's important to get the fix in 1.3.14.