Re: [Django] #36030: Expressions that divide an integer field by a constant decimal.Decimal returns inconsistent decimal places on SQLite (was: Expressions that divide an integer field by a constant decimal.Decimal returns inconsistent decimal places on PostgreSQL)

[Django]

#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
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_docs: 1 => 0
* summary:
Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on PostgreSQL
=>
Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite

Comment:

Some notes on the original report

Using `Decimal(3.0)` (or `Decimal(float_var)`) means you lose control over
the number of digits of precision:

[https://docs.python.org/3/library/decimal.html#decimal.Decimal python
docs]:
> If value is a float, the binary floating-point value is losslessly
converted to its exact decimal equivalent. This conversion can often
require 53 or more digits of precision. For example, Decimal(float('1.1'))
converts to
Decimal('1.100000000000000088817841970012523233890533447265625').

`Decimal(3.0)` requires no digits of precision:
{{{#!py
>>> Decimal(3.0)
Decimal('3')
}}}

Results are consistent if you provide a string to the constructor, e.g
`Decimal("3.0")`.
----
Sarah did reframe the issue when accepting (and retitled the issue to
match) in comment:7. I think we can continue in the same ticket: I posted
a solution on the PR that focuses on the SQLite discrepancy:

{{{#!diff
diff --git a/django/db/models/expressions.py
b/django/db/models/expressions.py
index 0d47366d2c..00c0b751a2 100644
--- a/django/db/models/expressions.py
+++ b/django/db/models/expressions.py
@@ -27,11 +27,12 @@ class SQLiteNumericMixin:

def as_sqlite(self, compiler, connection, **extra_context):
sql, params = self.as_sql(compiler, connection, **extra_context)
- try:
- if self.output_field.get_internal_type() == "DecimalField":
- sql = "(CAST(%s AS NUMERIC))" % sql
- except FieldError:
- pass
+ if not(isinstance(self, Value) and isinstance(self.value,
Decimal)):
+ try:
+ if self.output_field.get_internal_type() ==
"DecimalField":
+ sql = "(CAST(%s AS NUMERIC))" % sql
+ except FieldError:
+ pass
return sql, params
}}}

Please unset "patch needs improvement" when ready for a review.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:18>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.