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
29214	Invalid SQL generated when annotating a subquery with an outerref to an annotated field.	Oskar Persson		"An SQL-error is raised when updating a queryset which includes an annotation created using a subquery, which in turn also uses an annotation.


{{{

class Recursive(models.Model):
    name = models.CharField(max_length=10)
    link = models.IntegerField()
    parent = models.ForeignKey('self', models.CASCADE, null=True)

from django.db.models import OuterRef, Subquery, F

rec1 = Recursive.objects.create(name=""r1"", link=1)
rec2 = Recursive.objects.create(name=""r2"", link=1)
rec3 = Recursive.objects.create(name=""r11"", parent=rec1, link=2)

latest_parent = Recursive.objects.filter(link=OuterRef('parent_link')).order_by('id')

Recursive.objects.filter(parent__isnull=False) \
        .annotate(parent_link=F('parent__link')) \
        .annotate(p=Subquery(latest_parent.values('pk')[:1])) \
        .update(parent_id=F('p'))
}}}

{{{
Traceback (most recent call last):
  File ""tests.py"", line 88, in test_update_annotated_using_related_queryset
    .update(parent_id=F('p'))
  File ""/git/django/django/db/models/query.py"", line 647, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
  File ""/git/django/django/db/models/sql/compiler.py"", line 1204, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File ""/git/django/django/db/models/sql/compiler.py"", line 899, in execute_sql
    raise original_exception
OperationalError: no such column: T2.link
}}}

I've tried this in SQLite and MySQL in Django 1.11.11 and Django 2.0 using Python 3.6.0. All raising similar errors."	Bug	new	Database layer (models, ORM)	dev	Normal		queryset annotations	Mariusz Felisiak Simon Charette Chetan Khanna	Accepted	1	0	0	1	0	0