exclude fails on F object referencing annotation

[Aymeric Augustin]

Given these models:

from django.db import models

class Bill(models.Model):
    amount = models.IntegerField()

class Row(models.Model):
    bill = models.ForeignKey(Bill)
    amount = models.IntegerField()

this test case fails:

from django.db.models import F, Sum
from django.test import TestCase

from .models import Bill, Row


class AnnotateExcludeTest(TestCase):

    def test_annotate_exclude(self):
        bill = Bill.objects.create(amount=50)
        Row.objects.create(bill=bill, amount=20)
        Row.objects.create(bill=bill, amount=30)

        incoherent = Bill.objects.annotate(row_amount=Sum('row__amount')).exclude(row_amount=F('amount'))
        self.assertQuerysetEqual(incoherent, [])    # OK

        incoherent = Bill.objects.annotate(row_amount=Sum('row__amount')).exclude(amount=F('row_amount'))
        self.assertQuerysetEqual(incoherent, [])    # FAIL

The generated SQL is wrong for the second query. It's just missing the negation.

First query:

SELECT "testapp_bill"."id", "testapp_bill"."amount", SUM("testapp_row"."amount") AS "row_amount" FROM "testapp_bill"
LEFT OUTER JOIN "testapp_row" ON ("testapp_bill"."id" = "testapp_row"."bill_id")
GROUP BY "testapp_bill"."id", "testapp_bill"."amount"
HAVING NOT (SUM("testapp_row"."amount") =  "testapp_bill"."amount")

Second query:

SELECT "testapp_bill"."id", "testapp_bill"."amount", SUM("testapp_row"."amount") AS "row_amount" FROM "testapp_bill"
LEFT OUTER JOIN "testapp_row" ON ("testapp_bill"."id" = "testapp_row"."bill_id")
GROUP BY "testapp_bill"."id", "testapp_bill"."amount"
HAVING "testapp_bill"."amount" =  SUM("testapp_row"."amount")

[Anssi Kääriäinen]

If I recall correctly negation + having doesn't work too nicely. The underlying problem is that we have two targets - where and having - for filter clauses, and we fail to treat them equally in all cases.

There are other tickets dealing with similar issues, one case which seems somewhat related is #11293. I didn't find one tackling this issue specifically, so marking as accepted.

The branch mentioned in #11293 (​https://github.com/akaariai/django/tree/refactor_utils_tree) would likely solve this issue, too. The branch has some ugly code in it, but after some cleanup I think it is a good candidate for merging.

[Aleksandra Sendecka]

Closing as worksforme. Apparently it was fixed by akaariai some time ago.

[Anssi Kääriäinen]

Adding a test might be a good idea (unless there is already a test for this...).