#36030: SQLite backend unnecessarily applies AS NUMERIC cast when preparing
python
Decimals
-------------------------------------+-------------------------------------
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 Jacob Walls):
* summary:
Expressions that divide an integer field by a constant decimal.Decimal
returns inconsistent decimal places on SQLite
=>
SQLite backend unnecessarily applies AS NUMERIC cast when preparing
python Decimals
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.
>
> =============
> 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.
> =============
New description:
After two rounds of triage and a third round of investigation, the most
current thinking about the requirements for this ticket can be found at
comment:18 and comment:25.
Original report follows, but be aware that comment:18 and comment:25
describe this as more or less expected behavior:
----
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.
=============
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36030#comment:34>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019aa6a01e46-b7e7c4bc-db84-4291-a2e0-c42e22b37185-000000%40eu-central-1.amazonses.com.
