Different Chaining Of Qs+Filters behaves unexpectedly differently

[Sven R. Kunze]

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

[Simon Charette]

I believe this is the expected behavior per the ​spanning multi-valued relationships documentation. Can you confirm you've read this section of the documentation?