[Django] #18247: filter and Decimal equality doesn't work
Django
----------------------------------------------+----------------------------
Reporter: elmopl@… | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords: decimal filter
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
When I run
{{{
$ python manage.py version
1.4
$ python manage.py shell
[...]
>>> for r in StoreBill.objects.annotate( amount = Sum('parts__amount')
).filter( date = '2012-03-28' ): r.amount
...
Decimal('1.05')
>>> for r in StoreBill.objects.annotate( amount = Sum('parts__amount')
).filter( date = '2012-03-28', amount = Decimal("1.05") ): a = r.amount
...
>>> print connection.queries[-1]
{'time': '0.002', 'sql': u'SELECT "spendings_storebill"."id",
"spendings_storebill"."name", "spendings_storebill"."date",
SUM("spendings_cost"."amount") AS "amount" FROM "spendings_storebill" LEFT
OUTER JOIN "spendings_storebillpart" ON ("spendings_storebill"."id" =
"spendings_storebillpart"."bill_id") LEFT OUTER JOIN "spendings_cost" ON
("spendings_storebillpart"."cost_ptr_id" = "spendings_cost"."id") WHERE
("spendings_storebill"."date" = 2012-03-28 ) GROUP BY
"spendings_storebill"."id", "spendings_storebill"."name",
"spendings_storebill"."date" HAVING SUM("spendings_cost"."amount") = 1.05
'}
}}}
As you can see from first query there is a result matching second one, yet
I get no results for latter. If I run the generated query I get expected
result (left only "amount" column):
{{{
sqlite> SELECT SUM("spendings_cost"."amount") AS "amount" FROM
"spendings_storebill" LEFT OUTER JOIN "spendings_storebillpart" ON
("spendings_storebill"."id" = "spendings_storebillpart"."bill_id") LEFT
OUTER JOIN "spendings_cost" ON ("spendings_storebillpart"."cost_ptr_id" =
"spendings_cost"."id") GROUP BY "spendings_storebill"."id",
"spendings_storebill"."name", "spendings_storebill"."date" HAVING
SUM("spendings_cost"."amount") = 1.05 LIMIT 21;
1.05
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18247>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: elmopl@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: decimal filter | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by zbyte64):
* cc: zbyte64 (added)
* needs_docs: => 0
* type: Uncategorized => Bug
* needs_tests: => 0
* needs_better_patch: => 0
Comment:
I believe I have experienced the same issue and it only seems to apply to
Django 1.4 with sqlite.
{{{#!python
from django.db import models
class Transaction(models.Model):
tag = models.CharField(max_length=5)
amount = models.DecimalField(max_digits=19, decimal_places=4)
def test_transaction():
Transaction.objects.all().delete()
Transaction.objects.create(amount=5, tag='a')
Transaction.objects.create(amount=4, tag='a')
Transaction.objects.create(amount=1, tag='b')
Transaction.objects.create(amount='1.5', tag='b')
Transaction.objects.create(amount=1, tag='c')
qs =
Transaction.objects.values('tag').annotate(sumamount=models.Sum('amount'))
print qs #displays 3 results
qs =
Transaction.objects.values('tag').annotate(sumamount=models.Sum('amount')).filter(sumamount__gt=3)
print qs #displays 0 results when using sqlite + Django 1.4, displays
1 result when using mysql or Django 1.3.1
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:1>
Django
Reporter: elmopl@… | Owner: nobody
Type: Bug | Status: closed
Keywords: decimal filter | Unreviewed
Has patch: 0 | Needs documentation: 0
Changes (by anonymous):
* status: new => closed
* resolution: => invalid
Comment:
I was able to replicate that on just sqlite3 (C API), so it is not a bug
with Django or driver.
Marking as invalid, as the bug is outside Django itself.
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:2>
Django
Reporter: elmopl@… | Owner: nobody
Type: Bug | Status: reopened
(models, ORM) | Resolution:
Keywords: decimal filter | Unreviewed
Has patch: 0 | Needs documentation: 0
Changes (by fengb):
* status: closed => reopened
* resolution: invalid =>
Comment:
Per http://article.gmane.org/gmane.comp.db.sqlite.general/75696 , SUM
values should not have affinity so typecasting Decimal into a string will
never return real values.
I think changing the typecast of Decimal into float is "good enough".
Internally, sqlite3 already represents these values as floats, and it
would fix this bug. Otherwise, we would need separate casting engines
depending on whether SUM is used.
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:3>
Django
Reporter: elmopl@… | Owner: nobody
Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Keywords: decimal filter | decision needed
Changes (by aaugustin):
* stage: Unreviewed => Design decision needed
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:4>
Django
Reporter: elmopl@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Keywords: decimal filter | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by aaugustin):
* stage: Design decision needed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:6>
Django
Reporter: elmopl@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: decimal filter | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by zr):
#22117 was just closed as a duplicate of this ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:7>
Django
Reporter: elmopl@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: decimal filter | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: dylan@… (added)
* version: 1.4 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:8>
Django
Reporter: elmopl@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: decimal filter | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by dylan@…):
Replying to [comment:8 dylan@…]:
As noted in #22117 this is still an issue:
https://code.djangoproject.com/ticket/22117#comment:1
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:9>
Django
Reporter: elmopl@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: decimal filter | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by shai):
#22128 asked for the limitation to be documented; I closed it as a
duplicate of this. If this bug is wontfix'd, we should then add
appropriate warnings on the SQLite-specific notes.
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:10>
Django
Reporter: elmopl@… | Owner: NEOatNHNG
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Keywords: decimal filter | Triage Stage: Accepted
annotate having |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by NEOatNHNG):
* cc: NEOatNHNG (added)
* keywords: decimal filter => decimal filter annotate having
* status: new => assigned
* owner: nobody => NEOatNHNG
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:11>
Django
Reporter: elmopl@… | Owner: NEOatNHNG
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: decimal filter | Triage Stage: Accepted
annotate having |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by NEOatNHNG):
* has_patch: 0 => 1
Comment:
On sqlite the SUM() of a decimal column doesn't have a NUMERIC type so
when comparing it to a string literal (which a Decimal gets converted to
in django) it is not compared as NUMERIC as may be expected but as
something else (maybe string?).
I added a CAST to NUMERIC type when using a SQL function that is expected
to return a Decimal.
All tests pass under SQLite.
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: elmopl@… | Owner: NEOatNHNG
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
annotate having | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: elmopl@… | Owner: NEOatNHNG
Component: Database layer | Version: master
(models, ORM) |
Keywords: decimal filter | Triage Stage: Ready for
annotate having | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"3bbaf84d6533fb61ac0038f2bbe52ee0d7b4fd10" 3bbaf84d]:
{{{
#!CommitTicketReference repository=""
revision="3bbaf84d6533fb61ac0038f2bbe52ee0d7b4fd10"
Fixed #18247 -- Added cast to NUMERIC for Decimals on sqlite
On sqlite the SUM() of a decimal column doesn't have a NUMERIC type so
when comparing it to a string literal (which a Decimal gets converted to
in Django) it is not compared as expected.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: elmopl@… | Owner: NEOatNHNG
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: decimal filter | Triage Stage: Ready for
annotate having | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by suutari-ai):
Shouldn't this be backported to the current LTS release (1.8) too? The
commit seems to cherry-pick cleanly to stable/1.8.x branch.
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:15>
Django
-------------------------------------+-------------------------------------
| Tänzer
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: decimal filter | Triage Stage: Ready for
annotate having | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"068d75688f28f9a1530b771ae1d625f41e98cd82" 068d7568]:
{{{
#!CommitTicketReference repository=""
revision="068d75688f28f9a1530b771ae1d625f41e98cd82"
Refs #18247 -- Fixed SQLite QuerySet filtering on decimal result of Least
and Greatest.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: elmopl@… | Owner: Michael
| Tänzer
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: decimal filter | Triage Stage: Ready for
annotate having | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"8484cf4cd0a6d3553f6fc1d62fa6dc04f04624f0" 8484cf4c]:
{{{
#!CommitTicketReference repository=""
revision="8484cf4cd0a6d3553f6fc1d62fa6dc04f04624f0"
[1.11.x] Refs #18247 -- Fixed SQLite QuerySet filtering on decimal result
of Least and Greatest.
Backport of 068d75688f28f9a1530b771ae1d625f41e98cd82 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:17>
Django
-------------------------------------+-------------------------------------
Reporter: elmopl@… | Owner: Michael
| Tänzer
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: decimal filter | Triage Stage: Ready for
annotate having | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"c3c6c92d769d44a98299c462c48a9599c0172e91" c3c6c92d]:
{{{
#!CommitTicketReference repository=""
revision="c3c6c92d769d44a98299c462c48a9599c0172e91"
Refs #18247 -- Fixed filtering on
CombinedExpression(output_field=DecimalField()) annotation on SQLite.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18247#comment:18>