Using KeyTransform for JSONField produces invalid SQL in various places.
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.
# 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.
Change History
(21)
| Description: |
modified (diff)
|
| Keywords: |
KeyTransform ArrayAgg added
|
| Severity: |
Normal → Release blocker
|
| Triage Stage: |
Unreviewed → Accepted
|
| Cc: |
Sage Abdullah added
|
| Owner: |
changed from nobody to Mariusz Felisiak
|
| Status: |
new → assigned
|
| Has patch: |
set
|
| Summary: |
Using KeyTransform in ArrayAgg function produces invalid SQL → Using KeyTransform for JSONField produces invalid SQL in various places.
|
| Triage Stage: |
Accepted → Ready for checkin
|
| Resolution: |
→ fixed
|
| Status: |
assigned → closed
|
It's due to
OrderableAggMixin.as_sqlcalling its ordering expression as_sql method directly instead of doingcompiler.compile(expr)since the latter properly handles the vendor logic.This wasn't an issue when
KeyTransformwas only implemented forcontrib.postgresas it's implementation was not vendored but now it does.There might other instances of this problem lying around in
django.db.models.expressionsand friends since it's the first time I've seen this issue manifest itself and I don't remember this is something we kept an eye for during code reviews.