Opened 2 months ago

Closed 8 weeks ago

Last modified 8 weeks ago

#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 Changed 2 months ago by Simon Charette

Component: UncategorizedDatabase layer (models, ORM)
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

Using a query directly for a __in lookup has always been working and 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.

comment:2 Changed 2 months ago by Simon Charette

Another affected developer which didn't need to use Subquery in the first place.

comment:3 Changed 8 weeks ago by Tim Graham

I started a PR with a regression test but no solution.

comment:4 Changed 8 weeks ago by Tim Graham

Has patch: set

Added a fix suggested by Simon.

comment:5 Changed 8 weeks ago by GitHub <noreply@…>

Resolution: fixed
Status: newclosed

In f044955:

Fixed #28199 -- Fixed Subquery generating unnecessary/invalid CAST.

Thanks Simon Charette for the fix.

comment:6 Changed 8 weeks ago by Tim Graham <timograham@…>

In 9b9a810:

[1.11.x] Fixed #28199 -- Fixed Subquery generating unnecessary/invalid CAST.

Thanks Simon Charette for the fix.

Backport of f04495521ade8a2befc1aca70dd0a2c7aad4c987 from master

Note: See TracTickets for help on using tickets.
Back to Top