Code

Opened 13 months ago

Closed 13 months ago

Last modified 13 months 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

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('b_field_sum'))

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

Attachments (0)

Change History (2)

comment:1 Changed 13 months ago by raymondwanyoike

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

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.