Code

Opened 6 years ago

Closed 6 years ago

Last modified 3 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:

Description

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

Attachments (0)

Change History (6)

comment:1 Changed 6 years ago by jbronn

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

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 6 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(
            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

comment:3 Changed 6 years ago by gav

  • Keywords qsrf-cleanup added

comment:4 Changed 6 years ago by jacob

  • milestone set to 1.0

comment:5 Changed 6 years ago by mtredinnick

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

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 3 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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.