#29139 closed Bug (fixed)
Aggregate functions failing when using Postgres JSON field KeyTransform
Reported by: | trik | Owned by: | ddio |
---|---|---|---|
Component: | contrib.postgres | Version: | 2.2 |
Severity: | Normal | Keywords: | json keytransform unhashable |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description
When annotating by a nested KeyTransform applied to a JSON field, aggregate functions fail:
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'
Change History (9)
comment:1 by , 7 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 7 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:3 by , 6 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:4 by , 6 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
I resolved this issue when digging into nested KeyTransform.
Root Cause
- When using nested KeyTransform, it concatenate child keys in the wrong format, i.e. a list
[child1, child2]
- 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.
comment:5 by , 6 years ago
Has patch: | set |
---|---|
Patch needs improvement: | set |
PR (with improvements noted)
comment:6 by , 6 years ago
I closed #29139 as a duplicate but that could be confirmed if a patch is provided.
Version 0, edited 6 years ago by (next)
comment:8 by , 5 years ago
Version: | 2.0 → 2.2 |
---|
Note:
See TracTickets
for help on using tickets.
Similar exception as #28762 but the fix there doesn't resolve this.