[Django] #27343: Different Chaining Of Qs+Filters behaves unexpectedly differently
Django
----------------------------------------------+--------------------
Reporter: Sven R. Kunze | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.10
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Hi Django-devs,
I couldn't find an explanation in the docs of the following behavior.
The variable my_q has the following definition (to get you the complete
picture):
{{{
app_label, codename = perm.split('.')
user_q = Q(user_permissions__codename=codename,
user_permissions__content_type__app_label=app_label)
group_q = Q(groups__permissions__codename=codename,
groups__permissions__content_type__app_label=app_label)
my_q = Q(is_active=is_active) & (Q(is_superuser=is_superuser) | user_q |
group_q)
}}}
We had issues with the following querysets:
{{{
User.objects.filter(my_q).filter(groups__in=[my_group]).distinct() #
works
User.objects.filter(my_q, groups__in=[my_group]).distinct() # does
not work
my_group.user_set.filter(my_q).distinct() # does
not work
}}}
Those querysets actually try to get those users which have a certain
permission and are in a specific group. We expected all three to be
equivalent. However, the latter two yield superusers only.
Cheers,
Sven
Appendix - Related Queries
{{{
User.objects.filter(my_q).filter(groups__in=[my_group]).distinct().query
# works
'SELECT DISTINCT "auth_user"."id", "auth_user"."password",
"auth_user"."last_login", "auth_user"."is_superuser",
"auth_user"."username", "auth_user"."first_name", "auth_user"."last_name",
"auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."date_joined" FROM "auth_user" LEFT OUTER JOIN
"auth_user_user_permissions" ON ("auth_user"."id" =
"auth_user_user_permissions"."user_id") LEFT OUTER JOIN "auth_permission"
ON ("auth_user_user_permissions"."permission_id" = "auth_permission"."id")
LEFT OUTER JOIN "django_content_type" ON
("auth_permission"."content_type_id" = "django_content_type"."id") LEFT
OUTER JOIN "auth_user_groups" ON ("auth_user"."id" =
"auth_user_groups"."user_id") LEFT OUTER JOIN "auth_group" ON
("auth_user_groups"."group_id" = "auth_group"."id") LEFT OUTER JOIN
"auth_group_permissions" ON ("auth_group"."id" =
"auth_group_permissions"."group_id") LEFT OUTER JOIN "auth_permission" T8
ON ("auth_group_permissions"."permission_id" = T8."id") LEFT OUTER JOIN
"django_content_type" T9 ON (T8."content_type_id" = T9."id") INNER JOIN
"auth_user_groups" T10 ON ("auth_user"."id" = T10."user_id") WHERE
("auth_user"."is_active" = True AND ("auth_user"."is_superuser" = True OR
("auth_permission"."codename" = 'permission_of_my_app' AND
"django_content_type"."app_label" = 'my_app') OR (T9."app_label" =
'my_app' AND T8."codename" = 'permission_of_my_app')) AND T10."group_id"
IN (35))'
User.objects.filter(my_q, groups__in=[my_group]).distinct().query
# does not work
'SELECT DISTINCT "auth_user"."id", "auth_user"."password",
"auth_user"."last_login", "auth_user"."is_superuser",
"auth_user"."username", "auth_user"."first_name", "auth_user"."last_name",
"auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."date_joined" FROM "auth_user" LEFT OUTER JOIN
"auth_user_user_permissions" ON ("auth_user"."id" =
"auth_user_user_permissions"."user_id") LEFT OUTER JOIN "auth_permission"
ON ("auth_user_user_permissions"."permission_id" = "auth_permission"."id")
LEFT OUTER JOIN "django_content_type" ON
("auth_permission"."content_type_id" = "django_content_type"."id") INNER
JOIN "auth_user_groups" ON ("auth_user"."id" =
"auth_user_groups"."user_id") INNER JOIN "auth_group" ON
("auth_user_groups"."group_id" = "auth_group"."id") LEFT OUTER JOIN
"auth_group_permissions" ON ("auth_group"."id" =
"auth_group_permissions"."group_id") LEFT OUTER JOIN "auth_permission" T8
ON ("auth_group_permissions"."permission_id" = T8."id") LEFT OUTER JOIN
"django_content_type" T9 ON (T8."content_type_id" = T9."id") WHERE
("auth_user"."is_active" = True AND ("auth_user"."is_superuser" = True OR
("auth_permission"."codename" = 'permission_of_my_app' AND
"django_content_type"."app_label" = 'my_app') OR (T9."app_label" =
'my_app' AND T8."codename" = 'permission_of_my_app')) AND
"auth_user_groups"."group_id" IN (35))'
my_group.user_set.filter(my_q).distinct().query
# does not work
'SELECT DISTINCT "auth_user"."id", "auth_user"."password",
"auth_user"."last_login", "auth_user"."is_superuser",
"auth_user"."username", "auth_user"."first_name", "auth_user"."last_name",
"auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."date_joined" FROM "auth_user" INNER JOIN "auth_user_groups"
ON ("auth_user"."id" = "auth_user_groups"."user_id") INNER JOIN
"auth_group" ON ("auth_user_groups"."group_id" = "auth_group"."id") LEFT
OUTER JOIN "auth_user_user_permissions" ON ("auth_user"."id" =
"auth_user_user_permissions"."user_id") LEFT OUTER JOIN "auth_permission"
ON ("auth_user_user_permissions"."permission_id" = "auth_permission"."id")
LEFT OUTER JOIN "django_content_type" ON
("auth_permission"."content_type_id" = "django_content_type"."id") LEFT
OUTER JOIN "auth_group_permissions" ON ("auth_group"."id" =
"auth_group_permissions"."group_id") LEFT OUTER JOIN "auth_permission" T8
ON ("auth_group_permissions"."permission_id" = T8."id") LEFT OUTER JOIN
"django_content_type" T9 ON (T8."content_type_id" = T9."id") WHERE
("auth_user_groups"."group_id" = 35 AND "auth_user"."is_active" = True AND
("auth_user"."is_superuser" = True OR ("auth_permission"."codename" =
'permission_of_my_app' AND "django_content_type"."app_label" = 'my_app')
OR (T9."app_label" = 'my_app' AND T8."codename" =
'permission_of_my_app')))'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27343>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Sven R. Kunze | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
I believe this is the expected behavior per the
[https://docs.djangoproject.com/en/1.10/topics/db/queries/#spanning-multi-
valued-relationships spanning multi-valued relationships documentation].
Can you confirm you've read this section of the documentation?
--
Ticket URL: <https://code.djangoproject.com/ticket/27343#comment:1>
Django
Reporter: Sven R. Kunze | Owner: nobody
Component: Database layer | Version: 1.10
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* status: new => closed
* resolution: => needsinfo
--
Ticket URL: <https://code.djangoproject.com/ticket/27343#comment:2>