Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#32793 closed Bug (fixed)

Problem with decimal field when upgrade version

Reported by: Mohsen Tamiz Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 3.2
Severity: Release blocker Keywords: decimal
Cc: Simon Charette Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (12)

comment:1 by Mariusz Felisiak, 3 years ago

Cc: Simon Charette added

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

comment:2 by Simon Charette, 3 years ago

Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

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.

comment:3 by Mariusz Felisiak, 3 years ago

Resolution: needsinfo
Status: newclosed

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?

comment:4 by Mohsen Tamiz, 3 years ago

Resolution: needsinfo
Status: closednew

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

Last edited 3 years ago by Mohsen Tamiz (previous) (diff)

comment:5 by Mariusz Felisiak, 3 years ago

Triage Stage: AcceptedUnreviewed

comment:6 by Mariusz Felisiak, 3 years ago

Mohsen, Thanks. I will check it on Monday.

comment:7 by Mariusz Felisiak, 3 years ago

Triage Stage: UnreviewedAccepted

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.

comment:8 by Mariusz Felisiak, 3 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

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'>]

in reply to:  8 comment:9 by Mariusz Felisiak, 3 years ago

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)

comment:10 by Mariusz Felisiak, 3 years ago

Has patch: set

comment:11 by GitHub <noreply@…>, 3 years ago

Resolution: fixed
Status: assignedclosed

In e703b152:

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

Regression in 1e38f1191de21b6e96736f58df57dfb851a28c1f.

Thanks Mohsen Tamiz for the report.

comment:12 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top