#27343: Different Chaining Of Qs+Filters behaves unexpectedly differently
----------------------------------------------+--------------------
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.
--
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/050.424abf494275141444d097a671360054%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
