values_list on EmptyQuerySet used in a queryset filter returns full set

Question

Recently found something peculiar in a filter, I can't believe its intended behaviour.

from django.contrib.auth.models import User
print User.objects.filter(id__in=User.objects.none().values_list("id",flat=True))
print User.objects.filter(id__in=User.objects.all().values_list("id",flat=True))

Oddly both of these lists return the full set of users. It actually seems to be pretty easy to "fix" if I wrap the inner query in a list function e.g.

User.objects.filter(id__in=list(User.objects.none().values_list("id")))

Then this returns what I would expect (an empty list).

Seems like a bug to me, or am I missing something?

Steve

Answer 1

Here's the queries produced for both:

User.objects.filter(id__in=User.objects.none().values_list("id",flat=True))

SELECT "auth_user"."id",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."password",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."is_superuser",
       "auth_user"."last_login",
       "auth_user"."date_joined"
FROM "auth_user"
WHERE "auth_user"."id" IN
    (SELECT U0."id"
     FROM "auth_user" U0) LIMIT 21

User.objects.filter(id__in=User.objects.all().values_list("id",flat=True))

SELECT "auth_user"."id",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."password",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."is_superuser",
       "auth_user"."last_login",
       "auth_user"."date_joined"
FROM "auth_user"
WHERE "auth_user"."id" IN
    (SELECT U0."id"
     FROM "auth_user" U0) LIMIT 21

Notice anything? They're exactly the same queries. Also interesting is what happens if you try things like User.objects.none(), User.objects.filter(id__in=[]) and User.objects.filter(id__in=User.objects.none(). In all three of these circumstances, Django short-circuits the query. In other words, it doesn't even issue a query to the database because it determines beforehand that there will not be any results. My best guess here is that adding values_list to the end defeats the short-circuiting logic, allowing an actual query to be send, and that it's actually values_list that determines the query that should be sent. Which in both cases is really the same, when you think about it. Either way you want to select just id on an unfiltered queryset.

I emphasized that part, because I'm sure you're jumping up and down now saying but none should return an empty queryset. True, but it does so by virtue of automatically returning an EmptyQuerySet and never actually querying the database at all. It doesn't add any filters to the query.

Whether this is a bug or not is debatable. I'm more apt to call this an edge-case that most likely can't really be "fixed". It's a function of how all the interweaving parts come together in this one scenario.