Opened 10 years ago

Last modified 9 years ago

#24421 new Bug

Querying a reverse ForeignObject relation using exclude() fails

Reported by: Daniel Boeve Owned by: nobody
Component: Database layer (models, ORM) Version: 1.7
Severity: Normal Keywords: ForeignObject, MySQL, SQLite
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When joining on a field defined by a ForeignObject with multiple from_fields and to_fields, MySQL and SQLite throw operational errors. I have created a test cast which I know fails for SQLite and I assume fails for MySQL as well (I first saw the issue with production code using MySQL, but wanted to simplify the code submitted).

Attachments (2)

test_bug.py (674 bytes ) - added by Daniel Boeve 10 years ago.
24421-test.diff (1023 bytes ) - added by Tim Graham 10 years ago.

Download all attachments as: .zip

Change History (6)

by Daniel Boeve, 10 years ago

Attachment: test_bug.py added

comment:1 by Tim Graham, 10 years ago

ForeignObject isn't a public API, so I'm not sure if the issue is valid. Can you reproduce it using one of its subclasses: ForeignKey or GenericRelation?

comment:2 by Tim Graham, 10 years ago

Summary: Operational error when performing join using ForeignObjectQuerying a reverse ForeignObject relation using exclude() fails
Triage Stage: UnreviewedAccepted

I did some more investigation and reproduced this using models in Django's test suite, so I think it's valid. The test fails on the exclude() query as far back as I tested (1.6) with various errors depending on the database.

by Tim Graham, 10 years ago

Attachment: 24421-test.diff added

comment:3 by Joeri Bekker, 9 years ago

I added some comments on what's happening. The whole generated query for an exclude seems incorrect or incomplete.

    def test_reverse_query(self):
        membership = Membership.objects.create(membership_country=self.usa, person=self.bob, group=self.cia)

        # The following query is generated for a filter:
        #
        # SELECT ... FROM "foreign_object_group" INNER JOIN "foreign_object_membership" ON (
        #   "foreign_object_group"."group_country_id" = "foreign_object_membership"."membership_country_id" AND
        #   "foreign_object_group"."id" = "foreign_object_membership"."group_id"
        # ) WHERE "foreign_object_membership"."id" = 1 ORDER BY "foreign_object_group"."name" ASC

        self.assertQuerysetEqual(
            Group.objects.filter(membership=membership),
            ['<Group: CIA>']
        )
        # The following query is generated for an exclude:
        #
        # SELECT ... FROM "foreign_object_group" WHERE NOT (
        #   "foreign_object_group"."group_country_id" IN (
        #       SELECT U1."membership_country_id" AS Col1, U1."group_id" AS Col2 FROM "foreign_object_membership" U1 WHERE U1."id" = 1
        #   )
        # ) ORDER BY "foreign_object_group"."name" ASC

        # The error by SQLite:
        # OperationalError: only a single result allowed for a SELECT that is part of an expression

        self.assertQuerysetEqual(
            Group.objects.exclude(membership=membership),
            []
        )

comment:4 by Anssi Kääriäinen, 9 years ago

We don't have the smarts to handle exclude() queries for multicolumn joins just yet in Django. Fixing this requires changing Django to use EXISTS queries, as not all backends support multicolumn IN clauses of the form (WHERE col1, col2 IN (SELECT innercol1, innercol2 FROM ...).

We have other reasons to prefer EXISTS queries, too. For one, PostgreSQL performs very badly with NOT IN queries.

Unfortunately fixing this properly will likely require multiple days of work.

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