Перенос семантики NULL и пустых строк между Oracle, SQL Server, MySQL и SQLite
Обеспечьте переносимость обработки NULL в Oracle, SQL Server, MySQL и SQLite, сосредоточившись на четырех аспектах: тестировании NULL, результатах сравнений, приведении типов в булевом контексте и определении пустой строки. Учтите особенности Oracle с пустыми строками, настройки ANSI_NULLS в SQL Server и расширения SQLite.
В этом материале
Основная мысль
Для обеспечения переносимости обработки NULL между Oracle, SQL Server, MySQL и SQLite необходимо решить четыре ключевых вопроса: как тестируется NULL, что возвращает сравнение с NULL, приводятся ли NULL к значениям в булевом контексте и что считается пустой строкой. Наибольший риск представляет Oracle, который в настоящее время трактует символьную строку нулевой длины как NULL, однако документация Oracle предупреждает, что это может измениться в будущих версиях, и рекомендует не приравнивать пустые строки к NULL. Для тестирования следует использовать исключительно IS NULL и IS NOT NULL, так как любое другое условие с участием NULL приводит к результату UNKNOWN. В блоках WHERE UNKNOWN ведет себя почти как FALSE, но NOT UNKNOWN остается UNKNOWN. В SQL Server существует компромисс конфигурации: при SET ANSI_NULLS ON один или два операнда NULL дают UNKNOWN, тогда как при ANSI_NULLS OFF операторы равенства и неравенства трактуют NULL как известное значение, равное другим NULL, возвращая только TRUE или FALSE. MySQL сохраняет возврат NULL при сравнениях с NULL, но в булевом контексте трактует 0 или NULL как false, а все остальное как true; при этом в GROUP BY два NULL считаются равными, а при сортировке NULL помещаются в начало при ASC и в конец при DESC. SQLite предлагает компактные операторы IS и IS NOT, которые всегда возвращают 1 или 0 и никогда не возвращают NULL, но это задокументировано как расширение SQLite; стандарт SQL требует использования IS NOT DISTINCT FROM и IS DISTINCT FROM. Выбор стоит между локальной лаконичностью и общей переносимостью: использование IS NULL / IS NOT NULL в сочетании с явной обработкой через COALESCE является единственным способом, который предсказуемо работает во всех СУБД. Рекомендуется составить матрицу этих четырех пунктов для каждого движка, а также добавить PostgreSQL, проверив его поведение по официальной документации.
Контекст и рекомендации
Перед переносом запросов или общих SQL-модулей между Oracle, SQL Server, MySQL и SQLite составьте явный контрольный список переносимости для NULL: (1) способ тестирования NULL, (2) результат сравнения с NULL, (3) приведение NULL в булевом контексте и (4) определение пустой строки. Самым рискованным элементом является Oracle, который в данный момент считает строку нулевой длины равной NULL, но при этом прямо рекомендует не полагаться на это сходство. Зафиксируйте эти четыре пункта для каждого движка в виде матрицы, не полагаясь на то, что стандартное поведение SQL будет идентичным везде. Добавьте PostgreSQL в эту матрицу, предварительно проверив его документацию.
Основное правило переносимости: используйте для проверки NULL только IS NULL и IS NOT NULL. Oracle заявляет, что это единственные допустимые сравнения для NULL; любое другое условие с NULL оценивается как UNKNOWN. При этом UNKNOWN в блоке WHERE ведет себя почти как FALSE, но NOT UNKNOWN остается UNKNOWN, а не становится TRUE. В SQL Server есть зависимость от конфигурации: при SET ANSI_NULLS ON один или два операнда NULL дают UNKNOWN, а при ANSI_NULLS OFF операторы = и <> трактуют NULL как известное значение, равное другим NULL, возвращая только TRUE или FALSE, что меняет результаты фильтрации в зависимости от настроек сессии. MySQL возвращает NULL при сравнениях с NULL, но в булевом контексте считает 0 или NULL как false, а всё остальное как true. Также MySQL считает два NULL равными в GROUP BY и помещает NULL в начало при ASC и в конец при DESC. SQLite предоставляет компактные операторы IS и IS NOT, которые всегда возвращают 1 или 0, но это является расширением SQLite; стандарт SQL требует IS NOT DISTINCT FROM и IS DISTINCT FROM.
Существует компромисс между единообразием и переносимостью: специфичные для движка формы лаконичны и читаемы локально, в то время как IS NULL / IS NOT NULL вместе с явной обработкой в стиле COALESCE - это единственный синтаксис, который ведет себя предсказуемо везде. Не предполагайте, что запрос, написанный для одного движка, будет работать без изменений на другом, и не считайте пустую строку и NULL взаимозаменяемыми. Матрица должна фиксировать четыре указанных пункта для каждого движка, а также настройки сессии, влияющие на результат, и служить чек-листом для код-ревью.
Используйте выдержки из документации как доказательства для матрицы: Oracle рекомендует использовать только IS NULL и IS NOT NULL, утверждая, что любые другие условия с NULL дают UNKNOWN, и что Oracle считает строку нулевой длины как NULL, но советует не полагаться на это. Документация SQL Server указывает, что при SET ANSI_NULLS ON оператор с одним или двумя NULL возвращает UNKNOWN, а при OFF операторы = и <> трактуют NULL как эквивалентное любому другому NULL. MySQL показывает, что 1 = NULL, 1 <> NULL, 1 < NULL и 1 > NULL возвращают NULL, а '' IS NULL возвращает 0. SQLite сообщает, что выражения IS или IS NOT не могут вернуть NULL, но эти формы являются расширением, в то время как стандарт требует IS NOT DISTINCT FROM.
-- portable across the engines covered here
SELECT
'' IS NULL AS empty_is_null,
'' IS NOT NULL AS empty_is_not_null,
1 = NULL AS eq_null,
1 <> NULL AS ne_null;
-- run the same probe twice on SQL Server: once with ANSI_NULLS ON, once OFF
SET ANSI_NULLS ON;
SELECT 1 = NULL;
SET ANSI_NULLS OFF;
SELECT 1 = NULL;
-- row filtering: only this form is portable
SELECT * FROM t WHERE col IS NOT NULL;Обоснование и компромиссы
Движки согласны в базовых правилах, но расходятся в деталях, которые нарушают переносимость. Oracle утверждает, что IS NULL и IS NOT NULL - единственные способы сравнения с NULL, а любое другое условие дает UNKNOWN. В SQL Server результат зависит от настройки ANSI_NULLS: при значении ON получается UNKNOWN, при OFF - TRUE или FALSE. MySQL возвращает NULL при сравнениях, но в булевом контексте приводит 0 и NULL к false. SQLite предлагает расширения IS и IS NOT, которые всегда возвращают 1 или 0, в то время как стандарт требует IS NOT DISTINCT FROM.
Выбор между локальной оптимизацией и переносимостью критичен. Специфичные формы, такие как IS в SQLite, лаконичны, но не будут работать в СУБД, поддерживающих только IS NOT DISTINCT FROM. Поведение Oracle с пустыми строками чувствительно к версии. Настройка ANSI_NULLS в SQL Server может незаметно изменить смысл операторов = и <>, из-за чего один и тот же запрос может вернуть разные строки в разных сессиях. Булевое приведение в MySQL (0 и NULL в false) работает в контекстах условий, но не в сравнениях = и <>, которые по-прежнему возвращают NULL, поэтому запрос, работающий в WHERE, может дать сбой в CASE или аргументе функции.
Следовательно, матрица должна содержать не только результаты тестов, но и настройки сессий. Для SQL Server необходимо проверять фактическое значение ANSI_NULLS перед запуском тестов. Для Oracle следует фиксировать версию и проводить повторное тестирование после обновлений, так как правило пустой строки может измениться. Для MySQL важно различать булевы контексты и операторы сравнения, а также помнить о порядке NULL в ORDER BY. Для SQLite следует помнить, что IS и IS NOT - это расширения. Также необходимо зафиксировать поддержку IS NOT DISTINCT FROM, так как это стандартный способ проверки равенства NULL в SQL.
Рекомендуется писать запросы, используя IS NULL и IS NOT NULL для тестирования, и применять явный COALESCE для значений, которые могут быть NULL. Это единственный способ обеспечить предсказуемость, так как он не зависит от обработки пустых строк, настроек сессии или булевого контекста. Если нужно отличить пустую строку от NULL, используйте разные колонки или специальные значения-маркеры. Матрица должна использоваться как инструмент проверки кода и обновляться при выходе новых версий СУБД.
Конкретная иллюстрация
Запустите один и тот же простой тест на каждом целевом движке и запишите буквальный вывод: SELECT '' IS NULL, '' IS NOT NULL, 1 = NULL, 1 <> NULL, 0 = NULL; а также проверку булевого контекста, например SELECT NOT NULL, там, где это допустимо. В Oracle пустая строка будет вести себя как NULL; в MySQL '' IS NULL вернет 0, а '' IS NOT NULL вернет 1, при этом 1 = NULL вернет NULL; SQLite вернет 1 или 0 для IS и IS NOT. Затем используйте фильтр строк: WHERE value <> NULL и WHERE value IS NOT NULL, чтобы увидеть, какие строки возвращает каждый движок. Используйте таблицу-фикстуру, чтобы различия между сохраненными пустыми строками и NULL были явными.
Таблица-фикстура должна содержать один NULL, одну пустую строку, один ноль и одно обычное значение. В Oracle пустая строка хранится как NULL, поэтому '' IS NULL вернет 1, а 1 = NULL вернет NULL. В MySQL '' IS NULL вернет 0, а '' IS NOT NULL вернет 1, при этом все сравнения 1 = NULL, 1 <> NULL, 1 < NULL и 1 > NULL вернут NULL. В SQLite '' IS NULL вернет 0, а IS и IS NOT всегда вернут 1 или 0. В SQL Server тест нужно запустить дважды: с SET ANSI_NULLS ON и с SET ANSI_NULLS OFF, так как операторы = и <> возвращают UNKNOWN в первом случае и TRUE/FALSE во втором.
Проверка фильтрации строк покажет реальные результаты. WHERE value <> NULL не вернет строк в Oracle, MySQL и SQLite, так как сравнение возвращает NULL, а UNKNOWN в WHERE работает почти как FALSE. WHERE value IS NOT NULL вернет все строки, которые не являются NULL, и это единственная переносимая форма. В SQL Server WHERE value <> NULL может вернуть строки, если ANSI_NULLS выключен. Проверка булевого контекста покажет, что NOT NULL оценивается как UNKNOWN в системах с трехзначной логикой, и что WHERE с UNKNOWN не возвращает строк, в то время как контекст, приводящий UNKNOWN к FALSE, может вести себя иначе.
Запишите буквальный вывод каждого теста в матрицу, включая настройки сессии для SQL Server. Отметьте поддержку IS NOT DISTINCT FROM и статус правила пустой строки в Oracle. Результаты тестов служат доказательством для чек-листа переносимости и помогают выбрать правильный синтаксис для общих модулей. Переносимый вариант - это IS NULL / IS NOT NULL и явная обработка через COALESCE, так как этот подход независим от особенностей движка, настроек сессии или булевого контекста.
Ограничения применимости
Данная рекомендация охватывает только сравнение NULL и семантику пустых строк; она не касается агрегатного подсчета NULL, поведения индексов или производительности. Источники не включают документацию PostgreSQL, поэтому утверждения о поведении NULL в PostgreSQL здесь отсутствуют и должны быть проверены по официальным документам перед расширением матрицы. Правило Oracle о строках нулевой длины задокументировано как текущее поведение, которое может измениться, поэтому рассматривайте его как версию-зависимое допущение. Результаты SQL Server зависят от ANSI_NULLS, поэтому проверяйте фактическую конфигурацию сессии. Булевое приведение в MySQL касается контекстов условий, а не операторов = и <>, которые по-прежнему возвращают NULL. Компактные формы IS и IS NOT в SQLite являются расширением, поэтому такие запросы не будут работать в СУБД, поддерживающих только IS NOT DISTINCT FROM.
Эти ограничения важны, так как матрица является чек-листом, а не гарантией. Изменение правила пустой строки в Oracle может привести к поломке запросов после обновления. Настройка ANSI_NULLS в SQL Server может изменить набор возвращаемых строк. Приведение типов в MySQL может привести к тому, что запрос, работающий в WHERE, не сработает в CASE или аргументе функции. Компактный синтаксис SQLite не является стандартным. Матрица должна использоваться для ревью кода и обновляться при изменении поведения СУБД.
В матрице также следует указать поддержку IS NOT DISTINCT FROM, так как это стандартный способ проверки равенства NULL. Если поддержка отсутствует, используйте IS NULL / IS NOT NULL и COALESCE. Не пытайтесь вывести поведение одного движка на другой и не считайте пустую строку и NULL взаимозаменяемыми. Результаты тестов - это доказательная база для выбора синтаксиса в общих SQL-модулях. Переносимый синтаксис (IS NULL / IS NOT NULL + COALESCE) не зависит от обработки пустых строк, настроек сессии или булевого контекста.
Поскольку документация PostgreSQL не была предоставлена, любые данные по этой СУБД должны быть верифицированы отдельно. Матрица должна служить инструментом проверки кода и обновляться при выходе новых версий. Результаты проб являются основанием для выбора синтаксиса в общих модулях. Единственный предсказуемый способ - использование IS NULL и IS NOT NULL вместе с явной обработкой значений через COALESCE.
Условия применения
- Трактует ли целевой движок '' IS NULL как 0 и '' IS NOT NULL как 1, или он приравнивает пустую строку к NULL?
- Возвращает ли сравнение типа 1 = NULL значение NULL, UNKNOWN или TRUE/FALSE в зависимости от настроек сессии?
- Приводит ли булевый контекст (например, WHERE NOT NULL или IF(NOT NULL)) NULL к FALSE или сохраняет UNKNOWN?
- Размещает ли ORDER BY значения NULL в начале при ASC и в конце при DESC, или следует другому правилу?
- Считает ли GROUP BY два NULL равными и поддерживает ли движок IS NOT DISTINCT FROM?
- Задокументировано ли правило приравнивания пустой строки к NULL как текущее поведение, которое может измениться в будущем?
- Задокументирован ли компактный синтаксис IS и IS NOT как расширение, а не как стандарт SQL?
- Влияют ли на результаты настройки сессии, такие как ANSI_NULLS, и можете ли вы прочитать эту настройку перед тестом?
- Различает ли тестовая установка сохраненный NULL и сохраненную пустую строку, или они смешиваются?
- Была ли матрица проверена по документации PostgreSQL перед расширением на этот движок?
Границы применения
Данная рекомендация охватывает только сравнение NULL и семантику пустых строк; она не касается агрегатного подсчета NULL, поведения индексов или производительности. Источники не включают документацию PostgreSQL, поэтому утверждения о поведении NULL в PostgreSQL здесь отсутствуют и должны быть проверены по официальным документам. Правило Oracle о строках нулевой длины может измениться в будущих версиях. Результаты SQL Server зависят от настройки ANSI_NULLS. Булевое приведение в MySQL касается только контекстов условий, а не операторов сравнения. Компактные формы IS и IS NOT в SQLite являются расширением и не будут работать в СУБД, поддерживающих только IS NOT DISTINCT FROM.