[Django] #28199: `Subquery` in __in filter generating invalid SQL
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
Reporter: Murray | Owner: nobody
Christopherson |
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: subquery | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* component: Uncategorized => Database layer (models, ORM)
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
Comment:
Using a query directly for a `__in` lookup has always been working and
[https://github.com/django/django/commit/236ebe94bfe24d394d5b49f4405da445550e8aa6#commitcomment-20478749
I think we should adjust the documentation] to avoid encouraging usage of
`Subquery` if `OuterRef` 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 deprecate `Subquery` or make it a dummy wrapper.
In the mean time this issue is probably related to `Subquery.output_field`
causing an unnecessary `Cast`.
--
Ticket URL: <https://code.djangoproject.com/ticket/28199#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Murray | Owner: nobody
Christopherson |
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: subquery | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Another [https://groups.google.com/d/msg/django-
users/F9b8wUOf89Y/VklsYkg5AwAJ affected developer] which didn't need to
use `Subquery` in the first place.
--
Ticket URL: <https://code.djangoproject.com/ticket/28199#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Murray | Owner: nobody
Christopherson |
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: subquery | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
I started a [https://github.com/django/django/pull/8572 PR] with a
regression test but no solution.
--
Ticket URL: <https://code.djangoproject.com/ticket/28199#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Murray | Owner: nobody
Christopherson |
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: subquery | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
Added a fix suggested by Simon.
--
Ticket URL: <https://code.djangoproject.com/ticket/28199#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Murray | Owner: nobody
Christopherson |
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: subquery | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"f04495521ade8a2befc1aca70dd0a2c7aad4c987" f044955]:
{{{
#!CommitTicketReference repository=""
revision="f04495521ade8a2befc1aca70dd0a2c7aad4c987"
Fixed #28199 -- Fixed Subquery generating unnecessary/invalid CAST.
Thanks Simon Charette for the fix.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28199#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Murray | Owner: nobody
Christopherson |
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: subquery | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"9b9a81024a4955d6d3fb9f440b89567bb1634090" 9b9a810]:
{{{
#!CommitTicketReference repository=""
revision="9b9a81024a4955d6d3fb9f440b89567bb1634090"
[1.11.x] Fixed #28199 -- Fixed Subquery generating unnecessary/invalid
CAST.
Thanks Simon Charette for the fix.
Backport of f04495521ade8a2befc1aca70dd0a2c7aad4c987 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28199#comment:6>