[Django] #34358: qs.filter(Exact(expr, value)) doesn’t work

[Django]

#34358: qs.filter(Exact(expr, value)) doesn’t work
-------------------------------------+-------------------------------------
Reporter: Roman | Owner: nobody
Odaisky |
Type: Bug | Status: new
Component: Database | Version: 4.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
{{{
class Child(Model):
parent = ForeignKey(Parent)

a = Parent.objects.filter(Exact(Count("child"), 0)).count()
b = Parent.objects.annotate(n=Count("child")).filter(n=0).count()
}}}
Expected: a == b == <number of childless parents>
Actual: a is always 0, b is correct

The two result in different SQL:
{{{
-- a:
SELECT COUNT(*) AS "__count"
FROM "parent" LEFT OUTER JOIN "child" ON ("parent"."id" =
"child"."report_id")
HAVING COUNT("child"."id") = 0

-- b:
SELECT COUNT(*)
FROM (
SELECT COUNT("child"."id") AS "n"
FROM "parent" LEFT OUTER JOIN "child" ON ("parent"."id" =
"child"."parent_id")
GROUP BY "parent"."id"
HAVING COUNT("child"."id") = 0
)
}}}
Am I correct in assuming A should have worked as well?

--
Ticket URL: <https://code.djangoproject.com/ticket/34358>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#34358: qs.filter(Exact(expr, value)) doesn’t work
-------------------------------------+-------------------------------------

Reporter: Roman Odaisky | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* status: new => closed
* resolution: => fixed

Comment:

The issue appears to be fixed in Django 4.2 by
59bea9efd2768102fc9d3aedda469502c218e9b7 (#28477) which strips unused
annotation.

{{{#!sql
SELECT COUNT(*)
FROM (
SELECT "parent"."id"

FROM "parent"
LEFT OUTER JOIN "child" ON ("parent"."id" = "child"."parent_id")

GROUP BY 0

HAVING COUNT("child"."id") = 0

) subquery
}}}

Prior to this change the generate SQL was simply wrong as `HAVING` cannot
be used without a `GROUP BY`

Please test against 4.2b1 and confirm if your issue is fixed or not.

--
Ticket URL: <https://code.djangoproject.com/ticket/34358#comment:1>