Opened 4 years ago

Closed 4 years ago

#14971 closed (duplicate)

Exclude by annotation works as OR rather than AND

Reported by: orblivion Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

I'm using sqlite3. I believe that this is all the relevant code; I'd rather not share everything I'm doing so I changed names and redacted irrelevant identifying characteristics to protect the innocent.

Here's the model code:

class ObjGroup(models.Model):
    pass

class Revision(models.Model):
    obj_group = models.ForeignKey(ObjGroup)

class Obj(models.Model):
    created_obj_revision = models.ForeignKey(Revision, related_name = "new_objects")
    rejected_obj_revision = models.ForeignKey(Revision, related_name = "rejected_objects", null = True, blank = True)

Now I'm trying, in the shell at the moment, to query all Revisions in an Obj_Group where there's at least one object in revision.new_objects or revision.rejected_objects. So I figure what I should do is annotate the Count of new_objects and the Count of rejected_objects, and then exclude where
both are zero. Exclude is supposed to work as NOT ( condition AND condition ), which I tested works properly on other attributes. But on these annotations, it seems to work as NOT( condition OR condition ).

First list the count for all revisions in the group

 In : qs = obj_group.revision_set.annotate(
      num_rejected_objects = Count("rejected_objects"), num_new_objects = Count("new_objects")).all()

 In : [(q.num_new_objects, q.num_rejected_objects) for q in qs]

 Out : [(2, 0),
  (0, 1),
  (1, 0),
  (0, 1),
  (1, 1),
  (0, 0),
  (1, 0),
  (0, 1),
  (1, 0),
  (2, 2),
  (0, 0),
  (1, 1),
  (0, 0),
  (1, 0),
  (0, 0)]

Now let's exclude them

 In : qs = dg.revision_set.annotate(
      num_rejected_objects = Count("rejected_objects"), num_new_objects = Count("new_objects")).exclude(
      num_rejected_objects = 0, num_new_objects=0)

 In : [(q.num_new_objects, q.num_rejected_objects) for q in qs]

 Out: [(1, 1), (2, 2), (1, 1)]

It's eliminated all zeroes, rather than just the cases where both are zero

Now just for fun let's filter them instead

 In : qs = dg.revision_set.annotate(
      num_rejected_objects = Count("rejected_objects"), num_new_objects = Count("new_objects")).filter(
      num_rejected_objects = 0, num_new_objects=0)

 In : [(q.num_new_objects, q.num_rejected_objects) for q in qs]

 Out: [(0, 0), (0, 0), (0, 0), (0, 0)]

It properly selected only the ones where both are zero.

Let me know if you need any more details about my setup. Thanks.

Change History (2)

comment:1 Changed 4 years ago by orblivion

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

Sorry, the "dg" in the last two examples should be "obj_group".

comment:2 Changed 4 years ago by russellm

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

I think this is actually a manifestation of #10060 -- a problem with doing deep aggregates over multiple tables. The SQL for the exclude condition is being generated as HAVING NOT x=0 AND NOT y=0. The problem lies in the fact that the deep table structure causes two joins to be created on the Obj table, causing a cross product of results.

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