Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#20152 closed Bug (invalid)

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

Reported by: raymond.wanyoike@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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')) \

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

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

Here is the problem, if you change filter() to exclude(), the same same SQL code is generated. I even tried it using Q() and ~Q() and got the exact 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 have get the effect of == and !=

Change History (2)

comment:1 Changed 4 years ago by raymondwanyoike

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Resolution: invalid
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top