Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#28787 closed Bug (invalid)

QuerySet.update() fails on MySQL if a subquery references the base table

Reported by: Paulo Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords:
Cc: Mariusz Felisiak 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)

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.

Change History (5)

comment:1 by Paulo, 7 years ago

Description: modified (diff)

comment:2 by Tim Graham, 7 years ago

Component: UncategorizedDatabase layer (models, ORM)
Description: modified (diff)
Summary: MySQL Update failsQuerySet.update() fails on MySQL if a subquery references the base table
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

comment:3 by Mariusz Felisiak, 7 years ago

Cc: Mariusz Felisiak added

comment:4 by Sonu kumar, 7 years ago

Resolution: invalid
Status: newclosed

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

in reply to:  4 comment:5 by Oskar Persson, 7 years ago

Replying to 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 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 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
Version 0, edited 7 years ago by Oskar Persson (next)
Note: See TracTickets for help on using tickets.
Back to Top