Заметка про NULL

Всем привет!
Долго думал, что бы написать полезного про Оракл, перепробовал кучу тем. Каждый раз получалось слишком длинно, потому что уносило глубоко в дебри. Поэтому решил начать с максимально простой темы, чтобы оценить интерес аудитории и её отношение к моему стилю изложения материала (имхо, писатель из меня не очень). Несколько замечаний:

• заметка относится к СУБД Oracle, но большинство написанного касается и других СУБД;
• не получилось симпатично оформть код: тег source lang=«sql» подсвечивает слишком мало, как сделать по другому не в курсе. Если кто покажет, быстро всё приведу в более читабельный вид.

Итак.

Основные положения

Специальное значение NULL означает отсутствие данных, констатацию того факта, что значение неизвестно. По умолчанию это значение могут принимать столбцы и переменные любых типов, если только на них не наложено ограничение NOT NULL. Также, СУБД автоматически добавляет ограничение NOT NULL к столбцам, включенным в первичный ключ таблицы.

Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: =, <, >, like… Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ. Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО.

Таким образом, Oracle оперирует не двухзначной, а трёхзначной логикой. Эту особенность заложил в свою реляционную теорию дедушка Кодд, а Oracle, являясь реляционной СУБД, полностью следует его заветам. Чтобы не медитировать над “странными” результатами запросов, разработчику необходимо знать таблицу истинности трёхзначной логики. Ознакомиться с ней можно, например, на английской википедии: Three-valued_logic.

Для удобства сделаем процедуру, печатающую состояние булевого параметра:

create procedure test_bool( p_bool boolean ) is
begin
   case when p_bool = true  then dbms_output.put_line('TRUE');
        when p_bool = false then dbms_output.put_line('FALSE');
        else dbms_output.put_line('UNKNOWN');
   end case;
end test_bool;

и включим опцию печати сообщений на консоль:

set serveroutput on

Привычные операторы сравнения пасуют перед NULLом:

exec test_bool( null  = null );  -- UNKNOWN
exec test_bool( null != null );  -- UNKNOWN
exec test_bool( null  = 'a'  );  -- UNKNOWN
exec test_bool( null != 'a'  );  -- UNKNOWN

Сравнение с NULLом

Существуют специальные операторы IS NULL и IS NOT NULL, которые позволяют производить сравнения с NULLами. IS NULL вернёт истину, если операнд имеет значение NULL и ложь, если он им не является.

select case when null is null then 'YES' else 'NO' end from dual; -- YES
select case when 'a'  is null then 'YES' else 'NO' end from dual; -- NO

Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:

select case when 'a'  is NOT null then 'YES' else 'NO' end from dual; -- YES
select case when null is NOT null then 'YES' else 'NO' end from dual; -- NO

Кроме того, есть пара исключений из правил, касающихся сравнений с отсутствующими значениями. Во-первых, — это функция DECODE, которая считает два NULLа эквивалентными друг другу. Во-вторых, — это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.

DECODE идёт против системы:

select decode( null
          , 1, 'ONE'
          , null, 'EMPTY' -- это условие будет истинным 
          , 'DEFAULT'
          )
 from dual;

Пример с составными индексами находится в параграфе про индексы.

Логические операции и NULL

Обычно, состояние НЕИЗВЕСТНО обрабатывается так же, как ЛОЖЬ. Например, если вы выбираете строки из таблицы и вычисление условия x = NULL в предложении WHERE дало результат НЕИЗВЕСТНО, то вы не получите ни одной строки. Однако, есть и отличие: если выражение НЕ(ЛОЖЬ) вернёт истину, то НЕ(НЕИЗВЕСТНО) вернёт НЕИЗВЕСТНО. Логические операторы AND и OR также имеют свои особенности при обработке неизвестного состояния. Конкретика в примере ниже.

В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ:

select 1 from dual where dummy = null; -- запрос не вернёт записей

Отрицание неизвестности даёт неизвестность:

exec test_bool( not(null  = null) ); -- UNKNOWN
exec test_bool( not(null != null) ); -- UNKNOWN
exec test_bool( not(null  = 'a')  ); -- UNKNOWN
exec test_bool( not(null != 'a')  ); -- UNKNOWN

Оператор OR:

exec test_bool( null or true  );   -- TRUE    <- !!!!!
exec test_bool( null or false );   -- UNKNOWN
exec test_bool( null or null  );   -- UNKNOWN

Оператор AND:

exec test_bool( null and true  );  -- UNKNOWN
exec test_bool( null and false );  -- FALSE   <- !!!!!
exec test_bool( null and null  );  -- UNKNOWN

Операторы IN и NOT IN

Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL

create table t as select column_value a from table(sys.odcinumberlist(1,2,3,null));

Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.

set autotrace on

Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL):

select * from t where a in(1,2,null); -- вернёт [1,2]

-- Predicate Information: 
--   filter("A"=1 OR "A"=2 OR "A"=TO_NUMBER(NULL))

