#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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.29e2aeee507f2d7db62c596755f0f635%40djangoproject.com.
