Not sure if I should raise this as a bug, because I'm not sure if I'm using it correctly. I am writing code conceptually similar to the code I am actually working with, I hope it suffices.
Within my app, there is the concept of permits and suspensions:
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.
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):
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:
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
that's being added. Can anyone suggest if I'm doing it wrong, a workaround, or if this is indeed a bug?
Thanks!