Как видим, строка с NULLом не выбралась. Произошло это из-за того, что вычисление предиката "A"=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО. Для того, чтобы включить NULLы в результат запроса, придётся указать это явно:

select * from t where a in(1,2) or a is null; -- вернёт [1,2,NULL]

-- Predicate Information: 
--    filter("A" IS NULL OR "A"=1 OR "A"=2)

Попробуем теперь с NOT IN:

select * from t where a not in(1,2,null); -- no rows selected

-- Predicate Information:
--   filter("A"<>1 AND "A"<>2 AND "A"<>TO_NUMBER(NULL))

Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3:

   3<>1  AND  3<>2  AND  3<>to_number(NULL)
   \ /         \ /        \    /
   true  AND  true  AND  unknown
       \     /         /
        true    AND  unknown
            \       /
             unknown

Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.

NULL и пустая строка

Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.

NULL и пустая строка эквивалентны:

exec test_bool( '' is null );  -- TRUE

непременный атрибут жёсткого спора:

select count(*) comments_count, avg(c.vote) avg_vote
from habr.comments c join habr.posts p on( c.post_id = p.id )
where lower(p.title) like '%пробел%'
  and lower(p.title) like '%табуляци%';

  COMMENTS_COUNT  AVG_VOTE
 --------------- ---------
          100500      -100

Если последовать завету классика и посмотреть в корень, то причину эквивалентности пуcтой строки и NULLа можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Oracle хранит строки таблицы в структуре, состоящей из заголовка, за которым следуют столбцы данных. Каждый столбец представлен двумя полями: длина данных в столбце (1 или 3 байта) и, собственно, сами данные. Если varchar2 имеет нулевую длину, то в поле с данными писать нечего, оно не занимает ни байта, а в поле с длиной записывается специальное значение 0xFF, обозначающее отсутствие данных. NULL представлен точно так же: поле с данными отсутствует, а в поле с длиной записывается 0xFF. Разработчики Оракла могли бы, конечно, разделить эти два состояния, но так уж издревле у них повелось.

Лично мне эквивалентность пустой строки и NULLа кажется вполне естественной и логичной. Само название «пустая строка» подразумавает отсутствие значения, пустоту, дырку от бублика. NULL, в общем-то, обозначает то же самое. Но здесь есть неприятное следствие: если про пустую строку можно с уверенностью сказать, что её длина равна нулю, то длина NULLа никак не определена. Поэтому, выражение length('') вернёт вам NULL, а не ноль, как вы, очевидно, ожидали. Ещё одна проблема: нельзя сравнивать с пустой строкой. Выражение val = '' вернёт состояние НЕИЗВЕСТНО, так как, по сути, эквивалентно val = NULL.

Длина пустой строки не определена:

select length('') from dual; -- NULL

Сравнение с пустой строкой невозможно:

exec test_bool( 'a' != '' ); -- UNKNOWN

Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон [phone]), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…

Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом '' (пустая строка), но не позволяют сохранить элемент с индексом NULL:

declare
 procedure empty_or_null( p_val varchar2 ) is
   type tt is table of varchar2(1) index by varchar2(10);
   t tt;
 begin
   if p_val is not null then
     dbms_output.put_line('не пусто');
   else
     -- пытаемся создать элемент с индексом p_val
     t(p_val) := 'x';
     -- получилось! Значит, пустая строка
     dbms_output.put_line('пустая строка');
   end if;
 exception
   -- создать элемент с индексом p_val не получилось. Значит, NULL
   when others then dbms_output.put_line('NULL');
 end;
begin
 empty_or_null( 'qwe' );  -- не пусто
 empty_or_null( '' );     -- пустая строка
 empty_or_null( NULL );   -- NULL
end;

Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.

Математика NULLа

^{Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.}

Задача. У Маши до замужества с Колей было неизвестное количество любовников. Коля знает, что после замужества у Маши был секс с ним, Сашей и Витей. Помогите найти Коле точное количество любовников Маши.

Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:

select decode( null + 10,  null, 'неизвестно', 'известно') a from dual; -- неизвестно
select decode( null * 10,  null, 'неизвестно', 'известно') a from dual; -- неизвестно
select decode( abs(null),  null, 'неизвестно', 'известно') a from dual; -- неизвестно
select decode( sign(null), null, 'неизвестно', 'известно') a from dual; -- неизвестно

С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.

select null ||'AA'|| null ||'BB'|| null from dual; -- AABB

NULL и агрегатные функции

Почти все агрегатные функции, за исключением COUNT (и то не всегда), игнорируют пустые значения при расчётах. Если бы они этого не делали, то первый же залетевший NULL привёл бы результат функции к неизвестному значению. Возьмём для примера функцию SUM, которой необходимо просуммировать ряд (1, 3, null, 2). Если бы она учитывала пустые значения, то мы бы получили такую последовательность действий:
1 + 3 = 4; 4 + null = null; null + 2 = null.
Вряд ли вас устроит такой расчёт при вычислении агрегатов, ведь вы наверняка не это хотели получить. А какой бы был геморрой с построением хранилищ данных… Бррррр…

