#27498: Filtering annotated field in SQLite returns wrong results
-------------------------------------+-------------------------------------
Reporter: riemy | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 1.10
layer (models, ORM) | Keywords: sqlite,
Severity: Normal | annotations, filter
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When filtering an annotated field with SQLite backend the result from
Django QuerySet differs from what the raw SQL-query returns. This does not
affect other databases, only SQLite.
The following is the problematic queryset:
{{{
qs =
Product.objects.annotate(qty_available_sum=Sum('stock__qty_available')) \
.annotate(qty_needed=F('qty_target') -
F('qty_available_sum')) \
.filter(qty_needed__gt=0)
}}}
Here is a simple App with a test that shows the difference. The complete
files with imports are attached.
{{{
######
# models.py:
######
class Product(models.Model):
name = models.CharField(max_length=80)
qty_target = models.DecimalField(max_digits=6, decimal_places=2)
class Stock(models.Model):
product = models.ForeignKey(Product, related_name="stock")
qty_available = models.DecimalField(max_digits=6, decimal_places=2)
######
# tests.py:
######
class ErrorTestCase(TestCase):
def setUp(self):
p1 = Product.objects.create(name="Product1", qty_target=10)
p2 = Product.objects.create(name="Product2", qty_target=10)
p3 = Product.objects.create(name="Product3", qty_target=10)
s1 = Stock.objects.create(product=p1, qty_available=5)
s2 = Stock.objects.create(product=p1, qty_available=5)
s3 = Stock.objects.create(product=p1, qty_available=3) # 3 over
target
s4 = Stock.objects.create(product=p2, qty_available=5)
s5 = Stock.objects.create(product=p2, qty_available=4) # 1 under
target
s6 = Stock.objects.create(product=p3, qty_available=0) # 10 under
target
def testError(self):
# This is the exciting stuff:
qs =
Product.objects.annotate(qty_available_sum=Sum('stock__qty_available'))\
.annotate(qty_needed=F('qty_target') -
F('qty_available_sum'))\
.filter(qty_needed__gt=0)
# Retrieve by raw query:
query = str(qs.query)
print("# Query from QuerySet:")
print(query)
rows = []
with connection.cursor() as cursor:
cursor.execute(query)
rows = cursor.fetchall()
print("# Results from above Query:")
for r in rows:
print(r)
self.assertEqual(len(rows), 2, "Two products need stock by SQL
query.")
# retrieve by Django QuerySet:
print("# Results from QuerySet:")
for q in qs:
print(q, q.qty_needed)
self.assertEqual(qs.count(), 2, "Two products need stock by Django
QuerySet.")
}}}
And here is the output of the test. You can see the full query as it is
retrieved from the queryset and then run natively to retrieve the correct
amount of two rows. After that the same queryset is used to retreive the
data, but it retreived no row at all.
{{{
######
# Output with SQLite:
######
Creating test database for alias 'default'...
# Query from QuerySet:
SELECT "testapp_product"."id", "testapp_product"."name",
"testapp_product"."qty_target", CAST(SUM("testapp_stock"."qty_available")
AS NUMERIC) AS "qty_available_sum", ("testapp_product"."qty_target" -
CAST(SUM("testapp_stock"."qty_available") AS NUMERIC)) AS "qty_needed"
FROM "testapp_product" LEFT OUTER JOIN "testapp_stock" ON
("testapp_product"."id" = "testapp_stock"."product_id") GROUP BY
"testapp_product"."id", "testapp_product"."name",
"testapp_product"."qty_target" HAVING ("testapp_product"."qty_target" -
CAST(SUM("testapp_stock"."qty_available") AS NUMERIC)) > 0
# Results from above Query:
(2, 'Product2', Decimal('10'), 9, 1)
(3, 'Product3', Decimal('10'), 0, 10)
# Results from QuerySet:
F
======================================================================
FAIL: testError (testapp.tests.ErrorTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/tmp/testtmp/testapp/tests.py", line 44, in testError
self.assertEqual(qs.count(), 2, "Two products need stock by Django
QuerySet.")
AssertionError: 0 != 2 : Two products need stock by Django QuerySet.
----------------------------------------------------------------------
Ran 1 test in 0.004s
FAILED (failures=1)
Destroying test database for alias 'default'...
}}}
If the filter is inverted to {{{ .filter(qty_needed__lte=0) }}}, the raw
SQL correctly returns one row, while the QueySet returns all three rows.
So I guess there is something wrong with the filter(). I tried to debug
this problem, but failed because of lack of experience in Python
debugging.
With the attached models- and tests-files it is possible to recreate this
problem with a vanilla django project and app (including adding the app to
config, and migrating of course).
Other important information:
- Python version 3.4 on Gentoo Linux
- Django versions tested: 1.8 to 1.10.3
- SQLite Library in system: 3.13.0
--
Ticket URL: <https://code.djangoproject.com/ticket/27498>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/048.540e269e1985b29f7b2bf6c2061f8b75%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
