[Django] #20300: Django 1.5.1, update sql trouble

Django

unread,

Apr 21, 2013, 5:53:50 PM4/21/13

to django-...@googlegroups.com

#20300: Django 1.5.1, update sql trouble
------------------------------+--------------------------------------------
Reporter: hjcnbckfd@… | Owner: nobody
Type: | Status: new
Uncategorized | Version: 1.5
Component: | Keywords: update, orm, mysql, postgresql
Uncategorized | Has patch: 0
Severity: Normal | UI/UX: 0
Triage Stage: Unreviewed |
Easy pickings: 0 |
------------------------------+--------------------------------------------
Django 1.5.1

{{{
Comment.objects.filter(post_id__in=[1,2,3]).exclude(post_id__in=[4,5]) \
.update(is_published=True)
}}}

Такой queryset дает для PostgeSQL запрос вида:

{{{
UPDATE "message_comment" SET "is_published" = true WHERE
"message_comment"."post_id" IN (1, 2, 3)
}}}

Для MySQL же выдается:

{{{
UPDATE `message_comment` SET `is_published` = 1 WHERE
`message_comment`.`post_id` IN (SELECT U0.`post_id` FROM `message_comment`
U0 WHERE U0.`post_id` IN (1, 2, 3)); args=(True, 1, 2, 3)
}}}

Конечно же MySQL выдает ошибку ''''DatabaseError: (1093, "You can't
specify target table 'message_comment' for update in FROM clause")''''

--
Ticket URL: <https://code.djangoproject.com/ticket/20300>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Apr 21, 2013, 6:04:10 PM4/21/13

to django-...@googlegroups.com

#20300: Django 1.5.1, update sql trouble

-------------------------------------+-------------------------------------
Reporter: hjcnbckfd@… | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.5
Severity: Normal | Resolution:
Keywords: update, orm, mysql, | Triage Stage:
postgresql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by anonymous):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

Django 1.5.1

{{{
Comment.objects.filter(post_id__in=[1,2,3]).exclude(post_id__in=[4,5]) \
.update(is_published=True)
}}}

Specified queryset produce followed sql code for PostgreSQL:

{{{
UPDATE "message_comment" SET "is_published" = true WHERE
"message_comment"."post_id" IN (1, 2, 3)
}}}

And for MySQL:

{{{
UPDATE `message_comment` SET `is_published` = 1 WHERE
`message_comment`.`post_id` IN (SELECT U0.`post_id` FROM `message_comment`
U0 WHERE U0.`post_id` IN (1, 2, 3)); args=(True, 1, 2, 3)
}}}

This causes next error message: 'DatabaseError: (1093, "You can't specify

target table 'message_comment' for update in FROM clause")'

--
Ticket URL: <https://code.djangoproject.com/ticket/20300#comment:1>

Django

unread,

Apr 22, 2013, 5:55:35 PM4/22/13

to django-...@googlegroups.com

#20300: Django 1.5.1, update sql trouble

-------------------------------------+-------------------------------------
Reporter: hjcnbckfd@… | Owner: nobody
Type: Uncategorized | Status: closed
Component: Uncategorized | Version: 1.5
Severity: Normal | Resolution: needsinfo

Changes (by jacob):

* status: new => closed
* resolution: => needsinfo

Comment:

I think we need more info if we're going to fix this -- can you share your
`Comment` model, please?

Please feel free to reopen the ticket once you've provided that info!

--
Ticket URL: <https://code.djangoproject.com/ticket/20300#comment:2>

Django

unread,

Apr 22, 2013, 6:12:39 PM4/22/13

to django-...@googlegroups.com

#20300: Django 1.5.1, update sql trouble

-------------------------------------+-------------------------------------
Reporter: hjcnbckfd@… | Owner: nobody
Type: Uncategorized | Status: new

Component: Uncategorized | Version: 1.5
Severity: Normal | Resolution:

Changes (by hjcnbckfd@…):

* status: closed => new
* resolution: needsinfo =>

Comment:

Вот информация о модели Comment:
{{{
class StampModel(models.Model):
updated_at = models.DateTimeField(auto_now=True)
created_at = models.DateTimeField(auto_now_add=True)

class Meta:
abstract = True

class CommentManager(Manager):

def published(self):
return self.get_query_set().filter(is_published=True)

class Comment(StampModel):

complaint = models.ForeignKey(Complaint, verbose_name=_('Complaint'),
related_name='comments')
message = models.TextField(_('Message'))
thread_id = models.BigIntegerField(_('Thread ID'))
post_id = models.BigIntegerField(_('Post ID'), db_index=True)
author = models.CharField(_('Author'), max_length=128)
is_published = models.BooleanField(_('Published'), default=True)

objects = CommentManager()

class Meta:
ordering = ('-created_at',)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/20300#comment:3>

Django

unread,

Jun 16, 2013, 4:40:38 AM6/16/13

to django-...@googlegroups.com

#20300: Django 1.5.1, update sql trouble

Changes (by akaariai):

* status: new => closed

* resolution: => worksforme
* component: Uncategorized => Database layer (models, ORM)
* type: Uncategorized => Bug

Comment:

I can't reproduce this, the SQL I get on master is:
{{{
[{u'time': u'0.000', u'sql': u'UPDATE `delete_regress_comment` SET
`is_published` = 1 WHERE (`delete_regress_comment`.`post_id` IN (1, 2, 3)
AND NOT (`delete_regress_comment`.`post_id` IN (4, 5)))'}]
}}}
which seems correct.

--
Ticket URL: <https://code.djangoproject.com/ticket/20300#comment:4>

Django

unread,

Jul 29, 2013, 5:36:53 AM7/29/13

to django-...@googlegroups.com

#20300: Django 1.5.1, update sql trouble

-------------------------------------+-------------------------------------
Reporter: hjcnbckfd@… | Owner: nobody

Comment (by yann.malet@…):

I stumble on this issue too earlier today on django 1.5.1.

It is a mysql limitation (see the [http://dev.mysql.com/doc/refman/5.0/en
/subquery-errors.html last bullet point])

--
Ticket URL: <https://code.djangoproject.com/ticket/20300#comment:5>

Django

unread,

Nov 8, 2017, 8:55:43 AM11/8/17

to django-...@googlegroups.com

#20300: Django 1.5.1, update sql trouble

-------------------------------------+-------------------------------------
Reporter: hjcnbckfd@… | Owner: nobody