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