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

[omat@…]

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

[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.

[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(
            tables=[self.model._meta.db_table],
            where=[
                '%s.content_type_id = %%s' % rel_table,
                '%s.user_id = %%s' % rel_table,
                '%s.%s = %s.object_id' % (qn(Model._meta.db_table),
                                          qn(Model._meta.pk.column),
                                          rel_table)
            ],
            params=[ctype.id, user.id],
        )

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...

--
omat

[Malcolm Tredinnick]

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.

[Jacob]

Milestone 1.0 deleted