Opened 2 weeks ago

Closed 13 days 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: felixxm 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 (4)

comment:1 Changed 2 weeks ago by Paulo

Description: modified (diff)

comment:2 Changed 2 weeks ago by Tim Graham

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 Changed 2 weeks ago by felixxm

Cc: felixxm added

comment:4 Changed 13 days ago by Sonu kumar

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

Note: See TracTickets for help on using tickets.
Back to Top