Почему в запросе Django внешний ключ id __in не соответствует None?

При фильтрации набора запросов по обнуляемому внешнему ключу я могу фильтровать по значению ID (foo_id=123) или по None (foo_id=None). Однако, если я пытаюсь фильтровать по списку (foo_id__in=[123, None]), None игнорируется.

Почему это происходит, и какой лучший обходной путь для фильтрации по внешнему ключу с использованием списка, включающего None?

Пример:

from django.db import models

class Foo(models.Model):
  name = models.CharField(max_length=100)

class Bar(models.Model):
  foo = models.ForeignKey(Foo, on_delete=models.PROTECT,
                          blank=True, null=True)

foo = Foo.objects.create(name='myfoo')
Bar.objects.create(foo=foo)
Bar.objects.create(foo=None)

Bar.objects.count()                                    # 2
Bar.objects.filter(foo_id=foo.id).count()              # 1
Bar.objects.filter(foo_id=None).count()                # 1
Bar.objects.filter(foo_id__in=[foo.id, None]).count()  # 1 - Expected 2!

Я не знаю, почему игнорируется None в foo_id__in=[123, None], но лучшим обходным решением, которое я придумал, является следующее:

Bar.objects.filter(Q(foo_id=foo.id) | Q(foo_id=None)).count()

Ключевым моментом является то, что в SQL NULL представляет собой неизвестное значение, которое нельзя сравнивать с помощью обычных операторов:

SELECT NULL = NULL;
-- => NULL

Этот синтаксис работает не во всех DB движках - например, SQL Server - и в них вам придется писать что-то вроде SELECT CASE WHEN NULL = NULL THEN 't' ELSE 'f' END, но результат тот же: NULL = NULL оценивается как NULL, что ложно.

А NULL не равен другому NULL... но он также не отличается от другого NULL: NULL <> NULL также возвращает NULL. Фактически, NULL заражает все операторы: 1 + NULL, 1 < NULL, 1 >= NULL... все они приводят к NULL.

Рассуждение таково: например, если у вас есть два человека, фамилии которых вы не знаете, вы пометите их как NULL - и только потому, что они оба NULL, вы не можете заключить, что у них одна и та же фамилия (так же, как вы не можете заключить, что у них разные фамилии - вы просто не знаете, так или иначе).

Есть только один оператор, который позволяет избежать этой инфекционной природы NULL, и это IS NULL:

SELECT NULL IS NULL;
-- => t

По аналогии с x = NULL, x IN (NULL) также использует сравнение на равенство, которое никогда не может быть оценено как true:

SELECT 2 IN (1, NULL);
-- => NULL
SELECT NULL IN (1, NULL);
-- => NULL
SELECT 1 IN (1, NULL);
-- => t

SELECT 2 NOT IN (1, NULL);
-- => NULL
SELECT NULL NOT IN (1, NULL);
-- => NULL
SELECT 1 NOT IN (1, NULL);
-- => t

Есть ли там 2? Возможно; у меня есть значение, которое я не знаю, что это такое, поэтому я не могу сказать, есть там 2 или нет, потому что оно может совпасть с неизвестным. Как насчет другой вещи, о которой я понятия не имею? Не знаю, может быть 1, а может быть равно другой неизвестной вещи - или это может быть что-то совсем другое. Как насчет 1? Ну, что касается этого, я могу видеть 1 там, независимо от того, чем является или не является неизвестная вещь.

Так что вместо обычного IN, вы должны проверить наличие NULL явно:

SELECT * WHERE foo_id IN (1, 2) OR foo_id IS NULL;

В терминах django:

from django.db.models import Q
Bar.objects.filter(Q(foo_id=foo.id) | Q(foo_id__isnull=True)).count()

или если у вас несколько значений

Bar.objects.filter(Q(foo_id__in=[1, 2]) | Q(foo_id__isnull=True)).count()
Вернуться на верх