#27498: Filtering annotated field in SQLite returns wrong results
-------------------------------------+-------------------------------------
Reporter: Tim Düllmann | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, | Triage Stage: Accepted
annotations, filter |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Anton Samarchyan):
* version: 1.10 => master
Old description:
> 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)
> }}}
>
> This translates to the following Query, which returns the correct
> results, when run natively:
>
> {{{
> 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
> }}}
>
> Using the QuerySet, it does not respect the filter condition. For details
> see the test below.
>
> 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
New description:
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)
}}}
This translates to the following Query, which returns the correct results,
when run natively:
{{{
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
}}}
Using the QuerySet, it does not respect the filter condition. For details
see the test below.
Here is a simple App with a test that shows the difference. The complete
files with imports are attached.
{{{
######
# models.py:
######
from django.db import models
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:
######
from django.test import TestCase
from django.db.models.aggregates import Sum
from django.db import connection
from django.db.models.expressions import F
from testapp.models import Product, Stock
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#comment:3>
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/063.6d0ab9ea86518d4f028511d2582bf668%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
