Opened 6 years ago

Closed 4 years ago

Last modified 4 years ago

#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'

Failing test case

Change History (9)

comment:1 Changed 6 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

Similar exception as #28762 but the fix there doesn't resolve this.

comment:2 Changed 6 years ago by Piotr Domański

Owner: set to Piotr Domański
Status: newassigned

comment:3 Changed 5 years ago by Piotr Domański

Owner: Piotr Domański deleted
Status: assignednew

comment:4 Changed 5 years ago by ddio

Owner: set to ddio
Status: newassigned

I resolved 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.

Last edited 5 years ago by ddio (previous) (diff)

comment:5 Changed 5 years ago by Tim Graham

Has patch: set
Patch needs improvement: set

PR (with improvements noted)

comment:6 Changed 5 years ago by Tim Graham

I closed #30085 as a duplicate but that could be confirmed if a patch is provided.

Last edited 5 years ago by Tim Graham (previous) (diff)

comment:7 Changed 4 years ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In d87bd29:

Fixed #30335, #29139 -- Fixed crash when ordering or aggregating over a nested JSONField key transform.

comment:8 Changed 4 years ago by Mariusz Felisiak

Version: 2.02.2

comment:9 Changed 4 years ago by Mariusz Felisiak <felisiak.mariusz@…>

In e85317d7:

[2.2.x] Fixed #30335, #29139 -- Fixed crash when ordering or aggregating over a nested JSONField key transform.

Backport of d87bd29c4f8dfcdf3f4a4eb8340e6770a2416fe3 from master.

Note: See TracTickets for help on using tickets.
Back to Top