Re: [Django] #36030: Expressions that divide an integer field by a constant decimal.Decimal returns inconsistent decimal places on SQLite

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:19>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:20>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:21>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Bob Kline):

Thanks for your replies, Jacob. I'll take another closer look at all of
this tomorrow morning.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:22>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:23>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.

Does this explanation help?

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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:24>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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:

{{{#!py
(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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:25>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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!
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:26>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
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: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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:7. 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).
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:27>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: division | Triage Stage: Accepted
decimalfield |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* has_patch: 1 => 0
* needs_better_patch: 1 => 0

Comment:

(Unmarking has patch is probably clearest.)
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:28>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: division | Triage Stage: Accepted
decimalfield |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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. 😉
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:29>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: Gregory
| Mariani
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: division | Triage Stage: Accepted
decimalfield |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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 :-)
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:30>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------

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: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Gregory Mariani):

* owner: Gregory Mariani => (none)
* status: assigned => new

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

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
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: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Gregory Mariani):

Ok, I'm done with this ticket so. Thank you
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:32>

[Django]

#36030: Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
-------------------------------------+-------------------------------------
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: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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. ✌️
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:33>