#30315: StringAgg with ordering in subquery executes invalid string_agg() SQL
function call
-----------------------------------------+------------------------
Reporter: Reupen Shah | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 2.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
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"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30315>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.144d1afb90e49fc0a6e16969f2fa6ae6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
