id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
20153	QuerySet filter() and exclude() when used with annotate() generate same SQL	raymondwanyoike	nobody	"Here are the models:
{{{
#!python

## 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:
{{{
#!python

## 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:
{{{
#!sql

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 !="	Bug	closed	Database layer (models, ORM)	1.5	Normal	fixed			Unreviewed	0	0	0	0	0	0