Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#19380 closed Bug (worksforme)

exclude fails on F object referencing annotation

Reported by: aaugustin Owned by: ethlinn
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 3 years ago by akaariai

  • Component changed from Database layer (models, ORM) to ORM aggregation
  • Triage Stage changed from Unreviewed to Accepted

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 3 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)

comment:3 Changed 3 years ago by ethlinn

  • Cc asendecka@… added
  • Owner changed from nobody to ethlinn
  • Status changed from new to assigned

comment:4 Changed 3 years ago by ethlinn

  • Resolution set to worksforme
  • Status changed from assigned to closed

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

comment:5 Changed 3 years ago by akaariai

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