Opened 11 years ago
Closed 11 years ago
#22117 closed Bug (duplicate)
Filtering on an annotation returns no reults in sqlite3 when it should return results.
Reported by: | Owned by: | Martin Matusiak | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | nlsprint14 |
Cc: | Martin Matusiak | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Creating an annotation which sums through a many to many field, then filtering on that annotation produces no results when results are expected. I have attached a sample app with a test case which reproduces the bug, as well as a test case proving that the raw sql produced by the queryset indeed returns results.
To repro:
unzip repro.zip
python manage.py test repro.thing
look at repro/thing/tests.py for the code
found on: Ubuntu 12.04.4
django version: 1.6.1
pysqlite version: 2.6.0
libsqlite3-0 version: 3.7.9-2ubuntu1.1
Let me know if I can help!
Attachments (1)
Change History (10)
by , 11 years ago
comment:1 by , 11 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Version: | 1.6 → master |
Hi,
I can reproduce this issue (thanks for the testcase!) on master as well.
The test still fails on 1.5 so it doesn't appear to be a regression.
comment:2 by , 11 years ago
Cc: | added |
---|---|
Keywords: | nlsprint14 added |
Owner: | changed from | to
Status: | new → assigned |
comment:3 by , 11 years ago
For the record, I just tested it on postgres and this issue is not present so it seems to be a problem in the sqlite backend.
comment:4 by , 11 years ago
It might be related to the way instances of Decimal are formatted inside the sqlite query string. If you format an int (0) or a float (0.0), the query returns 1 row as expected, but when you format an instance of Decimal (Decimal('0')), the result set is empty.
comment:5 by , 11 years ago
Actually, I feel it is intrinsic to that query specifically, since regular filtering queries, using Decimal
and rev_typecast_decimal()
return the correct results.
comment:7 by , 11 years ago
It's most probably something to do with the "HAVING SUM" clause specifically:
>>> list(cursor.execute("SELECT * from thing_thing WHERE thing_thing.cost > (?) GROUP BY thing_thing.cost", (0,))) Out[26]: [(1, Decimal('1.23')), (2, Decimal('4.56')), (3, Decimal('7.89'))] >>> list(cursor.execute("SELECT * from thing_thing WHERE thing_thing.cost > (?) GROUP BY thing_thing.cost", (Decimal(0),))) Out[27]: [(1, Decimal('1.23')), (2, Decimal('4.56')), (3, Decimal('7.89'))] >>> list(cursor.execute("SELECT * from thing_thing WHERE thing_thing.cost > (?) GROUP BY thing_thing.cost", (Decimal('0'),))) Out[28]: [(1, Decimal('1.23')), (2, Decimal('4.56')), (3, Decimal('7.89'))] >>> list(cursor.execute("SELECT * from thing_thing GROUP BY thing_thing.cost HAVING SUM(thing_thing.cost) > (?)", (0,))) Out[24]: [(1, Decimal('1.23')), (2, Decimal('4.56')), (3, Decimal('7.89'))] >>> list(cursor.execute("SELECT * from thing_thing GROUP BY thing_thing.cost HAVING SUM(thing_thing.cost) > (?)", (Decimal('0.0'),))) Out[23]: []
comment:9 by , 11 years ago
Resolution: | → duplicate |
---|---|
Status: | assigned → closed |
Confirmed as duplicate of #18247.
sample app with test case to repro bug