#30011: Count with filter annotation bug on filter
-------------------------------------+-------------------------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.ee93dacdcf18f90974da8b1aac3547a8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
