[Django] #28787: MySQL Update fails

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

[Django] #28787: MySQL Update fails

Django
#28787: MySQL Update fails
-----------------------------------------+------------------------
               Reporter:  Paulo          |          Owner:  nobody
                   Type:  Uncategorized  |         Status:  new
              Component:  Uncategorized  |        Version:  1.11
               Severity:  Normal         |       Keywords:
           Triage Stage:  Unreviewed     |      Has patch:  0
    Needs documentation:  0              |    Needs tests:  0
Patch needs improvement:  0              |  Easy pickings:  0
                  UI/UX:  0              |
-----------------------------------------+------------------------
 Given the models below:


 {{{
 class House(models.Model):
     name = models.CharField(max_length=200)

 class Room(models.Model):
     name = models.CharField(max_length=200)
     house = models.ForeignKey(House, on_delete=models.CASCADE)
 }}}


 The following query fails:


 {{{
 houses_with_suites = House.objects.filter(room__name__icontains='suite')
 Room.objects.filter(house__in=houses_with_suites).update(name='suite')
 }}}

 With this error:


 {{{
 OperationalError: (1093, "You can't specify target table 'mysql_test_room'
 for update in FROM clause")
 }}}

 I think this has been around for a while
 (https://code.djangoproject.com/ticket/20300).

--
Ticket URL: <https://code.djangoproject.com/ticket/28787>
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/051.cc10a6dba0398914c03ac4310c53bf1f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #28787: MySQL Update fails

Django
#28787: MySQL Update fails
-------------------------------+--------------------------------------
     Reporter:  Paulo          |                    Owner:  nobody
         Type:  Uncategorized  |                   Status:  new
    Component:  Uncategorized  |                  Version:  1.11
     Severity:  Normal         |               Resolution:
     Keywords:                 |             Triage Stage:  Unreviewed
    Has patch:  0              |      Needs documentation:  0
  Needs tests:  0              |  Patch needs improvement:  0
Easy pickings:  0              |                    UI/UX:  0
-------------------------------+--------------------------------------
Description changed by Paulo:

Old description:

> Given the models below:
>

> {{{
> class House(models.Model):
>     name = models.CharField(max_length=200)
>
> class Room(models.Model):
>     name = models.CharField(max_length=200)
>     house = models.ForeignKey(House, on_delete=models.CASCADE)
> }}}
>

> The following query fails:
>

> {{{
> houses_with_suites = House.objects.filter(room__name__icontains='suite')
> Room.objects.filter(house__in=houses_with_suites).update(name='suite')
> }}}
>
> With this error:
>

> {{{
> OperationalError: (1093, "You can't specify target table
> 'mysql_test_room' for update in FROM clause")
> }}}
>
> I think this has been around for a while
> (https://code.djangoproject.com/ticket/20300).

New description:

 Given the models below:


 {{{
 class House(models.Model):
     name = models.CharField(max_length=200)

 class Room(models.Model):
     name = models.CharField(max_length=200)
     house = models.ForeignKey(House, on_delete=models.CASCADE)
 }}}


 The following query fails:


 {{{
 houses_with_suites = House.objects.filter(room__name__icontains='suite')
 Room.objects.filter(house__in=houses_with_suites).update(name='suite')
 }}}

 With this error:


 {{{
 OperationalError: (1093, "You can't specify target table 'mysql_test_room'
 for update in FROM clause")
 }}}

 I think this has been around for a while
 (https://code.djangoproject.com/ticket/20300).

 I've confirmed this only happens in MySQL. It works on PostgresSQL and
 SQLite.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28787#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/066.0ffa13490d01173f3195756255a9a473%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #28787: QuerySet.update() fails on MySQL if a subquery references the base table (was: MySQL Update fails)

Django
In reply to this post by Django
#28787: QuerySet.update() fails on MySQL if a subquery references the base table
-------------------------------------+-------------------------------------
     Reporter:  Paulo                |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):

 * component:  Uncategorized => Database layer (models, ORM)
 * stage:  Unreviewed => Accepted
 * type:  Uncategorized => Bug


Old description:

> Given the models below:
>

> {{{
> class House(models.Model):
>     name = models.CharField(max_length=200)
>
> class Room(models.Model):
>     name = models.CharField(max_length=200)
>     house = models.ForeignKey(House, on_delete=models.CASCADE)
> }}}
>

> The following query fails:
>

> {{{
> houses_with_suites = House.objects.filter(room__name__icontains='suite')
> Room.objects.filter(house__in=houses_with_suites).update(name='suite')
> }}}
>
> With this error:
>

> {{{
> OperationalError: (1093, "You can't specify target table
> 'mysql_test_room' for update in FROM clause")
> }}}
>
> I think this has been around for a while
> (https://code.djangoproject.com/ticket/20300).
>
> I've confirmed this only happens in MySQL. It works on PostgresSQL and
> SQLite.
New description:

 Given the models below:


 {{{
 class House(models.Model):
     name = models.CharField(max_length=200)

 class Room(models.Model):
     name = models.CharField(max_length=200)
     house = models.ForeignKey(House, on_delete=models.CASCADE)
 }}}


 The following query fails:


 {{{
 houses_with_suites = House.objects.filter(room__name__icontains='suite')
 Room.objects.filter(house__in=houses_with_suites).update(name='suite')
 }}}

 With this error:


 {{{
 OperationalError: (1093, "You can't specify target table 'mysql_test_room'
 for update in FROM clause")
 }}}

 I think this has been around for a while (#20300).

 I've confirmed this only happens in MySQL. It works on PostgresSQL and
 SQLite.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28787#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/066.1a5228d4d62852384065c1c62f3556f6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #28787: QuerySet.update() fails on MySQL if a subquery references the base table

Django
In reply to this post by Django
#28787: QuerySet.update() fails on MySQL if a subquery references the base table
-------------------------------------+-------------------------------------
     Reporter:  Paulo                |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    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/28787#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/066.efc6cdee3125cbd521e5f2f326e7202e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #28787: QuerySet.update() fails on MySQL if a subquery references the base table

Django
In reply to this post by Django
#28787: QuerySet.update() fails on MySQL if a subquery references the base table
-------------------------------------+-------------------------------------
     Reporter:  Paulo                |                    Owner:  nobody
         Type:  Bug                  |                   Status:  closed
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:  invalid
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Sonu kumar):

 * status:  new => closed
 * resolution:   => invalid


Comment:

 This doesn't look to be a bug in Django. This is a more of MySQL query
 parser bug.

 Query generated by Django ORM


 {{{
 UPDATE `CoreApp_room` SET `name` = 'suite'
 WHERE `CoreApp_room`.`house_id` IN
 ( SELECT U0.`id` FROM `CoreApp_house` U0
    INNER JOIN `CoreApp_room` U1
   ON (U0.`id` = U1.`house_id`)
   WHERE U1.`name` LIKE '%suite%'
 )
 }}}

 As we can the query generated by Django is totally valid but MySQL server
 has raise error

--
Ticket URL: <https://code.djangoproject.com/ticket/28787#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/066.10bbb4119bfdbf02c0b61ff1f3498b09%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #28787: QuerySet.update() fails on MySQL if a subquery references the base table

Django
In reply to this post by Django
#28787: QuerySet.update() fails on MySQL if a subquery references the base table
-------------------------------------+-------------------------------------
     Reporter:  Paulo                |                    Owner:  nobody
         Type:  Bug                  |                   Status:  closed
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:  invalid
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Oskar Persson):

 Replying to [comment:4 Sonu kumar]:
 > This doesn't look to be a bug in Django. This is a more of MySQL query
 parser bug.

 This is not a query parser bug but a documented limitation of
 [https://dev.mysql.com/doc/refman/5.7/en/subquery-errors.html MySQL]:

 > You can use a subquery for assignment within an UPDATE statement because
 subqueries are legal in UPDATE and DELETE statements as well as in SELECT
 statements. However, you cannot use the same table (in this case, table
 t1) for both the subquery FROM clause and the update target.

 and [https://mariadb.com/kb/en/library/subquery-limitations/#modifying-
 and-selecting-from-the-same-table MariaDB]:

 > It's not possible to both modify and select from the same table in a
 subquery. For example:
 > {{{
 > DELETE FROM staff WHERE name = (SELECT name FROM staff WHERE age=61);
 > ERROR 1093 (HY000): Table 'staff' is specified twice, both
 >   as a target for 'DELETE' and as a separate source for data
 > }}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28787#comment:5>
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/066.6209e171660332cdc94cc29f5ef60b7a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.