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

[Robin Ramael]

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.

[Robin Ramael]

patch

[Tim Graham]

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.

[Robin Ramael]

Opened a PR with the requested changes here: ​https://github.com/django/django/pull/9528/

[Tim Graham <timograham@…>]

In fbf64728:

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

[Robin Ramael]

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

[Tim Graham]

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