#32096: Using KeyTransform in ArrayAgg function produces invalid SQL
-------------------------------------+-------------------------------------
Reporter: Igor Jerosimić | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: KeyTransform, | Triage Stage:
ArrayAgg | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Igor Jerosimić):
* keywords: => KeyTransform, ArrayAgg
Old description:
> Using `KeyTransform` in `ordering` attribute of `ArrayAgg` function
> produces invalid SQL.
>
> {{{
> #!python
> # sample model
> from django.db import models
>
> class Parent(models.Model):
> name = models.CharField(default='test')
>
> class Child(models.Model):
> parent = models.ForeignKey(
> Parent,
> on_delete=models.SET_NULL,
> related_name='children',
> )
> data = models.JSONField(default=dict)
>
> # sample data
> parent = Parent.objects.create()
> Child.objects.create(parent=parent, data={'en': 'English', 'fr':
> 'French'})
>
> # error
> Parent.objects.annotate(
> children_array=ArrayAgg(
> KeyTextTransform('en', 'children__data'),
> distinct=True,
> ordering=[KeyTransform('en', 'children__data')],
> ),
> ).all()
> }}}
>
> Produces invalid SQL in the ORDER BY section:
> {{{
> ARRAY_AGG(DISTINCT ("children"."data" ->> 'default') ORDER BY
> None("children"."data"))
> }}}
>
> NOTE: This was working fine before Django 3.1.
New description:
Using `KeyTransform` in `ordering` attribute of `ArrayAgg` function
produces invalid SQL. I don't know if it matters but I'm using Postgres
for DB.
{{{
#!python
# sample model
from django.db import models
class Parent(models.Model):
name = models.CharField(default='test')
class Child(models.Model):
parent = models.ForeignKey(
Parent,
on_delete=models.SET_NULL,
related_name='children',
)
data = models.JSONField(default=dict)
# sample data
parent = Parent.objects.create()
Child.objects.create(parent=parent, data={'en': 'English', 'fr':
'French'})
# error
Parent.objects.annotate(
children_array=ArrayAgg(
KeyTextTransform('en', 'children__data'),
distinct=True,
ordering=[KeyTransform('en', 'children__data')],
),
).all()
}}}
Produces invalid SQL in the ORDER BY section:
{{{
ARRAY_AGG(DISTINCT ("children"."data" ->> 'default') ORDER BY
None("children"."data"))
}}}
NOTE: This was working fine before Django 3.1.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32096#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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.2c312aba2652ece8335c4b9407847deb%40djangoproject.com.
