Есть вещи, которые не нравятся, есть вещи, которые бесят, есть вызывающие жгучий гнев, и есть ситуация с NULL в SQL.
Догма
Мы все выучили наизусть: NULL не равен NULL. И не неравен NULL. И не больше, и не меньше. И не не больше, и не не меньше. Там вот такая вот особая хтонь, про которую надо постоянно помнить и которую надо постоянно аккуратно бочком обходить. Почему так всё причудливо, вам, конечно, с удовольствием объяснят. Дело в том, что NULL это неизвестность. Тень мирового зла. Каждый NULL это маленькая смерть.
Если ты допустил NULL в своей базе, ты грешен, и нет тебе пощады. Ты должен и будешь страдать. Капля неизвестности отравляет всю реку, взгляд в неизвестность ослепляет, размышление о неизвестности лишает разума.
Любая операция с неизвестностью (кроме специально выкованных эльфами операторов проверки на неизвестность) должна давать неизвестность. Это догма. Попытка оспорить догму - ересь.
Но давайте всё же попытаемся разобраться в вопросе, не особо стесняясь задавать неудобные вопросы.
В чём проблема
Проблема в том, что NULL в SQL это сейчас единственное место в информационных технологиях, где реально "в Проде" применяется троичная логика. Всё было бы ничего, но:
Далеко не все об этом догадываются, и работают с этим как с обычной двоичной логикой, отягощённой некой загадочной "фичей".
Троичная логика не применяется нигде кроме вычисления условий. Вне условий результат UNDEFINED интерпретируется как FALSE. Даже внутри самого SQL.
Всё остальное окружение – бэкенды, фронтенды, аналитические инструменты, мозги разработчиков, мозги пользователей – всё приспособлено работать с двоичной логикой, а с троичной логикой работает через пень колоду.
В родной двоичной у нас так:
A and B |
A or B |
||||||||
A |
not A |
A \ B |
false |
true |
A \ B |
false |
true |
||
false |
true |
false |
false |
false |
false |
false |
true |
||
true |
false |
true |
false |
true |
true |
true |
true |
||
В троичной всё причудливее:
A and B |
A or B |
||||||||||
A |
not A |
A \ B |
false |
NULL |
true |
A \ B |
false |
NULL |
true |
||
false |
true |
false |
false |
false |
false |
false |
false |
NULL |
true |
||
NULL |
NULL |
NULL |
false |
NULL |
NULL |
NULL |
NULL |
NULL |
true |
||
true |
false |
true |
false |
NULL |
true |
true |
true |
true |
true |
||
И дополнительно:
A |
A is NULL |
A is not NULL |
A is TRUE |
A is FALSE |
false |
false |
true |
false |
true |
NULL |
true |
false |
false |
false |
true |
false |
true |
true |
false |
По сути, внутри вычисления условия применяется троичная логика, но потом к результату применяется "is TRUE", чтобы перепрыгнуть в нормальную двоичную логику, используемую дальше.
Потому что никто понятия не имеет, как сделать систему, целиком основанную на троичной логике.
Ситуацию можно было бы посчитать нормальной, если бы SQL был экспериментальным изделием, на котором академическое сообщество обкатывает безумные идеи. Но это уже давно далеко не так.
Вообще изучать эту троичную логику одно удовольствие. Она интересна, красива. Но когда переходишь к практическому использованию в реальных проектах, слов, кроме матерных, не остаётся.
А насколько вообще нужен NULL?
Может быть можно и без него? Тогда бы и проблема троичной логики ушла бы сама собой.
На это есть два соображения – одно практическое, другое глубоко теоретическое:
С практической точки зрения NULL – чрезвычайно удобная штука (если бы не троичная логика, конечно). Идеальный вариант для значения по умолчанию. Если по какой-то причине, коих кроме незнания ещё штук сто, нужно воздержаться от вписывания значения, NULL – лучший кандидат. Гораздо лучше, чем вписывать фейковое значение "родного" типа.
С точки зрения математической основы реляционных баз данных nullable колонки даже намного естественнее и роднее, чем "not null".
Раскрою второй пункт подробнее.
Почему NULL полноправный гражданин в реляционных базах данных
Знаете почему реляционные базы данных называются реляционными? Реляция (relation) это связь, отношение. Когда говорят, что база реляционная, связь чего с чем имеется в виду? Я провёл небольшое исследование и опросил коллег. Все они неплохие разработчики, давно имеющие дело с базами данных, и все мне ответили, что термин "relational" делает акцент на том, что там есть не только таблички, но и связи между ними по внешнему ключу. Стрелочки на Entity-Relationship (ага, вот оно!) диаграммах. Что, конечно же, неправильно.
Отношениями в теории реляционных баз данных являются не связи между таблицами, а сами таблицы. И даже не сами таблицы, а подмножества их колонок.
Рассмотрим такую таблицу:
id |
Фамилия |
Имя |
Отчество |
1 |
Иванов |
Сергей |
Петрович |
2 |
Smith |
John |
|
... |
Она задаёт отношения (те самые relations) между следующими множествами:
Множество "сущностей", представленное колонкой
id.Множество фамилий.
Множество человеческих имён.
Множество отчеств.
Факты, сохранённые в строчках, можно сформулировать так:
Человек #1 имеет фамилию Иванов, имя Сергей, отчество Петрович.
Человек #2 имеет фамилию Smith, имя John, отчество... ээээ... [смущается, плачет]... давайте впишем NULL.
Можно заметить, что по сути, каждая строчка содержит по три отдельных факта вида "человек #1 имеет фамилию Иванов", "человек #1 имеет имя Сергей" и т.д. Ах да, вторая строчка не содержит факта про отчество.
С точки зрения реляционной теории мы вполне вправе разделить таблицу persons на четыре отдельные: собственно persons, в которой остаётся только колонка id, и три связанные с ней по внешнему ключу таблицы person_lnames, person_fnames и person_mnames. Когда будет нужно вытащить всё вместе, будем вязать "колоночные" таблицы к основной внешним соединением. Что, конечно, не так удобно, но сути происходящего не меняет. За исключением того, что структура с четырьмя таблицами эквивалентна исходной только в том случае, если атрибуты Фамилия, Имя и Отчество все nullable. Жёсткий эквивалент "not NULL" в конструкции с четырьмя таблицами организовать не получится. При этом, что интересно, сами "колоночные" таблицы полностью "not NULL", а пустые значения возникают в результате внешнего соединения. Например, для Джона Смита просто нет соответствующей записи в таблице person_mnames.
Избавиться от хранения значений NULL в колонках можно, но они всё равно будут возникать в результатах внешних соединений. А если избавиться от внешних соединений, получим неполноту реализации реляционной алгебры.
Итак, NULL это не ошибка, не нелепица и не результат раздолбайства, а вещь, естественным и необходимым образом присутствующая в идейной основе реляционных баз данных. Об этом, кстати, ещё даже великий Кодд писал.
NULL и троичная логика
Троичная логика не нужна. Без неё здесь можно обойтись точно так же, как мы без неё обходимся во всех остальных случаях. Двоичной логики "TRUE - FALSE" достаточно для любых применений. Без лишних усложнений. Для этого достаточно понять и принять два простых правила:
NULL равен NULL. Да, вот так, без глупостей. Единственное вполне объяснимое исключение – уникальный индекс на nullable колонке. Как мы помним, nullable колонка логически эквивалентна отдельной "колоночной" таблице, в которую в случае отсутствия значения просто не добавляется строка.
NULL меньше любого не-NULL значения. Почему не больше? Да просто потому что POLA (Principle of least astonishment).
Насколько мне известно, не существует ни одного рационального аргумента в пользу того, что результат сравнения "NULL = NULL" должен быть чем-то кроме TRUE. Только всякое бессмысленное словоблудие об инфернальной природе пустоты. Больше нигде – ни в науке, ни в технике, ни в культуре, ни в искусстве нет такого, чтобы отношение идентичности "is" было слабее отношения равенства.
Что касается практической полезности троичной логики, то полезность эта отсутствует. По крайней мере, я не могу вспомнить ни одного случая, когда эта самая троичная магия мне для чего-то пригодилась. Она всегда помеха, которую приходится преодолевать.
Единственное, для чего эти пляски вокруг NULL по-настоящему пригождаются, это чтобы трахать студентов на зачётах и экзаменах.
Выводы
Нужно признать, что решение применить троичную логику для NULL было концептуальной ошибкой, последствия которой индустрия разгребает уже несколько десятилетий, каждый день тратя уйму ресурсов и проливая тонны пота и слёз. И не имея взамен ровным счётом ничего.
Спасибо, господа академики, опыт был интересным и поучительным. Теперь пора исправить ошибку и двигаться дальше. В любом случае, рано или поздно, ошибку придётся исправлять.
Идея для стартапа: форкнуть Постгрес и сделать Postgres BFE (Bullshit-free Edition). И посмотреть, как народу зайдёт такая еретическая идея. Да, не соответствует стандарту SQL, ну так это... стандарты надо нормальные делать.
Комментарии (130)
rrrad
26.08.2022 11:49+5Из опыта собеседований на junior-middle позиции, не менее четверти кандидатов не знают как работает сравнение с null.
В отличии от Oracle, в PostgreSQL тип boolean - это first class citizen, а значит троичную логику можно выпилить только с выпиливанием NULL-ов вообще. Если задуматься о смысле значения NULL, то правила работы с логическим NULL-ом устроены достаточно логично.
Что характерно, троичную логику, применив в нужных местах coalesce, достаточно легко соорудить двоичную, а вот обратная конструкция будет крайне нетривиальна. Из собственного опыта могу сказать, что в большинстве случаев при продумывании того, как будут отрабатывать значения NULL в сложных комбинированных условиях, оказывалось, что текущая троичная логика соответствует требованиям. Так что выпиливание троичной логики сделает многие системы сильно сложнее.
Автор, конечно, волен замутить PostgreSQL с выпиленной двоичной логикой (хотя я бы посоветовал подумать о более корректном названии), но думаю, что он будет единственным пользователем. Никому не нужны ни с чем несовместимые системы.
maslyaev Автор
26.08.2022 16:18Монга не соответствует стандарту SQL, Эластик не соответствует, много кто ещё не соответствует. Но это не мешает людям ими с удовольствием пользоваться.
Вообще, тенденция сейчас такова, что SQL потихоньку просачивается в самые неожиданные места. Не удивлюсь, если скоро приделают запросы к DOM в браузере. Если ещё не приделали. Тащить везде за собой эту глупую концептуальную ошибку с троичной логикой... ну, не обязательно, разве нет?
rrrad
26.08.2022 18:30+4Давайте я вам помогу: при анализе дампов памяти java в Memory Analyzer'е можно использовать sql-подобный язык для поиска объектов.
Это всё не важно: есть стандарт, которому должны следовать (в меру возможностей) все уважающие себя RDBMS и он общепризнан. Ни монга, ни эластик RDBMS не являются. Давать такие примеры - это всё равно, что говорить, что несущие крылья для самолёта - это концептуальная ошибка, т.к. они занимают много места и вообще, у вертолёта Ми-8 нет таких крыльев и это не мешает ему летать.
maslyaev Автор
26.08.2022 23:13-1Реляционная алгебра тоже вполне применима к данным, лежащим в Монге или Эластике. Помню, как нарисовал ER-диаграмму монговской базы, и это оказалось вот прям очень полезно.
Да, они не SQL. Они "Not only SQL". Но они тоже RDBMS. Не надо ставить знак равенства между SQL и RDBMS.
rrrad
27.08.2022 02:49+4Вы довольно таки широко трактуете термины, за пределами классического их использования. В такой трактовке, термины теряют свой смысл. Также при этом, PostgreSQL, внезапно, становится настоящей Not only SQL базой, при чём, возможно, в большей степени, чем вышеназванные, ведь она поддерживает настоящий полновесный диалект SQL, выполняя подавляющее большинство стандарта этого языка, а также позволяет использовать её как документоориентированную базу данных за счёт поддержки json/jsonb.
То, что MongoDB может использоваться в качестве rdbms - это всё-таки в некотором роде её развитие (вызванное тем, что людям из соседней экосистемы хочется выполнять знакомые действия привычным способом), однако большинство источников их противопоставляют.
m03r
26.08.2022 11:53+21Если
NULLбудет равен сам себе, то внешние соединения по нулевым полям дадут совершенно неинтуитивный результат. Отсутствующее отчество у Джона Смита — это не то же, что отсутствующее отчество у Джимми Вонга
KongEnGe
26.08.2022 13:19+2Это как в разборе математических софизмов: ищи, где втихую обе части равенства на ноль домножили или разделили :)
maslyaev Автор
26.08.2022 13:54-1Вот тут как раз интуиция всегда оказывается на стороне того, что NULL=NULL. И чтобы вспомнить, что это "на самом деле" не так, приходится делать над собой усилие. Кстати, в некоторых случаях SQL работает без глупостей. Например:
select mname, count(*) from persons group by mnameКто-нибудь страдает от такого хамского попрания догмы со стороны group by? Неа, все только наслаждаются.
m03r
26.08.2022 16:37+5В одном из ответов на stackoverflow приведены интересные цитаты из спецификации SQL. Если кратко:
NULLдруг другу не равны (not equal), но при этом они неразличимы (not distinct). Таким образом,GROUP BYне попирает догму, а просто использует иную концепцию.Собственно, SQL2003 определяет оператор
IS DISTINCT FROM, а в MySQL есть<=>maslyaev Автор
26.08.2022 21:25+2NULLдруг другу не равны (not equal), но при этом они неразличимы (not distinct)Я же говорю, единственное место в наследии нашей цивилизации, где идентичность слабее равенства. Это как если бы в JS "===" давало true, а результат "==" интерпретировался бы как false.
m03r
27.08.2022 13:00Почему Вы упорно пытаетесь свести
NOT DISTINCTк равенству/идентичности? Это просто отдельный концепт.Если уж говорить об аналогиях
NULLсреди типов данных языков программирования, то для меня это всегда казалось чем-то близким монаде MayBe или типуOption.А поскольку
NULLможет случиться где угодно, вOptionполучаются неявно завёрнуты вообще все значения.И distinct/равенство, отлично вписываются в эту концепцию: distinct сравнивает сами
Option'ы (и возвращаетbool), а равенство — их содержимое (и возвращает тожеOption). А вот в JS ничего подобного нет.maslyaev Автор
27.08.2022 14:32а равенство — их содержимое (и возвращает тоже
Option)Зачем? Какой в этом практический смысл? Можете привести пример кода, где это полезно?
m03r
27.08.2022 18:17+2Вам ни разу не приходилось делать
JOINпо nullable-колонкам? Если ко всемNULLам из одной таблицы приджойнятся всеNULLы из другой, будет хорошо и правильно?
playermet
27.08.2022 09:30+6Вот тут как раз интуиция всегда оказывается на стороне того, что NULL=NULL
Почему? Просто NULL следует трактовать как неизвестное значение. Равно ли одно неизвестное значение другому неизвестному значению? Неизвестно. Ну т.е. NULL.
michael_v89
27.08.2022 11:42Просто NULL следует трактовать как неизвестное значение.
Почему? Почему не как отсутствующее значение? Равно ли отсутствие значения отсутствию значения? Да.
playermet
27.08.2022 18:01+2Можно и как отсутствующее. Только нужно понимать, что это не значение поля равняется отсутствию, это значение поля отсутствует в БД, а следовательно для БД оно неизвестно. Ну а дальше все то же, что я написал ранее.
michael_v89
27.08.2022 18:24отсутствует в БД, а следовательно для БД оно неизвестно
Нет, отсутствие это именно отсутствие, а не присутствие неизвестного значения. У англичанина отчество именно отсутствует, а не имеется какое-то, но неизвестное БД. NULL показывает отсутствие значения, отсутствие значения равно отсутствию значения.
это не значение поля равняется отсутствию
Тут 2 значения NULL, а не одно. "Значение поля" в конкретной строке это NULL, это показывает отсутствие значения. Оно сравнивается например с константой из WHERE, которая тоже NULL и показывает, что нам нужны записи с отсутствием значения. То есть это не "значение поля равняется отсутствию", а "отсутствие равняется отсутствию".
playermet
27.08.2022 18:39+1У англичанина отчество именно отсутствует
SQL не вкладывают какую-то дополнительную семантику в NULL. Сказано - значение отсутствует в БД, и все. Причин по которым оно отсутствует могут быть десятки, это уже не проблема СУБД.
"отсутствие равняется отсутствию"
Стремлением так это трактовать вы сами себе создаете проблемы на ровном месте, при том что исправить их кроме как созданием машины времени и нападения на авторов спецификации SQL невозможно.
michael_v89
27.08.2022 18:48Сказано — значение отсутствует в БД, и все.
Ну так я про это и говорю, значение именно отсутствует в БД, а не присутствует, но неизвестно какое. То есть аргументы, основанные на семантике неизвестности, некорректны.
Стремлением так это трактовать вы сами себе создаете проблемы на ровном месте
В языках программирования null == null работает без всяких проблем.
playermet
27.08.2022 19:05+1а не присутствует, но неизвестно какое.
Это то же самое. Если значение отсутствует в БД, то оно по определению неизвестно для БД. NULL это не часть диапазона возможных значений поля, это специальное слово, указывающее что значения нет. А потому и сравнивать по значению ничего нельзя - его нет.
В языках программирования null == null работает без всяких проблем.
В языках программирования у null совсем другая семантика, которая исторически уходит корнями в нулевой указатель на тип. В то же время в языках программирования NaN == NaN дает false, потому что слева нет определенного значения, и справа нет определенного значения, а результат сравнения двух неопределенных значений неизвестен.
michael_v89
27.08.2022 20:29+2Это то же самое. Если значение отсутствует в БД, то оно по определению неизвестно для БД.
Вот я как раз и пытаюсь объяснить, что это не то же самое. "Значение неизвестно" подразумевает, что значение есть, но какое точно, неизвестно. А его с большой вероятностью вообще нет. Это является в общем-то наиболее часто моделируемым случаем, потому что моделирование наличия какого-то неизвестного значения для практических целей бесполезно, потому что мы его никак не можем обработать. Там где это все-таки нужно, наличие какого-то неизвестного значения моделируется полем boolean, а не null.
В языках программирования у null совсем другая семантика
Семантика там абсолютно такая же, отсутствие значения, и при загрузке данных из БД NULL напрямую мапится в null языка программирования.
исторически уходит корнями в нулевой указатель на тип
Это как раз нулевой указатель появился исключительно для того, чтобы обозначать отсутствие значения. Ни для чего другого он не нужен, так как правильного значения по историческим причинам там быть не может.
maslyaev Автор
27.08.2022 11:59А можно пример запроса, в котором действительно оказывается удобно, что ((null = null) is true) даёт false?
playermet
27.08.2022 18:21Любой запрос ипользующий JOIN, например. Причем не обязательно внешний, как писали выше, INNER JOIN тоже будет мусор выдавать.
maslyaev Автор
27.08.2022 19:05Не надо слов. Покажите мне код.
playermet
27.08.2022 19:15+3CREATE TABLE foo (x int); CREATE TABLE bar (x int); INSERT INTO foo (x) VALUES (1), (2), (NULL), (NULL), (NULL); INSERT INTO bar (x) VALUES (1), (2), (NULL), (NULL), (NULL); SELECT * FROM foo INNER JOIN bar ON foo.x = bar.x;Сейчас результатом будут ожидаемые две строки. А если поменять поведение NULL, то результатом будут эти же две строки, а после них девять строк c NULL. Попробуйте все NULL в этом коде заменить на 5, например, и посмотрите сами.
maslyaev Автор
27.08.2022 19:33-2Попробуйте все NULL в этом коде заменить на 5, например, и посмотрите сами.
А вот действительно, если колонки не nullable, и мы джойним по колонкам, в которых повторяются значения, кого будем ругать за то, что у нас вылезло 11 строк? Самих себя, или потребуем, чтобы операция сравнения "5 = 5" давала бы какую-нибудь хтонь, которая бы конвертировалась в булево как false?
playermet
27.08.2022 19:48+1LEFT JOIN постоянно делается по колонкам, в которых есть NULL. По сути, это главный его юзкейс. И в нем будет такой такой же эффект. Вы сейчас буквально просите сломать все запросы сложнее хелло-ворлдов. И все это только ради того, чтобы не писать "is" или "is not distinct from" вместо "=", которые нужны не сказать что повсеместно, и это не то чтобы большое отличие.
maslyaev Автор
27.08.2022 21:11-1Всё в порядке с хелловорлдами. Основной юзкейс лефт-джойна - хождение между табличками по внешним ключам. Если мы идём в прямом направлении, у нас nullable слева, но not null справа (потому что там это первичный ключ). А если идём в обратном направлении, первичный ключ у нас слева.
Если ради чего-то мы используем в джойне nullable и слева, и справа, то почти наверняка будем ожидать, что дублирующиеся NULLы будут вести себя так же, как дублирующиеся пятёрки. По крайней мере у меня с ходу не получается выдумать разумный пример для обратного.
m03r
27.08.2022 21:40+2Допустим, табличка с пользователями, у некоторых есть дата рождения, и табличка с заказами, в которых есть дата оплаты, но только в том случае, если он оплачен.
Интересно, кто что себе покупает на ДР.
Типа:
ON user.id = order.user_id AND user.bdate = DATE(order.paid_at).
В реальности я бы скорее смотрел заказы за неделю до дня рождения, т. е.BETWEEN bd - INTERVAL 7 DAY AND bd, и всё бы тоже прекрасно работало.
maslyaev Автор
28.08.2022 13:54-1Интересный пример. В этом случае следование стратегии "NULL=NULL" даст хоть и неправильный, но объяснимый результат. Ситуация, когда не отфильтровали пустые значения и получили гору мусора на выходе, не редкость и сейчас. Троичная логика от этого не защищает.
michael_v89
28.08.2022 16:33ON user.id = order.user_id AND user.bdate = DATE(order.paid_at)Ой нет, вот пожалуйста не надо продвигать эти подходы, когда в ON есть куча условий кроме соединения по ключам. Потом в таком запросе фиг разберешься, почему он иногда выдает неправильные результаты. Есть WHERE, все фильтры можно писать там.
Если нам нужны записи, у которых указана дата заказа и дата рождения пользователя, можно так и написать
WHERE user.bdate != NULL AND order.paid_at != NULL, а не использовать хитрые хаки с отсечением части результатов в джойне. Так сразу понятно, что мы хотим получить.
m03r
28.08.2022 22:35Да хоть во
WHERE, не нужны дополнительные проверки наNULL, потому что то же равенство даст тот жеNULLи строки не будут выбраны
michael_v89
28.08.2022 23:01Вот я как раз и говорю, что лучше написать явно, что нам нужны строки с заполненными датами, а не скрывать это в неявных побочных эффектах.
m03r
29.08.2022 00:27Насчёт правильного условия в
JOIN— согласен, сравнение дат лучше воWHERE.Тем не менее, для меня очевидно и ожидаемо, что
WHERE user.bdate = DATE(order.ctime)отбросит нулевые значения.
michael_v89
29.08.2022 09:02Это для вас очевидно, потому что вы его писали. А завтра в выводимых данных найдут баг, и другому программисту придется разбираться в вашем запросе, и он не знает, какие из всех возможных побочных эффектов вы использовали намеренно, а про какие не подумали, из-за чего и возник баг.
Даже в описании задачи вы пишете "у некоторых есть дата рождения", ну так почему бы в формальной записи тоже это не написать. Формальная запись это модель задачи. Чем ближе модель к требованиям, тем проще ее поддерживать, в том числе при изменениях требований.
shai_hulud
26.08.2022 13:54Вроде в стандарте скуэльэтот тип данных назывался unknown и сразу все было ппонятно почему нельзя сравнивать неизвестные значения.
sukharichev
26.08.2022 12:09-1Я, конечно, вообще ничего не понимаю в базах данных, но по-моему, в примере отчество Джона Смита не null а эмм... NationallyTraditionallyNotApplicable и его не надо делать NULL и на уровне приложения надо обрабатывать ситуацию NationallyTraditionallyNotApplicable, правда же? Просто это лишние телодвижения, неопределенность, потому что этих самых национальных традиций именования людей миллион вариаций, и никто не будет этим заморачиваться. Зато так у нас никакой троичной логики, а просто множество дополнительных двоичных веток с вариациями национальных имен. Правильно я понимаю?
pavel_raskin
26.08.2022 12:41+1Как мне кажется, замена задачи "ФИО/ФИ" на " NationallyTraditionallyNotApplicable" всего лишь передвинет вопрос NULL в другую часть предметной области, плюс неоправданно усложнит манипуляцию с данными.
Ну и нужно понимать, что автор выбрал пример "ФИО/ФИ" ради иллюстрации своих рассуждений про NULL, а не наоборот.
sukharichev
26.08.2022 13:50+2Да, спасибо, я просто как раз начинаю разбираться в базах и это чисто ради моего мысленного упражнения.
nin-jin
27.08.2022 21:27+1Ключевой момент тут в том, что NULL не участвует в релейшенах. По хорошему, это, конечно, должно быть не одно захардкоженное значение, а enum с перечислением значений не допускающих джойны. Что-то типа:
CREATE TABLE foo (x int | #0 | #broken | #unknown | #meaningless | #secret);Да, тут ещё и ноль не будет джойниться.
Tanner
26.08.2022 12:40Наверное, то, о чём вы говорите, называется Null Object. В Python сделано примерно так, там
None == None`даётTrue.maslyaev Автор
26.08.2022 14:13-1Ну вот да, в Питоне None==None, и никто горя не знает. А совет использовать там "is" вместо "==" при проверке на None исключительно потому, что некоторые всратые библиотеки могут переопределять оператор равенства для своих объектов, и делать это неочевидным образом. Например, могут упороться и применить троичную логику.
Tanner
26.08.2022 14:22Дело даже не в равенстве как таковом, а в том, что переменная со значением
None– это не пустая ссылка, а ссылка на объект, который ведёт себя как объект. Например, реализует методы. Сравнение, например.В случае SQL нулевой объект в качестве
NULLмог бы реализовывать поведение, подобающее для отсутствующего отношения. Или мы слишком многого хотим от SQL?rrrad
26.08.2022 18:35Требовать от SQL поведения аналогичного императивным языкам - очень странно. Например, в SQL неопределён порядок вычисления булевых выражений. Нужно объяснять, почему так сделано?
0xd34df00d
27.08.2022 18:28+1Деление на императивные и неимперативные языки переоценено, так что объясните, пожалуйста, если не сложно.
rrrad
28.08.2022 15:59По поводу порядка вычисления булевых выражений (точнее - предикатов). Очевидно, чтобы планировщик мог свободно решать, какие из предикатов применять в каком порядке, руководствуясь их селективностью, а не порядком следования.
0xd34df00d
28.08.2022 17:12+1Насколько я знаю, в реализациях SQL не все функции тотальны, поэтому от порядка вычисления успех (или неуспех) вычисления вполне себе может зависеть.
rrrad
28.08.2022 23:39По стандарту SQL, все части предиката будут вычислены. Поэтому обычно в SQL условия типа
(N - число) AND (to_number(N) > 5)приведут к ошибке. По опыту, даже оборачивание первого условие внутрь подзапроса не всегда поможет. Хотите жесткой определённости последовательности вычислений? Есть вариант с использованием оператораcase, но только, например, в PostgreSQL если аргумент - литерал или входящий параметр (который также может считаться литералом, т.к. первоначально учитывается при планировании), а функция детерминирована вообще (immutable) или в пределах транзакции (stable), то планировщик может попытаться её вычислить даже если она находится в неработающей ветке (просто потому что так работает последовательное упрощение дерева вычислений).
Akina
26.08.2022 12:53+5Троичная логика не применяется нигде кроме вычисления условий. Вне условий результат UNDEFINED интерпретируется как FALSE. Даже внутри самого SQL.
Увы Вам. Будучи полученным в CHECK constraint, NULL разрешает создание/изменение записи, т.е. интерпретируется как / выполняет тот же выбор, что и TRUE.
rrrad
26.08.2022 18:36+2в данном случае работает логика "мы не можем проверить эту строчку, поэтому пропускаем check". Нужно другое поведение? Никто не мешает использовать coalesce.
Ну или другая интерпретация: check constraint проверяет строку на соответствие условию (expr) = false. При выполнении - кидает ошибку.
Вообще, в SQL достаточно странностей (например - работа типов
varchar(n)), но едва ли к странностям можно отнести работу NULL-ов в булевых выражениях.
avshkol
26.08.2022 13:17+10Насколько мне известно, не существует ни одного рационального аргумента в пользу того, что результат сравнения "NULL = NULL" должен быть чем-то кроме TRUE.
Нет, NULL - это неопределенность, например, в булевом поле это означает: "пока нет данных, 0 это или 1". Поэтому два NULL могут оказаться разными, а могут и одинаковыми (поэтому - неопределенность, NULL при сравнении NULL == NULL).
Далее NULL is NULL = True, то есть на вопрос "это неопределенность?" отвечаем, видя NULL - да, это
проклятаянеопределенность!А or B, если А=1, будет 1, даже если B неопределено (каким бы оно не было).
А or B, если А=0, будет NULL, даже если B неопределено (то есть на месте В возможен и 1 и 0).
А and B, если А=0, будет 0, при любом, даже неопределенном В
А and B, если А=1, будет NULL, поскольку возможны оба варианта.
По-моему, все понятно и логично. В примере с отчеством нужно понимать, что NULL не равно текстовой строке "", хотя в печати обычно так и выводится. Вместо NULL может быть и "" (нет отчества - то есть фактически - "нет ни одного символа отчества"), и "мы пока не знаем отчества, запишем, когда узнаем".
maslyaev Автор
26.08.2022 21:14+2NULL - это неопределенность
Далеко не всегда. Ещё Кодд писал про то, что нам нужно два отдельных NULLа - тот, который неизвестность и тот, который "атрибут неприменим". Вес услуги или нематериального товара, расход топлива в литрах для электромобиля, дата смерти живого человека. Но идея Кодда народу не зашла, и даже примерно понятно почему.
Вообще, обстоятельств, почему клеточка в таблице осталась пустой, сильно больше, чем два. Кодировать их отдельными null-like значениями (null для одного, undefined для другого, void для пятого, empty для десятого) - только всех запутать. Одного NULLа достаточно, а если действительно будет нужно уточнение, мы точно придумаем, как его записать в базу.
avshkol
26.08.2022 21:43+1NULL-неприменимость можно убрать, изменив схему данных. Если мы для товара хотим ввести признак «шаг резьбы» и «под крестовую отвертку», нам лучше создать отдельную таблицу/таблицы, где будет id товара и этот признак.
maslyaev Автор
26.08.2022 22:24+2Да, как в примере с выносом фамилии, имени и отчества в отдельные таблицы, где атрибуты будут not null, но в результатах запроса всё равно будем иметь тот самый null.
Sergeant101
27.08.2022 09:18А если вместо null иметь в таблице запись "-" (прочерк), тогда во первых значение отчества будет точно определено - отчества нет, его не забыли написать, просто нет.
mSnus
27.08.2022 11:30Прочерк - это определенное состояние, т.е. вы точно знаете, что отчества нет. А NULL - неопределенное, когда вы ещё не спросили Джона, есть ли у него middle name
maslyaev Автор
27.08.2022 12:22+3Вписывание "специальных" фейковых значений - очень плохая практика.
0xd34df00d
27.08.2022 18:31+2А если иметь вторую колонку, где 0 означает валидное значение в первой, 1 — его отсутствие, 2 — неизвестность… тьфу ты, кажется, я переизобретаю кодирование ADT в памяти после компиляции.
dmitryvolochaev
27.08.2022 01:47Ну вот почему Питон не дает наследоваться от NoneType? None ведет себя как объект, но NoneType не ведет себя как класс
mSnus
27.08.2022 11:24пока нет данных, 0 это или 1". Поэтому два NULL могут оказаться разными, а могут и одинаковыми
Нет же, как только появляются данные, они перестают быть NULL. А пока "неизвестно" - нам одинаково неизвестно про любую переменную, что там в ней.
nin-jin
27.08.2022 21:37Пустая коробка и коробка с неизвестным рычащим зверем внутри - две большие разницы.
s_f1
26.08.2022 13:19+1Насколько мне известно, не существует ни одного рационального аргумента в пользу того, что результат сравнения «NULL = NULL» должен быть чем-то кроме TRUE.
Что есть рациональный аргумент? Вот у нас есть
Можно ли сказать, что все дома без дверей имеют одинаковый цвет двери? Имхо тут найдётся аргументация для противоположных ответов, и, соответственно, для противоположных взглядов на вопрос равенства NULL друг другу.HAS_DOOR boolean DOOR_COLOUR colourmaslyaev Автор
26.08.2022 14:53-1Если придерживаться догмы, то да. Но когда дело дойдёт до дела, равенство "никаких" цветов между собой будет наименее удивляющим поведением системы.
Deosis
26.08.2022 15:20+5Вообще-то про цвет отсутствующих дверей можно говорить что угодно.
Что все отсутствующие двери одного цвета, либо у каждой отсутствующей двери уникальный цвет.
Эти утверждения непроверяемые.
nin-jin
27.08.2022 21:40-2Вас обманули. Про цвет несуществующих дверей ничего говорить нельзя, кроме того, что их не существует. Любые другие утверждения являются абсурдом.
0xd34df00d
27.08.2022 23:33У вас опять ваша четверичная логика пошла? Не, я не против продолжить разговор — в конце концов, вы мне так и не ответили за аксиому исключённого десятого.
michael_v89
26.08.2022 16:04+1Можно ли сказать, что все дома без дверей имеют одинаковый цвет двери?
А они не имеют одинаковый цвет ни в каком смысле. Типы "сolour null" и "colour not null" отличаются тем, что для null есть отдельный скрытый флаг. Вот он и показывает признак "запись имеет свойство цвет". Когда этот флаг true, это означает, что запись информации о цвете не имеет, и то значение, которое записано в соседних байтах где по схеме хранится фактический colour, не имеет смысла, и учитывать его не надо.
NULL показывает отсутствие значения в информационной системе. И в этом смысле да, все дома без дверей имеют одинаковый признак, что цвета двери у них нет, потому и равенство между ними выглядит логично.
nin-jin
27.08.2022 21:43все дома без дверей имеют одинаковый признак, что цвета двери у них нет, потому и равенство между ними по этому признаку выглядит бредово
Пофиксил, не благодарите.
michael_v89
27.08.2022 22:00Утверждение "у этого дома нет цвета двери и у того дома нет цвета двери, в этом они одинаковы" выглядит бредово, а утверждение "у этого дома нет цвета двери и у того дома нет цвета двери, в этом они различаются" выглядит не бредово? Не, это чушь какая-то.
nin-jin
27.08.2022 22:13Обратите внимание, что в обоих ваших утверждениях нет сравнения по цвету ("у этих домов одинаковые цвета дверей" или "left.doorcolor === right.doorcolor"), но есть сравнение по наличию признака ("left has-attribute doorcolor === right has-attribute doorcolor" или "left.doorcolor is NULL and right.doorcolor is NULL").
michael_v89
27.08.2022 22:27Обратите внимание, что в обоих ваших утверждениях нет сравнения по цвету
Естественно, я про это и сказал. "для null есть отдельный скрытый флаг. Вот он и показывает признак "запись имеет свойство цвет"".
Dekmabot
26.08.2022 14:20+2А зачем вы сравниваете null с boolean-значениями?
Null идеально подходит для обозначения отсутствия внешней связи. Используйте его по назначению, и тьма рассеется.maslyaev Автор
26.08.2022 15:19Домен boolean-значений такой же, как и все другие, в том числе и "сущностные" домены. Не хуже и не лучше. И boolean-колонка это relation между доменом первичного ключа и boolean-домена. В случае отсутствия relation будет честный null.
(впрочем, это не отменяет того, что за nullable boolean колонки нужно бить по рукам)
MonkAlex
26.08.2022 17:36+6(впрочем, это не отменяет того, что за nullable boolean колонки нужно бить по рукам)
Почему? Жили у нас клиенты в БД.
В каком то году, государство обязало нас уведомлять их о каких-то специфических вещах, из разряда "примите новые условия обслуживания".
И наивная реализация будет как раз nullable boolean - клиент либо ещё не заходил и его решение - null, либо зашел и решил будет он пользоваться дальше (true), либо нет (false).
Быстрая, наивная и вполне удобная реализация.
Да, можно завести отдельную табличку, создавать в ней записи, наличие записи - факт действия пользователя, все дела. Но обязательно ли это? Очевидно, нет.
Delta-f
26.08.2022 18:20+1Поддерживаю: банальная задача - Таблица А со справочником товаров и таблица В с весогабаритными характеристиками товаров, связаны с собой по артикулу товара. Поиск товаров, которые есть в таблице А, но не имеют описания ВГХ в таблице В, происходит элементарным лефт джойном по условию "артикул товара в таблице В как раз NULL".
AnthonyMikh
26.08.2022 19:20+1Ну так это концептуально неправильная реализация, вообще говоря. NULL слишком перегружен смыслами. Означает ли NULL, что мы не знаем, что решил клиент? Нет, тут смысл вполне конкретный: клиент ещё не сделал решения. Собственно, все три варианта вполне себе покрываются отдельным типом:
enum ClientDecision { NotDecided, Agreed, Disagreed, }И для значения этого типа факт равенства одного
NotDecidedдругому — абсолютно логичная вещь.Но, разумеется, пользовательские сумм-типы — это слишком сложная вещь для SQL. ????
MonkAlex
26.08.2022 19:28+1Означает ли NULL, что мы не знаем, что решил клиент?
И это решение программиста\бизнеса. В данном случае - да, клиент ещё не сделал решения.
Делать отдельный тип на то, что прекрасно закрывается стандартным типом - оверинжениринг.
ПС: навскидку кстати не уверен, даже если делать тип и прочее - по моему скулю дешевле будет создать колонку со значениями в null, чем колонку со значениями в
NotDecided, что на большой БД может быть существенно.AnthonyMikh
26.08.2022 23:12Делать отдельный тип на то, что прекрасно закрывается стандартным типом — оверинжениринг.
прекрасноВовсе нет. Как вы зафиксируете семантику NULL для этой колонки? БД об этом ничего не знает. И, как я уже сказал, в данном случае неравенство NULL самому себе бессмысленно.
MonkAlex
27.08.2022 04:43Я, честно, не понял вопроса. Что такое семантика null для колонки?
И зачем нужно иметь какой то смысл в "неравенство NULL самому себе" тоже не понял.
mSnus
27.08.2022 11:37+1А также: NotYetQuestioned, StillThinking, DataLostOnTheWay, PodpisNerazborchiva. Но если бизнесу важно только знать - получен ли определенный ответ, и если да, то какой - тогда NULL, TRUE, FALSE.
vadimr
28.08.2022 16:27Вообще тут описаны просто три равноправные состояния документа, и кошерно будет поле CHAR (1) со значениями вроде Y/N/U.
MonkAlex
28.08.2022 16:41Есть ли у вашего предложения плюсы перед nullable boolean? Потому что я вижу только (из небольших минусов) необходимость работы с константами в процедурах либо на бэке (этими самыми YNU) и необходимость явно заполнять дефолтное значение, что на больших объемах тоже не бесплатно.
vadimr
28.08.2022 16:51Тут нет дефолтного значения, это фактический статус документа, который нужно как-то определить (узнать). Эти три значения сами по себе не исключают возможности использования и NULL тоже, как признака неизвестности.
MonkAlex
28.08.2022 17:01Так я неспроста описал бизнес кейс в первом посте.
Какая бизнесовая разница у вас между null и U?
И ещё раз, вопрос был - в чём плюс вашего предложения с char.
vadimr
28.08.2022 17:26U - о документе известно, что он не подписывался. NULL - нет информации о статусе документа (например, в нашу базу интегрируются данные нового Урюпинского дочернего предприятия, которые до того велись в Урюпинске нерегулярно и на бересте).
Плюс в том, что NULL не предназначен для кодирования содержательного значения атрибута. Соответственно, дальше не возникнут такие проблемы, как описаны автором.
MonkAlex
28.08.2022 17:32Так с точки зрения бизнеса оно равноценно же.
В случае пока у клиента null или U - мы ему предлагаем ознакомиться с новыми условиями и принять или отказаться.
Т.е. с точки зрения поведения нашей системы - нет разницы, нет смысла делать так.
vadimr
28.08.2022 17:53Если он уже подписал или отказался, надо выяснить статус, а не повторно предоставлять. Иначе получится, что он может два раза сделать разные выборы, что приведёт к юридической коллизии.
Сценарии тяжело обсуждать вне конкретики. Но и при проектировании БД редко мы знаем всю конкретику досконально. Я бы не взял ответственность объединять эти случаи на уровне хранения данных.
AnthonyMikh
26.08.2022 19:26+1Идея для стартапа: форкнуть Постгрес и сделать Postgres BFE (Bullshit-free Edition). И посмотреть, как народу зайдёт такая еретическая идея.
Рвение ценю, но Postgres — та ещё хтонь, её невозможно развивать в одиночку.
А насчёт темы: я вот недавно читал папир с формализацией SQL с NULL (формализации SQL были и раньше, но они не брали NULL в расчёт), и там, в частности, было показано, что троичная логика в плане выразительности не имеет никаких преимуществ перед двоичной: запрос в двоичной логике можно перевести в запрос на троичной логике и наоборот без потери смысла.
Delta-f
27.08.2022 15:26+1Вот чего автор привязался к троичной логике в SQL-92, она работает нормально, и вполне себе используется теми, кому она нужна )
Предлагаю- следующую тему для систем на COBOLе пусть обсудит. Вот это гораздо интереснее (нет).maslyaev Автор
27.08.2022 17:16+1Очень хотелось бы посмотреть на пример запрса, в котором троичная логика оказывается полезна. Вот пример, где она мешает:
select * from persons where mname = :MName or mname is null and :MName is nullгде
:MNameэто подставляемый из переменной параметр.Мерзкий костыль, разве нет? Конструкции, подобные этому "or ..." приходится городить постоянно. Особенно оно доставляет в сложных запросах с множеством джойнов и нетривиальными условиями. Обход этой дебильной "фичи" уже давно доведён до автоматизма, но всё равно время от времени где-нибудь да словишь западло. Это как головная боль, к которой невозможно привыкнуть.
Всё ещё надеюсь увидеть пример запроса, где троичная логика полезна, но пока что мне что-то подсказывает, что жду зря.
vadimr
27.08.2022 18:29У вас неправильно спроектирована база данных, в таком случае целесообразно использовать пустую строку, а не null.
maslyaev Автор
27.08.2022 19:03-1Так лучше?
select * from persons left outer join person_mnames on person_mnames.person_id = person.id where person_mnames.mname = :MName or person_mnames.mname is null and :MName is null
miksoft
27.08.2022 21:58+1Напомнило ситуацию, когда для поля person_mnames.mname надо было различать NULL, хранящийся в приджойненной записи, и NULL, возникший из-за неприджойненной записи.
miksoft
27.08.2022 21:59+2Сумятицы добавляет тот факт, что в некоторых СУБД пустая строка тождественна NULL, а в некоторых это разные вещи.
playermet
27.08.2022 18:47Ваш пример можно легко переписать в удобном виде:
select * from persons where mname is :MName;maslyaev Автор
27.08.2022 19:13Это не везде работает. В Посгресе, например, если параметр не null, не true и не false, отвалится с ошибкой.
playermet
27.08.2022 19:21+1Вот так в постгресе точно должно работать:
select * from persons where mname is not distinct from :MName;maslyaev Автор
27.08.2022 22:44Прикольно. В Постгресе "1 is 1" не работает, а "1 is not distinct from 1" работает. А в SQLite наоборот.
В любом случае 99.9% из миллионов тех, кто пишет на SQL, голосуют ногами за "=". Потому что всякое такое "is not distinct from" это долбаная экзотика, даже не имеющая единообразной поддержки со стороны основных игроков.
Ну ладно, с равенством (пардон, с нотдистинктностью) разобрались. Что будем делать с другими парами операторов сравнения, от которых нам нужна работа по закону исключённого третьего, а мы получаем в лицо закон исключённого четвёртого? Я имею в виду меньше/не меньше и больше/не больше?
m03r
27.08.2022 23:53+1А как по-вашему
1 > NULL— это истина или ложь? А1 < NULL?Если я хочу сравнить с числом средний чек пользователя, а у него покупок нет, то
AVG(amount)будетNULL, даже если вообще вся база у меня спроектирована без нулевых колонок.Как ещё предложите обрабатывать такие случаи?
maslyaev Автор
28.08.2022 13:02С точки зрения Мировой Истины, Сияющей, Вечной и Неизменной, вопрос "что больше, 1 или неизвестно сколько", смысла не имеет. Но когда мы сортируем nullable-колонку по возрастанию, "nulls first" является наименее удивительным поведением. Соответственно, если по убыванию, то "nulls last". Именно поэтому "NULL < 1" должно давать true.
Можно, конечно, сделать наоборот. С позиции Сияющей Истины пофиг. Но с точки зрения least astonishment совсем не пофиг.
nin-jin
28.08.2022 15:48А если нужен nulls last? Тут больше проблема в слабой типизации. По хорошему программист тут обязан сделать явное приведение обоих операндов к единому типу, а не полагаться, что свои иррациональные ожидания.
maslyaev Автор
28.08.2022 16:16Тогда, как и раньше, пишем
order by ... nulls last.
Фишка в том, чтобы применяемое по умолчанию упорядочивание стопроцентно соответствовало логике операций сравнения на больше-меньше. Иначе западло и источник багов.maslyaev Автор
28.08.2022 17:08-1Понимаете ли, нам совершенно плевать на глобальную экзистенциальную истинность того, что вот эта пара координат больше вон той другой. Нам всего лишь нужно отношение упорядочивания, которое работает понятным, единообразным и неабсурдным образом.
Вот, например, кому-нибудь плохо от того, что"Больше" < "Меньше"даёт true?
nin-jin
28.08.2022 01:51В реальной жизни "закон исключённого третьего" работает примерно никогда, так как почти всегда есть та или иная форма неопределённости (не задано, бессмысленно, ещё не загружено, удалено, да и банальное всё не так однозначно). Бинарная логика - это математическая абстракция, работающая только в своём мани-мирке.
maslyaev Автор
28.08.2022 12:31У нас и 2+2 это математическая абстракция, живущая только в своём тавтологически самом на себя замкнутом мини-мирке (см. "Логико-философский трактат" Витгенштейна), но это нам не мешает использовать арифметику как простой, понятный, надёжный и полезный инструмент.
nin-jin
28.08.2022 15:51Только 2+2 не противоречит реальности.
maslyaev Автор
28.08.2022 19:49-3Пример: скидка 2% плюс ещё одна скидка 2% даёт общую скидку 3.96%
При этом наценка 2% плюс ещё наценка 2% будет 4.04% наценки. Получается прямо как в том анекдоте - "а мы покупаем или продаём?"
2+2=4 только когда применяем правило к аддитивным величинам. А вопрос о том, являются ли величины аддитивными, обычно вне математики.nin-jin
28.08.2022 22:30И где вы в реальности проценты нашли?
±2%- это короткая запись для множителя( 100±2 )/100
maslyaev Автор
28.08.2022 23:38-3Ещё пример. Вчера на улице было 2 десятка градусов, и сегодня 2 десятка. Итого в выходные у нас было 40°С, ага?
michael_v89
27.08.2022 18:49С темой статьи согласен, но вообще обычно это делается на уровне приложения в квери билдере, если параметр null, он ставит "is", если не null, ставит "=".
maslyaev Автор
27.08.2022 19:19Ага, обкостылить эту дурацкую фичу специальным ифом в коде бэкенда. Хрен редьки не слаще.
michael_v89
27.08.2022 20:44А какая вам разница, если вам не надо вручную это писать, ни код бэкенда, ни сам SQL? Поставили через пакетный менеджер и пишете как удобно. Вам важнее понятность кода вашего приложения или количество ифов где-то внутри сторонней библиотеки?
maslyaev Автор
27.08.2022 23:25Речь о всяких вещах типа Dbeaver?
michael_v89
28.08.2022 01:33Нет, просто код фреймворка.
maslyaev Автор
28.08.2022 16:22Некоторые ORMки сами умеют это обкостылевать, некоторые (например, SQLAlchemy) нет. И я не уверен на сто процентов, действительно ли я хочу, чтобы ORM за меня додумывал этот момент.
0x1000000
Есть ещё один очень популярный язык программирование в котором всё время приходится учитывать троичную логику (и даже в каком-то смысле четверичную) — это JavaScript со своими undefined и null
Amareis
И не дай боже null через typeof проверять...
slonopotamus
В вашем примере всё становится логично, если считать что объекты в JS - это всего лишь
Map<String, Object>. Разница между пустымMapиMap, в котором есть запись"b" -> undefinedвполне очевидна. Это впрочем никак не оправдывает кучу другой дичи в JS.3735928559
Ну тогда уже стоит вспомнить и TypeScript, где проблема nullability решается на уровне типов. Или Kotlin.