QuerySet filter() and exclude() when used with annotate() generate same SQL

[raymondwanyoike]

Here are the models:

## Models
from django.db import models

class ModelA(models.Model):
    aafield = models.IntegerField()

class ModelB(models.Model):
    bbfield = models.IntegerField()
    model_a = models.OneToOneField(ModelA)

Here is the query:

## QuerySet
from django.db.models import Sum, F

# 1. Get all ModelA objects
# 2. Annotate sum of modelb__bbfield (OneToOne relationship)
# 3. Filter where ModelA.aafield == ModelB.bbfield_sum
result = ModelA.objects.all() \
    .annotate(bbfield_sum=Sum('modelb__bbfield')) \
    .filter(aafield=F('bbfield_sum'))

Up to this point the generated SQL is valid and correct:

SELECT ... FROM `...` LEFT OUTER JOIN `...` ON (`...` = `...`) GROUP BY `...` 
HAVING `project_modela`.`aafield` = SUM(`project_modelb`.`bbfield`)

Here is the problem, if you change filter() to exclude(), the same SQL code is generated. I also tried it using Q() and ~Q() and got the same SQL, the filter cannot be negated. In other words .exclude() and ~Q() have no effect.

I am currently falling back to aafield__gt and aafield__lt to get the effect of == and !=

[Anssi Kääriäinen]

This is fixed in master:

print(
    ModelA.objects.all().annotate(
        bbfield_sum=Sum('modelb__bbfield')
    ).exclude(
        aafield=F('bbfield_sum')
    ).query
)
SELECT "TESTS_MODELA"."ID", "TESTS_MODELA"."AAFIELD", SUM("TESTS_MODELB"."BBFIELD") AS "BBFIELD_SUM"
FROM "TESTS_MODELA" LEFT OUTER JOIN "TESTS_MODELB" ON ( "TESTS_MODELA"."ID" = "TESTS_MODELB"."MODEL_A_ID" )
GROUP BY "TESTS_MODELA"."ID", "TESTS_MODELA"."AAFIELD"
HAVING NOT ("TESTS_MODELA"."AAFIELD" =  SUM("TESTS_MODELB"."BBFIELD"))

Backpatch to 1.5 isn't going to happen, the changes done in master are too large, and this problem doesn't fall into the categories of fixes to be backpatched.