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
30315	StringAgg with ordering in subquery generates invalid string_agg() SQL function call	Reupen Shah	Caio Ariede	"Consider the following models (in the `people` app):


{{{
from django.db import models


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.""""""

    people = models.ManyToManyField(Person)
}}}


The following query fails:

{{{
from django.contrib.postgres.aggregates import StringAgg
from django.db.models import Subquery, OuterRef

from people.models import Person, Book

subquery = Book.objects.annotate(
    _annotated_value=StringAgg('people__first_name', ', ', ordering=('people__first_name')),
).filter(
    pk=OuterRef('pk'),
).values(
    '_annotated_value',
)
Book.objects.annotate(_names=Subquery(subquery))

}}}

with the following error:

{{{
ProgrammingError: function string_agg(text, text, unknown) does not exist
}}}

The SQL it executes is as follows:

{{{
SELECT ""people_book"".""id"",
       (SELECT STRING_AGG(U2.""first_name"", U2.""first_name"", ', '
                          ORDER BY ""people_person"".""first_name"") AS ""_annotated_value""
        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"") AS ""_names""
FROM ""people_book""
}}}

This is the case with both Django 2.2 and the (current) master branch.

There are two problems in `STRING_AGG(U2.""first_name"", U2.""first_name"", ', 'ORDER BY ""people_person"".""first_name"")`: 

1. the ordering value is also added to the `string_agg()` call as a positional argument for some unknown reason
2. the ORDER BY expression is referencing `""people_person""` instead of `U2`.

\\


For comparison, the following query executes correctly:

{{{
Book.objects.annotate(
    _names=StringAgg('people__first_name', ', ', ordering=('people__first_name')),
)
}}}

SQL for that query:

{{{
SELECT ""people_book"".""id"",
       STRING_AGG(""people_person"".""first_name"", ', ' ORDER BY ""people_person"".""first_name"") AS ""_names""
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""
}}}

\\


This query also executes correctly:


{{{
subquery = Book.objects.annotate(
    _annotated_value=StringAgg('people__first_name', ', '),
).filter(
    pk=OuterRef('pk'),
).values(
    '_annotated_value',
)
Book.objects.annotate(_names=Subquery(subquery))
}}}

SQL:

{{{
SELECT ""people_book"".""id"",
       (SELECT STRING_AGG(U2.""first_name"", ', ') AS ""_annotated_value""
        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"") AS ""_names""
FROM ""people_book""
}}}
"	Bug	closed	contrib.postgres	2.2	Release blocker	fixed			Ready for checkin	1	0	0	0	0	0