SQLite backend: division with Decimal("x.0") operand discards fractional portion of result

[Bartłomiej Nowak]

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

[Tim Graham]

Which database are you using? Can you give examples of the specific querysets and the expected results?

[Natalia Bidart]

Closing as needsinfo, Bartłomiej Nowak please reopen when you can provide further details as requested. A way to reproduce would be crucial. Thank you!

[Bartłomiej Nowak]

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

[Sarah Boyce]

I was able to replicate. Note that when using Decimal("3.0") it returns Decimal("0.66666666666666666666")
But when doing the same thing on SQLite, I consistently get Decimal("0")
I'm not sure what this _should_ do but I agree this is confusing and requires more investigation

Thank you for the ticket

[Gregory Mariani]

I can reproduce too with sqlite3 and postgre17. I'll have a look

[Gregory Mariani]

Need help to create the tests. Have a patch to check

[Sarah Boyce]

Hi Gregory, a reviewer must mark the ticket as "Ready for checkin" 🙂

[Jacob Walls]

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:

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

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

django/db/models/expressions.py

@@ -27 +27 @@
 
     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.

[Bob Kline]

Wait—are we saying that the bug only affected the SQLite back end? I'm pretty sure I was able to confirm that the unpatched code failed both on SQLite and with PostgreSQL (working correctly on the other supported databases). And the creator of this ticket was running PostgreSQL when he encountered the problem.

[Jacob Walls]

Yep, did you see the first part of my reply? The behavior reported by OP is user error, and changing it would be backward incompatible as far as I see.

[Jacob Walls]

If there are additional failing test cases on the PR that are more elucidating than what I'm looking at from the original report, let me know--I'm happy to look closer.

[Bob Kline]

Thanks for your replies, Jacob. I'll take another closer look at all of this tomorrow morning.

[Gregory Mariani]

I didn't get ping on this thread sorry for my late answer. During my investigation I get error for both SQLite and Postgre that's right.

Another point, like I said on github, it could be useful to integrate "earlier" the Value type in the code execution. It probably will be a breaking change but we could easily simplify mostly of the functions which have "specials" calculation due to their type (decimal, duration, others...).
That's all for my report to the ORM team

[Bob Kline]

Good morning, Jacob. Thanks for the follow-up and the patch, and thanks for the notes about Decimal(float) and precision — that behavior from the decimal module is definitely good to keep in mind. It is indeed true that using a floating-point value for a Decimal constructor can produce different results for addition operations than using a string with trailing digits (even if those trailing digits are all zeros):

>>> from decimal import Decimal as D
>>> D(2.000) + D(3.00000)
Decimal('5')
>>> D(2.000) + D("3.00000")
Decimal('5.00000')

I think there’s a slightly different (and narrower) concern at the heart of this ticket, and I want to try to spell it out explicitly to make sure we’re all discussing the same thing.

In Python, division by a Decimal value always produces a Decimal result, regardless of how that Decimal was constructed:

from decimal import Decimal as D

2 / D(3)           # Decimal('0.6666…')
2 / D(3.0)         # Decimal('0.6666…')
2 / D("3")         # Decimal('0.6666…')
2 / D("3.0000000") # Decimal('0.6666…')

That is: even though D(3.0) and D("3.0") differ in how they’re created, the operation 2 / … is always carried out in Decimal arithmetic and respects the current decimal context.

What the ticket is highlighting is that the ORM currently doesn’t preserve that invariant:

• For some backends / combinations, F("int_field") / Decimal(3) ends up being compiled to a SQL expression where both operands are seen as integers by the database (e.g. int / int in PostgreSQL), so you get 0.
• For others, the same Django expression is compiled so that the database sees a NUMERIC operand and performs decimal division, yielding something like 0.6666….

In other words, the behavior of:

SomeModel.objects.annotate(
    x=F("some_field_of_type_int") / Decimal(3.0),
).get().x

currently depends on:

• how that Decimal constant is rendered into SQL (e.g. 3 vs 3.0 vs a NUMERIC cast), and
• which backend you’re using (e.g. integer division vs decimal division rules),

even though the Python-level expression is the same.

That’s the surprising part from a user’s perspective: the same Django expression can mean “integer division” on some backends and “decimal division” on others, whereas plain Python 2 / Decimal(3) is always decimal division.

Your point about Decimal(float_var) being a poor choice for controlling precision is absolutely correct in general, but here the main problem isn’t the number of decimal places encoded in the literal — it’s that:

