Re: [Django] #36030: SQLite backend: division with Decimal("x.0") operand discards fractional portion of result

[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
-------------------------------------+-------------------------------------
Comment (by VIZZARD-X):

Update: The patch for skipping the unnecessary CAST(... AS NUMERIC) on
literal Value(Decimal(...)) expressions for SQLite is now submitted in
PR: https://github.com/django/django/pull/20309

All unrelated backend tests pass. The remaining failures in the Windows +
SQLite + Python 3.14 jobs appear to come from areas outside this change
(duration functions and form constraint validation), and not from the
SQLiteNumericMixin.as_sqlite() logic or the regression test added for this
ticket.

If needed, I can open a separate ticket to track those unrelated
Windows/SQLite issues.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:42>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[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
-------------------------------------+-------------------------------------

Description changed by Bob Kline:

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

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:43>

[Django]

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 ==

{{{#!python

Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:44>