Opened 8 weeks ago

Last modified 4 weeks ago

#36030 assigned Bug

Expressions that divide an integer field by a constant decimal.Decimal returns inconsistent decimal places on PostgreSQL

Reported by: Bartłomiej Nowak Owned by: Gregory Mariani
Component: Database layer (models, ORM) Version: 5.1
Severity: Normal Keywords:
Cc: Bartłomiej Nowak Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no
Pull Requests:19004, 18995 unmerged, 19003 unmerged, 19000 unmerged, 18963 unmerged, 18962 unmerged, 18961 unmerged

Description (last modified by Bartłomiej Nowak)

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.

See examples:

Decimal(1000.0) --> will render as 1000 at query and will be INT on db level.
Decimal(1000) --> will render as 1000 at query and will be INT on db level.
Decimal("1000.0") -> will render as 1000,0 at query and will be NUMERIC on db level.
models.Value(1000.0, output_field=DecimalField()) -> will render as 1000 at query and will be INT on db level.
models.Value(1000.0) (no decimal provided as above) -> will render as 1000,0 at query and will be NUMERIC on db level.

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.

=============
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 Decimal of 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.
=============

According to the ticket's flags, the next step(s) to move this issue forward are:

  • For anyone except the patch author to review the patch using the patch review checklist and either mark the ticket as "Ready for checkin" if everything looks good, or leave comments for improvement and mark the ticket as "Patch needs improvement".

Change History (14)

comment:1 by Tim Graham, 8 weeks ago

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

comment:2 by Natalia Bidart, 8 weeks ago

Resolution: needsinfo
Status: newclosed

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!

comment:3 by Bartłomiej Nowak, 8 weeks ago

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.

comment:4 by Bartłomiej Nowak, 8 weeks ago

Description: modified (diff)
Resolution: needsinfo
Status: closednew

comment:5 by Bartłomiej Nowak, 8 weeks ago

Description: modified (diff)

comment:6 by Sarah Boyce, 8 weeks ago

Summary: Rendering decimal to SQL is incoherent and leads to bugs. It relays on str formating not type.Expressions that divide an integer field by a constant decimal.Decimal returns no inconsistent decimal places on PostgreSQL
Triage Stage: UnreviewedAccepted

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

Last edited 8 weeks ago by Sarah Boyce (previous) (diff)

comment:7 by Sarah Boyce, 8 weeks ago

Summary: Expressions that divide an integer field by a constant decimal.Decimal returns no inconsistent decimal places on PostgreSQLExpressions that divide an integer field by a constant decimal.Decimal returns inconsistent decimal places on PostgreSQL

comment:8 by Gregory Mariani, 8 weeks ago

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

comment:9 by Gregory Mariani, 8 weeks ago

Has patch: set
Needs tests: set

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

comment:10 by Gregory Mariani, 8 weeks ago

Has patch: unset
Needs tests: unset

comment:11 by Gregory Mariani, 8 weeks ago

Has patch: set

in reply to:  9 comment:12 by Natalia Bidart, 7 weeks ago

Needs documentation: set
Needs tests: set
Owner: set to Gregory Mariani
Patch needs improvement: set
Status: newassigned

comment:13 by Gregory Mariani, 4 weeks ago

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:14 by Sarah Boyce, 4 weeks ago

Triage Stage: Ready for checkinAccepted

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

Note: See TracTickets for help on using tickets.
Back to Top