• the database no longer “knows” that one operand is a decimal/numeric type at all, so it defaults to truncating integer division, and
• that loss of type information happens in some cases but not others, depending on how the constant is constructed and which backend is in use.

So the behavior that feels “buggy” is:

• Same ORM expression, different result across official backends.
• Same conceptual intent (“divide by a decimal constant”), but the database sees INT / INT in some cases because the decimal-ness of the constant wasn’t preserved in the generated SQL.

From a framework-user point of view, the ideal outcome would be:

• For expressions like F("int_field") / Decimal(...) (and analogous arithmetic), Django consistently ensures that the database sees a NUMERIC/DECIMAL operand, so the operation is always done in decimal arithmetic; or
• If that’s not feasible, the limitation and backend differences are clearly documented.

I’m very much in favor of the ongoing work to address the SQLite discrepancy. I just want to make sure the underlying cross-backend consistency concern (integer vs decimal division when a Decimal constant is involved) doesn’t get lost in the focus on the construction of Decimal from floats or on SQLite alone.

Does this explanation help?

[Jacob Walls]

I was using some shorthand in my reply, so it's totally fair to ask for clarification (happy to), but it looks like you echoed back LLM output mostly verbatim. I can do that myself; it doesn't need to be posted on Trac. I would have appreciated you condensing your reply and writing it in your own voice.

What the ticket is highlighting is that the ORM currently doesn’t preserve that invariant:

Actually, it's not the ORM, it's the database adapter.

Django sends Decimal objects to psycopg2. For the query Book.objects.filter(price=42).annotate(new_price=F("pages") / Decimal(28)), the ORM sends Decimal objects to the adapter:

(Pdb) params
(Decimal('28'), Decimal('42'))
(Pdb) sql
'SELECT ("annotations_book"."pages" / %s) AS "new_price" FROM "annotations_book" WHERE "annotations_book"."price" = %s LIMIT 21'

... and psycopg2 makes the reasonable choice to mogrify Decimal(28) to "28". If that is undesired, there are multiple ways to fix this, including adding an explicit cast, or by using string formatting to preserve the precision.

I don't think Decimal(28) or Decimal(float_var) is standard usage that we should memorialize in the docs. We don't document all database quirks. This ticket should be enough. Hope that helps.

[Gregory Mariani]

Good evening Jacob,

I wanted to ask if I should remove the documentation part from this PR and unset the "needs improvement" label, or if everything is fine as it is?
I’m a little bit confused about what’s left to do and what is expected on my side.

Thank you for your guidance!

[Jacob Walls]

Thanks for the follow-up Gregory.

In my view, the original issue was worthy of discussion, but was never accepted. After investigation, I'm proposing to treat it as invalid. Another triager will let me know if I got this part wrong.

Sarah reframed and accepted this ticket for investigation specifically regarding the SQLite behavior difference in comment:6. I debated whether to close this issue and start a new issue for that, but instead I decided to continue here since that's how Sarah originally triaged it.

So this ticket should stay in "needs improvement" until we have a PR with the correct test cases and fix to match the accepted issue. The currently linked PR has test cases that I don't think are correct (targeting the original report that was not accepted).

[Jacob Walls]

(Unmarking has patch is probably clearest.)

[Bob Kline]

Hi, Jacob. You are right that I used an LLM, but not in the way you think. I created a response by hand (I really am that verbose), then fed it to the agent with the request to tweak it so that it was as polite and non-threatening as possible. The result was indeed slightly modified, but pretty much the same as what I fed it. If anything, it scaled back my wordiness. 😉

[Jacob Walls]

Sorry if my response was off-putting. I appreciate that clarification! I can only represent how things feel to me. In the kitchen, too much fennel or dill or spearmint can overpower a dish. Specific LLM turns of phrase are like that for me. I'm only one data point, but I decided to mention it in case you encounter it again :-)

[Gregory Mariani]

Ok, I'm done with this ticket so. Thank you

[Bob Kline]

Same. When I came across this ticket, it was marked as Accepted, with the description of the bug pretty much unchanged from what the reporter wrote. My understanding of what that implied was clearly mistaken. Time for me to step away. ✌️

[VIZZARD-X]

PR: ​https://github.com/django/django/pull/20294

[VIZZARD-X]

Recreated my fork and restored the branch. New PR: ​https://github.com/django/django/pull/#20309

Patch content is unchanged and still valid.

[Mariusz Felisiak]

​PR

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