[Django] #32096: Using KeyTransform in ArrayAgg function produces invalid SQL

87 views
Skip to first unread message

Django

unread,
Oct 9, 2020, 11:14:21 AM10/9/20
to django-...@googlegroups.com
#32096: Using KeyTransform in ArrayAgg function produces invalid SQL
-------------------------------------+-------------------------------------
Reporter: Igor | Owner: nobody
Jerosimić |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/32096>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,
Oct 9, 2020, 11:18:19 AM10/9/20
to django-...@googlegroups.com
#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:

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.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/32096#comment:1>

Django

unread,
Oct 14, 2020, 2:56:25 PM10/14/20
to django-...@googlegroups.com
#32096: Using KeyTransform for JSONField produces invalid SQL in various places.
-------------------------------------+-------------------------------------
Reporter: Igor Jerosimić | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: KeyTransform, | Triage Stage: Ready for
ArrayAgg | checkin
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:"7bfdd3b95131805513465863b49511557182c978" 7bfdd3b9]:
{{{
#!CommitTicketReference repository=""
revision="7bfdd3b95131805513465863b49511557182c978"
Refs #32096 -- Added test for window expressions with JSONField key
transforms.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32096#comment:7>

Django

unread,
Oct 14, 2020, 2:56:25 PM10/14/20
to django-...@googlegroups.com
#32096: Using KeyTransform for JSONField produces invalid SQL in various places.
-------------------------------------+-------------------------------------
Reporter: Igor Jerosimić | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: KeyTransform, | Triage Stage: Ready for
ArrayAgg | checkin
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:"7e1e198494d4fc72cf6e153f9d24fe2493c17dc1" 7e1e1984]:
{{{
#!CommitTicketReference repository=""
revision="7e1e198494d4fc72cf6e153f9d24fe2493c17dc1"
Refs #32096 -- Fixed __in lookup crash against key transforms for
JSONField.

Regression in 6789ded0a6ab797f0dcdfa6ad5d1cfa46e23abcd and
1251772cb83aa4106f526fe00738e51c0eb59122.

Thanks Simon Charette and Igor Jerosimić for the report.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32096#comment:10>

Django

unread,
Oct 14, 2020, 2:56:26 PM10/14/20
to django-...@googlegroups.com
#32096: Using KeyTransform for JSONField produces invalid SQL in various places.
-------------------------------------+-------------------------------------
Reporter: Igor Jerosimić | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: KeyTransform, | Triage Stage: Ready for
ArrayAgg | checkin
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:"1f31027bb3ad460864fbcbbb89eeb328c0a2f184" 1f31027]:
{{{
#!CommitTicketReference repository=""
revision="1f31027bb3ad460864fbcbbb89eeb328c0a2f184"
Refs #32096 -- Fixed crash of ArrayAgg/StringAgg/JSONBAgg with ordering
over JSONField key transforms.

Regression in 6789ded0a6ab797f0dcdfa6ad5d1cfa46e23abcd.

Thanks Igor Jerosimić for the report.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32096#comment:9>

Django

unread,
Oct 14, 2020, 2:56:27 PM10/14/20
to django-...@googlegroups.com
#32096: Using KeyTransform for JSONField produces invalid SQL in various places.
-------------------------------------+-------------------------------------
Reporter: Igor Jerosimić | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: KeyTransform, | Triage Stage: Ready for
ArrayAgg | checkin
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:"bbd55e58639c33b4c5adff5f41b78deffc915c11" bbd55e5]:
{{{
#!CommitTicketReference repository=""
revision="bbd55e58639c33b4c5adff5f41b78deffc915c11"
Refs #32096 -- Fixed ExpressionWrapper crash with JSONField key
transforms.

Regression in 6789ded0a6ab797f0dcdfa6ad5d1cfa46e23abcd.

Thanks Simon Charette and Igor Jerosimić for the report.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32096#comment:11>

Django

unread,
Oct 14, 2020, 2:56:27 PM10/14/20
to django-...@googlegroups.com
#32096: Using KeyTransform for JSONField produces invalid SQL in various places.
-------------------------------------+-------------------------------------
Reporter: Igor Jerosimić | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: KeyTransform, | Triage Stage: Ready for
ArrayAgg | checkin
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:"ee0abac169c2dcc6818d583247903c2a8ef55f7c" ee0abac1]:
{{{
#!CommitTicketReference repository=""
revision="ee0abac169c2dcc6818d583247903c2a8ef55f7c"
Refs #32096 -- Fixed ExclusionConstraint crash with JSONField key
transforms in expressions.

Regression in 6789ded0a6ab797f0dcdfa6ad5d1cfa46e23abcd.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32096#comment:12>

Django

unread,
Oct 14, 2020, 4:13:13 PM10/14/20
to django-...@googlegroups.com
#32096: Using KeyTransform for JSONField produces invalid SQL in various places.
-------------------------------------+-------------------------------------
Reporter: Igor Jerosimić | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: KeyTransform, | Triage Stage: Ready for
ArrayAgg | checkin
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:"536213278c77884e9f985c18a8f9e1158f0d680a" 53621327]:
{{{
#!CommitTicketReference repository=""
revision="536213278c77884e9f985c18a8f9e1158f0d680a"
[3.1.x] Refs #32096 -- Fixed ExclusionConstraint crash with JSONField key
transforms in expressions.

Regression in 6789ded0a6ab797f0dcdfa6ad5d1cfa46e23abcd.

Backport of ee0abac169c2dcc6818d583247903c2a8ef55f7c from master.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32096#comment:17>

Django

unread,
Oct 14, 2020, 4:13:19 PM10/14/20
to django-...@googlegroups.com
#32096: Using KeyTransform for JSONField produces invalid SQL in various places.
-------------------------------------+-------------------------------------
Reporter: Igor Jerosimić | Owner: Mariusz
| Felisiak
Type: Bug | Status: closed

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed

Keywords: KeyTransform, | Triage Stage: Ready for
ArrayAgg | 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


--
Ticket URL: <https://code.djangoproject.com/ticket/32096#comment:18>

Reply all
Reply to author
Forward
0 new messages