Opened 8 months ago

Closed 7 months ago

Last modified 6 months ago

#28811 closed Bug (fixed)

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: yes
Easy pickings: no UI/UX: no


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:

from django.db import models

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

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
                   .annotate(multiplied_value_sum=Sum(F('multiplier') * F('value')))


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).
ERROR: test_simple (annotate.tests.AnnotateTests)
Traceback (most recent call last):
  File "/Users/robin/src/ormweirdness/annotate/", 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/", 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/", line 973, in add_annotation
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/", 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/", 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/", 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/", line 471, in resolve_expression
    return query.resolve_ref(, allow_joins, reuse, summarize)
  File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-packages/django/db/models/sql/", 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:!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 8 months ago.

Download all attachments as: .zip

Change History (9)

comment:1 Changed 8 months ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:2 Changed 8 months ago by Robin Ramael

Owner: changed from nobody to Robin Ramael
Status: newassigned

Changed 8 months ago by Robin Ramael

Attachment: patchfor28811.diff added


comment:3 Changed 8 months ago by Robin Ramael

Has patch: set

comment:4 Changed 7 months ago by Tim Graham

Patch needs improvement: set

Are you able to send a pull request? Please reuse an existing model rather than adding a new one. Perhaps using Publisher in tests/annotations would be fine.

comment:5 Changed 7 months ago by Robin Ramael

Opened a PR with the requested changes here:

comment:6 Changed 7 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In fbf64728:

Fixed #28811 -- Fixed crash when combining regular and group by annotations.

comment:7 Changed 6 months ago by Robin Ramael

This bug is also present in 1.11. Any chance it might be merged into that branch as well?

comment:8 Changed 6 months ago by Tim Graham

Not unless it's a regression (see our supported versions policy).

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