[Django] #32793: Problem with decimal field when upgrade version
Django
-------------------------------------+-------------------------------------
Reporter: mohiz | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords: decimal
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
A simple example such as:
{{{
#!python
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:
{{{
#!mysql
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32793>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: decimal | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Simon Charette (added)
Comment:
This behavior was changed in 1e38f1191de21b6e96736f58df57dfb851a28c1f,
however I cannot reproduce a rounding issue on MySQL 8.0.25.
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: decimal | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: decimal | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
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`?
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
Comment:
I created an example project which shows the problem. It brings up two
container 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 }}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: decimal | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Unreviewed
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:5>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: decimal | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Mohsen, Thanks. I will check it on Monday.
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:6>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:7>
Django
-------------------------------------+-------------------------------------
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: decimal | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Mariusz Felisiak
* status: new => assigned
Comment:
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'>]
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:8>
Django
-------------------------------------+-------------------------------------
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: decimal | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Replying to [comment:8 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)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:9>
Django
-------------------------------------+-------------------------------------
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: decimal | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/14470 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:10>
Django
-------------------------------------+-------------------------------------
| Felisiak
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: decimal | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"e703b152c6148ddda1b072a4353e9a41dca87f90" e703b152]:
{{{
#!CommitTicketReference repository=""
revision="e703b152c6148ddda1b072a4353e9a41dca87f90"
Fixed #32793 -- Fixed loss of precision for temporal operations with
DecimalFields on MySQL.
Regression in 1e38f1191de21b6e96736f58df57dfb851a28c1f.
Thanks Mohsen Tamiz for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:11>
Django
-------------------------------------+-------------------------------------
| Felisiak
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: decimal | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"94675a76339f98ed6fa9d07d4e647f8ef33953a0" 94675a76]:
{{{
#!CommitTicketReference repository=""
revision="94675a76339f98ed6fa9d07d4e647f8ef33953a0"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32793#comment:12>