#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
--------------------------------------------+------------------------
Reporter: Laurent Tramoy | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | 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 |
--------------------------------------------+------------------------
This issue is similar to issue #30315 but using the `filter` keyword
instead of `ordering`, so I'll reuse the same structure. I tested it on
Django 2.2.8
Consider the following models (in the people app):
{{{
from django.db import models
from django.contrib.postgres.fields import ArrayField
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."""
category = ArrayField(models.CharField(max_length=32), null=True,
default=list)
people = models.ManyToManyField(Person)
}}}
with the following objects:
{{{
p1 = Person.objects.create(first_name="John", last_name="Doe")
p2 = Person.objects.create(first_name="Jane", last_name="Doe")
b1 = Book.objects.create()
b1.people.add(p1)
b2 = Book.objects.create()
b2.people.add(p1, p2)
b3 = Book.objects.create()
}}}
This fails:
{{{
from django.contrib.postgres.aggregates import StringAgg
from django.db.models import Subquery, OuterRef, Q
from people.models import Book
subquery = (
Book.objects.annotate(
_annotated_value=StringAgg(
"people__first_name", ",",
filter=Q(people__first_name__startswith="Ja")
),
)
.filter(pk=OuterRef("pk"),)
.exclude(_annotated_value="")
.values("id")
)
Book.objects.filter(id__in=Subquery(subquery))
}}}
The generated SQL is
{{{
SELECT
"people_book"."id",
"people_book"."category"
FROM
"people_book"
WHERE
"people_book"."id" IN (
SELECT
U0."id"
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"
HAVING
NOT (STRING_AGG(, ',') FILTER (WHERE U2."first_name") =))
}}}
as we can see, the `STRING_AGG argument is wrong.
The same query without the `filter` works fine:
{{{
subquery = (
Book.objects.annotate(
_annotated_value=StringAgg(
"people__first_name", ","
),
)
.filter(pk=OuterRef("pk"),)
.exclude(_annotated_value="")
.values("id")
)
Book.objects.filter(id__in=Subquery(subquery))
}}}
SQL query:
{{{
SELECT
"people_book"."id",
"people_book"."category"
FROM
"people_book"
WHERE
"people_book"."id" IN (
SELECT
U0."id"
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"
HAVING
NOT (STRING_AGG(U2."first_name", ',') =))
}}}
as well as the same query without using `Subquery`:
{{{
query = (
Book.objects.annotate(
_annotated_value=StringAgg(
"people__first_name", ",",
filter=Q(people__first_name__startswith="Ja")
),
)
.exclude(_annotated_value="")
)
}}}
SQL query:
{{{
SELECT
"people_book"."id",
"people_book"."category",
STRING_AGG("people_person"."first_name", ',') FILTER (WHERE
"people_person"."first_name"::text LIKE Ja %) AS "_annotated_value"
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"
HAVING
NOT (STRING_AGG("people_person"."first_name", ',') FILTER (WHERE
("people_person"."first_name"::text LIKE Ja %)) =)
}}}
Just to make sure I wasn't using an old version, I tried the query from
#30315, which works fine.
NB: I originally noticed that bug using ArrayAgg instead of StringAgg, but
I encountered another bug using ArrayAgg (or at least what I think is a
bug) while writing the example code, I'll report it later if needed
--
Ticket URL: <https://code.djangoproject.com/ticket/31097>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.29e2aeee507f2d7db62c596755f0f635%40djangoproject.com.
