id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
19380	exclude fails on F object referencing annotation	Aymeric Augustin	Aleksandra Sendecka	"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"")
}}}"	Bug	closed	Database layer (models, ORM)	dev	Normal	worksforme		asendecka@…	Accepted	0	0	0	0	0	0