Q objects problem with disjunction

[mikemintz]

Thank you for fixing my bug in #8439, but I'm still having issues with these complex queries. Below is the exact system I'm trying to implement, although I've simplified it as much as I could for this report.

Basically, there are "users" and "groups" (which contain users), and "resources" that they have privileges to access with respect to particular "abilities". Here are my models:

from django.db import models

class XResource(models.Model):
    pass

class XUser(models.Model):
    pass

class XGroup(models.Model):
    pass

class XMembership(models.Model):
    user = models.ForeignKey(XUser)
    group = models.ForeignKey(XGroup)

class XRole(models.Model):
    pass

class XAbility(models.Model):
    role = models.ForeignKey(XRole)
    name = models.CharField(max_length=100)
    allowed = models.BooleanField()

class XUserPriv(models.Model):
    user = models.ForeignKey(XUser)
    resource = models.ForeignKey(XResource)
    role = models.ForeignKey(XRole)

class XGroupPriv(models.Model):
    group = models.ForeignKey(XGroup)
    resource = models.ForeignKey(XResource)
    role = models.ForeignKey(XRole)

So I create a user, who is in a group, and the group has privileges to use the "display" ability on a resource.

      user = XUser.objects.create()
     group = XGroup.objects.create()
membership = XMembership.objects.create(user=user, group=group)
  resource = XResource.objects.create()
      role = XRole.objects.create()
   ability = XAbility.objects.create(role=role, name="display", allowed=True)
group_priv = XGroupPriv.objects.create(group=group, resource=resource, role=role)

I do the following query (I want to get a list of all resources that the user is directly allowed to display, along with those that the user is allowed to display via his group, given that he is not explicitly denied the privilege himself), and I get no results (but I should get back [resource]):

direct_yes_q = Q(xuserpriv__user=user,
                 xuserpriv__role__xability__name="display",
                 xuserpriv__role__xability__allowed=True)
direct_no_q = Q(xuserpriv__user=user,
                xuserpriv__role__xability__name="display",
                xuserpriv__role__xability__allowed=False)
group_yes_q = Q(xgrouppriv__group__xmembership__user=user,
                xgrouppriv__role__xability__name="display",
                xgrouppriv__role__xability__allowed=True)

viewable_q = direct_yes_q | (~direct_no_q & group_yes_q)
print XResource.objects.filter(viewable_q).all()

The SQL generated here is (I removed table prefixes and x's and some quotes to make it easier to read):

SELECT resource.id FROM resource
LEFT OUTER JOIN userpriv   ON (resource.id        = userpriv.resource_id)
LEFT OUTER JOIN role       ON (userpriv.role_id   = role.id)
     INNER JOIN ability    ON (role.id            = ability.role_id)
LEFT OUTER JOIN grouppriv  ON (resource.id        = grouppriv.resource_id)
LEFT OUTER JOIN role T7    ON (grouppriv.role_id  = T7.id)
LEFT OUTER JOIN group      ON (grouppriv.group_id = group.id)
LEFT OUTER JOIN membership ON (group.id           = membership.group_id) WHERE ((ability.name = E'display'  AND ability.allowed = true  AND userpriv.user_id = 1 ) OR (NOT (resource.id IN (SELECT userpriv.resource_id FROM resource
     INNER JOIN userpriv   ON (resource.id        = userpriv.resource_id)
     INNER JOIN role       ON (userpriv.role_id   = role.id)
     INNER JOIN ability    ON (role.id            = ability.role_id) WHERE ability.name = E'display' ) AND resource.id IN (SELECT userpriv.resource_id FROM resource
     INNER JOIN userpriv   ON (resource.id        = userpriv.resource_id)
     INNER JOIN role       ON (userpriv.role_id   = role.id)
     INNER JOIN ability    ON (role.id            = ability.role_id) WHERE ability.allowed = false ) AND resource.id IN (SELECT userpriv.resource_id FROM resource
     INNER JOIN userpriv   ON (resource.id        = userpriv.resource_id) WHERE userpriv.user_id = 1 )) AND ability.allowed = true  AND membership.user_id = 1  AND ability.name = E'display' ))

Now, when I do a simpler version of the query without one of the disjuncts, I actually get results back:

viewable_q = ~direct_no_q & group_yes_q
print XResource.objects.filter(viewable_q).all()

And it generates the following SQL:

SELECT resource.id FROM resource
INNER JOIN grouppriv  ON (resource.id        = grouppriv.resource_id)
INNER JOIN role       ON (grouppriv.role_id  = role.id)
INNER JOIN ability    ON (role.id            = ability.role_id)
INNER JOIN group      ON (grouppriv.group_id = group.id)
INNER JOIN membership ON (group.id           = membership.group_id) WHERE (NOT (resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv   ON (resource.id        = userpriv.resource_id)
INNER JOIN role       ON (userpriv.role_id   = role.id)
INNER JOIN ability    ON (role.id            = ability.role_id) WHERE ability.name = E'display' ) AND resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv   ON (resource.id        = userpriv.resource_id)
INNER JOIN role       ON (userpriv.role_id   = role.id)
INNER JOIN ability    ON (role.id            = ability.role_id) WHERE ability.allowed = false ) AND resource.id IN (SELECT userpriv.resource_id FROM resource
INNER JOIN userpriv   ON (resource.id        = userpriv.resource_id) WHERE userpriv.user_id = 1 )) AND ability.allowed = true  AND membership.user_id = 1  AND ability.name = E'display' )

I know it's not a problem with my example, because in the second query, the Q returns a non-empty array, but when I add a disjunct (which can only add more results), the Q returns an empty array.

Mike

[Malcolm Tredinnick]

(In [8853]) Fixed #8790 -- Multi-branch join trees that shared tables of the same name were
sometimes also sharing aliases, instead of creating their own. This was
generating incorrect SQL.

No representative test for this fix yet because I haven't had time to write one
that fits in nicely with the test suite. But it works for the monstrous example
in #8790 and a bunch of other complex examples I've created locally. Will write
a test later.

[Jacob]

Milestone 1.0 deleted