id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 30011 Count with filter annotation bug on filter Taqi Abbas nobody "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." Bug closed Database layer (models, ORM) 2.1 Normal fixed Annotation, Count, Filter Ready for checkin 1 0 0 0 0 0