Subquery filter (__in) causing invalid SQL

40 views
Skip to first unread message

Murray Christopherson

unread,
May 12, 2017, 11:02:22 AM5/12/17
to Django users
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
CAST(... as uuid)
that's being added. Can anyone suggest if I'm doing it wrong, a workaround, or if this is indeed a bug?

Thanks!

Simon Charette

unread,
May 12, 2017, 1:25:55 PM5/12/17
to Django users
Hi Murray,

That looks like a bug to me.

Do you get similar results if you don't use the Subquery expression and pass the query
directly instead? Something along the lines of

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

Simon

Murray Christopherson

unread,
May 12, 2017, 1:39:37 PM5/12/17
to Django users
Huh, yes, that seems to work now. I'm going to confirm real quick, but I don't think that worked back in v1.10. I was attempting to do the subquery-ing in v1.10 for the past few days, because it was slow to do it as 2 separate queries, and then just happened upon the release notes of v1.11 and the new Subquery type, so I assumed it must be the new "correct" way. I will update this thread with my findings.

Murray Christopherson

unread,
May 12, 2017, 1:51:45 PM5/12/17
to Django users
Unfortunately, my checks come up fruitless. Simon's code seems to work in both 1.10 and 1.11. I would have swore I tried this code during my attempts, but I will trust that I am mistaken, and thank you Simon for pointing me in the right direction.

Murray Christopherson

unread,
May 12, 2017, 1:54:38 PM5/12/17
to Django users
I am assuming you are a regular to the Django community - possibly even a moderator? Despite the workaround (thanks again), would you recommend I add this to the issue tracker as a bug?


On Friday, 12 May 2017 13:25:55 UTC-4, Simon Charette wrote:

Simon Charette

unread,
May 12, 2017, 2:35:48 PM5/12/17
to Django users
Murray,

Glad I could help!

I recently contributed changes to this part of the ORM hence why it rang a bell when
I saw your issue.

Subquery was introduced in 1.11 so it's still a new feature that hasn't been battle
tested yet.

Please file a new ticket on Trac [0].

Cheers,
Simon

Reply all
Reply to author
Forward
0 new messages