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

[Tom Lockhart]

On May 19, 2015, at 8:13 AM, Chi Gao  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

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@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/7ED016F8-6A8C-4CA4-9D06-663F9BEED0C8%40gmail.com.
For more options, visit https://groups.google.com/d/optout.

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

[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


On Thursday, May 14, 2015 at 2:26:04 PM UTC-4, Alejandro Treviño wrote:
>
> 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
>

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@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/95bf9df8-5ea5-46de-87a4-36e4cc8171ce%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

[aRkadeFR]

Hey!

Maybe you should open a ticket :)

Have a good one

On 05/14/2015 08:13 PM, Alejandro Treviño wrote:

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

--
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+unsubscr...@googlegroups.com 
.
To post to this group, send email to django-users@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

--
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@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/55599603.6070902%40arkade.info.
For more options, visit https://groups.google.com/d/optout.

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

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@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.