Opened 7 months ago

Closed 5 months ago

Last modified 5 months ago

#30315 closed Bug (fixed)

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

Reported by: Reupen Shah Owned by: Caio Ariede
Component: contrib.postgres Version: 2.2
Severity: Release blocker Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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"

Change History (9)

comment:1 Changed 7 months ago by Reupen Shah

Summary: StringAgg with ordering in subquery executes invalid string_agg() SQL function callStringAgg with ordering in subquery generates invalid string_agg() SQL function call

comment:2 Changed 7 months ago by Reupen Shah

Component: Uncategorizedcontrib.postgres
Description: modified (diff)

comment:3 Changed 7 months ago by felixxm

Triage Stage: UnreviewedAccepted
Version: 2.2master

comment:4 Changed 5 months ago by Caio Ariede

Owner: changed from nobody to Caio Ariede
Status: newassigned

comment:5 Changed 5 months ago by Caio Ariede

Has patch: set

comment:6 Changed 5 months ago by felixxm

Severity: NormalRelease blocker

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

Last edited 5 months ago by felixxm (previous) (diff)

comment:7 Changed 5 months ago by felixxm

Triage Stage: AcceptedReady for checkin
Version: master2.2

comment:8 Changed 5 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In a3f91891:

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

comment:9 Changed 5 months ago by 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.

Note: See TracTickets for help on using tickets.
Back to Top