[Django] #33232: DecimalField rounding error when upgrading from 3.1 to 3.2
Django
-------------------------------------+-------------------------------------
Reporter: Michał | Owner: nobody
Szczepańczyk |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: decimal,
Severity: Normal | decimalfield, rounding, postgres
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Example:
{{{
from django.db import models
class Foo(models.Model):
amount = models.DecimalField(decimal_places=2, max_digits=12)
from myapp.models import Foo
f = Foo(amount='1.125')
f.save()
f.refresh_from_db()
# the issue is here
f.amount # returns Decimal('1.12') in 3.1 and Decimal('1.13') in 3.2
}}}
Executed query for 3.2:
{{{
INSERT INTO "myapp_foo" ("amount") VALUES (1.125) RETURNING
"myapp_foo"."id"
}}}
Executed query for 3.1:
{{{
INSERT INTO "myapp_foo" ("amount") VALUES ('1.12') RETURNING
"myapp_foo"."id"
}}}
Issue was reproduced only when using postgres database.
--
Ticket URL: <https://code.djangoproject.com/ticket/33232>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Michał Szczepańczyk | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: decimal, | Triage Stage:
decimalfield, rounding, postgres | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/33232#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Michał Szczepańczyk | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: decimal, | Triage Stage:
decimalfield, rounding, postgres | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Simon Charette (added)
* status: new => closed
* resolution: => invalid
Comment:
This behavior was changed in 9c5c9bd7091b60fdccc405890dc4f44a8010e954
however I believe that the current one is correct, because we longer cast
`Decimals` to strings. If you need to round differently I recommend to do
so before passing values to the field.
--
Ticket URL: <https://code.djangoproject.com/ticket/33232#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Michał Szczepańczyk | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: decimal, | Triage Stage:
decimalfield, rounding, postgres | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Michał Szczepańczyk):
That's for the response. In such case I think it would be good to mention
the change in release docs.
Another consequence of this change is that we have different results
between sqlite and postgres. When using sqlite the following query is
generated:
{{{
INSERT INTO "myapp_foo" ("amount") VALUES ('1.12')
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33232#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Michał Szczepańczyk | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: decimal, | Triage Stage:
decimalfield, rounding, postgres | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Replying to [comment:3 Michał Szczepańczyk]:
> That's for the response. In such case I think it would be good to
mention the change in release docs.
I will try to figure out how to mention it in docs.
> Another consequence of this change is that we have different results
between sqlite and postgres. When using sqlite the following query is
generated:
> {{{
> INSERT INTO "myapp_foo" ("amount") VALUES ('1.12')
> }}}
Yes but it returns `1.13` as in MySQL and Oracle.
--
Ticket URL: <https://code.djangoproject.com/ticket/33232#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Michał Szczepańczyk | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: decimal, | Triage Stage:
decimalfield, rounding, postgres | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
SQLite is also not a [https://www.sqlite.org/datatype3.html stellar
example of ambiguous data type handling as detailed in their
documentation]. Since it doesn't have a proper type to store decimal data
the ''rounding'' happens on the Django side.
--
Ticket URL: <https://code.djangoproject.com/ticket/33232#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Michał Szczepańczyk | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: decimal, | Triage Stage:
decimalfield, rounding, postgres | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Alex):
The problem is that right now if I just want to round down the value I
have to get the precision of the field from the model internals, which is
a bit ugly I think. And also this has to be done everywhere where this
field is updated. Why can't we keep everything that
`django.db.backends.utils.format_number` does, but the last line that
converts the value to string? There's a lot of flexibility that Python
`Decimal` provides that's not available in SQL.
The code I had before:
{{{
with localcontext() as ctx:
ctx.rounding = ROUND_DOWN
self.items.update(
split=Decimal("100") * split / self.number_of_items
)
}}}
The code I have to have now:
{{{
with localcontext() as ctx:
value = Decimal("100") * split / number_of_items
ctx.rounding = ROUND_DOWN
max_digits = Model.split.field.max_digits
decimal_places = Model.split.field.decimal_places
if max_digits is not None:
ctx.prec = max_digits
if decimal_places is not None:
value =
value.quantize(Decimal(1).scaleb(-decimal_places))
self.items.update(
split=value
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33232#comment:6>