Opened 4 weeks ago

Last modified 3 weeks ago

#28811 assigned Bug

KeyError when using a regular annotation inside an F-statement in a group by annotation

Reported by: Robin Ramael Owned by: Robin Ramael
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When a queryset is first annotated in the regular way (just adding a column), and then that annotation is used inside an F-expression in a group by-annotation, the query generation crashes with a KeyError because it can't find the first annotation.

Code to reproduce:

models.py:

from django.db import models

class Data(models.Model):
    option = models.CharField(max_length=2)
    value = models.FloatField()

tests.py:

from django.db.models import F, Sum, Value
from django.test import TestCase

from .models import Data, Thing


class AnnotateTests(TestCase):

    def test_simple(self):

        Data.objects.create(option='A', value=1)
        Data.objects.create(option='A', value=2)

        Data.objects.create(option='B', value=3)
        Data.objects.create(option='B', value=4)

        data_qs = (Data.objects
                   .annotate(multiplier=Value(3))   # will of course be far more complex in the wild
                   # group by option => sum of value * multiplier
                   .values('option')
                   .annotate(multiplied_value_sum=Sum(F('multiplier') * F('value')))
                   .order_by())

        print(list(data_qs))

There is a workaround, though: replacing the F-expression in the second with the value of the first doesn't require the annotation lookup, avoiding the KeyError, so the solution for this might just be a better error message, although this gets unwieldy for complex annotations.

Running this test will result in the following traceback:

Creating test database for alias 'default'...
System check identified no issues (0 silenced).
E
======================================================================
ERROR: test_simple (annotate.tests.AnnotateTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/robin/src/ormweirdness/annotate/tests.py", line 21, in test_simple
    .annotate(multiplied_value_sum=Sum(F('multiplier') * F('value')))
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/query.py", line 945, in annotate
    clone.query.add_annotation(annotation, alias, is_summary=False)
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/sql/query.py", line 973, in add_annotation
    summarize=is_summary)
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/aggregates.py", line 19, in resolve_expression
    c = super(Aggregate, self).resolve_expression(query, allow_joins, reuse, summarize)
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/expressions.py", line 548, in resolve_expression
    c.source_expressions[pos] = arg.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/expressions.py", line 411, in resolve_expression
    c.lhs = c.lhs.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/expressions.py", line 471, in resolve_expression
    return query.resolve_ref(self.name, allow_joins, reuse, summarize)
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1472, in resolve_ref
    return self.annotation_select[name]
KeyError: 'multiplier'

----------------------------------------------------------------------
Ran 1 test in 0.004s

FAILED (errors=1)
Destroying test database for alias 'default'...

I've found one person that also seemed to have this problem on the django-users mailing list: https://groups.google.com/forum/#!topic/django-users/SYAGaVuEjHY

Happens on Django 1.11.7, python3.6. sqlite3 as well as postgres10.

Attachments (1)

patchfor28811.diff (4.5 KB) - added by Robin Ramael 3 weeks ago.
patch

Download all attachments as: .zip

Change History (4)

comment:1 Changed 4 weeks ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:2 Changed 3 weeks ago by Robin Ramael

Owner: changed from nobody to Robin Ramael
Status: newassigned

Changed 3 weeks ago by Robin Ramael

Attachment: patchfor28811.diff added

patch

comment:3 Changed 3 weeks ago by Robin Ramael

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