StringAgg with ordering in subquery executes invalid string_agg() SQL function call

[Reupen Shah]

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"

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"