StringAgg with ordering in subquery generates 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"

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"

[Mariusz Felisiak]

Reproduced at 1ffddfc233e2d5139cc6ec31a4ec6ef70b10f87f.

[Caio Ariede]

​PR

[Mariusz Felisiak]

Bumped to the release blocker since it is a bug in a new feature.

[Mariusz Felisiak <felisiak.mariusz@…>]

In a3f91891:

Fixed #30315 -- Fixed crash of ArrayAgg and StringAgg with ordering when used in Subquery.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 1172f078:

[2.2.x] Fixed #30315 -- Fixed crash of ArrayAgg and StringAgg with ordering when used in Subquery.

Backport of a3f91891d2c7f4bdc33f48ae70820ef6f36da26e from master.