Opened 7 months ago

Last modified 3 months ago

#29214 new Bug

Invalid SQL when updating after annotating using subquery

Reported by: Oskar Persson Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: queryset annotations
Cc: felixxm Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

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.

Change History (4)

comment:1 Changed 7 months ago by Tim Graham

Description: modified (diff)
Summary: SQL-error when updating after annotating using subqueryInvalid SQL when updating after annotating using subquery
Triage Stage: UnreviewedAccepted

comment:2 Changed 4 months ago by Carlton Gibson

Related: #29542

comment:3 Changed 3 months ago by felixxm

Cc: felixxm added

comment:4 Changed 3 months ago by Oskar Persson

Just tried this with the fix for #29542 and the problem remains.

Note: See TracTickets for help on using tickets.
Back to Top