[Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

classic Classic list List threaded Threaded
30 messages Options
12
Reply | Threaded
Open this post in threaded view
|

[Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
#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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/049.29e2aeee507f2d7db62c596755f0f635%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
#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            |               Resolution:
     Keywords:                    |             Triage Stage:  Unreviewed
    Has patch:  0                 |      Needs documentation:  0
  Needs tests:  0                 |  Patch needs improvement:  0
Easy pickings:  0                 |                    UI/UX:  0
----------------------------------+--------------------------------------

Comment (by Simon Charette):

 The fact the grouping conditions are surfaced to the outer query makes me
 believe it could be related to another issue that was addressed in 3.0.
 Could you try reproducing with Django 3.0 as well.

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:1>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.246fea9b92251a6c15966a8500061fc2%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#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:  3.0
     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 Baptiste Mispelon):

 * version:  2.2 => 3.0
 * stage:  Unreviewed => Accepted


Comment:

 Reproduced on current master (ff00a053478fee06bdfb4206c6d4e079e98640ff)
 using the code in the ticket.

 Here's the prettified query reported by `--debug-sql` after the testcase
 failure:
 {{{#!sql lineno=1 marks=20
 SELECT
   "aaaaaaticket31097_book"."id",
   "aaaaaaticket31097_book"."category"
 FROM
   "aaaaaaticket31097_book"
 WHERE
   "aaaaaaticket31097_book"."id" IN (
     SELECT
       U0."id"
     FROM
       "aaaaaaticket31097_book" U0
       LEFT OUTER JOIN "aaaaaaticket31097_book_people" U1 ON (U0."id" =
 U1."book_id")
       LEFT OUTER JOIN "aaaaaaticket31097_person" U2 ON (U1."person_id" =
 U2."id")
     WHERE
       U0."id" = "aaaaaaticket31097_book"."id"
     GROUP BY
       U0."id"
     HAVING
       NOT (
         STRING_AGG(, ',') FILTER (
           WHERE
             U2."first_name"
         ) = ''
       )
   )
 }}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.c111169b42b931eb40e021ed15d1c5de%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  assigned
    Component:  contrib.postgres     |                  Version:  3.0
     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 David Wobrock):

 * status:  new => assigned
 * owner:  (none) => David Wobrock


Comment:

 Hi, I'm claiming the ticket if nobody bothers.
 I already wrote a testcase that reproduced the bug and started to pin down
 the source of the issue.

 The first (very) technical info I found:
 It seems to be linked to the logic of relabeling aliases of the the filter
 clause in {{{django.db.models.sql.query.Query.change_aliases}}}, in the
 {{{self.where.relabel_aliases(change_map)}}} logic.
 The first node itself is a {{{WhereNode}}} containing {{{(AND: (NOT (AND:
 <django.db.models.lookups.Exact object at 0x7f9ea9bfc9b0>)))}}}
 When going down the filter tree, we first hit again a {{{WhereNode}}} with
 the negation: {{{(NOT (AND: <django.db.models.lookups.Exact object at
 0x7f9ea9bfc9b0>))}}} for our exclude clause.
 Finally coming to the {{{Exact}}} object, we relabel the left hand-side of
 the condition, being the {{{StringAgg}}}.
 This calls the
 {{{django.db.models.expressions.BaseExpression.relabeled_clone}}} where we
 depend on the fields found by {{{self.get_source_expressions()}}}

 Either, those source expressions are incomplete because of the way the
 {{{OrderableAggMixin}}} plays with the {{{self.get_source_expressions()}}}
 (not calling the super), or the {{{change_map}}} which is passed down is
 missing some potential aliases.

 I'll try to keep investigating and propose a patch in the coming weeks :)

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:3>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.b3aabc4a0f4529a531134e1c866b859f%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  assigned
    Component:  contrib.postgres     |                  Version:  3.0
     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 Simon Charette):

 * cc: Simon Charette (added)


Comment:

 Did you manage to reproduce against the latest `master` as well? It looks
 like it might have been fixed by commits related to #31094.

 If it's still the case you seem to be heading in the right direction;
 there's likely an issue with `OrderableAggMixin` and the use of `filter`
 given how both require some special source expressions handling.

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:4>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.6475e0d1406e2c3c354828c1a4533901%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  assigned
    Component:  contrib.postgres     |                  Version:  3.0
     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
-------------------------------------+-------------------------------------

Comment (by David Wobrock):

 Yes, I do reproduce on the latest {{{master}}}. I'll keep digging in that
 direction.

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:5>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.1ac5f6bf2e17845cf8effc785f4f5562%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  assigned
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:
     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 David Wobrock):

 * has_patch:  0 => 1


Comment:

 The PR https://github.com/django/django/pull/12259

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:6>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.773d7ea1b332faf1eb1af75bfef64b7e%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  assigned
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  1
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

 * needs_better_patch:  0 => 1


Comment:

 Left comments for improvement on the tests but the corrective seems good
 to me.

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:7>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.adf41cd4f71dc0f6b695e2a38aba9e57%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  assigned
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:
     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 Simon Charette):

 * needs_better_patch:  1 => 0


