Aggregate on union of querysets generates invalid SQL

[Jaap Roes]

I'm getting the following error when trying to perform an aggregate on a union of querysets:

django.db.utils.ProgrammingError: column "__col1" does not exist
LINE 1: SELECT COALESCE(BOOL_OR("__col1"), false), COALESCE(BOOL_OR(...

What I have are the following models:

class Organisation(models.Model):
    ...

class Partner(models.Model):
    ...

class Entity(models.Model):
    organisation = models.ForeignKey(Organisation)
    partner = models.ForeignKey(Partner, null=True)

A user can have certain permissions on Entities, either directly or through an Organisation or Partner.

class BaseEntityPermissions(models.Model):
    user = models.ForeignKey(User)
    can_discombobulate = models.BooleanField(default=True)
    can_frobnicate = models.BooleanField(default=False)

    class Meta:
        abstract = True

class EntityPermissions(BaseEntityPermissions):
    entity = models.ForeignKey(Entity)

    class Meta:
        unique_together = ('user', 'entity')

class OrganisationEntityPermissions(BaseEntityPermissions):
    organisation = models.ForeignKey(Organisation)

    class Meta:
        unique_together = ('user', 'organisation')

class PartnerEntityPermissions(BaseEntityPermissions):
    partner = models.ForeignKey(Partner)

    class Meta:
        unique_together = ('user', 'partner')

These permissions stack; for example, a user can get the permission to "discombobulate" all Entities of a specific Organisation on the Organisation level and can gain the permission to "frobnicate" a specific Entity.

I therefore wrote this function to get the applicable permissions for a user and an entity:

def get_user_entity_permissions(user, entity):
    querysets = [
        EntityPermissions.objects.filter(
            entity_id=entity.id, user=user
        ).values('can_discombobulate', 'can_frobnicate'),
        OrganisationEntityPermissions.objects.filter(
            organisation_id=entity.organisation_id, user=user
        ).values('can_discombobulate', 'can_frobnicate')
    ]

    if entity.partner_id:
        querysets.append(
            PartnerEntityPermissions.objects.filter(
                partner_id=entity.partner_id, user=user
            ).values('can_discombobulate', 'can_frobnicate')
        )

    qs = functools.reduce(lambda qs1, qs2: qs1.union(qs2), querysets)

    return qs.aggregate(
        can_discombobulate=Coalesce(BoolOr('can_discombobulate'), False),
        can_frobnicate=Coalesce(BoolOr('can_frobnicate'), False)
    )

Sadly, execution this code results in the error previously mentioned.

The full generated SQL is as follows:

SELECT COALESCE(BOOL_OR("__col1"), false), COALESCE(BOOL_OR("__col2"), false) 
FROM (
  (
    SELECT "foo_entitypermissions"."can_discombobulate", "foo_entitypermissions"."can_frobnicate" 
    FROM "foo_entitypermissions" 
    WHERE ("foo_entitypermissions"."user_id" = 1 AND "foo_entitypermissions"."entity_id" = 1)
  ) 
  UNION (
    SELECT "foo_organisationentitypermissions"."can_discombobulate", "foo_organisationentitypermissions"."can_frobnicate" 
    FROM "foo_organisationentitypermissions" 
    WHERE ("foo_organisationentitypermissions"."user_id" = 1 AND "foo_organisationentitypermissions"."organisation_id" = 1)
  )
) subquery

This is almost correct, except for the __col1 and __col2 aliases, which don't exist.

[Jaap Roes]

I've been able to work around this issue by manually creating aliases for the colums I want to aggregate:

def get_entity_permissions(user, entity):
    querysets = [
        EntityPermissions.objects.filter(
            entity_id=entity.id, user=user
        ).values(
            _can_discombobulate=F('can_discombobulate'),
            _can_frobnicate=F('can_frobnicate')
        ),
        OrganisationEntityPermissions.objects.filter(
            organisation_id=entity.organisation_id, user=user
        ).values(
            _can_discombobulate=F('can_discombobulate'),
            _can_frobnicate=F('can_frobnicate')
    ]

    if entity.partner_id:
        querysets.append(
            PartnerEntityPermissions.objects.filter(
                partner_id=entity.partner_id, user=user
            ).values(
                _can_discombobulate=F('can_discombobulate'),
                _can_frobnicate=F('can_frobnicate')
            )
        )

    qs = functools.reduce(lambda qs1, qs2: qs1.union(qs2), querysets)

    return qs.aggregate(
        can_discombobulate=Coalesce(BoolOr('_can_discombobulate'), False),
        can_frobnicate=Coalesce(BoolOr('_can_frobnicate'), False)
    )

[Mariusz Felisiak]

As ​​per the documentation, "only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying columns (i.e. slicing, count(), order_by(), and values()/values_list()) are allowed on the resulting QuerySet.". See #27995 for a ticket to raise a helpful message about this and also a ticket #28519 to support these features.

Duplicate of #28519.

[Jaap Roes]

I did read that in the docs, and I had a different version of the function before that tried to uses .values on the union queryset before calling .aggregate:

def get_entity_permissions(user, entity):
    querysets = [
        EntityPermissions.objects.filter(
            entity_id=entity.id, user=user
        ),
        OrganisationEntityPermissions.objects.filter(
            organisation_id=entity.organisation_id, user=user
        )
    ]

    if entity.partner_id:
        querysets.append(
            PartnerEntityPermissions.objects.filter(
                partner_id=entity.partner_id, user=user
            )
        )

    qs = functools.reduce(lambda qs1, qs2: qs1.union(qs2), querysets)

    return qs.values('can_discombobulate', 'can_frobnicate').aggregate(
        can_discombobulate=Coalesce(BoolOr('can_discombobulate'), False),
        can_frobnicate=Coalesce(BoolOr('can_frobnicate'), False)
    )

Calling this raises the following error:

django.core.exceptions.FieldError: Cannot resolve keyword '' into field. Choices are: can_discombobulate, can_frobnicate, user, user_id, id, entity, entity_id

Sad to see this closed as a duplicate of #28519. I feel this ticket is much more narrowly scoped and Django seems to be almost capable of doing the right thing so it seems like an easier fix/addition.

Hopefully this doesn't get "fixed" in a way similar to #27995 as that would most likely break my workaround.

[Mariusz Felisiak]

Sad to see this closed as a duplicate of #28519. I feel this ticket is much more narrowly scoped and Django seems to be almost capable of doing the right thing so it seems like an easier fix/addition.

Closing as a duplicate doesn't changes much, we can still add support only for aggregate() as a part of #28519.

Hopefully this doesn't get "fixed" in a way similar to #27995 as that would most likely break my workaround

That's why I didn't propose to raise an error.

[Jaap Roes]

Replying to felixxm:

Sad to see this closed as a duplicate of #28519. I feel this ticket is much more narrowly scoped and Django seems to be almost capable of doing the right thing so it seems like an easier fix/addition.

Closing as a duplicate doesn't changes much, we can still add support only for aggregate() as a part of #28519.

OK, that wasn't clear at all from your initial message.

Hopefully this doesn't get "fixed" in a way similar to #27995 as that would most likely break my workaround

That's why I didn't propose to raise an error.

OK, good to know, that also wasn't clear. I now feel better for commiting the workaround :-)