Changes between Version 34 and Version 37 of Ticket #36030


Ignore:
Timestamp:
Nov 23, 2025, 10:50:46 AM (7 days ago)
Author:
Bob Kline
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #36030

    • Property 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
    • Property Has patch set

  • Ticket #36030 – Description

    v34 v37  
    1 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
    2 comment:18 and comment:25.
     1== Expected Behavior ==
     2When 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`.
    33
     4== Observed Behavior ==
     5When 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`.
    46
    5 Original report follows, but be aware that comment:18 and comment:25 describe this as more or less expected behavior:
    6 ----
     7== Repro Case ==
     8{{{
     9#!/usr/bin/env python3
    710
    8 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.
     11"""
     12Repro case for ticket #36030.
     13"""
    914
    10 **See examples:**
     15from decimal import Decimal
     16from django import setup
     17from django.conf import settings
     18from django.db import connection
     19from django.db.models import Value, DecimalField, IntegerField
     20from django.db.models.sql import Query
    1121
    12 `Decimal(1000.0)` --> will render as `1000` at query and will be **INT** on db level.
    13 `Decimal(1000)`  --> will render as `1000` at query and will be **INT** on db level.
    14 `Decimal("1000.0")` -> will render as `1000,0` at query and will be **NUMERIC** on db level.
    15 `models.Value(1000.0, output_field=DecimalField())` ->  will render as `1000` at query and will be **INT** on db level.
    16 `models.Value(1000.0)` (no decimal provided as above) -> will render as `1000,0` at query and will be **NUMERIC** on db level.
     22settings.configure(
     23    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}},
     24    INSTALLED_APPS=["django.contrib.contenttypes"],
     25)
    1726
    18 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.
    19 
    20 =============
    21 I am using Postgres.
    22 
    23 {{{
    24 SomeModel.objects.create(some_field_of_type_int=2)
    25 sm = SomeModel.objects.annotate(x=F("some_field_of_type_int") / Decimal(3.0)).get()
    26 sm.x # returns Decimal of 0
     27setup()
     28numerator = Value(2, output_field=IntegerField())
     29denominator = Value(Decimal("3.0"), output_field=DecimalField())
     30expression = numerator / denominator
     31compiler = connection.ops.compiler("SQLCompiler")(Query(None), connection, None)
     32sql, params = expression.resolve_expression(Query(None)).as_sql(compiler, connection)
     33with connection.cursor() as cursor:
     34    cursor.execute(f"SELECT {sql}", params)
     35    result = cursor.fetchone()[0]
     36    print("result:", result). # prints 0
    2737}}}
    2838
    29 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)
    30 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.
     39== Notes ==
     40The 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.
    3141
    32 Therefore, Django will return Decimal('0'), which I consider as Bug. This is not what anyone suppose to get.
    33 =============
     42It 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.
     43
     44It was further decided that the user documentation should ''not'' describe the remaining inconsistencies between backends.
     45
     46== Version Information ==
     47* Python 3.12.3
     48* Django 5.1.2
     49* O/S Ubuntu 24.04.3 LTS (6.8.0-88-generic x86_64)
Back to Top