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

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

[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" 🙂