{{{
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.
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>
* 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>
* cc: felixxm (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28787#comment:3>
* 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>
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>