Re: [Django] #36030: SQLite backend: division with Decimal("x.0") operand and integer operand fails to preserve precision (was: SQLite backend unnecessarily applies AS NUMERIC cast when preparing python Decimals)

[Django]

#36030: SQLite backend: division with Decimal("x.0") operand and integer operand
fails to preserve precision
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: (none)
Type: Bug | Status: new
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: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Bob Kline):

* summary:
SQLite backend unnecessarily applies AS NUMERIC cast when preparing
python Decimals
=>
SQLite backend: division with Decimal("x.0") operand and integer
operand fails to preserve precision


Old description:

> After two rounds of triage and a third round of investigation, the most
> current thinking about the requirements for this ticket can be found at
> comment:18 and comment:25.
>

> Original report follows, but be aware that comment:18 and comment:25
> describe this as more or less expected behavior:
> ----
>
> When I am using Decimal at Python level, I expect to use numeric type on
> database level. But it seems to depend on string formatting of decimal
> itself instead of type of object.
>
> **See examples:**
>
> `Decimal(1000.0)` --> will render as `1000` at query and will be **INT**
> on db level.
> `Decimal(1000)` --> will render as `1000` at query and will be **INT**
> on db level.
> `Decimal("1000.0")` -> will render as `1000,0` at query and will be
> **NUMERIC** on db level.
> `models.Value(1000.0, output_field=DecimalField())` -> will render as
> `1000` at query and will be **INT** on db level.
> `models.Value(1000.0)` (no decimal provided as above) -> will render as
> `1000,0` at query and will be **NUMERIC** on db level.
>
> It leads to bugs, cuz at DB LVL, INT / INT is also INT (2/3 = 0), and I
> doubt anyone who provides decimal there, excepts that behavior.
>
> =============
> I am using Postgres.
>
> {{{
> SomeModel.objects.create(some_field_of_type_int=2)
> sm = SomeModel.objects.annotate(x=F("some_field_of_type_int") /
> Decimal(3.0)).get()
> sm.x # returns Decimal of 0
> }}}
>
> It will render Decimal of 3.0 to the query as 3 (INT). Because str(...)
> from Decimal(3.0) returns 3. (See cases at description)
> At python is not a problem, but at database it is, cus it breaks types.
> Calculation of two INTs at postgres, will return int as well, which is in
> this case 0, instead of 0.6666, which database would produce, if Django
> would render 3.0 instead of 3.
>
> Therefore, Django will return Decimal('0'), which I consider as Bug. This
> is not what anyone suppose to get.
> =============

New description:

== Expected Behavior ==
When division is performed in Django, and at least one of the operands is
a `decimal.Decimal` value created using the string constructor with at
least one digit following the decimal point (for example,
`Decimal("3.0")`), the precision of the result should be preserved. For
example, `2 / Decimal("3.0")` should produce a value close to `0.6667`.

== Observed Behavior ==
When such a division is performed using the SQLite backend and one of the
operands is an integer, integer ("floor") division is performed unless the
decimal value has a non-zero fractional part. For example, `2 /
Decimal("3.1")` produces `0.64516129032258064516`, but `2 /
Decimal("3.0")` produces `0`.

== Repro Case ==
{{{
#!/usr/bin/env python3

"""
Repro case for ticket #36030.
"""

from decimal import Decimal
from django import setup
from django.conf import settings
from django.db import connection
from django.db.models import Value, DecimalField, IntegerField
from django.db.models.sql import Query

settings.configure(
DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME":
":memory:"}},
INSTALLED_APPS=["django.contrib.contenttypes"],
)

setup()
numerator = Value(2, output_field=IntegerField())
denominator = Value(Decimal("3.0"), output_field=DecimalField())
expression = numerator / denominator
compiler = connection.ops.compiler("SQLCompiler")(Query(None), connection,
None)
sql, params = expression.resolve_expression(Query(None)).as_sql(compiler,
connection)
with connection.cursor() as cursor:
cursor.execute(f"SELECT {sql}", params)
result = cursor.fetchone()[0]
print("result:", result). # prints 0
}}}

== Notes ==
The original ticket reported that division using PostgreSQL as the backend
did not consistently preserve precision when the denominator was a
`Decimal` value. Further investigation confirmed that the same problem
arises with SQLite, and that the inconsistent behavior is observed whether
the `Decimal` operand is the numerator or the denominator. Testing showed
that the other three officially supported database backends all preserve
decimal precision in much the same way as native Python division mixing
integer and decimal operands.

It has been decided that Django does not need to enforce consistent
division behavior across all backends, as long as there is a way to force
preservation of precision. With PostgreSQL, precision is preserved if the
`Decimal` value is created with the string constructor and includes at
least one digit after the decimal point (e.g., `Decimal("3.0")`). With
SQLite, even `Decimal("3.0")` fails to preserve precision. Therefore, the
scope of this ticket has been narrowed to address only the behavior of the
SQLite driver.

It was further decided that the user documentation should ''not'' describe
the remaining inconsistencies between backends.

== Version Information ==
* Python 3.12.3
* Django 5.1.2
* O/S Ubuntu 24.04.3 LTS (6.8.0-88-generic x86_64)

--
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:37>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.