Re: [Django] #36030: SQLite backend: division with Decimal("x.0") operand discards fractional portion of result (was: SQLite backend: division with Decimal("x.0") operand and integer operand fails to preserve precision)

[Django]

#36030: SQLite backend: division with Decimal("x.0") operand discards fractional
portion of result
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: VIZZARD-X
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: division | Triage Stage: Accepted
decimalfield sqlite |
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Bob Kline):

* summary:
SQLite backend: division with Decimal("x.0") operand and integer
operand fails to preserve precision
=>
SQLite backend: division with Decimal("x.0") operand discards
fractional portion of result


Old 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)

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 fractional portion of the result should be
retained. 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, 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). # -> result: 0
}}}

== Notes ==
This ticket originally 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 the fractional portion of the result. With PostgreSQL,
this is achieved 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
the fractional portion of the result of the division operation. Therefore,
the scope of this ticket has been narrowed to address only the behavior of
the SQLite driver.

Note that the [https://docs.djangoproject.com/en/5.2/ref/databases
/#sqlite-decimal-handling Django documentation for decimal handling using
the SQLite backend] says that for this backend "Decimal values are
internally converted to the `REAL` data type."

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:41>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.