February 1, 2009

NULL и NOT IN()

Перевод NULLs vs. NOT IN() с select * from depesz;

Как-то раз мой друг, как ему показалось, нашел ошибку в PG.

Очень простой запрос
select * from table where id not in (select field from other_table)
не возвращал ни одной строки, несмотря на то, что точно существовали такие id, которых не было в other_table.field. Как это?

Для начала смоделируем ситуацию:
CREATE TABLE objects (id INT4 PRIMARY KEY);
CREATE TABLE secondary (object_id INT4);
INSERT INTO objects (id) VALUES (1), (2), (3);
INSERT INTO secondary (object_id) VALUES (NULL), (2), (4);
Данные:
# select * from objects ;
id
----
1
2
3
(3 rows)

# select * from secondary ;
object_id
-----------
[null]
2
4
(3 rows)
Ок. Первым делом проверим:
# SELECT * FROM objects WHERE id IN (SELECT object_id FROM secondary);
id
----
2
(1 row)
Таким образом, опираясь на то, что у нас есть 3-и id объектов (1, 2, 3) и IN работает только для 2-ки, NOT IN должен вернуть 1 и 3. Правильно? Не правильно:
# SELECT * FROM objects WHERE id NOT IN (SELECT object_id FROM secondary);
id
----
(0 rows)

Чё за... Почему?

Проверим результат id NOT IN для каждого id:
# SELECT id, id NOT IN (SELECT object_id FROM secondary) FROM objects;
id | ?column?
----+----------
1 | [null]
2 | f
3 | [null]
(3 rows)
Хм... но почему?

Разберём случай с id = 2.

Сравнение с 2 во второй таблице - id (2) равен 2, т.ч. можем утверждать, что NOT IN вернёт false. Дальнейшая проверка не требуется.
Сравнение с 4 во второй таблице - id (2) не равен 4, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с NULL во второй таблице - сравнение id и NULL даст NULL, т.к. NULL рассматривается как "неизвестно". Может равно, а может не равно. И всё ещё требуется дальнейшая проверка.

Получается, что для id = 2 у нас будет false, потому что во второй таблице есть строка с object_id = 2.

Но что будет при сравнении с id = 3?

Сравнение с 2 во второй таблице - id (3) не равен 2, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с 4 во второй таблице - id (3) не равен 4, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с NULL во второй таблице - сравнение id и NULL даст NULL, т.к. NULL рассматривается как "неизвестно". Может равно, а может не равно. И всё ещё требуется дальнейшая проверка.

Для двух строк NOT IN сработало, но для одной - мы не уверены. NULL - "неизвестно", т.ч. оно может оказаться и тройкой (3). Мы не знаем. И по этому NOT IN должен вернуть NULL.

Если посмотреть ближе, то можно увидеть, что технически не возможно получить true в данной ситуации, пока во второй таблице есть значения NULL!

Что делать?

Есть очень простое решение:
# SELECT id, id NOT IN (SELECT object_id FROM secondary WHERE object_id IS NOT NULL) FROM objects;
id | ?column?
----+----------
1 | t
2 | f
3 | t
(3 rows)
Которое возвращает строки с id = 1 и id = 3.

2 comments:

grayhemp said...

После статьи Хуберта, в комментариях разворачивается довольно "холиворная" дискуссия, советую почитать...

Unknown said...

Видел подобный пример в книге у Том Кайта

Post a Comment