[Django] #32090: Negated query on nullable field in conditonnal aggregations

[Django]

#32090: Negated query on nullable field in conditonnal aggregations
-------------------------------------------+------------------------
Reporter: Aurélien Pardon | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.1
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------------+------------------------
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)
}}}

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