Django 1.5.1, update sql trouble

[hjcnbckfd@…]

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")'

[anonymous]

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")'

[Jacob]

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!

[hjcnbckfd@…]

Вот информация о модели 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',)

[Anssi Kääriäinen]

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.

[yann.malet@…]

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

It is a mysql limitation (see the ​last bullet point)

[Tim Graham]

#28787 is a follow up ticket.