Problem with decimal field when upgrade version

[Mohsen Tamiz]

A simple example such as:

from django.db import models

class Foo(models.Model):
    amount = models.DecimalField(
        null=False,
        decimal_places=18,
        max_digits=44)


# A simple insertion example 
from api.models import Foo
from django.db.models import F
from decimal import Decimal

f = Foo.objects.create(amount=Decimal('0.5'))
f.amount = F('amount') - Decimal('0.4')
f.save(update_fields=['amount', ])
f.refresh_from_db()
print(f.amount)

This creates an unexpected result (0.099999999999999980) in new version, but in previous version it was ok. I checked the difference and I found that new version sends a decimal value in a quotation, but in the previous version it would send in number format. Below is the query for two different versions:

3.1
UPDATE `api_foo` SET `amount` = (`api_foo`.`amount` - 0.4) WHERE `api_foo`.`id` = 1

3.2
UPDATE `api_foo` SET `amount` = (`api_foo`.`amount` - '0.4') WHERE `api_foo`.`id` = 1

I am using mysql-8.0.19.

[Mariusz Felisiak]

This behavior was changed in 1e38f1191de21b6e96736f58df57dfb851a28c1f, however I cannot reproduce a rounding issue on MySQL 8.0.25.

[Simon Charette]

I've not looked into how but we should fix this.

I believe it happens to be addressed in MySQL 8.0.21 (relased 2020-07-13) due to implicit casts injections as is likely why it wasn't caught by our test suite

​https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html

Building on work done in MySQL 8.0.18, the server now performs injection of casts into queries to avoid mismatches when comparing string data types with those of numeric or temporal types; as when comparing numeric and temporal types, the optimizer now adds casting operations in the item tree inside expressions and conditions in which the data type of the argument and the expected data type do not match. This makes queries in which string types are compared with numeric or temporal types equivalent to queries which are compliant with the SQL standard, while maintaining backwards compatibility with previous releases of MySQL.

It's not clear to me whether this was broken in 8.0.18 and then resolved in 8.0.21

​https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html

MySQL now performs injection of casts into queries to avoid certain data type mismatches; that is, the optimizer now adds casting operations in the item tree inside expressions and conditions in which the data type of the argument and the expected data type do not match. This makes the query as executed equivalent to one which is compliant with the SQL standard while maintaining backwards compatibility with previous releases of MySQL.

[Mariusz Felisiak]

I've checked multiple versions and couldn't reproduce a rounding issue on:

• MySQL 5.7.32,
• MySQL 8.0.17,
• MySQL 8.0.18,
• MySQL 8.0.19,
• MySQL 8.0.20,
• MySQL 8.0.21,
• MySQL 8.0.22,
• MySQL 8.0.23,
• MySQL 8.0.24,
• MySQL 8.0.25.

Can you double check MySQL version? also, are you sure that the amount field has numeric datatype and not e.g. double precision?

[Mohsen Tamiz]

I created an example project which shows the problem. It brings up two containers one for mysql and another for a simple django project. After startup time (you must wait some time after second container got ready, because it must finish migration process) you can get the failure error using:
sudo docker-compose exec db python manage.py test api.tests --keepdb

The project could clone from github:

git clone https://github.com/mohiz/test_djanog_decimal_field.git

[Mariusz Felisiak]

Mohsen, Thanks. I will check it on Monday.

[Mariusz Felisiak]

Thanks for extra details. Increasing the number of decimal places did the trick. I can reproduce this issue on MySQL 5.7.32, 8.0.11, 8.0.19, 8.0.25 so all versions seem to be affected.

Regression in 1e38f1191de21b6e96736f58df57dfb851a28c1f.
Reproduced at d270dd584e0af12fe6229fb712d0704c232dc7e5.

[Mariusz Felisiak]

I noticed that the same test but with f.amount = F('amount') - Decimal(0.4) (no parentheses) crashes on MySQL:

  File "/tests/django/django/db/backends/utils.py", line 238, in format_number
    value = context.create_decimal(value)
decimal.Rounded: [<class 'decimal.Rounded'>]

[Mariusz Felisiak]

Replying to Mariusz Felisiak:

I noticed that the same test but with f.amount = F('amount') - Decimal(0.4) (no parentheses) crashes on MySQL:

  File "/tests/django/django/db/backends/utils.py", line 238, in format_number
    value = context.create_decimal(value)
decimal.Rounded: [<class 'decimal.Rounded'>]

I tried to add a test with Decimal(0.4) but currently it crashes on SQLite and Oracle, IMO we can leave it as a separate issue.

    def test_decimal_expression_(self):
        n = Number.objects.create(integer=1, decimal_value=Decimal('0.5'))
        n.decimal_value = F('decimal_value') - Decimal(0.4)
        n.save()
        n.refresh_from_db()
        self.assertAlmostEqual(n.decimal_value, Decimal('0.1'), 16)

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In e703b152:

Fixed #32793 -- Fixed loss of precision for temporal operations with DecimalFields on MySQL.

Regression in 1e38f1191de21b6e96736f58df57dfb851a28c1f.

Thanks Mohsen Tamiz for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 94675a76:

[3.2.x] Fixed #32793 -- Fixed loss of precision for temporal operations with DecimalFields on MySQL.

Regression in 1e38f1191de21b6e96736f58df57dfb851a28c1f.

Thanks Mohsen Tamiz for the report.
Backport of e703b152c6148ddda1b072a4353e9a41dca87f90 from main