Opened 6 years ago

Closed 5 years ago

Last modified 5 years ago

#19380 closed Bug (worksforme)

exclude fails on F object referencing annotation

Reported by: Aymeric Augustin Owned by: Aleksandra Sendecka
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: asendecka@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Given these models:

from django.db import models

class Bill(models.Model):
    amount = models.IntegerField()

class Row(models.Model):
    bill = models.ForeignKey(Bill)
    amount = models.IntegerField()

this test case fails:

from django.db.models import F, Sum
from django.test import TestCase

from .models import Bill, Row

class AnnotateExcludeTest(TestCase):

    def test_annotate_exclude(self):
        bill = Bill.objects.create(amount=50)
        Row.objects.create(bill=bill, amount=20)
        Row.objects.create(bill=bill, amount=30)

        incoherent = Bill.objects.annotate(row_amount=Sum('row__amount')).exclude(row_amount=F('amount'))
        self.assertQuerysetEqual(incoherent, [])    # OK

        incoherent = Bill.objects.annotate(row_amount=Sum('row__amount')).exclude(amount=F('row_amount'))
        self.assertQuerysetEqual(incoherent, [])    # FAIL

The generated SQL is wrong for the second query. It's just missing the negation.

First query:

SELECT "testapp_bill"."id", "testapp_bill"."amount", SUM("testapp_row"."amount") AS "row_amount" FROM "testapp_bill"
LEFT OUTER JOIN "testapp_row" ON ("testapp_bill"."id" = "testapp_row"."bill_id")
GROUP BY "testapp_bill"."id", "testapp_bill"."amount"
HAVING NOT (SUM("testapp_row"."amount") =  "testapp_bill"."amount")

Second query:

SELECT "testapp_bill"."id", "testapp_bill"."amount", SUM("testapp_row"."amount") AS "row_amount" FROM "testapp_bill"
LEFT OUTER JOIN "testapp_row" ON ("testapp_bill"."id" = "testapp_row"."bill_id")
GROUP BY "testapp_bill"."id", "testapp_bill"."amount"
HAVING "testapp_bill"."amount" =  SUM("testapp_row"."amount")

Change History (5)

comment:1 Changed 6 years ago by Anssi Kääriäinen

Component: Database layer (models, ORM)ORM aggregation
Triage Stage: UnreviewedAccepted

If I recall correctly negation + having doesn't work too nicely. The underlying problem is that we have two targets - where and having - for filter clauses, and we fail to treat them equally in all cases.

There are other tickets dealing with similar issues, one case which seems somewhat related is #11293. I didn't find one tackling this issue specifically, so marking as accepted.

The branch mentioned in #11293 ( would likely solve this issue, too. The branch has some ugly code in it, but after some cleanup I think it is a good candidate for merging.

comment:2 Changed 5 years ago by Anssi Kääriäinen

Component: ORM aggregationDatabase layer (models, ORM)

comment:3 Changed 5 years ago by Aleksandra Sendecka

Cc: asendecka@… added
Owner: changed from nobody to Aleksandra Sendecka
Status: newassigned

comment:4 Changed 5 years ago by Aleksandra Sendecka

Resolution: worksforme
Status: assignedclosed

Closing as worksforme. Apparently it was fixed by akaariai some time ago.

comment:5 Changed 5 years ago by Anssi Kääriäinen

Adding a test might be a good idea (unless there is already a test for this...).

Note: See TracTickets for help on using tickets.
Back to Top