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
34285	Index transforms on filtered array aggregates produces incorrect SQL query	Nils Van Zuijlen	Nils Van Zuijlen	"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""
}}}"	Bug	closed	Database layer (models, ORM)	3.2	Normal	fixed			Ready for checkin	1	0	0	0	0	0