Opened 3 years ago

Closed 3 years ago

#22117 closed Bug (duplicate)

Filtering on an annotation returns no reults in sqlite3 when it should return results.

Reported by: dylan@… Owned by: Martin Matusiak
Component: Database layer (models, ORM) Version: master
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)

repro.zip (8.0 KB) - added by dylan@… 3 years ago.
sample app with test case to repro bug

Download all attachments as: .zip

Change History (10)

Changed 3 years ago by dylan@…

Attachment: repro.zip added

sample app with test case to repro bug

comment:1 Changed 3 years ago by Baptiste Mispelon

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: UnreviewedAccepted
Version: 1.6master

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 Changed 3 years ago by Martin Matusiak

Cc: Martin Matusiak added
Keywords: nlsprint14 added
Owner: changed from nobody to Martin Matusiak
Status: newassigned

comment:3 Changed 3 years ago by Baptiste Mispelon

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 Changed 3 years ago by Rigel Di Scala

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 Changed 3 years ago by Rigel Di Scala

Actually, I feel it is intrinsic to that query specifically, since regular filtering queries, using Decimal and rev_typecast_decimal() return the correct results.

Last edited 3 years ago by Rigel Di Scala (previous) (diff)

comment:6 Changed 3 years ago by Rigel Di Scala

The bug also affects pysqlite 2.6.3 and sqlite3 (Python 2.7.4).

comment:7 Changed 3 years ago by Rigel Di Scala

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:8 Changed 3 years ago by Rigel Di Scala

comment:9 Changed 3 years ago by Rigel Di Scala

Resolution: duplicate
Status: assignedclosed

Confirmed as duplicate of #18247.

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