[Django] #30011: Count with filter annotation bug on filter
Django
-------------------------------------+-------------------------------------
Reporter: Taqi | Owner: nobody
Abbas |
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.1
layer (models, ORM) | Keywords: Annotation, Count,
Severity: Normal | Filter
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Given these models:
{{{
class Ticket(Model):
heading = models.TextField("One liner for the ticket to explain the
ticket")
participants = models.ManyToManyField(common_models.Users,
related_name='participants')
assigned_to = models.ForeignKey(common_models.Users,
on_delete=models.PROTECT, related_name='+', null=True)
requested_by = models.ForeignKey(common_models.Users,
on_delete=models.PROTECT, related_name='+')
category = models.ForeignKey(policy_models.TicketCategory,
on_delete=models.PROTECT)
blocker_set = models.ManyToManyField(
'self', symmetrical=False, through='TicketRelation',
related_name='blocked_set',
through_fields=('blocked', 'blocker')
)
...
class TicketRelation(Model):
blocked = models.ForeignKey(Ticket, on_delete=models.PROTECT,
related_name='+')
blocker = models.ForeignKey(Ticket, on_delete=models.PROTECT,
related_name='+')
depth = models.IntegerField(null=True)
...
class TicketUpload(Model):
ticket = models.ForeignKey(Ticket, on_delete=models.CASCADE,
related_name='upload_set')
...
class Commit(Model):
ticket = models.ForeignKey(Ticket, on_delete=models.PROTECT)
message = models.TextField("Message for the work done")
...
class Comment(Model):
ticket = models.ForeignKey(ticket_models.Ticket,
on_delete=models.CASCADE)
text = models.TextField("The text in the comments")
...
}}}
I want to query this:
{{{
queryset = Ticket.objects.filter(is_deleted=False)\
.select_related('category')\
.prefetch_related('upload_set')\
.annotate(commit_count=Count('commit',
filter=models.Q(commit__is_deleted=False))) \
.annotate(comment_count=Count('comment',
filter=models.Q(comment__is_deleted=False))) \
.annotate(upload_count=Count('upload_set',
filter=models.Q(upload_set__is_deleted=False))) \
.annotate(blocked_set_count=Count(
'blocked_set', filter=models.Q(blocked_set__is_deleted=False,
blocked_set__status__is_terminal=False)
))\
.annotate(blocker_set_count=Count(
'blocker_set', filter=models.Q(blocker_set__is_deleted=False,
blocker_set__status__is_terminal=False)
))
}}}
Which works and results in a query (I have ommited some some columns in
the models but all the relevant models are up):
{{{
print(queryset.query)
SELECT "ticketing_ticket"."id",
"ticketing_ticket"."uuid",
"ticketing_ticket"."created_on",
"ticketing_ticket"."updated_on",
"ticketing_ticket"."server_created_on",
"ticketing_ticket"."server_updated_on",
"ticketing_ticket"."is_deleted",
"ticketing_ticket"."updated_by_id",
"ticketing_ticket"."created_by_id",
"ticketing_ticket"."heading",
"ticketing_ticket"."description",
"ticketing_ticket"."expected_completion_time",
"ticketing_ticket"."category_id",
"ticketing_ticket"."priority_id",
"ticketing_ticket"."status_id",
"ticketing_ticket"."assigned_to_id",
"ticketing_ticket"."requested_by_id",
"ticketing_ticket"."ticket_details_id",
Count("ticketing_commit"."id") filter (WHERE
"ticketing_commit"."is_deleted" = false) AS "commit_count",
count("ticketing_comment"."id") filter (WHERE
"ticketing_comment"."is_deleted" = false) AS "comment_count",
count("ticketing_ticketupload"."id") filter (WHERE
"ticketing_ticketupload"."is_deleted" = false) AS "upload_count",
count("ticketing_ticketrelation"."blocked_id") filter
(WHERE (
t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" =
false)) AS "blocked_set_count",
count(t8."blocker_id") filter (WHERE (
t9."is_deleted" = false
AND t10."is_terminal" = false)) AS
"blocker_set_count",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text) AS "display_name",
"ticketing_ticketcategory"."id",
"ticketing_ticketcategory"."uuid",
"ticketing_ticketcategory"."created_on",
"ticketing_ticketcategory"."updated_on",
"ticketing_ticketcategory"."server_created_on",
"ticketing_ticketcategory"."server_updated_on",
"ticketing_ticketcategory"."is_deleted",
"ticketing_ticketcategory"."updated_by_id",
"ticketing_ticketcategory"."created_by_id",
"ticketing_ticketcategory"."name",
"ticketing_ticketcategory"."type",
"ticketing_ticketcategory"."for_mobile",
"ticketing_ticketcategory"."department_id",
"ticketing_ticketcategory"."default_priority_id",
"ticketing_ticketcategory"."default_assignment_method",
"ticketing_ticketcategory"."default_assigned_user_id",
"ticketing_ticketcategory"."default_assigned_job_title_id",
"ticketing_ticketcategory"."ticket_form_id"
FROM "ticketing_ticket"
LEFT OUTER JOIN "ticketing_commit"
ON (
"ticketing_ticket"."id" =
"ticketing_commit"."ticket_id")
LEFT OUTER JOIN "ticketing_comment"
ON (
"ticketing_ticket"."id" =
"ticketing_comment"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketupload"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketupload"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketrelation"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketrelation"."blocker_id")
LEFT OUTER JOIN "ticketing_ticket" t6
ON (
"ticketing_ticketrelation"."blocked_id" =
t6."id")
LEFT OUTER JOIN "ticketing_ticketstatus"
ON (
t6."status_id" =
"ticketing_ticketstatus"."id")
LEFT OUTER JOIN "ticketing_ticketrelation" t8
ON (
"ticketing_ticket"."id" = t8."blocked_id")
LEFT OUTER JOIN "ticketing_ticket" t9
ON (
t8."blocker_id" = t9."id")
LEFT OUTER JOIN "ticketing_ticketstatus" t10
ON (
t9."status_id" = t10."id")
INNER JOIN "ticketing_ticketcategory"
ON (
"ticketing_ticket"."category_id" =
"ticketing_ticketcategory"."id")
LEFT OUTER JOIN "department"
ON (
"ticketing_ticketcategory"."department_id"
= "department"."id")
WHERE "ticketing_ticket"."is_deleted" = false
GROUP BY "ticketing_ticket"."id",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text),
"ticketing_ticketcategory"."id"
}}}
Then I have a filtered_queryset:
{{{
filtered_queryset = queryset.filter(Q(heading__icontains=''))
}}}
which also works and results in the query
{{{
SELECT "ticketing_ticket"."id",
"ticketing_ticket"."uuid",
"ticketing_ticket"."created_on",
"ticketing_ticket"."updated_on",
"ticketing_ticket"."server_created_on",
"ticketing_ticket"."server_updated_on",
"ticketing_ticket"."is_deleted",
"ticketing_ticket"."updated_by_id",
"ticketing_ticket"."created_by_id",
"ticketing_ticket"."heading",
"ticketing_ticket"."description",
"ticketing_ticket"."expected_completion_time",
"ticketing_ticket"."category_id",
"ticketing_ticket"."priority_id",
"ticketing_ticket"."status_id",
"ticketing_ticket"."assigned_to_id",
"ticketing_ticket"."requested_by_id",
"ticketing_ticket"."ticket_details_id",
Count("ticketing_commit"."id") filter (WHERE
"ticketing_commit"."is_deleted" = false) AS "commit_count",
count("ticketing_comment"."id") filter (WHERE
"ticketing_comment"."is_deleted" = false) AS "comment_count",
count("ticketing_ticketupload"."id") filter (WHERE
"ticketing_ticketupload"."is_deleted" = false) AS "upload_count",
count("ticketing_ticketrelation"."blocked_id") filter
(WHERE (
t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" =
false)) AS "blocked_set_count",
count(t8."blocker_id") filter (WHERE (
t9."is_deleted" = false
AND t10."is_terminal" = false)) AS
"blocker_set_count",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text) AS "display_name",
"ticketing_ticketcategory"."id",
"ticketing_ticketcategory"."uuid",
"ticketing_ticketcategory"."created_on",
"ticketing_ticketcategory"."updated_on",
"ticketing_ticketcategory"."server_created_on",
"ticketing_ticketcategory"."server_updated_on",
"ticketing_ticketcategory"."is_deleted",
"ticketing_ticketcategory"."updated_by_id",
"ticketing_ticketcategory"."created_by_id",
"ticketing_ticketcategory"."name",
"ticketing_ticketcategory"."type",
"ticketing_ticketcategory"."for_mobile",
"ticketing_ticketcategory"."department_id",
"ticketing_ticketcategory"."default_priority_id",
"ticketing_ticketcategory"."default_assignment_method",
"ticketing_ticketcategory"."default_assigned_user_id",
"ticketing_ticketcategory"."default_assigned_job_title_id",
"ticketing_ticketcategory"."ticket_form_id"
FROM "ticketing_ticket"
LEFT OUTER JOIN "ticketing_commit"
ON (
"ticketing_ticket"."id" =
"ticketing_commit"."ticket_id")
LEFT OUTER JOIN "ticketing_comment"
ON (
"ticketing_ticket"."id" =
"ticketing_comment"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketupload"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketupload"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketrelation"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketrelation"."blocker_id")
LEFT OUTER JOIN "ticketing_ticket" t6
ON (
"ticketing_ticketrelation"."blocked_id" =
t6."id")
LEFT OUTER JOIN "ticketing_ticketstatus"
ON (
t6."status_id" =
"ticketing_ticketstatus"."id")
LEFT OUTER JOIN "ticketing_ticketrelation" t8
ON (
"ticketing_ticket"."id" = t8."blocked_id")
LEFT OUTER JOIN "ticketing_ticket" t9
ON (
t8."blocker_id" = t9."id")
LEFT OUTER JOIN "ticketing_ticketstatus" t10
ON (
t9."status_id" = t10."id")
INNER JOIN "ticketing_ticketcategory"
ON (
"ticketing_ticket"."category_id" =
"ticketing_ticketcategory"."id")
LEFT OUTER JOIN "department"
ON (
"ticketing_ticketcategory"."department_id"
= "department"."id")
WHERE (
"ticketing_ticket"."is_deleted" = false
AND upper("ticketing_ticket"."heading"::text)
LIKE upper(%%))
GROUP BY "ticketing_ticket"."id",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text),
"ticketing_ticketcategory"."id"
}}}
All of this is fine. I have separately done the following and all of these
work:
{{{
filtered_queryset.filter(id=33)
filtered_queryset.filter(id__in=[33])
queryset.filter(id=33)
queryset.filter(id__in=[33])
}}}
However, when I try doing this:
{{{
queryset.filter(id__in=filtered_queryset.values('id))
}}}
It blows up on evaluation. And now both queryset are mutated and can not
be evaluated. So any filter or get calls on them separately fail.
I checked the query for the that specific query:
{{{
print(queryset.filter(id__in=filtered_queryset.values('id)).query)
SELECT "ticketing_ticket"."id",
"ticketing_ticket"."uuid",
"ticketing_ticket"."created_on",
"ticketing_ticket"."updated_on",
"ticketing_ticket"."server_created_on",
"ticketing_ticket"."server_updated_on",
"ticketing_ticket"."is_deleted",
"ticketing_ticket"."updated_by_id",
"ticketing_ticket"."created_by_id",
"ticketing_ticket"."heading",
"ticketing_ticket"."description",
"ticketing_ticket"."expected_completion_time",
"ticketing_ticket"."category_id",
"ticketing_ticket"."priority_id",
"ticketing_ticket"."status_id",
"ticketing_ticket"."assigned_to_id",
"ticketing_ticket"."requested_by_id",
"ticketing_ticket"."ticket_details_id",
Count("ticketing_commit"."id",
"ticketing_commit"."is_deleted" = false, "ticketing_commit"."is_deleted" =
false) filter (WHERE "ticketing_commit"."is_deleted" = false)
AS "commit_count",
count("ticketing_comment"."id",
"ticketing_comment"."is_deleted" = false, "ticketing_comment"."is_deleted"
= false) filter (WHERE "ticketing_comment"."is_deleted" = false)
AS "comment_count",
count("ticketing_ticketupload"."id",
"ticketing_ticketupload"."is_deleted" = false,
"ticketing_ticketupload"."is_deleted" = false) filter (WHERE
"ticketing_ticketupload"."is_deleted" = false) AS "upload_count",
count("ticketing_ticketrelation"."blocked_id",
(t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" = false),
(t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" = false)) filter
(WHERE (
t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" =
false)) AS "blocked_set_count",
count(t8."blocker_id", (t9."is_deleted" = false
AND t10."is_terminal" = false), (t9."is_deleted" = false
AND t10."is_terminal" = false)) filter (WHERE (
t9."is_deleted" = false
AND t10."is_terminal" = false)) AS
"blocker_set_count",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text) AS "display_name",
"ticketing_ticketcategory"."id",
"ticketing_ticketcategory"."uuid",
"ticketing_ticketcategory"."created_on",
"ticketing_ticketcategory"."updated_on",
"ticketing_ticketcategory"."server_created_on",
"ticketing_ticketcategory"."server_updated_on",
"ticketing_ticketcategory"."is_deleted",
"ticketing_ticketcategory"."updated_by_id",
"ticketing_ticketcategory"."created_by_id",
"ticketing_ticketcategory"."name",
"ticketing_ticketcategory"."type",
"ticketing_ticketcategory"."for_mobile",
"ticketing_ticketcategory"."department_id",
"ticketing_ticketcategory"."default_priority_id",
"ticketing_ticketcategory"."default_assignment_method",
"ticketing_ticketcategory"."default_assigned_user_id",
"ticketing_ticketcategory"."default_assigned_job_title_id",
"ticketing_ticketcategory"."ticket_form_id"
FROM "ticketing_ticket"
LEFT OUTER JOIN "ticketing_commit"
ON (
"ticketing_ticket"."id" =
"ticketing_commit"."ticket_id")
LEFT OUTER JOIN "ticketing_comment"
ON (
"ticketing_ticket"."id" =
"ticketing_comment"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketupload"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketupload"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketrelation"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketrelation"."blocker_id")
LEFT OUTER JOIN "ticketing_ticket" t6
ON (
"ticketing_ticketrelation"."blocked_id" =
t6."id")
LEFT OUTER JOIN "ticketing_ticketstatus"
ON (
t6."status_id" =
"ticketing_ticketstatus"."id")
LEFT OUTER JOIN "ticketing_ticketrelation" t8
ON (
"ticketing_ticket"."id" = t8."blocked_id")
LEFT OUTER JOIN "ticketing_ticket" t9
ON (
t8."blocker_id" = t9."id")
LEFT OUTER JOIN "ticketing_ticketstatus" t10
ON (
t9."status_id" = t10."id")
INNER JOIN "ticketing_ticketcategory"
ON (
"ticketing_ticket"."category_id" =
"ticketing_ticketcategory"."id")
LEFT OUTER JOIN "department"
ON (
"ticketing_ticketcategory"."department_id"
= "department"."id")
WHERE (
"ticketing_ticket"."is_deleted" = false
AND "ticketing_ticket"."id" IN
(
SELECT u0."id"
FROM
"ticketing_ticket" u0
LEFT OUTER JOIN
"ticketing_commit" u1
ON (
u0."id" = u1."ticket_id")
LEFT OUTER JOIN
"ticketing_comment" u2
ON (
u0."id" = u2."ticket_id")
LEFT OUTER JOIN
"ticketing_ticketupload" u3
ON (
u0."id" = u3."ticket_id")
LEFT OUTER JOIN
"ticketing_ticketrelation" u4
ON (
u0."id" = u4."blocker_id")
LEFT OUTER JOIN
"ticketing_ticket" u5
ON (
u4."blocked_id" = u5."id")
LEFT OUTER JOIN
"ticketing_ticketstatus" u6
ON (
u5."status_id" = u6."id")
LEFT OUTER JOIN
"ticketing_ticketrelation" u7
ON (
u0."id" = u7."blocked_id")
LEFT OUTER JOIN
"ticketing_ticket" u8
ON (
u7."blocker_id" = u8."id")
LEFT OUTER JOIN
"ticketing_ticketstatus" u9
ON (
u8."status_id" = u9."id")
INNER JOIN
"ticketing_ticketcategory" u10
ON (
u0."category_id" = u10."id")
LEFT OUTER JOIN
"department" u11
ON (
u10."department_id" = u11."id")
WHERE
u0."is_deleted" = false
GROUP BY u0."id",
concat(
CASE
WHEN u10."department_id" IS NOT NULL THEN
CASE
WHEN u11."key" IS NOT NULL THEN u11."key"
ELSE upper(substring(u11."name" FROM (1) FOR (3)))
END
ELSE sys
END, -,
u0."id"::text)))
GROUP BY "ticketing_ticket"."id",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text),
"ticketing_ticketcategory"."id"
}}}
Now both query printed separately, show:
{{{
print(queryset.query)
SELECT "ticketing_ticket"."id",
"ticketing_ticket"."uuid",
"ticketing_ticket"."created_on",
"ticketing_ticket"."updated_on",
"ticketing_ticket"."server_created_on",
"ticketing_ticket"."server_updated_on",
"ticketing_ticket"."is_deleted",
"ticketing_ticket"."updated_by_id",
"ticketing_ticket"."created_by_id",
"ticketing_ticket"."heading",
"ticketing_ticket"."description",
"ticketing_ticket"."expected_completion_time",
"ticketing_ticket"."category_id",
"ticketing_ticket"."priority_id",
"ticketing_ticket"."status_id",
"ticketing_ticket"."assigned_to_id",
"ticketing_ticket"."requested_by_id",
"ticketing_ticket"."ticket_details_id",
Count("ticketing_commit"."id",
"ticketing_commit"."is_deleted" = false, "ticketing_commit"."is_deleted" =
false) filter (WHERE "ticketing_commit"."is_deleted" = false)
AS "commit_count",
count("ticketing_comment"."id",
"ticketing_comment"."is_deleted" = false, "ticketing_comment"."is_deleted"
= false) filter (WHERE "ticketing_comment"."is_deleted" = false)
AS "comment_count",
count("ticketing_ticketupload"."id",
"ticketing_ticketupload"."is_deleted" = false,
"ticketing_ticketupload"."is_deleted" = false) filter (WHERE
"ticketing_ticketupload"."is_deleted" = false) AS "upload_count",
count("ticketing_ticketrelation"."blocked_id",
(t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" = false),
(t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" = false)) filter
(WHERE (
t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" =
false)) AS "blocked_set_count",
count(t8."blocker_id", (t9."is_deleted" = false
AND t10."is_terminal" = false), (t9."is_deleted" = false
AND t10."is_terminal" = false)) filter (WHERE (
t9."is_deleted" = false
AND t10."is_terminal" = false)) AS
"blocker_set_count",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text) AS "display_name",
"ticketing_ticketcategory"."id",
"ticketing_ticketcategory"."uuid",
"ticketing_ticketcategory"."created_on",
"ticketing_ticketcategory"."updated_on",
"ticketing_ticketcategory"."server_created_on",
"ticketing_ticketcategory"."server_updated_on",
"ticketing_ticketcategory"."is_deleted",
"ticketing_ticketcategory"."updated_by_id",
"ticketing_ticketcategory"."created_by_id",
"ticketing_ticketcategory"."name",
"ticketing_ticketcategory"."type",
"ticketing_ticketcategory"."for_mobile",
"ticketing_ticketcategory"."department_id",
"ticketing_ticketcategory"."default_priority_id",
"ticketing_ticketcategory"."default_assignment_method",
"ticketing_ticketcategory"."default_assigned_user_id",
"ticketing_ticketcategory"."default_assigned_job_title_id",
"ticketing_ticketcategory"."ticket_form_id"
FROM "ticketing_ticket"
LEFT OUTER JOIN "ticketing_commit"
ON (
"ticketing_ticket"."id" =
"ticketing_commit"."ticket_id")
LEFT OUTER JOIN "ticketing_comment"
ON (
"ticketing_ticket"."id" =
"ticketing_comment"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketupload"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketupload"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketrelation"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketrelation"."blocker_id")
LEFT OUTER JOIN "ticketing_ticket" t6
ON (
"ticketing_ticketrelation"."blocked_id" =
t6."id")
LEFT OUTER JOIN "ticketing_ticketstatus"
ON (
t6."status_id" =
"ticketing_ticketstatus"."id")
LEFT OUTER JOIN "ticketing_ticketrelation" t8
ON (
"ticketing_ticket"."id" = t8."blocked_id")
LEFT OUTER JOIN "ticketing_ticket" t9
ON (
t8."blocker_id" = t9."id")
LEFT OUTER JOIN "ticketing_ticketstatus" t10
ON (
t9."status_id" = t10."id")
INNER JOIN "ticketing_ticketcategory"
ON (
"ticketing_ticket"."category_id" =
"ticketing_ticketcategory"."id")
LEFT OUTER JOIN "department"
ON (
"ticketing_ticketcategory"."department_id"
= "department"."id")
WHERE "ticketing_ticket"."is_deleted" = false
GROUP BY "ticketing_ticket"."id",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text),
"ticketing_ticketcategory"."id"
}}}
and
{{{
SELECT "ticketing_ticket"."id",
"ticketing_ticket"."uuid",
"ticketing_ticket"."created_on",
"ticketing_ticket"."updated_on",
"ticketing_ticket"."server_created_on",
"ticketing_ticket"."server_updated_on",
"ticketing_ticket"."is_deleted",
"ticketing_ticket"."updated_by_id",
"ticketing_ticket"."created_by_id",
"ticketing_ticket"."heading",
"ticketing_ticket"."description",
"ticketing_ticket"."expected_completion_time",
"ticketing_ticket"."category_id",
"ticketing_ticket"."priority_id",
"ticketing_ticket"."status_id",
"ticketing_ticket"."assigned_to_id",
"ticketing_ticket"."requested_by_id",
"ticketing_ticket"."ticket_details_id",
Count("ticketing_commit"."id",
"ticketing_commit"."is_deleted" = false, "ticketing_commit"."is_deleted" =
false) filter (WHERE "ticketing_commit"."is_deleted" = false)
AS "commit_count",
count("ticketing_comment"."id",
"ticketing_comment"."is_deleted" = false, "ticketing_comment"."is_deleted"
= false) filter (WHERE "ticketing_comment"."is_deleted" = false)
AS "comment_count",
count("ticketing_ticketupload"."id",
"ticketing_ticketupload"."is_deleted" = false,
"ticketing_ticketupload"."is_deleted" = false) filter (WHERE
"ticketing_ticketupload"."is_deleted" = false) AS "upload_count",
count("ticketing_ticketrelation"."blocked_id",
(t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" = false),
(t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" = false)) filter
(WHERE (
t6."is_deleted" = false
AND "ticketing_ticketstatus"."is_terminal" =
false)) AS "blocked_set_count",
count(t8."blocker_id", (t9."is_deleted" = false
AND t10."is_terminal" = false), (t9."is_deleted" = false
AND t10."is_terminal" = false)) filter (WHERE (
t9."is_deleted" = false
AND t10."is_terminal" = false)) AS
"blocker_set_count",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text) AS "display_name",
"ticketing_ticketcategory"."id",
"ticketing_ticketcategory"."uuid",
"ticketing_ticketcategory"."created_on",
"ticketing_ticketcategory"."updated_on",
"ticketing_ticketcategory"."server_created_on",
"ticketing_ticketcategory"."server_updated_on",
"ticketing_ticketcategory"."is_deleted",
"ticketing_ticketcategory"."updated_by_id",
"ticketing_ticketcategory"."created_by_id",
"ticketing_ticketcategory"."name",
"ticketing_ticketcategory"."type",
"ticketing_ticketcategory"."for_mobile",
"ticketing_ticketcategory"."department_id",
"ticketing_ticketcategory"."default_priority_id",
"ticketing_ticketcategory"."default_assignment_method",
"ticketing_ticketcategory"."default_assigned_user_id",
"ticketing_ticketcategory"."default_assigned_job_title_id",
"ticketing_ticketcategory"."ticket_form_id"
FROM "ticketing_ticket"
LEFT OUTER JOIN "ticketing_commit"
ON (
"ticketing_ticket"."id" =
"ticketing_commit"."ticket_id")
LEFT OUTER JOIN "ticketing_comment"
ON (
"ticketing_ticket"."id" =
"ticketing_comment"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketupload"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketupload"."ticket_id")
LEFT OUTER JOIN "ticketing_ticketrelation"
ON (
"ticketing_ticket"."id" =
"ticketing_ticketrelation"."blocker_id")
LEFT OUTER JOIN "ticketing_ticket" t6
ON (
"ticketing_ticketrelation"."blocked_id" =
t6."id")
LEFT OUTER JOIN "ticketing_ticketstatus"
ON (
t6."status_id" =
"ticketing_ticketstatus"."id")
LEFT OUTER JOIN "ticketing_ticketrelation" t8
ON (
"ticketing_ticket"."id" = t8."blocked_id")
LEFT OUTER JOIN "ticketing_ticket" t9
ON (
t8."blocker_id" = t9."id")
LEFT OUTER JOIN "ticketing_ticketstatus" t10
ON (
t9."status_id" = t10."id")
INNER JOIN "ticketing_ticketcategory"
ON (
"ticketing_ticket"."category_id" =
"ticketing_ticketcategory"."id")
LEFT OUTER JOIN "department"
ON (
"ticketing_ticketcategory"."department_id"
= "department"."id")
WHERE (
"ticketing_ticket"."is_deleted" = false
AND upper("ticketing_ticket"."heading"::text)
LIKE upper(%%))
GROUP BY "ticketing_ticket"."id",
concat(
CASE
WHEN
"ticketing_ticketcategory"."department_id" IS NOT NULL THEN
CASE
WHEN
"department"."key" IS NOT NULL THEN "department"."key"
ELSE
upper(substring("department"."name" FROM (1) FOR (3)))
END
ELSE sys
END, -, "ticketing_ticket"."id"::text),
"ticketing_ticketcategory"."id"
}}}
Please guide as to what would kind of test would you need for this or if
this is some problem from my side.
I am using Postgres.
As you can see, the Count is taking more than one (and wrong) arguments in
both the queries. It starts taking the filter argument as a part of the
Count argument.
I did some preliminary debugging and (I could be wrong) but the Count
__init__ class is called with the correct expressions through out the
flow.
TLDR; I am not entirely sure where this breaks but when a queryset with
Count annotation (with filter kwarg) is called with
queryset.filter(id__in=filtered_queryset.values('id))
where filtered_queryset = queryset.filter(heading__icontains='')
Count adds the filter as a argument to the queryset hence making invalid
SQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/30011>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Annotation, Count, | Triage Stage:
Filter | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Hello Taqi,
I see that somehow your `COUNT` annotations get their expressions
duplicated when a subquery is added. This is probably an issue with
expression cloning when you create `filtered_queryset` from `queryset`.
One thing that would tremendously help debugging this further is if you
could reduce your reproduction case to a minimal set of conditions by
progressively removing annotations and simplifying your querysets. Right
now there's a lot of things involved that makes your query massive and
hard to pinpoint the exact source of the failure.
To get you started, does the following crash as well?
{{{#!python
queryset = Ticket.objects.annotate(
commit_count=Count('commit',
filter=models.Q(commit__is_deleted=False))
)
filtered_queryset = queryset.filter(Q(heading__icontains=''))
print(queryset.filter(id__in=filtered_queryset.values('id')).query)
}}}
By the way, you probably haven't noticed it yet but your queryset is
probably not returning the right results for these annotations because
multiple tables are the cross joined. This is explained in the
[https://docs.djangoproject.com/en/2.1/topics/db/aggregation/#combining-
multiple-aggregations documentation] and tracked in #10060. This is a
limitation that #28296 ought to fix by eventually providing a `subquery`
argument to `Count`.
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Annotation, Count, | Triage Stage:
Filter | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Taqi Abbas):
Hey Simon,
Your suggested code also breaks. This is the line that invalidates the
sql.
{{{
queryset.filter(id__in=filtered_queryset.values('id'))
}}}
As for the cross joins issue, this is the code that I basically use to
remove duplicates.
{{{
if hasattr(queryset.model, 'id'):
return queryset.filter(id__in=filtered_queryset.values('id'))
else:
return filtered_queryset.distinct(*view.get_ordering(request))
}}}
its done like this because distinct fails for a model that has a jsonb
field. So I just do this.
I have made a github repo with a testcase to reproduce it. Heres the
link[https://github.com/taqi457/Django-Count-TestCase]:
This a simpler query reproduced from the repo above, hope this helps.
{{{
queryset = Ticket.objects.filter(is_deleted=False)\
.annotate(commit_count=Count('commit_set', filter=Q(is_deleted=False)))
filtered_queryset = queryset.filter(heading__icontains='')
result = queryset.filter(id__in=filtered_queryset.values('id'))
print(result.query)]
SELECT
"test_count_ticket"."id",
"test_count_ticket"."uuid",
"test_count_ticket"."created_on",
"test_count_ticket"."updated_on",
"test_count_ticket"."server_created_on",
"test_count_ticket"."server_updated_on",
"test_count_ticket"."is_deleted",
"test_count_ticket"."heading",
Count("test_count_commit"."id",
"test_count_ticket"."is_deleted" = false, "test_count_ticket"."is_deleted"
= false) filter (WHERE "test_count_ticket"."is_deleted" = false) AS
"commit_count"
FROM "test_count_ticket"
LEFT OUTER JOIN "test_count_commit"
ON (
"test_count_ticket"."id" =
"test_count_commit"."ticket_id")
WHERE (
"test_count_ticket"."is_deleted" = false
AND "test_count_ticket"."id" IN
(
SELECT u0."id"
FROM
"test_count_ticket" u0
LEFT OUTER JOIN
"test_count_commit" u1
ON (
u0."id" = u1."ticket_id")
WHERE (
u0."is_deleted" = false
AND
upper(u0."heading"::text) LIKE upper(%%))
GROUP BY u0."id"))
GROUP BY "test_count_ticket"."id"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Annotation, Count, | Triage Stage:
Filter | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Raphael Kimmig):
I've got a somewhat smaller repro here, the bug seems to require both a
filtered annotation and a nested filter.
{{{
from django.db.models import Count, Q
from django.test import TestCase
from django.db import models, OperationalError
class Thing(models.Model):
pass
class RelatedThing(models.Model):
thing = models.ForeignKey(
Thing, on_delete=models.CASCADE,
)
class TestCountExtraArg(TestCase):
def setUp(self):
t = Thing.objects.create()
t.relatedthing_set.create()
def test_issue_not_triggered_without_filter_in_(self):
queryset = Thing.objects.annotate(
related_count=Count("relatedthing")
)
result = queryset.filter(id__in=queryset.values("id"))
self.assertEqual(result[0].related_count, 1, result)
def test_issue_not_triggered_without_nested_query(self):
queryset = Thing.objects.annotate(
related_count=Count("relatedthing", filter=Q(id__gt=0))
)
result = queryset.filter(id__in=list(queryset.values_list("id",
flat=True)))
self.assertEqual(result[0].related_count, 1, result)
def test_triggering_issue(self):
queryset = Thing.objects.annotate(
related_count=Count("relatedthing", filter=Q(id__gt=0))
)
result = queryset.filter(id__in=queryset.values("id"))
with self.assertRaises(OperationalError):
self.assertEqual(result[0].related_count, 1, result)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Filter |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
Comment:
Thanks for confirming as well Raphael!
With both your reports I managed to come up with a patch. We happened to
alter `Aggregate.source_expressions` on `get_expressions` when a `filter`
was specified.
https://github.com/django/django/pull/10731
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Filter | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:5>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Filter |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Ready for checkin => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:6>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Filter | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:7>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 2.1
(models, ORM) |
Keywords: Annotation, Count, | Triage Stage: Ready for
Filter | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"53269bcaaf1fc2ce4db797d7d8935d98a53494df" 53269bca]:
{{{
#!CommitTicketReference repository=""
revision="53269bcaaf1fc2ce4db797d7d8935d98a53494df"
Fixed #30011 -- Fixed queries that reuse filtered aggregates.
Thanks Taqi Abbas and Raphael Kimmig for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:8>