Comment:

 Adjusted patch seems ready for a final review.

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:8>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.64084a588a58a982720ec6c8f26875de%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     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 Mariusz Felisiak <felisiak.mariusz@…>):

 * status:  assigned => closed
 * resolution:   => fixed


Comment:

 In [changeset:"2f565f84aca136d9cc4e4d061f3196ddf9358ab8" 2f565f84]:
 {{{
 #!CommitTicketReference repository=""
 revision="2f565f84aca136d9cc4e4d061f3196ddf9358ab8"
 Fixed #31097 -- Fixed crash of ArrayAgg and StringAgg with filter when
 used in Subquery.
 }}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:10>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.cd79a3da3a5aaf19882f7285fdc1f508%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  assigned
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

 In [changeset:"7d44aeb388a33ac56353a658b41a6119e93931ff" 7d44aeb3]:
 {{{
 #!CommitTicketReference repository=""
 revision="7d44aeb388a33ac56353a658b41a6119e93931ff"
 Refs #31097 -- Added tests for filter in ArrayAgg and StringAgg.
 }}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:9>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.07fe049e6c01ac5d6fcc97dd59868c45%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by yozzo):

 Replying to [comment:10 Mariusz Felisiak <felisiak.mariusz@…>]:
 > In [changeset:"2f565f84aca136d9cc4e4d061f3196ddf9358ab8" 2f565f84]:
 > {{{
 > #!CommitTicketReference repository=""
 revision="2f565f84aca136d9cc4e4d061f3196ddf9358ab8"
 > Fixed #31097 -- Fixed crash of ArrayAgg and StringAgg with filter when
 used in Subquery.
 > }}}

 Can this fix be backported to v2.2.x also, please?

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:11>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.57a1c591c9b9bbde09bc1bcb3ddc9672%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by felixxm):

 Replying to [comment:11 John Fieldman]:
 > Can this fix be backported to 3.0.x and 2.2.x also, please?

 This patch doesn't qualify for a backport.

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:12>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.d2d128a50f1ed002b0995d8de6933fe0%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by John Fieldman):

 Replying to [comment:12 felixxm]:
 > This patch doesn't qualify for a backport.

 The issue is happening for 3.0.x and 2.2.x also, should it be solved
 differently for these versions, or won't be solved at all?

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:13>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.1fb34181791e9690b6484dd48467dd81%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

 John, based on the investigation that lead to this patch the issue has
 been present since the feature was introduced.

 Per our backporting policy this means it doesn't qualify for a backport to
 3.0.x or 2.2.x anymore.

 See https://docs.djangoproject.com/en/3.0/internals/release-process/ for
 more details.

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:14>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.36f82d08c8a3eac060adfe083e1ad453%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Reupen Shah):

 It does seem that there was a regression in Django 2.2 to me.

 The following query works in Django 2.1.15 and master, but not 2.2.9 and
 3.0.x:

 {{{#!python
 from django.contrib.postgres.aggregates import StringAgg
 from django.db.models import CharField, OuterRef, Q, Subquery

 subquery = Book.objects.annotate(
     _annotated_value=StringAgg(
         'people__first_name', ', ',
         filter=Q(people__first_name='Jane'),
         output_field=CharField(),
     ),
 ).filter(
     pk=OuterRef('pk'),
 ).values(
     '_annotated_value',
 )

 queryset = Book.objects.annotate(names=Subquery(subquery))

 print([book.names for book in queryset])

 }}}

 (I can file a separate bug for that if you want, though.)

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:15>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.7dff142b20cf4b72f6652414227ce6a3%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

 Reupen, even if this was a regression in 2.2 this release series is only
 receiving security and data loss backport at this point.

 Can you confirm this is the case Mariusz?

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:16>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.3e20a80ff538f57d21b4e9aa638d7fbd%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by felixxm):

 Yes it's a regression in 96199e562dcc409ab4bdc2b2146fa7cf73c7c5fe (Django
 2.2) so when we added support for `ordering` we also introduced a
 regression in `filter`. Nevertheless Django 2.2 is already in extended
 support so this doesn't qualify for a backport.

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:17>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.17a7a191aecbcf5b55968d45a49b2b93%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Claude Paroz):

 But maybe a backport to 3.0.x?

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:18>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.68f4c5cfd4dd75c6c7d1bcdd96e0128f%40djangoproject.com.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #31097: StringAgg And ArrayAgg with filtering in subquery generates invalid string_agg() SQL function call

Django
In reply to this post by Django
#31097: StringAgg And ArrayAgg with filtering in subquery generates invalid
string_agg() SQL function call
-------------------------------------+-------------------------------------
     Reporter:  Laurent Tramoy       |                    Owner:  David
                                     |  Wobrock
         Type:  Bug                  |                   Status:  closed
    Component:  contrib.postgres     |                  Version:  3.0
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by felixxm):

 Normally we backport only regression from the latest version (in the
 mainstream support), e.g. we'll not backport regressions introduced in
 Django < 3.0 to the Django 3.0, unless it's "critical".

--
Ticket URL: <https://code.djangoproject.com/ticket/31097#comment:19>
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/064.82664430af0bc624971662ec64323a88%40djangoproject.com.
12