Opened 12 years ago

Closed 11 years ago

Last modified 7 years ago

#20300 closed Bug (worksforme)

Django 1.5.1, update sql trouble

Reported by: hjcnbckfd@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords: update, orm, mysql, postgresql
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (6)

comment:1 by anonymous, 12 years ago

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

comment:2 by Jacob, 12 years ago

Resolution: needsinfo
Status: newclosed

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!

comment:3 by hjcnbckfd@…, 12 years ago

Resolution: needsinfo
Status: closednew

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

comment:4 by Anssi Kääriäinen, 11 years ago

Component: UncategorizedDatabase layer (models, ORM)
Resolution: worksforme
Status: newclosed
Type: UncategorizedBug

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.

comment:5 by yann.malet@…, 11 years ago

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

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

comment:6 by Tim Graham, 7 years ago

#28787 is a follow up ticket.

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