#34285: Index transforms on filtered array aggregates produces incorrect SQL
query
--------------------------------------------+------------------------
Reporter: nils-van-zuijlen | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------------+------------------------
For example, with the following models:
{{{
class MembershipKind(models.TextChoices):
MEMBER = "member", _("Member")
DIRECTOR = "director", _("Director")
MANAGER = "manager", _("Manager")
class Project(models.Model):
name = models.CharField(max_length=255)
members = models.ManyToManyField(User, related_name="projects",
blank=True, through="ProjectMember")
class ProjectMember(models.Model):
project = models.ForeignKey(
Project, related_name="memberships", on_delete=models.CASCADE,
verbose_name=_("project")
)
user = models.ForeignKey(User, related_name="project_memberships",
on_delete=models.CASCADE, verbose_name=_("user"))
kind = models.CharField(choices=MembershipKind.choices, max_length=10)
class Meta:
constraints = [models.UniqueConstraint(fields=("project", "user"),
name="project_user_unique_link")]
}}}
The following query has missing parenthesis around the annotated field
first_director_id.
{{{
>>> Project.objects.all().annotate(
... director_ids=ArrayAgg('memberships__user_id',
filter=Q(memberships__kind=MembershipKind.DIRECTOR))
... ).annotate(
... first_director_id=F('director_ids__0')
... ).query.sql_with_params()
(
'''SELECT "imputations_project"."id", "imputations_project"."name",
ARRAY_AGG("imputations_projectmember"."user_id" ) FILTER (WHERE
"imputations_projectmember"."kind" = %s) AS "director_ids",
ARRAY_AGG("imputations_projectmember"."user_id" ) FILTER (WHERE
"imputations_projectmember"."kind" = %s)[%s] AS "first_director_id"
FROM "imputations_project"
LEFT OUTER JOIN "imputations_projectmember" ON ("imputations_project"."id"
= "imputations_projectmember"."project_id")
GROUP BY "imputations_project"."id"''',
(<MembershipKind.DIRECTOR: 'director'>, <MembershipKind.DIRECTOR:
'director'>, 1)
)
}}}
It should be
{{{
(ARRAY_AGG("imputations_projectmember"."user_id" ) FILTER (WHERE
"imputations_projectmember"."kind" = %s))[%s] AS "first_director_id"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34285>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070185df94f57b-76b4d2f1-076e-412f-8533-bd9f6828c94a-000000%40eu-central-1.amazonses.com.
