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
28199	`Subquery` in __in filter generating invalid SQL	Murray Christopherson	nobody	"Duplicated from https://groups.google.com/forum/#!topic/django-users/aq7mL9Opd-s

Within my app, there is the concept of permits and suspensions:
{{{#!python
class Permit(models.Model):
    class Meta:
       db_table = 'permits'

    uuid = models.UUIDField(primary_key=True, db_column='uuid')

class Suspension(models.Model):
    class Meta:
        db_table = 'suspensions'

    uuid = models.UUIDField(primary_key=True, db_column='uuid')
    permit = models.ForeignKey(Permit, db_column='permits_uuid')
    startDate = models.DateField(db_column='start_date')
    endDate = models.DateField(null=True, db_column='end_date')
}}}

Within the app, I am try to get a set of permits that are not currently expired, so I attempted to generate the query like this.
{{{#!python
activeSuspensionPermitUuidsQuery = Suspension.objects.filter(Q(startDate__lte=Now()) & (Q(endDate__isnull=True) | Q(endDate__gt=Now()))).distinct('permit__uuid')

activeSuspensionPermits = Permit.objects.filter(~Q(uuid__in=Subquery(activeSuspensionPermitUuidsQuery.values('permit__uuid')))
}}}
The SQL generated by this is (for PostgreSQL 9.4):
{{{#!sql
SELECT ""permits"".""uuid"" FROM ""permits"" WHERE (NOT (""permits"".""uuid"" IN (CAST(SELECT DISTINCT ON (U0.""permits_uuid"") U0.""permits_uuid"" FROM ""suspensions"" U0 INNER JOIN ""permits"" U1 ON (U0.""permits_uuid"" = U1.""uuid"") WHERE (U0.""start_date"" <= (STATEMENT_TIMESTAMP()) AND (U0.""end_date"" IS NULL OR U0.""end_date"" > (STATEMENT_TIMESTAMP()))) AS uuid))));
}}}

This generates the following error:
{{{
ERROR:  syntax error at or near ""SELECT""
LINE 1: ... FROM ""permits"" WHERE (NOT (""permits"".""uuid"" IN (CAST(SELECT DIS...
}}}

If I edit and run the SQL myself, like this:
{{{#!sql
SELECT ""permits"".""uuid"" FROM ""permits"" WHERE (NOT (""permits"".""uuid"" IN (SELECT DISTINCT ON (U0.""permits_uuid"") U0.""permits_uuid"" FROM ""suspensions"" U0 INNER JOIN ""permits"" U1 ON (U0.""permits_uuid"" = U1.""uuid"") WHERE (U0.""start_date"" <= (STATEMENT_TIMESTAMP()) AND (U0.""end_date"" IS NULL OR U0.""end_date"" > (STATEMENT_TIMESTAMP()))))));
}}}

It works fine. So the problem is the `CAST(... as uuid)` that's being added.

As per the mailing list discussion, it turns out that this works:
{{{#!python
activeSuspensionPermitUuidsQuery = Suspension.objects.filter(Q(startDate__lte=Now()) & (Q(endDate__isnull=True) | Q(endDate__gt=Now()))).distinct('permit__uuid')

activeSuspensionPermits = Permit.objects.filter(~Q(uuid__in=activeSuspensionPermitUuidsQuery.values('permit__uuid'))
}}}

However, it should probably work in both cases, unless I misunderstand the point/purpose of the `Subquery` object."	Bug	closed	Database layer (models, ORM)	1.11	Release blocker	fixed	subquery		Accepted	1	0	0	0	0	0