Code

Opened 12 months ago

Closed 10 months ago

Last modified 9 months 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")'

Attachments (0)

Change History (5)

comment:1 Changed 12 months ago by anonymous

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 12 months ago by jacob

  • Resolution set to needsinfo
  • Status changed from new to closed

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 Changed 12 months ago by hjcnbckfd@…

  • Resolution needsinfo deleted
  • Status changed from closed to new

Вот информация о модели 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 Changed 10 months ago by akaariai

  • Component changed from Uncategorized to Database layer (models, ORM)
  • Resolution set to worksforme
  • Status changed from new to closed
  • Type changed from Uncategorized to Bug

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 Changed 9 months ago by 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)

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.