#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 [email protected].
To post to this group, send email to [email protected].
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.
