id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32032	Aggregate on union of querysets generates invalid SQL	Jaap Roes	nobody	"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."	New feature	closed	Database layer (models, ORM)	3.1	Normal	duplicate	aggregate union		Unreviewed	0	0	0	0	0	0