Opened 10 years ago

Closed 10 years ago

Last modified 7 years ago

#7372 closed (duplicate)

queryset intersection either returns wrong result or raises KeyError depending on order (after queryset-refactor merge)

Reported by: omat@… Owned by: nobody
Component: Core (Other) Version: master
Severity: Keywords: qsrf-cleanup queryset union, combine querysets
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


qs1 and qs2 are 2 combinable query sets.

qs1 & qs2 resutls in qs2 (the larger set) where it should give the intersection, and

qs2 & qs1 raises KeyError.

And here are the details:

The query representing qs1 is:

SELECT "notes_note"."id", "notes_note"."title", "notes_note"."slug", "notes_note"."note", 
       "notes_note"."tags", "notes_note"."location", "notes_note"."user_id", "notes_note"."added"
FROM "notes_note" , "favorites_favorite" 
WHERE "favorites_favorite".content_type_id = 22 
    AND "favorites_favorite".user_id = 30006 
    AND "notes_note"."id" = "favorites_favorite".object_id

The query representing qs2 is:

SELECT "notes_note"."id", "notes_note"."title", "notes_note"."slug", "notes_note"."note", 
       "notes_note"."tags", "notes_note"."location", "notes_note"."user_id", "notes_note"."added"
FROM "notes_note" 
WHERE NOT ("notes_note"."title" IS NULL)

The query representing (qs1 & qs2) is equivalent to the one of qs2, which is the larger query set.

When the order is reversed, i.e. (qs2 & qs1) results in "KeyError: 'favorites_favorite'"

Change History (6)

comment:1 Changed 10 years ago by jbronn

Can you please provide your model definitions here so we can determine if this a Django problem and/or a third-party application problem (I see that you have tags) here. The raw SQL is insufficient to make a diagnosis.

comment:2 Changed 10 years ago by omat@…

I don't think "tags" are specifically important, because it is not involved in a filter condition. It is a TagField provided by django-tagging application and is an instance of CharField. IMHO, changing of the exception with changing queryset order signals a problem on Django side.

Here is the model for the Note:

class Note(models.Model):
    title = TagField(blank=True, null=True, db_index=True)
    slug = models.SlugField(blank=True, null=True, editable=False)
    note = models.TextField('not', max_length=1000)
    tags = TagField(blank=True, null=True)
    location = models.CharField(max_length=100, blank=True, null=True)
    user = models.ForeignKey(User, editable=False)
    added = models.DateTimeField(editable=False)

Here is the model for the Favorite:

class Favorite(models.Model):
    user = models.ForeignKey(User)
    content_type = models.ForeignKey(ContentType)
    object_id = models.IntegerField()
    object = generic.GenericForeignKey()

    objects = FavoriteManager()

And this is the FavoriteManager:

class FavoriteManager(models.Manager):
    def get_by_user(self, Model, user):
        ctype = ContentType.objects.get_for_model(Model)
        rel_table = qn(self.model._meta.db_table)
        return Model._default_manager.extra(
                '%s.content_type_id = %%s' % rel_table,
                '%s.user_id = %%s' % rel_table,
                '%s.%s = %s.object_id' % (qn(Model._meta.db_table),

And I try to build a new queryset by intersecting (&ing) the following querysets:

qs1 = Favorite.objects.get_by_user(Note, user)
qs2 = Note.objects.exclude(title__isnull=True)

For the querysets given,

qs1 & qs2 returns qs2 and,

qs2 & qs1 raises KeyError?.

Thank you for your interest. Please let me know if I can help further...


comment:3 Changed 10 years ago by George Vilches

Keywords: qsrf-cleanup added

comment:4 Changed 10 years ago by Jacob

milestone: 1.0

comment:5 Changed 10 years ago by Malcolm Tredinnick

Resolution: duplicate
Status: newclosed

Closing as a dupe of #7314. At the moment we just use the extra() bits from the RHS. I'll change that to merge the LHS and RHS, but note that it will still be incredibly fragile to do this. Django isn't about to examine your extra SQL to determine which aliases need to be relabelled, etc, so it wouldn't be too hard to get unexpected results. Better to use proper filters (and custom Q-like objects, where necessary) than try to stick a lot of stuff into extra() every single time like this.

comment:6 Changed 7 years ago by Jacob

milestone: 1.0

Milestone 1.0 deleted

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