#28199 closed Bug (fixed)
`Subquery` in __in filter generating invalid SQL
Reported by: | Murray Christopherson | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.11 |
Severity: | Release blocker | Keywords: | subquery |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Duplicated from https://groups.google.com/forum/#!topic/django-users/aq7mL9Opd-s
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.
As per the mailing list discussion, it turns out that this works:
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.
Change History (6)
comment:1 by , 7 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Severity: | Normal → Release blocker |
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 7 years ago
Another affected developer which didn't need to use Subquery
in the first place.
Using a query directly for a
__in
lookup has always been working and I think we should adjust the documentation to avoid encouraging usage ofSubquery
ifOuterRef
is not required.An even better solution would be to make
__in=queryset.filter(field=OuterRef('other_field'))
work out of the box and either deprecateSubquery
or make it a dummy wrapper.In the mean time this issue is probably related to
Subquery.output_field
causing an unnecessaryCast
.