Таблица с данными. Используется ниже много раз:

create table agg( id int, n int );
insert into agg values( 1, 1 );
insert into agg values( 2, 3 );
insert into agg values( 3, null );
insert into agg values( 4, 2 );
commit;

Пустые значения игнорируются агрегатами:

select sum(n) from agg; -- 6

Функция подсчёта количества строк COUNT, если используется в виде COUNT(*) или COUNT(константа), будет учитывать пустые значения. Однако, если она используется в виде COUNT(выражение), то пустые значения будут игнорироваться.

с константой:

select count(*)    from agg; -- 4
select count(1+1)  from agg; -- 4
select count(user) from agg; -- 4

С выражением:

select count(n)      from agg; -- 3
select count(id)     from agg; -- 4
select count(abs(n)) from agg; -- 3

Также, следует быть осторожным с функциями вроде AVG. Поскольку она проигнорирует пустые значения, результат по полю N будет равен (1+3+2)/3, а не (1+3+2)/4. Возможно, такой расчёт среднего вам не нужен. Для решения подобных проблем есть стандартное решение — воспользоваться функцией NVL:

select avg(n)        from agg; -- (1 + 3 + 2) / 3 = 2
select avg(nvl(n,0)) from agg; -- (1 + 3 + 0 + 2) / 4 = 1.5

Агрегатные функции возвращают состояние НЕИЗВЕСТНО, если они применяются к пустому набору данных, либо если он состоит только из NULLов. Исключение составляют предназначенные для подсчёта количества строк функции REGR_COUNT и COUNT(выражение). Они в перечисленных выше случаях вернут ноль.

Набор данных только из NULLов:

select sum(n)          from agg where n is null; -- НЕИЗВЕСТНО
select avg(n)          from agg where n is null; -- НЕИЗВЕСТНО
select regr_count(n,n) from agg where n is null; -- 0
select count(n)        from agg where n is null; -- 0

Пустой набор данных:

select sum(n)          from agg where 1 = 0; -- НЕИЗВЕСТНО
select avg(n)          from agg where 1 = 0; -- НЕИЗВЕСТНО
select regr_count(n,n) from agg where 1 = 0; -- 0
select count(n)        from agg where 1 = 0; -- 0

NULL в OLAP

Очень коротко ещё об одной особенности, связанной с агрегатами. В многомерных кубах NULL в результах запроса может означать как отсутствие данных, так и признак группировки по измерению. Самое противное, что на глаз эти две его ипостаси никак не различишь. К счастью, есть специальные функции GROUPING и GROUPING_ID, у которых глаз острее. GROUPING(столбец) вернёт единицу, если NULL в столбце измерения означает признак группировки по этому столбцу и ноль, если там содержится конкретное значение (в частности, NULL). Функция GROUPING_ID — это битовый вектор из GROUPINGов, в этой заметке она точно лишняя.

В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING, а за подробностями велкам ту Data Warehousing Guide, глава 21.

Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:

set null [NULL]

Проверяем дуализм NULLа в многомерном кубе:

with t as (
     -- Иван купил 1 кефир
     select 'IVAN' customer, 'KEFIR'  product, 1 qty from dual union all
     -- неизвестный покупатель купил 2 молока
     select NULL   customer, 'MOLOKO' product, 2 qty from dual union all
     -- Иван купил 2 молока
     select 'IVAN' customer, 'MOLOKO' product, 2 qty from dual
     )
select customer cust, grouping(customer) grp_c
     , product prod,  grouping(product)  grp_p
     , sum(qty) qty
from t
group by cube(customer, product)
order by grp_c, grp_p;

CUST GRP_C PROD   GRP_P  QTY
---- ----- ------ ----- ----
[NULL]   0 MOLOKO     0    2  -- безымянный клиент купил 2 упаковки молока
IVAN     0 KEFIR      0    1  -- Иван купил 1 упаковку кефира
IVAN     0 MOLOKO     0    2  -- Иван купил 2 упаковки молока
IVAN     0 [NULL]     1    3  -- Иван купил 3 упаковки всех товаров
[NULL]   0 [NULL]     1    2  -- безымянный клиент купил 2 упаковки всех товаров
[NULL]   1 MOLOKO     0    4  -- все клиенты купили 4 упаковки молока
[NULL]   1 KEFIR      0    1  -- все клиенты купили 1 упаковку кефира
[NULL]   1 [NULL]     1    5  -- все клиенты купили 5 упаковок всех товаров

Что осталось за кадром

Получилось довольно длинно, поэтому часть информации пришлось вырезать. А именно:

• NULL в индексах (обычные, составные, бинарные, функциональные). Там у меня больше листингов, чем текста;
• разбор функций для работы с NULLами: документированных и не очень;
• работа с NULLами в запросах (всяческие кейсы)
• может, ещё какие-то моменты забыл упомянуть, пишите в комментарии, добавлю.

Критика, дополнения и намёки на некомпетентность автора (с отсылкой в доку) приветствуются. Собственно, для этого всё и затевалось.