[Django] #30315: StringAgg with ordering in subquery executes invalid string_agg() SQL function call
Django
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.
Django
function call
-------------------------------+--------------------------------------
Reporter: Reupen Shah | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 2.2
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:1>
Django
function call
----------------------------------+--------------------------------------
Reporter: Reupen Shah | Owner: nobody
Type: Bug | Status: new
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:
> {{{
> from django.db import models
>
>
New description:
{{{
from django.db import models
class Person(models.Model):
"""Person model."""
class Book(models.Model):
"""Book model."""
people = models.ManyToManyField(Person)
}}}
The following query fails:
}}}
with the following error:
This is the case with both Django 2.2 and the (current) master branch.
\\
SQL for that query:
\\
SQL:
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:2>
Django
function call
----------------------------------+------------------------------------
Reporter: Reupen Shah | Owner: nobody
Type: Bug | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* version: 2.2 => master
* stage: Unreviewed => Accepted
Comment:
Reproduced at 1ffddfc233e2d5139cc6ec31a4ec6ef70b10f87f.
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:3>
Django
function call
----------------------------------+---------------------------------------
Reporter: Reupen Shah | Owner: Caio Ariede
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Caio Ariede):
* owner: nobody => Caio Ariede
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:4>
Django
function call
----------------------------------+---------------------------------------
Reporter: Reupen Shah | Owner: Caio Ariede
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Caio Ariede):
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/11413/files PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:5>
Django
function call
----------------------------------+---------------------------------------
Reporter: Reupen Shah | Owner: Caio Ariede
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* severity: Normal => Release blocker
Comment:
Bumped to the `release blocker` since it is a bug in a new feature.
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:6>
Django
function call
-------------------------------------+-------------------------------------
Reporter: Reupen Shah | Owner: Caio
| Ariede
Type: Bug | Status: assigned
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* version: master => 2.2
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:7>
Django
function call
-------------------------------------+-------------------------------------
Reporter: Reupen Shah | Owner: Caio
| Ariede
Component: contrib.postgres | Version: 2.2
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"a3f91891d2c7f4bdc33f48ae70820ef6f36da26e" a3f91891]:
{{{
#!CommitTicketReference repository=""
revision="a3f91891d2c7f4bdc33f48ae70820ef6f36da26e"
Fixed #30315 -- Fixed crash of ArrayAgg and StringAgg with ordering when
used in Subquery.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:8>
Django
function call
-------------------------------------+-------------------------------------
Reporter: Reupen Shah | Owner: Caio
| Ariede
Type: Bug | Status: closed
Component: contrib.postgres | Version: 2.2
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"1172f078eb04e0125413b061d3ccf7a283993b89" 1172f078]:
{{{
#!CommitTicketReference repository=""
revision="1172f078eb04e0125413b061d3ccf7a283993b89"
[2.2.x] Fixed #30315 -- Fixed crash of ArrayAgg and StringAgg with
ordering when used in Subquery.
Backport of a3f91891d2c7f4bdc33f48ae70820ef6f36da26e from master.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30315#comment:9>