Code

#20153 closed Bug (fixed)

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

Reported by: raymondwanyoike 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

Description

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 !=

Attachments (0)

Change History (1)

comment:1 Changed 13 months ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to fixed
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.