#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: | dev |
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 |
Description
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 by , 12 years ago
Component: | Database layer (models, ORM) → ORM aggregation |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 12 years ago
Component: | ORM aggregation → Database layer (models, ORM) |
---|
comment:3 by , 11 years ago
Cc: | added |
---|---|
Owner: | changed from | to
Status: | new → assigned |
comment:4 by , 11 years ago
Resolution: | → worksforme |
---|---|
Status: | assigned → closed |
Closing as worksforme. Apparently it was fixed by akaariai some time ago.
comment:5 by , 11 years ago
Adding a test might be a good idea (unless there is already a test for this...).
Note:
See TracTickets
for help on using tickets.
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 (https://github.com/akaariai/django/tree/refactor_utils_tree) 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.