#28199: `Subquery` in __in filter generating invalid SQL
-------------------------------------+-------------------------------------
Reporter: Murray | Owner: nobody
Christopherson |
Type: Bug | Status: new
Component: | Version: 1.11
Uncategorized |
Severity: Normal | Keywords: subquery
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28199>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.ade357b365bfa3ca10fe8be10c745e14%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
