Possible bug in QuerySet API when chaining filter() and update() methods?

[Alejandro Treviño]

Hello everyone, first-time poster here!

I ran into an interesting scenario earlier today that I thought was worth sharing:

Given this update statement using the django ORM:

    >>> pks = MyModel.objects.all().values_list('pk', flat=True)

    >>> pks

    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, '...(remaining elements truncated)...']

    >>> MyModel.objects.filter(pk__in=pks).update(foo_field=True)

When pks is a "reasonably small" size, the django ORM generates this (valid) SQL statement:

    UPDATE `djangoapp_mymodel` SET `foo_field` = 1 WHERE `djangoapp_mymodel`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

However, when pks is very large (300k records on my data set), it generates this instead:

    UPDATE `djangoapp_mymodel` SET `foo_field` = 1 WHERE `djangoapp_mymodel`.`id` IN (SELECT U0.`id` FROM `djangoapp_mymodel` U0)

Which is not allowed in MySQL:

    django.db.utils.OperationalError: (1093, "You can't specify target table 'djangoapp_mymodel' for update in FROM clause")

I'm wondering if this classifies as a bug, or if this is just a known limitation?  My workaround is to just do the updates in smaller batches.  An update on 100k records still generated valid SQL for me, but I haven't done enough testing to figure out what cut-off point is.

Environment:

Python 3.4.2

Django 1.8.1

mysql  Ver 14.14 Distrib 5.5.43, for osx10.8 (i386) using readline 5.1

Thanks!

Alex

[aRkadeFR]

Hey!

Maybe you should open a ticket :)

Have a good one

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/10d0d83b-733a-47d2-b5d1-6f9c904269a6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
aRkadeFR

[Chi Gao]

I encounter a similar problem. This issue is due to in MySQL "Currently, you cannot update a table and select from the same table in a subquery." A workaround way (in MySQL) is to create a template table from the selection:

 UPDATE `djangoapp_mymodel` SET `foo_field` = 1 WHERE `djangoapp_mymodel`.`id` IN (SELECT U0.`id` FROM `djangoapp_mymodel` U0 AS template_table)

But I started to use django since yesterday and don't know how to fix it in django. Any suggestions?

Thanks

Chi

[Tom Lockhart]

On May 19, 2015, at 8:13 AM, Chi Gao <gaoch...@gmail.com> wrote:

I encounter a similar problem. This issue is due to in MySQL "Currently, you cannot update a table and select from the same table in a subquery." A workaround way (in MySQL) is to create a template table from the selection:

 UPDATE `djangoapp_mymodel` SET `foo_field` = 1 WHERE `djangoapp_mymodel`.`id` IN (SELECT U0.`id` FROM `djangoapp_mymodel` U0 AS template_table)

But I started to use django since yesterday and don't know how to fix it in django. Any suggestions?

Postgres.

hth

- Tom