[Django] #29214: SQL-error when updating after annotating using subquery

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #29214: SQL-error when updating after annotating using subquery

Django
#29214: SQL-error when updating after annotating using subquery
-------------------------------------+-------------------------------------
               Reporter:  Oskar      |          Owner:  nobody
  Persson                            |
                   Type:  Bug        |         Status:  new
              Component:  Database   |        Version:  1.11
  layer (models, ORM)                |       Keywords:  queryset
               Severity:  Normal     |  annotations
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29214>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/055.f473a6f0158d19b4cb0ad68322705a30%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29214: Invalid SQL when updating after annotating using subquery (was: SQL-error when updating after annotating using subquery)

Django
#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 [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29214: Invalid SQL when updating after annotating using subquery

Django
In reply to this post by Django
#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
-------------------------------------+-------------------------------------

Comment (by Carlton Gibson):

 Related: #29542

--
Ticket URL: <https://code.djangoproject.com/ticket/29214#comment:2>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/070.060a459266d75f56e253f682d4aa57f9%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29214: Invalid SQL when updating after annotating using subquery

Django
In reply to this post by Django
#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 felixxm):

 * cc: felixxm (added)


--
Ticket URL: <https://code.djangoproject.com/ticket/29214#comment:3>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/070.2226eea9e51c9e7d50c29ad1d0162855%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29214: Invalid SQL when updating after annotating using subquery

Django
In reply to this post by Django
#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
-------------------------------------+-------------------------------------

Comment (by Oskar Persson):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/29214#comment:4>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/070.4f7fd098f24990b8562f4b7fca0c43e0%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.