Count with filter annotation bug on filter

[Taqi Abbas]

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(idin=filtered_queryset.values('id))
where filtered_queryset = queryset.filter(headingicontains=)
Count adds the filter as a argument to the queryset hence making invalid SQL.

[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?

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 ​documentation and tracked in #10060. This is a limitation that #28296 ought to fix by eventually providing a subquery argument to Count.

[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"

[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)

[Simon Charette]

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

[Tim Graham <timograham@…>]

In 53269bca:

Fixed #30011 -- Fixed queries that reuse filtered aggregates.

Thanks Taqi Abbas and Raphael Kimmig for the report.