Блог СУБД SoQoL

Про функцию NULLIF

В общем, и в SoQoL, и в Oracle и в PostgreSQL реализация данной функции схожа:
- она имеет одинаковый синтаксис NULLIF (<выражение1>, <выражение2>);
- допускает для аргументов значения любого типа данных;
- тип данных результата соответствует типу данных первого аргумента.

И если посмотреть элементарный пример, то и результаты во всех озвученных СУБД будут одинаковые:

select nullif (10, 5);
Результат: 10, т.к. значения аргументов не равны

select nullif('admin', 'admin');
Результат: NULL, так как значения равны

Но есть момент в деталях реализации NULLIF в Oracle, который проявляется при иных случаях применения функции.

Возьмём простой генератор псевдослучайных чисел, который возвращает либо 0, либо 1:

dbms_random.value (0, 2)

И попробуем получить ряд целых случайных чисел или NULL, если сгенерированное число = 0.
Записываем и исполняем несколько раз:

select nullif (trunc (dbms_random.value(0, 2)), 0) FROM dual;

Результат: 1
Результат: NULL
Результат: 0

Но если сгенерированное число 0, то результат должен быть NULL. Почему иногда возвращает 0?

А тут стоит обратить внимание на детали процесса, происходящего внутри:
1. Сначала вычисляется значение первого аргумента, и если он равен второму, то возвращается NULL;
2. Если же нет, то первый аргумент вычисляется заново и возвращается полученный результат. Т.е. в примере выше сначала генератор вернул значение 1, функция проверила, что 1 не равно 0, и подставила в результат первое выражение. Только сгенерировалось новое значение, которое оказалось равным 0.

Получается, что все выражения функции NULLIF в Oracle трансформируются оптимизатором в:
CASE WHEN <a> = <b> THEN NULL ELSE <a> END

Тогда логично, что число генерируется дважды и функция NULLIF возвращает 0.

Если смотреть SoQoL, то в нём запрос:
select nullif (trunc (random_value (0, 2)), 0);
возвращает значение только двух вариантов: NULL или 1.

В Postgres запрос select nullif (trunc (random ()*2), 0); возвращает NULL или 1.

Так почему в Oracle выбран именно такой сценарий реализации функции NULLIF? Есть мнение, что NULLIF был введен в целях совместимости, поэтому является закамуфлированной версией CASE.

Но может есть в такой реализации функции более глубокие причины? Кто знает Oracle досконально? Поделитесь свои мнением.

Обсудить в телеграме!