Negated query on nullable field in conditonal aggregations.

[Aurélien Pardon]

Hello,

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

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)

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

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 :

print(Book.objects.filter(~Q(genre=Genre.scifi)).query)

SELECT [...]
FROM "book"
WHERE NOT ("book"."genre" = 2)

[Mariusz Felisiak]

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 :

These queries are not the same. In the first queryset i.e.

Author.objects.annotate(c=Count('book', filter=~Q(book__genre=Genre.scifi)))

we have a left outer join and a reverse relation. Such relations are always nullable. As a consequence, book.genre is also nullable.

[Aurélien Pardon]

My bad. Thanks for the explanation!