Здравствуйте уважаемые читатели блога sqlCMD.ru, постоянные и новые — автор блога рад новой встрече с вами. Наступивший сентябрь вызвал традиционный всплеск активности посетителей блога — писем и комментариев стало больше в разы. По всему видно — вновь наступила пора работы/учебы. 
Как обычно, вся эта активность служит автору отличным индикатором интереса и подсказывает ему какую тему следует осветить в первую очередь. Однако конкретно в данном случае ваш автор решился на довольно смелый эксперимент.
Дело в том, что достаточно давно, если и не на самой заре своей IT-карьеры то определенно где-то в тех числах, автор принялся вести этакий «внутренний SQL Server FAQ», а проще говоря текстовый файл, куда он записывал непонятные ему вопросы, мысли, идеи имеющие отношение к SQL Server. Потом непонятные вопросы дополнялись понятными ответами, и вот так составился довольно внушительный FAQ имеющий в настоящее время объем в несколько мегабайт, притом что его формат как был так и остался чисто текстовым, без единой иллюстрации. Так вот смелый эксперимент заключается в том, что, как подумалось автору, куски этого FAQ можно без проблем «конвертировать» в статьи блога. Ведь если тот или иной вопрос был интересен лично мне, и/или был в свое время до конца мне непонятен, то, с большой степенью вероятности можно предположить, что в той же ситуации оказались/окажутся несколько (десятков? сотен? — кто знает...) читателей блога. Которые с радостью прочтут готовый ответ на этот самый мучающий их вопрос. Поэтому автор и решил вырезать наиболее интересные места своего персонального FAQ, «причесывать» их, существенно дополнять поясняющим текстом/иллюстрациями/кодом T-SQL и в таком переработанном виде выкладывать на сайт. Что из того получится покажет время и, конечно же, ваши отзывы. Разумеется, статьи «по письмам читателей» так же будут продолжать создаваться, и более того, одна из них уже готова в черновике. Переложение FAQ просто станет еще одним и, как видится автору, довольно многообещающим способом поделиться накопленными знаниями/опытом со своими читателями.
Раздел FAQ которым автор решил сегодня поделиться с вами анализирует реакцию нашего любимого сервера на такую любопытную «зверушку» как значение NULL. Нельзя сказать, что данный вопрос сложен. Нет, как раз для именно понимания он не только прост, а даже, в определенной степени, элементарен. Хитрость заключается исключительно в разветвленности, то есть многообразии той самой реакции, и, самое главное, ее непоследовательности. Как известно абсолютно всем читателям данных строк NULL никогда не равен ничему, даже другому NULL. То есть выражение NULL=<что_угодно> не вернет нам TRUE никогда и ни за что. Правильно? В теории — на 100% правильно. А вернет ли тоже самое выражение FALSE? Нет, конечно, ведь NULL это «место подо что-то», полная неизвестность. Как мы можем утверждать что NULL точно не равен десяти? А если все-таки равен? Поэтому и FALSE мы тоже никогда из этого выражения не получим. Правильно? В теории — снова на 100% правильно. А на практике? А вот если бы те же самые утверждения выполнялись бы на практике хотя бы в 80% случаев, то и не было бы соответствующего раздела в персональном FAQ автора. И, как следствие, не было бы данной статьи. А она — перед вами. Так что давайте приступать...
NULL, UNKNOWN и троичная логика.
Начнем мы с очень базовых концепций известных, надо полагать, любому SQL-администратору/разработчику с опытом работы от года. Но, во-первых, повторение мать сами знаете кого/чего, во-вторых, систематизация знаний еще никому ущерба не принесла, а в-третьих, несмотря на всю базовость информации излагаемой в данном разделе статьи автор верит, что пару-тройку «юзабельных хинтов» в ней отыщется и для самых что ни на есть «SQL-гуру».
Сразу определимся, что в «большую науку» лезть мы не будем и рассуждать о принципиальной допустимости значений NULL в реляционных СУБД как таковых тоже не станем. Апологеты «чистоты» таких СУБД и их избавления от самого духа NULL-значений свою позицию обозначили давно, четко и ясно. И привели аргументы в поддержку той позиции. Аргументы, к слову сказать, весьма существенные, а некоторые — так и просто «железобетонные», спорить с которыми очень и очень проблематично. Да вот только все такие непрошибаемые аргументы ни на йоту не помогли нашим апологетам — NULL-ы в большинстве современных СУБД есть, и точка. Другое дело, что в SQL Server вы вольны тотально запретить это «нехорошее» значение обозначив все и каждую колонку своей базы NOT NULL. Однако на практике дизайн со столь суровыми ограничениями встречается зело нечасто, а поэтому, отринув дальнейшее хождение вокруг да около, мы можем переходить к простому и понятному вопросу — как нам жить с NULL и успешно миновать все «грабли», что этот самый NULL заботливо для нас приготовил?
Самых больших проблем с NULL-ом две. Первая формулируется очень просто: сравнение NULL c чем угодно, включая и другой NULL, дает в результате UNKNOWN.
В общем-то, в самом факте что сравнение с NULL дает не TRUE, не FALSE, а именно UNKNOWN нет ничего страшного. «Страшно» следствие из этого факта: привычная многим (и даже почти всем) программистам языков высокого уровня двоичная логика сменяется троичной. «И что», возможно спросите вы, «это добавочное и третье логическое значение так уж усложняет жизнь SQL-администратора/разработчика»? Намного, ответит вам автор! Именно это самое третье значение вносит в жизнь означенных IT-персон то разнообразие, которое приятным никак не назовешь. Собственно говоря, разбор и анализ этого самого разнообразия является центральным вопросом статьи читаемой вами в настоящий момент.
Вторая большая проблема — в SQL Server нет типа bool. Вот все мыслимые (а иногда и трудно вообразимые) типы есть, а bool — нет. Внимательные читатели сразу же зададутся вопросом — осведомлен ли автор текущих строк о таком замечательном типе нашего сервера, как bit? Автор спешит уверить, что о данном замечательном типе он узнал уже много-много лет назад, и большой опыт работы с ним позволяет ему утверждать — это не есть тип bool. Сравните:
1 2 | bool b; b=true; |
Это отрывок кода на C#, имеющего первоклассный и «всамделишный» тип bool. «И?», снова спросят те же читатели. «А вот так на T-SQL»:
1 2 | DECLARE @b bit SET @b='TRUE' |
«не тоже ли самое»? Уверяю вас, в мире нет ничего дальше отстоящего друг от друга чем два показанных фрагмента. Разумеется, вы вольны считать что на логическом уровне там и там переменная b получила значение истина, и основания для подобного утверждения у вас есть. Ну а вот если копнуть «физику процесса»? А вот тогда выясняется, что в C#:
- переменная b может принимать только значения логического типа. Попытка поместить туда, к примеру, целое (b=1) заканчивается ошибкой компиляции;
- переменную b можно сравнивать только с булевым же значением. Попытка соотнести ее с единицей (b==1) вновь вызывает недовольство компилятора, а вот b==(1>2) — пожалуйста, true сравнивается с false, все законно.
Параллельно выясняется же, что в T-SQL:
- переменная @b получает значение 1, а вовсе никакое не TRUE. Да и как может быть иначе, если по своему определению тип bit целочисленный? Он априори может быть или 1 (число!) или 0 (число!). Строковые константы TRUE/FALSE просто приводятся к этим двум числам, успешно создавая у нас иллюзию «bool-ориентированности» языка T-SQL. Еще тип bit может быть NULL, но пока оставим это дабы не усложнять и без того непростые вещи;
- вы запросто можете присвоить 1 (число!) нашей переменной — SET @b=1. А хотите — сравните их: IF (@b=1) PRINT '@b is 1';
- а вот IF (@b=(1>2)) PRINT 'Must be false' — не прокатывает. Сравнение 1>2 дает «реальный» тип bool из мира T-SQL, для которого у нас просто нет подходящего контейнера! И уж конечно этот «реальный bool» никоим образом не может быть соотнесен с простой цифрой.
Итого, в T-SQL тип bool мало того что усложнен третьим возможным значением, так он еще по природе своей столь эфемерен, что значения этого типа возникают только в момент исполнения одного из операторов сравнения (равно, больше, меньше и т.д.) и «испаряются» сразу же после этого. Мы не можем (как это совершенно без проблем делают C#-программисты) «законсервировать» результат такого сравнения, дабы проанализировать его позже — нам попросту некуда его поместить! То есть, в C#: b=2>3 — запросто, все отлично, b теперь ложь. В T-SQL: SET @b=(2>3) — Incorrect syntax near '>'. А что, собственно, вы бы хотели что бы оказалось в переменной @b с учетом ее типа? Ну не ноль же, в самом деле... «Два больше трех равно нулю» это определенно не та фраза которую хочется прочесть в документации к любой программной платформе. А сверх того, T-SQL лишен изящества с которым тот же C# выявляет окончательный логический результат того или иного выражения. В последнем языке Console.WriteLine(2>3) с удовольствием напечатает вам False, в T-SQL PRINT 2>3 не напечатает ровным счетом ничего и даже не исполнится. А все потому, что в C# bool является именно что типом. Определены его характеристики, свойства, поведение, и, в том числе, визуальное представление каждого из его возможных значений. T-SQL всего этого праздника начисто лишен. Отсюда и берутся столь уродливые конструкции как SELECT 1 WHERE 2>3, которые, между прочим, так же не до конца корректны (а почему они не являются верными на 100% мы выясним через пару абзацев) когда мы хотим однозначно установить итоговое логическое значение того или иного выражения в T-SQL.
Теперь зафиксируем для себя пару вещей которые «и так всем хорошо известны».
Пример:
1 2 3 4 5 6 7 8 9 10 11 | create table T2 (Col1 int) GO insert T2 VALUES (NULL) GO SELECT NULL SELECT NULL+NULL SELECT 'X'+NULL+' ' SELECT 2+3*4/NULL SELECT 5-MAX(Col1) FROM T2 SELECT 'AA'+CAST(MAX(Col1) AS varchar(10))+'BB' FROM T2 SELECT MAX(Col1)/0 FROM T2 |
Все приведенные только что SELECT-ы будут просчитаны в одно и тоже значение, и вы, разумеется, догадались в какое. Забавно, что NULL, в определенном смысле, даже попирает законы арифметики. Последний SELECT по хорошему должен бы заканчиваться ошибкой деления на ноль, а он как ни в чем не бывало возвращает нам NULL.
Проверьте свои знания T-SQL: Что изменится для трех последних SELECT-ов, если мы после создания таблицы T2 не вставим в нее ни единой строки?
Вторая «и так всем известная» вещь:
То есть, если структура и содержимое таблицы T2 те же самые что и в предыдущем примере, то все показанные ниже логические выражения будут вычислены именно в UNKNOWN
1 2 3 4 5 6 7 8 9 10 | IF(NULL>NULL+4)... IF(0=NULL)... IF(NULL=NULL)... IF(NULL<>NULL)... IF(''=NULL)... DECLARE @max int SELECT @max=MAX(Col1) FROM T2 IF(@max=@max)... IF(@max=NULL)... |
Как автор может быть в этом уверен? Нет, понятно что правило только что было объявлено и согласно ему все верно — везде UNKNOWN. Однако — доказательства?
Как отмечалось в статье чуть выше отсутствие в T-SQL «вменяемого» bool-типа приводит к уродливым конструкциям наподобие SELECT 1 WHERE ''=NULL. А как еще оценить довольно сложное логическое выражение? Однако и она, конструкция эта, не доказывает что исследуемое выражение просчитывается в UNKNOWN. Тот факт что показанный SELECT вернет пустой резалт-сет однозначно перечеркивает лишь мысль о возможной эквивалентности пустой строки и значения NULL. То есть мы лишь установим что ''=NULL точно НЕ TRUE, на что сложно было рассчитывать и без всяких проверочных кодов. Однако пустой резалт-сет того же SELECT-а вовсе не отменяет той возможности, что результатом сопоставления ''=NULL является FALSE, а вовсе не UNKNOWN. Приходится быть хитрее:
1 2 3 | IF (~) PRINT 'TRUE' ELSE IF NOT (~) PRINT 'FALSE' ELSE PRINT 'UNKNOWN ' |
В показанном фрагменте кода тильду (~) следует заменить тем логическим выражением, чей результат вызывает у вас неуверенность. Например, вместо этого символа можно было бы впечатать все те же ''=NULL. Разумеется выражение должно быть одним и тем же и в первой, и во второй строке показанного фрагмента. И вот только пройдя такую двойную проверку мы выносим неопровержимый вердикт: ''=NULL есть UNKNOWN, как и любое иное логическое выражение где фигурирует NULL.
Из только что рассмотренных «и так всем известных» фактов следуют два далеко неочевидных вывода. Первый из которых: когда мы читаем фразы вида «NULL плюс что угодно равно NULL...» или «NULL сравнить с чем угодно будет UNKNOWN...», то под NULL в этих фразах (а так же в тех двух правилах, что автор выделил для вас специальной разметкой чуть выше) следует понимать как строковый литерал NULL, так и любое выражение вычисляемое в то же самое значение. Например строки SELECT MAX(Col1)/0 FROM T2 и IF(@max=@max)... вроде бы и не содержат никаких NULL-ов, а соответствующие правила к ним применимы всецело. И все потому, что и MAX(Col1), и @max представляют собой именно NULL. Так что если вы просматриваете код и не видите там никаких упоминаний о NULL-ах это вовсе не гарантирует что в ходе его выполнения вы не получите UNKNOWN как результат оценки логического выражения.
Теперь полистаем наш любимый и глубокоуважаемый BOL. Возьмем, к примеру, вот такую его статеечку — = (Equals). Прямо черными английскими буквами по белому полю сказано (выделение автора):
Это удивительно, но автору известны лишь три статьи из BOL где все сказано правильно «NULL vs.<что_угодно>=UNKNOWN». И порядка десятка статей утверждающих что сравнение с NULL приводит к результату FALSE (что попросту чудовищно по степени ошибочности), или что сравнение с NULL приводит к результату NULL (что несколько лучше, но снова «не айс», хотя бы в силу того обстоятельства что NULL не является итоговым результатом сопоставления двух значений; максимум чем он может быть — одним из этих значений). С учетом что подобные предложения трудолюбивые работники MS аккуратно копи-пастят (видимо не читая?) при выходе очередной версии SQL Server так что срок их жизни только на памяти автора составляет лет по 15-ти, приходим ко второму далеко неочевидному выводу: стабильность — не всегда признак мастерства, а изучать BOL следует... осмотрительно, как минимум. Следствие: если вы читаете что угодно по SQL Server (причем источник информации не важен абсолютно) и при этом в соседнем окне у вас не открыта SQL Server Management Studio с целью контроля достоверности информации (если не сплошной, то выборочной как минимум) — вы напрасно это читаете.
Вернемся к нашему фрагменту кода «двойной проверки»:
1 2 3 | IF (~) PRINT 'TRUE' ELSE IF NOT (~) PRINT 'FALSE' ELSE PRINT 'UNKNOWN ' |
Почему именно такой код позволяет нам уверенно вычленять те логические выражения что в результате дают UNKNOWN? Да потому, что только это единственное (из трех возможных) bool-значение обладает уникальным свойством: его отрицание (NOT) никак не влияет на результат! Иными словами NOT UNKNOWN=UNKNOWN. TRUE/FALSE при той же операции меняют свои значения на противоположные, как это всем хорошо известно. А что у нас с логическими операторами OR/AND связывающих два и более логических выражения одно из которых просчитано в UNKNOWN? Тут у многих SQL-администраторов/разработчиков наблюдается интересный, вполне объяснимый, но от того не менее ошибочный «дедуктивный» вывод: «раз NULL плюс что угодно равно NULL», рассуждают они, «то и UNKNOWN плюс что угодно будет UNKNOWN». Насчет NULL они совершенно правы, а вот насчет UNKNOWN — заблуждаются. Опять же, здесь начинаются довольно тонкие материи: NULL это значение, UNKNOWN это возможный результат сравнения двух значений. И если связываются два логических выражения одно из которых просчитано в UNKNOWN, а второе может иметь любое из трех bool-значений, то:
- UNKNOWN OR TRUE = TRUE
- UNKNOWN OR FALSE = UNKNOWN
- UNKNOWN OR UNKNOWN = UNKNOWN
И, соответственно:
- UNKNOWN AND FALSE = FALSE
- UNKNOWN AND TRUE = UNKNOWN
- UNKNOWN AND UNKNOWN = UNKNOWN
Как видите UNKNOWN AND/OR <что_угодно> чаще всего будет действительно давать в итоге снова UNKNOWN. Но — отнюдь не всегда! Ну и что бы завершить картину взаимоотношений логических операторов с UNKNOWN и еще раз подчеркнуть уникальное свойство этого результата сопоставления двух значений:
- NOT TRUE=FALSE
- NOT FALSE=TRUE
- NOT UNKNOWN = UNKNOWN
Теперь — подумаем над механизмом выполнения команды типа SELECT...FROM...WHERE Col1=NULL. Да, автор уже слышит справедливые «выкрики с места»: кто ж так с NULL сравнивает-то? IS [NOT] NULL для кого сделаны были? Все это совершенно правильно и понятно, но! Показанный фрагмент абсолютно корректен с точки зрения «голого» синтаксиса T-SQL. И выполняется он тоже без проблем. А мы с вами, как IT-профессионалы стремящиеся к полному и совершенному постижению SQL Server и того языка на котором пишутся программы для него обязаны понимать механику работы любого синтаксически корректного выражения. Хотя бы с целью его исправления и приведения к логически корректному виду. Так вот, задается вопрос — как работает наша «неправильная» команда? Вне сомнений, к текущим строкам статьи, мы без всяких тестов и проверок уверенно заявляем: Col1=NULL это у нас UNKNOWN. То есть: берется первая строка исходной таблицы и условие для ее включения/исключения в/из итоговый резалт-сет просчитывается в это «неудобное» bool-значение. Что делать? В теории показанный SELECT должен открыть интерактивную сессию вопросов-ответов с пользователем: «строка 1, критерий включения в итоговый резалт-сет неизвестен, включаем?»; «строка 2, критерий неизвестен...» и т.д. На практике же UNKNOWN в данном случае (а случаи, как мы вскоре выясним, бывают разные) приравнивается к FALSE, что вносит изрядную долю путаницы в мысли администраторов, разработчиков, да и писатели статей для BOL видимо так же «дают сбои» на том же самом месте. То есть: в теории у нас получилось UNKNOWN, на практике мы работаем с FALSE. Вот в этот самый «разлом» между теорией и практикой и вклинивается наша статья. Ну а ответ на заданный вопрос — вот так и работает, условие для включения в итоговый резалт-сет для каждой строки просчитывается в FALSE (причем гарантированно для каждой строки без исключения) и в силу этого обстоятельства мы получаем пустой итоговый резалт-сет (и снова гарантированно).