#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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.3048c5e8f03ae335a496f8d284b8c31c%40djangoproject.com.
