Opened 8 years ago

Closed 8 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: UI/UX:


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' )

Change History (1)

comment:1 Changed 8 years ago by Karen Tracey

Resolution: duplicate
Status: newclosed

This sounds like #11535, which has been fixed on trunk (r12405) and the 1.1.X branch (r12406).

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