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	SQL-error when updating after annotating using subquery	Oskar Persson	nobody	"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)

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)	1.11	Normal		queryset annotations		Unreviewed	0	0	0	0	0	0