[Django] #24421: Operational error when performing join using ForeignObject
Django
-------------------------------------+-------------------------------------
Reporter: animan1 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) | Keywords: ForeignObject, MySQL,
Severity: Normal | SQLite
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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).
--
Ticket URL: <https://code.djangoproject.com/ticket/24421>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: animan1 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ForeignObject, | Triage Stage:
MySQL, SQLite | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
`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`?
--
Ticket URL: <https://code.djangoproject.com/ticket/24421#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: animan1 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ForeignObject, | Triage Stage: Accepted
MySQL, SQLite |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24421#comment:2>
Django
Reporter: animan1 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ForeignObject, | Triage Stage: Accepted
MySQL, SQLite |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by joeri):
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),
[]
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24421#comment:3>
Django
Reporter: animan1 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: ForeignObject, | Triage Stage: Accepted
MySQL, SQLite |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akaariai):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24421#comment:4>