Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call

[Django]

#30315: StringAgg with ordering in subquery generates invalid string_agg() SQL
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
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Mariusz Felisiak ):

 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: 
Django 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.c6114ff09b1c4984e6b7424817a742f9%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call

[Django]

#30315: StringAgg with ordering in subquery generates invalid string_agg() SQL
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
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Mariusz Felisiak ):

 * 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: 
Django 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.7940857715c96fc3bbd26698fbdbedd5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call

[Django]

#30315: StringAgg with ordering in subquery generates invalid string_agg() SQL
function call
-+-
 Reporter:  Reupen Shah  |Owner:  Caio
 |  Ariede
 Type:  Bug  |   Status:  assigned
Component:  contrib.postgres |  Version:  2.2
 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: 
Django 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.6db869b1363eb671a1c3115d986928e7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call

[Django]

#30315: StringAgg with ordering in subquery generates invalid string_agg() SQL
function call
--+---
 Reporter:  Reupen Shah   |Owner:  Caio Ariede
 Type:  Bug   |   Status:  assigned
Component:  contrib.postgres  |  Version:  master
 Severity:  Release blocker   |   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 felixxm):

 * severity:  Normal => Release blocker


Comment:

 Bumped to the `release blocker` since it is a bug in a new feature.

-- 
Ticket URL: 
Django 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.3eb68b194db36de9df841e5105eb5648%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call

[Django]

#30315: StringAgg with ordering in subquery generates invalid string_agg() SQL
function call
--+---
 Reporter:  Reupen Shah   |Owner:  Caio Ariede
 Type:  Bug   |   Status:  assigned
Component:  contrib.postgres  |  Version:  master
 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 Caio Ariede):

 * has_patch:  0 => 1


Comment:

 [https://github.com/django/django/pull/11413/files PR]

-- 
Ticket URL: 
Django 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.176071c6bd13c4271c5fce9309c2286a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call

[Django]

#30315: StringAgg with ordering in subquery generates invalid string_agg() SQL
function call
--+---
 Reporter:  Reupen Shah   |Owner:  Caio Ariede
 Type:  Bug   |   Status:  assigned
Component:  contrib.postgres  |  Version:  master
 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 Caio Ariede):

 * owner:  nobody => Caio Ariede
 * status:  new => assigned


-- 
Ticket URL: 
Django 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.14041f842b6dcae6290426f28ced%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call

[Django]

#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:  master
 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: 
Django 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.cd5ce0962646de89cf8492f74cc356a5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call

[Django]

#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 

Re: [Django] #30315: StringAgg with ordering in subquery generates invalid string_agg() SQL function call (was: StringAgg with ordering in subquery executes invalid string_agg() SQL function call)

[Django]

#30315: StringAgg with ordering in subquery generates invalid string_agg() SQL
function call
---+--
 Reporter:  Reupen Shah|Owner:  nobody
 Type:  Bug|   Status:  new
Component:  Uncategorized  |  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
---+--

-- 
Ticket URL: 
Django 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.98d21a8253d544fc8878b2e4dd4fc8a0%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.