Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#23680 closed Bug (invalid)

DecimalField and Postgres ORM produces incorrect SQL on filter comparison

Reported by: David Burke Owned by: nobody
Component: Database layer (models, ORM) Version: 1.6
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (2)

comment:1 by Shai Berger, 10 years ago

Resolution: invalid
Status: newclosed
>>> 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.

comment:2 by David Burke, 10 years ago

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.

Note: See TracTickets for help on using tickets.
Back to Top