#28811: KeyError when using a regular annotation inside an F-statement in a
group
by annotation
-------------------------------------+-------------------------------------
Reporter: Robin | Owner: nobody
Ramael |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28811>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/054.83aa70a4733176ac3dc536c575e68c94%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
