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