5

I have a situation like this:

ids = [None, None, None]
foo = Foo.objects.filter(common=True).exclude(id__in=ids)

This seems to exclude all always.

Why is id of id__in threated as None in this case? pk__in didn't work either. I expect it to not exclude anything as all objects have valid id's.

foo = Foo.objects.filter(common=True)

Returns all objects like expected.

user2061057
  • 962
  • 1
  • 9
  • 20
  • 1
    `None` gets converted to sql NULL in the query, and then the db does something weird. The primary key should never be `None` anyway, so what are you actually trying to query here?? – wim Nov 01 '17 at 15:44
  • I'm just trying to exclude objects by id and the exclusion list may contain None's. It's irrelevant here. – user2061057 Nov 01 '17 at 15:47
  • 3
    Pre-filter the exclusion list not to contain None's. – wim Nov 01 '17 at 15:48

1 Answers1

6

Your queryset will generate SQL similar to select * from foo where NOT (id in (NULL));

In SQL both x in (NULL) and NOT (x in (NULL)) evaluate to null, so the query returns no rows. See this question for more info.

The solution, as @wim pointed out in the comments, is to filter out the None values from the list:

foo = Foo.objects.filter(common=True).exclude(id__in=[x for x in ids if x is not None])
Alasdair
  • 298,606
  • 55
  • 578
  • 516