DecimalField and Postgres ORM produces incorrect SQL on filter comparison

[David Burke]

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.

[Shai Berger]

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

[David Burke]

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.