[Django] #23680: DecimalField and Postgres ORM produces incorrect SQL on filter comparison
Django
----------------------------------------------+--------------------
Reporter: bufke | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Create a DecimalField like
{{{
max_grade = models.DecimalField(max_digits=5, decimal_places=2)
}}}
Set the field. Run a filter like
{{{
In [11]: GradeScaleRule.objects.get(id=1).max_grade
Out[11]: Decimal('59.99')
In [12]: GradeScaleRule.objects.filter(max_grade=59.99)
Out[12]: []
In [13]: GradeScaleRule.objects.filter(max_grade=Decimal(59.99))
Out[13]: []
}}}
Here is the sql generated.
{{{
SELECT "sis_gradescalerule"."id", "sis_gradescalerule"."min_grade",
"sis_gradescalerule"."max_grade", "sis_gradescalerule"."letter_grade",
"sis_gradescalerule"."numeric_scale",
"sis_gradescalerule"."grade_scale_id" FROM "sis_gradescalerule" WHERE
"sis_gradescalerule"."max_grade" =
59.99000000000000198951966012828052043914794921875
}}}
Here is a psql statement showing the 59.99 max_grade is saved correctly.
{{{
postgres=# select * from sis_gradescalerule;
id | min_grade | max_grade | letter_grade | numeric_scale |
grade_scale_id
----+-----------+-----------+--------------+---------------+----------------
1 | 50.00 | 59.99 | F | 1.00 |
1
}}}
The ORM code works fine with sqlite.
--
Ticket URL: <https://code.djangoproject.com/ticket/23680>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: bufke | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.6
(models, ORM) | Resolution: invalid
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by shaib):
* status: new => closed
* needs_better_patch: => 0
* resolution: => invalid
* needs_tests: => 0
* needs_docs: => 0
Comment:
{{{#!python
>>> from decimal import Decimal as D
>>> D(59.99)
Decimal('59.99000000000000198951966012828052043914794921875')
>>> D('59.99')
Decimal('59.99')
>>>
}}}
I don't think there's anything wrong with the ORM here. Your problem is
that 59.99 cannot be represented as a float. Feel free to reopen if you
have a justification.
--
Ticket URL: <https://code.djangoproject.com/ticket/23680#comment:1>
Django
Reporter: bufke | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.6
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by bufke):
I agree - here is how I fixed it in case someone finds this searching.
`GradeScaleRule.objects.filter(max_grade=Decimal('59.99'))`
My issue was that `59.99` is a float. Then converted to a Decimal. I just
expected the Decimal precision to be (5,2) but there is no guarantee for
this to be so.
--
Ticket URL: <https://code.djangoproject.com/ticket/23680#comment:2>