#29214: Invalid SQL when updating after annotating using subquery
-------------------------------------+-------------------------------------
Reporter: Oskar Persson | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset | Triage Stage: Accepted
annotations |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
Old description:
> 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.
New description:
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.
--
Comment:
Reproduced at feb683c4c2c5ecfb61e4cb490c3e357450c0c0e8.
--
Ticket URL: <https://code.djangoproject.com/ticket/29214#comment:1>
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/070.c64f31171ee768b0a0b3c64fe15dc24d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
