#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: division | Triage Stage: Accepted
decimalfield |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Bob Kline):
Good morning, Jacob. Thanks for the follow-up and the patch, and thanks
for the notes about Decimal(float) and precision — that behavior from the
decimal module is definitely good to keep in mind. It is indeed true that
using a floating-point value for a Decimal constructor can produce
different results for ''addition'' operations than using a string with
trailing digits (even if those trailing digits are all zeros):
{{{
>>> from decimal import Decimal as D
>>> D(2.000) + D(3.00000)
Decimal('5')
>>> D(2.000) + D("3.00000")
Decimal('5.00000')
}}}
I think there’s a slightly different (and narrower) concern at the heart
of this ticket, and I want to try to spell it out explicitly to make sure
we’re all discussing the same thing.
In Python, division by a Decimal value always produces a Decimal result,
regardless of how that Decimal was constructed:
{{{
from decimal import Decimal as D
2 / D(3) # Decimal('0.6666…')
2 / D(3.0) # Decimal('0.6666…')
2 / D("3") # Decimal('0.6666…')
2 / D("3.0000000") # Decimal('0.6666…')
}}}
That is: even though D(3.0) and D("3.0") differ in how they’re created,
the operation 2 / … is always carried out in Decimal arithmetic and
respects the current decimal context.
What the ticket is highlighting is that the ORM currently doesn’t preserve
that invariant:
* For some backends / combinations, F("int_field") / Decimal(3) ends up
being compiled to a SQL expression where both operands are seen as
integers by the database (e.g. int / int in PostgreSQL), so you get 0.
* For others, the same Django expression is compiled so that the database
sees a NUMERIC operand and performs decimal division, yielding something
like 0.6666….
In other words, the behavior of:
{{{
SomeModel.objects.annotate(
x=F("some_field_of_type_int") / Decimal(3.0),
).get().x
}}}
currently depends on:
* how that Decimal constant is rendered into SQL (e.g. 3 vs 3.0 vs a
NUMERIC cast), and
* which backend you’re using (e.g. integer division vs decimal division
rules),
even though the Python-level expression is the same.
That’s the surprising part from a user’s perspective: the same Django
expression can mean “integer division” on some backends and “decimal
division” on others, whereas plain Python 2 / Decimal(3) is always decimal
division.
Your point about Decimal(float_var) being a poor choice for controlling
precision is absolutely correct in general, but here the main problem
isn’t the number of decimal places encoded in the literal — it’s that:
the database no longer “knows” that one operand is a decimal/numeric type
at all, so it defaults to truncating integer division, and
that loss of type information happens in some cases but not others,
depending on how the constant is constructed and which backend is in use.
So the behavior that feels “buggy” is:
Same ORM expression, different result across official backends.
Does this explanation help?
Same conceptual intent (“divide by a decimal constant”), but the database
sees INT / INT in some cases because the decimal-ness of the constant
wasn’t preserved in the generated SQL.
From a framework-user point of view, the ideal outcome would be:
For expressions like F("int_field") / Decimal(...) (and analogous
arithmetic), Django consistently ensures that the database sees a
NUMERIC/DECIMAL operand, so the operation is always done in decimal
arithmetic; or
If that’s not feasible, the limitation and backend differences are clearly
documented.
I’m very much in favor of the ongoing work to address the SQLite
discrepancy. I just want to make sure the underlying cross-backend
consistency concern (integer vs decimal division when a Decimal constant
is involved) doesn’t get lost in the focus on the construction of Decimal
from floats or on SQLite alone.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:24>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019aa1532400-696313eb-06a8-4154-bfcc-58cb20353fff-000000%40eu-central-1.amazonses.com.
