Opened 15 years ago
Closed 15 years ago
#13195 closed (duplicate)
Generic relations produce wrong queries with Q objects
Reported by: | Claude Paroz | Owned by: | nobody |
---|---|---|---|
Component: | Contrib apps | Version: | 1.1 |
Severity: | Keywords: | ||
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Here is my test case model:
from django.db import models from django.contrib.contenttypes import generic from django.contrib.contenttypes.models import ContentType class BaseObject(models.Model): status = models.CharField(max_length=10) permissions = generic.GenericRelation("Permission") class Permission(models.Model): content_type = models.ForeignKey(ContentType, related_name="permissions") object_id = models.PositiveIntegerField() content_object = generic.GenericForeignKey('content_type', 'object_id') perm = models.CharField(max_length=10)
When I make queries OR'ed with Q objects, the join condition on content_type_id is placed in a fragile position which can produce wrong queries. By example, the query BaseObject.objects.filter(Q(status='public') | Q(permissions__perm='rw'))
is producing this (wrong) SQL query:
SELECT "testapp_baseobject"."id", "testapp_baseobject"."status" FROM "testapp_baseobject" LEFT OUTER JOIN "testapp_permission" ON ("testapp_baseobject"."id" = "testapp_permission"."object_id") WHERE (("testapp_baseobject"."status" = 'public' OR "testapp_permission"."perm" = 'rw' ) AND "testapp_permission"."content_type_id" = 8 )
If I simply inverse the Q objects (which should produce identical results) BaseObject.objects.filter(Q(permissions__perm='rw') | Q(status='public'))
, the SQL query (correct by accident?) is:
SELECT "testapp_baseobject"."id", "testapp_baseobject"."status" FROM "testapp_baseobject" LEFT OUTER JOIN "testapp_permission" ON ("testapp_baseobject"."id" = "testapp_permission"."object_id") WHERE (("testapp_permission"."perm" = 'rw' AND "testapp_permission"."content_type_id" = 8 ) OR "testapp_baseobject"."status" = 'public' )
In my opinion, the right thing (cleaner and more robust) would be to add the content_type_id clause inside the OUTER JOIN "ON" clause:
SELECT "testapp_baseobject"."id", "testapp_baseobject"."status" FROM "testapp_baseobject" LEFT OUTER JOIN "testapp_permission" ON ("testapp_baseobject"."id" = "testapp_permission"."object_id" AND "testapp_permission"."content_type_id" = 8) WHERE ("testapp_permission"."perm" = 'rw' OR "testapp_baseobject"."status" = 'public' )
This sounds like #11535, which has been fixed on trunk (r12405) and the 1.1.X branch (r12406).