Opened 7 years ago

Closed 7 years ago

Last modified 4 years ago

#8254 closed (fixed)

incorrect sql query in many to many backwards relation using an intermediate table

Reported by: ido Owned by: mtredinnick
Component: Core (Other) Version: master
Severity: Keywords: m2m many2many ManyToMany sql query through
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

using the following models:

class Project(models.Model):
    name       = models.CharField(max_length = 30, primary_key = True)
    startDate  = models.DateField(auto_now_add = True)
    active     = models.BooleanField(default = True)

    def __unicode__(self):
        return self.name

class Tag(models.Model):
    id      = models.AutoField(primary_key = True)
    name    = models.CharField(max_length = 100)
    type    = models.CharField(max_length = 100)
    project = models.ForeignKey(Project)

    class Meta:
        unique_together = ('name', 'project')

    def __unicode__(self):
        return self.name

class Document(models.Model):
    text      = models.CharField(max_length=200)
    project   = models.ForeignKey(Project)
    tags      = models.ManyToManyField(Tag, through='Document2Tag')

    class Meta:
        unique_together = ("text", "project")

    def __unicode__(self):
        return unicode(self.id)

class Document2Tag(models.Model):
    document  = models.ForeignKey(Document)
    tag     = models.ForeignKey(Tag)
    user    = models.ForeignKey(User)

    def __unicode__(self):
        return u'(%s, %s, %s)' % (self.document.id, self.tag.name, self.user.username)

I've made the following query:

>>> from general.models import *
>>> from django.contrib.auth.models import User
>>> user1 = User.objects.get(username='user1')
>>> user2 = User.objects.get(username='user2')
>>> project = Project.objects.get(name='project1')
>>> Document.objects.all()
[<Document: 1>, <Document: 2>]
>>> Tag.objects.all()
[<Tag: tag1>, <Tag: tag2>]
>>> Document2Tag.objects.all()
[<Document2Tag: (1, tag2, user2)>, <Document2Tag: (1, tag1, user1)>]
>>> tag1 = Tag.objects.get(name='tag1')
>>> tag1.document_set.filter(document2tag__user=user1)
[<Document: 1>]
>>> tag1.document_set.filter(document2tag__user=user2)
[<Document: 1>]

as you can see in the above scenario, 'tag1' is incorrectly associated with 'document1' when querying about 'user2'

the expected result would be an empty list on the last query.

the sql query for reference:

>>> print tag1.document_set.filter(document2tag__user=user2).query
SELECT `general_document`.`id`, `general_document`.`text`, `general_document`.`project_id` 
FROM `general_document` 
INNER JOIN `general_document2tag` 
    ON (`general_document`.`id` = `general_document2tag`.`document_id`) 
INNER JOIN `general_document2tag` T4 
    ON (`general_document`.`id` = T4.`document_id`) 
WHERE (`general_document2tag`.`tag_id` = BINARY 1  AND T4.`user_id` = BINARY 3 )

(note the double inner join with general_document2tag)

Change History (4)

comment:1 Changed 7 years ago by mtredinnick

  • milestone changed from 1.0 beta to 1.0
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

This is just a bug (maybe; I haven't examined it to see what's going on yet, but the example looks simple enough to be suspicious). It doesn't require additional feature additions to fix, so it's not on the beta track.

comment:2 Changed 7 years ago by mtredinnick

  • Owner changed from nobody to mtredinnick

Related to #8046, but probably not fixed by that commit -- I need to write a proper test case (a smaller example) first. Doing that now.

comment:3 Changed 7 years ago by mtredinnick

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

(In [8521]) Added a test to show that [8472] also fixed #8254.

comment:4 Changed 4 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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