#30315: StringAgg with ordering in subquery generates invalid string_agg() SQL
function call
----------------------------------+--------------------------------------
Reporter: Reupen Shah | Owner: nobody
Type: Bug | Status: new
Component: contrib.postgres | Version: 2.2
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------
Changes (by Reupen Shah):
* component: Uncategorized => contrib.postgres
Old description:
> 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"
> }}}
New description:
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"
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:2>
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/064.6ac16f663cc5651bf40ceb509fed0f58%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
