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
32090	Negated query on nullable field in conditonal aggregations.	Aurélien Pardon	nobody	"Hello, 

The Django ORM adds useless ""AND col IS NOT NULL"" in negated conditionnal aggregations on non-nullable fields. Here is an example :

{{{#!python
class Author(models.Model):
    name = models.CharField(max_length=100)

class Genre(models.IntegerChoices):
    fantasy = 0, 'Fantasy'
    horror = 1, 'Horror'
    scifi = 2, 'Science Fiction'

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    genre = models.IntegerField(choices=Genre, null=False)
}}}

{{{#!python
print(Author.objects.annotate(c=Count('book', filter=~Q(book__genre=Genre.scifi))).query)
}}}

{{{#!sql
SELECT [...], COUNT(""book"".""id"") FILTER (WHERE NOT (""book"".""genre"" = 2 AND ""book"".""genre"" IS NOT NULL)) AS ""c""
FROM ""author""
LEFT OUTER JOIN ""book""
    ON (""author"".""id"" = ""book"".""author_id"")
GROUP BY ""author"".""id""
}}}

The aggregate expression should be, `COUNT(""book"".""id"") FILTER (WHERE NOT (""book"".""genre"" = 2))`. 
In the same way that, when building simple filtered query, Django negate correctly the `where` clause knowing that `genre` is not nullable :

{{{#!python
print(Book.objects.filter(~Q(genre=Genre.scifi)).query)
}}}
{{{#!sql
SELECT [...]
FROM ""book""
WHERE NOT (""book"".""genre"" = 2)
}}}"	Cleanup/optimization	closed	Database layer (models, ORM)	3.1	Normal	invalid			Unreviewed	0	0	0	0	0	0