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
31097	StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call	Laurent Tramoy	David Wobrock	"This issue is similar to issue #30315 but using the `filter` keyword instead of `ordering`, so I'll reuse the same structure. I tested it on Django 2.2.8

Consider the following models (in the people app):

{{{
from django.db import models
from django.contrib.postgres.fields import ArrayField


class Person(models.Model):
    """"""Person model.""""""

    first_name = models.TextField()
    last_name = models.TextField()
    country = models.TextField(null=True, blank=True)


class Book(models.Model):
    """"""Book model.""""""
    category = ArrayField(models.CharField(max_length=32), null=True, default=list)
    people = models.ManyToManyField(Person)
}}}

with the following objects:
{{{
p1 = Person.objects.create(first_name=""John"", last_name=""Doe"")
p2 = Person.objects.create(first_name=""Jane"", last_name=""Doe"")

b1 = Book.objects.create()
b1.people.add(p1)

b2 = Book.objects.create()
b2.people.add(p1, p2)

b3 = Book.objects.create()
}}}

This fails: 
{{{
from django.contrib.postgres.aggregates import StringAgg
from django.db.models import Subquery, OuterRef, Q

from people.models import  Book

subquery = (
    Book.objects.annotate(
        _annotated_value=StringAgg(
            ""people__first_name"", "","", filter=Q(people__first_name__startswith=""Ja"")
        ),
    )
    .filter(pk=OuterRef(""pk""),)
    .exclude(_annotated_value="""")
    .values(""id"")
)
Book.objects.filter(id__in=Subquery(subquery))
}}}

The generated SQL is 

{{{
SELECT
    ""people_book"".""id"",
    ""people_book"".""category""
FROM
    ""people_book""
WHERE
    ""people_book"".""id"" IN (
        SELECT
            U0.""id""
        FROM
            ""people_book"" U0
        LEFT OUTER JOIN ""people_book_people"" U1 ON (U0.""id"" = U1.""book_id"")
    LEFT OUTER JOIN ""people_person"" U2 ON (U1.""person_id"" = U2.""id"")
WHERE
    U0.""id"" = (""people_book"".""id"")
GROUP BY
    U0.""id""
HAVING
    NOT (STRING_AGG(, ',') FILTER (WHERE U2.""first_name"") =))
}}}

as we can see, the `STRING_AGG argument is wrong.

The same query without the `filter` works fine: 

{{{
subquery = (
    Book.objects.annotate(
        _annotated_value=StringAgg(
            ""people__first_name"", "",""
        ),
    )
    .filter(pk=OuterRef(""pk""),)
    .exclude(_annotated_value="""")
    .values(""id"")
)
Book.objects.filter(id__in=Subquery(subquery))
}}}

SQL query:

{{{
SELECT
    ""people_book"".""id"",
    ""people_book"".""category""
FROM
    ""people_book""
WHERE
    ""people_book"".""id"" IN (
        SELECT
            U0.""id""
        FROM
            ""people_book"" U0
        LEFT OUTER JOIN ""people_book_people"" U1 ON (U0. ""id"" = U1.""book_id"")
    LEFT OUTER JOIN ""people_person"" U2 ON (U1.""person_id"" = U2.""id"")
WHERE
    U0.""id"" = (""people_book"".""id"")
GROUP BY
    U0.""id""
HAVING
    NOT (STRING_AGG(U2.""first_name"", ',') =))
}}}


as well as the same query without using `Subquery`:

{{{
query = (
    Book.objects.annotate(
        _annotated_value=StringAgg(
            ""people__first_name"", "","", filter=Q(people__first_name__startswith=""Ja"")
        ),
    )
    .exclude(_annotated_value="""")
)
}}}
 
SQL query:

{{{
SELECT
    ""people_book"".""id"",
    ""people_book"".""category"",
    STRING_AGG(""people_person"".""first_name"", ',') FILTER (WHERE ""people_person"".""first_name""::text LIKE Ja %) AS ""_annotated_value""
FROM
    ""people_book""
    LEFT OUTER JOIN ""people_book_people"" ON (""people_book"".""id"" = ""people_book_people"".""book_id"")
    LEFT OUTER JOIN ""people_person"" ON (""people_book_people"".""person_id"" = ""people_person"".""id"")
GROUP BY
    ""people_book"".""id""
HAVING
    NOT (STRING_AGG(""people_person"".""first_name"", ',') FILTER (WHERE (""people_person"".""first_name""::text LIKE Ja %)) =)
}}}

Just to make sure I wasn't using an old version, I tried the query from #30315, which works fine. 

NB: I originally noticed that bug using ArrayAgg instead of StringAgg, but I encountered another bug using ArrayAgg (or at least what I think is a bug) while writing the example code, I'll report it later if needed
"	Bug	closed	contrib.postgres	3.0	Normal	fixed		Simon Charette	Accepted	1	0	0	0	0	0