Filtering annotated field in SQLite returns wrong results

[Tim Düllmann]

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