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 [0] https://code.djangoproject.com/newticket Le vendredi 12 mai 2017 13:54:38 UTC-4, Murray Christopherson a écrit : > > 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: >> >> 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 >> >> Le vendredi 12 mai 2017 11:02:22 UTC-4, Murray Christopherson a écrit : >>> >>> 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! >>> >> -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/4ed13baa-68fb-46bd-af97-7e78e343d6a4%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
