Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#32090 closed Cleanup/optimization (invalid)

Negated query on nullable field in conditonal aggregations.

Reported by: Aurélien Pardon Owned by: nobody
Component: Database layer (models, ORM) Version: 3.1
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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)

Change History (2)

comment:1 by Mariusz Felisiak, 3 years ago

Component: UncategorizedDatabase layer (models, ORM)
Resolution: invalid
Status: newclosed
Summary: Negated query on nullable field in conditonnal aggregationsNegated query on nullable field in conditonal aggregations.
Type: UncategorizedCleanup/optimization

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.

comment:2 by Aurélien Pardon, 3 years ago

My bad. Thanks for the explanation!

Note: See TracTickets for help on using tickets.
Back to Top