id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28811	KeyError when using a regular annotation inside an F-statement in a group by annotation	Robin Ramael	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.
"	Bug	closed	Database layer (models, ORM)	1.11	Normal	fixed			Accepted	1	0	0	1	0	0