Exclude by annotation works as OR rather than AND

[orblivion]

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.

[orblivion]

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

[Russell Keith-Magee]

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.