#33018: Incorrect annotation value when doing a subquery on empty queryset
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
decomorreno |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: orm, annotate,
Severity: Normal | EmptyQuerySet, empty, count
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
ORM seems to generate annotation/subqueries incorrectly if empty queryset
is used.
Models:
{{{
class Article(models.Model):
author_name = models.CharField(max_length=100)
content = models.TextField()
is_public = models.BooleanField()
class Comment(models.Model):
article = models.ForeignKey(Article, related_name="comments",
on_delete=models.CASCADE)
author_name = models.CharField(max_length=100)
content = models.TextField()
}}}
test data:
{{{
article = Article.objects.create(author_name="Jack", content="Example
content", is_public=True)
comment = Comment.objects.create(article=article, author_name="John",
content="Example comment")
}}}
queries:
{{{
qs = Article.objects.all()
# keep one list_x uncommented to see the difference:
list_x = ["random_thing_that_is_not_equal_to_any_authors_name"] # list not
empty, bug doesnt occur
#list_x = [] # if this list is empty, then the bug occurs
comment_qs = Comment.objects.filter(author_name__in=list_x)
qs = qs.annotate(
A=Coalesce(Subquery(
comment_qs.annotate(x=Count('content')).values('x')[:1],
output_field=IntegerField(),
), 101) # if list_x == [], Coalesce wont work and A will be 0 instead
of 101
)
# please note that above annotation doesnt make much logical sense, its
just for testing purposes
qs = qs.annotate(
B=Value(99, output_field=IntegerField())
)
qs = qs.annotate(
C=F("A") + F("B") # if list_x == [], C will result in 0 sic! instead
of 101 + 99 = 200
)
data = {
"A": qs.last().A,
"B": qs.last().B,
"C": qs.last().C,
}
print(data)
print(format_sql(qs.query))
}}}
console output for
{{{list_x=["random_thing_that_is_not_equal_to_any_authors_name"]}}}
(expected, correct):
{{{
{'A': 101, 'B': 99, 'C': 200}
SELECT "articles_article"."id",
"articles_article"."author_name",
"articles_article"."content",
"articles_article"."is_public",
COALESCE(
(SELECT COUNT(U0."content") AS "x"
FROM "articles_comment" U0
WHERE U0."author_name" IN
(random_thing_that_is_not_equal_to_any_authors_name)
GROUP BY U0."id", U0."article_id", U0."author_name",
U0."content"
LIMIT 1), 101) AS "A",
99 AS "B",
(COALESCE(
(SELECT COUNT(U0."content") AS "x"
FROM "articles_comment" U0
WHERE U0."author_name" IN
(random_thing_that_is_not_equal_to_any_authors_name)
GROUP BY U0."id", U0."article_id", U0."author_name",
U0."content"
LIMIT 1), 101) + 99) AS "C"
FROM "articles_article"
}}}
console output for {{{list_x=[]}}} (incorrect):
{{{
{'A': 0, 'B': 99, 'C': 0}
SELECT "articles_article"."id",
"articles_article"."author_name",
"articles_article"."content",
"articles_article"."is_public",
0 AS "A",
99 AS "B",
0 AS "C"
FROM "articles_article"
}}}
Background story: Above queries are made up (simplified), but based on
some parts of logic that I had in my code. list_x was generated
dynamically, and it was very hard to detect what is causing unexpected
results. This behavior is very strange, I believe its a bug and needs to
be fixed, because it is totally unintuitive that:
{{{SomeModel.objects.filter(x__in=["something_that_causes_this_qs_lenth_to_be_0"])}}}
and
{{{SomeModel.objects.filter(x__in=[]) }}}
may yield different results when used in queries later, even though
results of this querysets are logically equivalent
I will attach a minimal repro project (with code from above)
--
Ticket URL: <https://code.djangoproject.com/ticket/33018>
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/054.0055a2dbc8a09a9efc1e03451183e23f%40djangoproject.com.
