[Django] #22117: Filtering on an annotation returns no reults in sqlite3 when it should return results.
Django
return results.
----------------------------------------------+--------------------
Reporter: dylan@… | 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
----------------------------------------------+--------------------
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!
--
Ticket URL: <https://code.djangoproject.com/ticket/22117>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
return results.
Reporter: dylan@… | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by bmispelon):
* needs_docs: => 0
* needs_better_patch: => 0
* version: 1.6 => master
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:1>
Django
return results.
Reporter: dylan@… | Owner: numerodix
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: numerodix (added)
* status: new => assigned
* owner: nobody => numerodix
* keywords: => nlsprint14
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:2>
Django
return results.
Reporter: dylan@… | Owner: numerodix
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: nlsprint14 | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by bmispelon):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:3>
Django
return results.
Reporter: dylan@… | Owner: numerodix
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: nlsprint14 | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by zr):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:4>
Django
return results.
Reporter: dylan@… | Owner: numerodix
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: nlsprint14 | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by zr):
It might be intrinsic to that query specifically, since regular filtering
queries, using `Decimal` and `rev_typecast_decimal()` return the correct
results.
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:5>
Django
return results.
Reporter: dylan@… | Owner: numerodix
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: nlsprint14 | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by zr):
The bug also affects pysqlite 2.6.3 and sqlite3 (Python 2.7.4).
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:6>
Django
return results.
Reporter: dylan@… | Owner: numerodix
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: nlsprint14 | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by zr):
It's most probably something to do with the "HAVING SUM" clause
specifically:
{{{
#!python
>>> 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]: []
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:7>
Django
return results.
Reporter: dylan@… | Owner: numerodix
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: nlsprint14 | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by zr):
Related to: https://code.djangoproject.com/ticket/18247
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:8>
Django
return results.
Reporter: dylan@… | Owner: numerodix
Component: Database layer | Version: master
Severity: Normal | Triage Stage: Accepted
Keywords: nlsprint14 | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => duplicate
Comment:
Confirmed as duplicate of #18247.
--
Ticket URL: <https://code.djangoproject.com/ticket/22117#comment:9>