[Django] #29139: Aggregate functions failing when using Postgres JSON field KeyTransform
Django
-------------------------------------+-------------------------------------
Reporter: trik | Owner: (none)
Type: Bug | Status: new
Component: | Version: 2.0
contrib.postgres | Keywords: json keytransform
Severity: Normal | unhashable
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When annotating by a nested KeyTransform applied to a JSON field,
aggregate functions fail:
{{{#!python
JSONModel.objects.annotate(history=KeyTransform('-1', 'field'))\
.annotate(last_state=KeyTransform('state', 'history'))\
.filter(last_state__gte=5).count()
JSONModel.objects.annotate(history=KeyTransform('-1', 'field'))\
.annotate(last_state=KeyTransform('state', 'history'))\
.filter(last_state__isnull=False).aggregate(Sum('last_state'))
}}}
{{{
Traceback (most recent call last):
File
"/Users/trik/Projects/3d_party/django/tests/postgres_tests/test_json.py",
line 305, in test_keytransform
.filter(last_state__gte=5).count(),
File "/Users/trik/Projects/3d_party/django/django/db/models/query.py",
line 384, in count
return self.query.get_count(using=self.db)
File
"/Users/trik/Projects/3d_party/django/django/db/models/sql/query.py", line
494, in get_count
number = obj.get_aggregation(using, ['__count'])['__count']
File
"/Users/trik/Projects/3d_party/django/django/db/models/sql/query.py", line
462, in get_aggregation
outer_query.add_subquery(inner_query, using)
File
"/Users/trik/Projects/3d_party/django/django/db/models/sql/subqueries.py",
line 193, in add_subquery
self.subquery, self.sub_params =
query.get_compiler(using).as_sql(with_col_aliases=True)
File
"/Users/trik/Projects/3d_party/django/django/db/models/sql/compiler.py",
line 443, in as_sql
extra_select, order_by, group_by = self.pre_sql_setup()
File
"/Users/trik/Projects/3d_party/django/django/db/models/sql/compiler.py",
line 55, in pre_sql_setup
group_by = self.get_group_by(self.select + extra_select, order_by)
File
"/Users/trik/Projects/3d_party/django/django/db/models/sql/compiler.py",
line 130, in get_group_by
if (sql, tuple(params)) not in seen:
TypeError: unhashable type: 'list'
}}}
[[https://github.com/gnucoop/django/commit/34769e05be720792e8afc2aebdac0230a77e04c0|Failing
test case]]
--
Ticket URL: <https://code.djangoproject.com/ticket/29139>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: trik | Owner: (none)
Type: Bug | Status: new
Severity: Normal | Resolution:
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
Similar exception as #28762 but the fix there doesn't resolve this.
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:1>
Django
-------------------------------------+-------------------------------------
| Domański
Type: Bug | Status: assigned
Component: contrib.postgres | Version: 2.0
Severity: Normal | Resolution:
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: (none) => Piotr Domański
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: trik | Owner: (none)
Type: Bug | Status: new
Severity: Normal | Resolution:
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => new
* owner: Piotr Domański => (none)
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: contrib.postgres | Version: 2.0
Severity: Normal | Resolution:
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: (none) => ddio
Comment:
I resolve this issue when digging into nested KeyTransform.
== Root Cause
1. When using nested KeyTransform, it concatenate child keys in the wrong
format, i.e. a list `[child1, child2]`
2. Instead, according to pgsql, it should be a string like `'{child1,
child2, ...}'`
== Solution
After updating the as_sql() transformation, the issue is resolved. Will
send PR soon.
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: contrib.postgres | Version: 2.0
Severity: Normal | Resolution:
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/10240 PR] (with improvements noted)
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:5>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: contrib.postgres | Version: 2.0
Severity: Normal | Resolution:
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
I closed #29139 as a duplicate but that could be confirmed if a patch is
provided.
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:6>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: contrib.postgres | Version: 2.0
Severity: Normal | Resolution: fixed
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"d87bd29c4f8dfcdf3f4a4eb8340e6770a2416fe3" d87bd29]:
{{{
#!CommitTicketReference repository=""
revision="d87bd29c4f8dfcdf3f4a4eb8340e6770a2416fe3"
Fixed #30335, #29139 -- Fixed crash when ordering or aggregating over a
nested JSONField key transform.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:7>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 2.0 => 2.2
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:8>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: contrib.postgres | Version: 2.2
Severity: Normal | Resolution: fixed
Keywords: json keytransform | Triage Stage: Accepted
unhashable |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"e85317d73113382c96e87f36f0d732e3084df145" e85317d7]:
{{{
#!CommitTicketReference repository=""
revision="e85317d73113382c96e87f36f0d732e3084df145"
[2.2.x] Fixed #30335, #29139 -- Fixed crash when ordering or aggregating
over a nested JSONField key transform.
Backport of d87bd29c4f8dfcdf3f4a4eb8340e6770a2416fe3 from master.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29139#comment:9>