[Django] #36030: Rendering decimal to SQL is incoherent and leads to bugs. It relays on str formating not type.

[Django]

#36030: Rendering decimal to SQL is incoherent and leads to bugs. It relays on str
formating not type.
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36030>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36030: Rendering decimal to SQL is incoherent and leads to bugs. It relays on str
formating not type.
-------------------------------------+-------------------------------------

Reporter: Bartłomiej Nowak | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

Which database are you using? Can you give examples of the specific
querysets and the expected results?
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:1>

[Django]

#36030: Rendering decimal to SQL is incoherent and leads to bugs. It relays on str
formating not type.
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: (none)

Type: Bug | Status: closed

Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution: needsinfo

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* resolution: => needsinfo
* status: new => closed

Comment:

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

[Django]

#36030: Rendering decimal to SQL is incoherent and leads to bugs. It relays on str
formating not type.
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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

[Django]

#36030: Rendering decimal to SQL is incoherent and leads to bugs. It relays on str
formating not type.
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: (none)

Type: Bug | Status: new

Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Bartłomiej Nowak):

* resolution: needsinfo =>
* status: closed => new


Old description:

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

New description:

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

=============

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

[Django]

#36030: Rendering decimal to SQL is incoherent and leads to bugs. It relays on str
formating not type.
-------------------------------------+-------------------------------------
Reporter: Bartłomiej Nowak | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Bartłomiej Nowak:

Old description:

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

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

--
--

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