Re: [Django] #24421: Querying a reverse ForeignObject relation using exclude() fails

[Django]

#24421: Querying a reverse ForeignObject relation using exclude() fails
-+-
 Reporter:  animan1  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.7
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ForeignObject,   | Triage Stage:  Accepted
  MySQL, SQLite  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  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: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.7023898ab0e1d41cb8c1dbaf2e51075c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24421: Querying a reverse ForeignObject relation using exclude() fails

[Django]

#24421: Querying a reverse ForeignObject relation using exclude() fails
-+-
 Reporter:  animan1  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.7
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ForeignObject,   | Triage Stage:  Accepted
  MySQL, SQLite  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  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),
 ['']
 )
 # 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: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.7ea042fe48852a4433c51a2c9c9cb2a8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24421: Querying a reverse ForeignObject relation using exclude() fails (was: Operational error when performing join using ForeignObject)

[Django]

#24421: Querying a reverse ForeignObject relation using exclude() fails
-+-
 Reporter:  animan1  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.7
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  ForeignObject,   | Triage Stage:  Accepted
  MySQL, SQLite  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by timgraham):

 * 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: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.8e4924b5693fd51ea9edd5aa83728a4b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.