При работе с запросами в системе 1С:Предприятие одной из самых распространенных ловушек для разработчиков является поведение значений NULL. Часто возникает ситуация: мы выполняем левое соединение, ожидая увидеть все записи из левой таблицы, но после наложения условий в секции ГДЕ часть данных таинственным образом исчезает. Особенно это заметно, когда мы пытаемся сравнить «пустое» значение (результат соединения) с каким-либо числом или строкой.
В этой статье мы подробно разберем, почему классические операторы сравнения пасуют перед NULL, как работает трехзначная логика SQL и какими методами можно обеспечить корректную выборку данных.
Рассмотрим типичную ситуацию, описанную участниками обсуждения. Нам необходимо получить список номенклатуры с их базовыми ценами и сравнить их с текущими розничными ценами. Если розничная цена отличается от новой расчетной (или ее вовсе нет), запись должна попасть в результат для последующего обновления.
Типичный код запроса, который «не работает», выглядит так:
ВЫБРАТЬ
ТБ.Номенклатура КАК Номенклатура,
ТБ.Цена КАК БазоваяЦена,
ТЦ.Цена КАК ТекущаяЦена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ВидЦен = &ВидЦенБазовый) КАК ТБ
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ВидЦен = &ВидЦенРозница) КАК ТЦ
ПО ТБ.Номенклатура = ТЦ.Номенклатура
ГДЕ
ТЦ.Цена <> &НоваяЦена
Проблема: Если по какой-то позиции в таблице ТЦ (розничные цены) нет записи, то поле ТЦ.Цена примет значение NULL. Логически мы понимаем, что «ничто» (NULL) не равно конкретной цене (например, 100 рублям), и такая строка должна пройти фильтр <>. Однако на практике такие строки полностью исключаются из результата. Выясним причину этого поведения.
В отличие от обычного программного кода, где логическое выражение всегда либо Истина, либо Ложь, язык запросов (базирующийся на SQL) использует трехзначную логику. Любая операция сравнения со значением NULL (будь то =, <>, > или <) возвращает не Ложь, а именно NULL (неизвестность).
Проанализируем ситуацию с точки зрения системы:
ГДЕ — это фильтр, который пропускает запись только в том случае, если результат выражения равен ИСТИНА.ТЦ.Цена равна NULL, то выражение NULL <> 100 вычисляется как NULL.ИСТИНОЙ, запись отсекается.Важно помнить: NULL — это не значение, а флаг отсутствия данных. С точки зрения математики нельзя сказать, равно ли «неизвестно» числу 100 или нет. Ответ всегда будет — «неизвестно».
Самый простой и популярный способ заставить сравнение работать — преобразовать NULL в реальное значение (например, в ноль для числовых полей) прямо в условии. Рассмотрим, как изменится наш запрос:
ГДЕ
ЕСТЬNULL(ТЦ.Цена, 0) <> &НоваяЦена
Теперь, если цена отсутствует, функция ЕСТЬNULL вернет 0. Сравнение 0 <> 100 вернет ИСТИНА, и запись успешно попадет в выборку. Это именно то решение, к которому пришел автор темы.
Хотя первый способ удобен, он не всегда корректен, если 0 является допустимым значением в базе. Более строгий и «SQL-правильный» метод — использование специального оператора ЕСТЬ NULL. Проанализируем комбинированное условие:
ГДЕ
(ТЦ.Цена ЕСТЬ NULL) ИЛИ (ТЦ.Цена <> &НоваяЦена)
В данном случае мы явно говорим системе: «Дай нам те записи, где цены нет совсем, ИЛИ те, где цена есть, но она не совпадает с новой». Это решение считается более производительным в некоторых СУБД, так как позволяет лучше использовать индексы (SARGability), не оборачивая поле в функцию.
В сообщениях форума (например, сообщение 47) предлагается альтернативный, более элегантный подход. Вместо того чтобы дважды обращаться к одной и той же виртуальной таблице СрезПоследних и соединять их (что может быть медленно), мы можем извлечь все данные одним пакетом и «развернуть» их с помощью группировки:
ВЫБРАТЬ
Т.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВЫБОР КОГДА Т.ВидЦен = &ВидЦенБазовый ТОГДА Т.Цена ИНАЧЕ 0 КОНЕЦ) КАК БазоваяЦена,
МАКСИМУМ(ВЫБОР КОГДА Т.ВидЦен = &ВидЦенРозница ТОГДА Т.Цена ИНАЧЕ 0 КОНЕЦ) КАК ТекущаяЦена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ВидЦен В (&ВидЦенБазовый, &ВидЦенРозница)) КАК Т
СГРУППИРОВАТЬ ПО
Т.Номенклатура
ИМЕЮЩИЕ
МАКСИМУМ(ВЫБОР КОГДА Т.ВидЦен = &ВидЦенРозница ТОГДА Т.Цена ИНАЧЕ 0 КОНЕЦ) <> &НоваяЦена
Преимущества этого метода:
ИНАЧЕ 0 в конструкции ВЫБОР мы сразу избавляемся от NULL в результатах агрегатных функций.Обсуждение на форуме также коснулось культуры написания кода. Чтобы ваш запрос был понятен коллегам и вам самим через полгода, стоит придерживаться стандартов 1С:
ТБ или ТЦ. Используйте понятные имена: ЦеныБазовые, ЦеныРозничные. Согласно стандартам, псевдоним должен быть осмысленным термином предметной области.СрезПоследних — это сложный подзапрос. Соединение двух виртуальных таблиц может привести к неоптимальному плану выполнения на больших объемах данных. Если отчет работает медленно, попробуйте сначала выгрузить результаты во временные таблицы с индексами по измерениям.// для пояснения сложной логики сравнения с NULL.Разберем еще один нюанс: почему NULL нельзя сравнивать через обычное =? Если вы напишете ГДЕ Поле = NULL, запрос не выдаст ошибку, но он никогда не вернет ни одной строки. Для проверки на пустоту в языке запросов существует только оператор ЕСТЬ NULL и его антипод ЕСТЬ НЕ NULL.
Подведем итог: понимание того, что NULL — это состояние неизвестности, а не значение, позволяет избежать множества ошибок при построении отчетов и механизмов проведения. Всегда проверяйте свои левые соединения на предмет появления «пустот» и заранее решайте, как ваш фильтр должен их обрабатывать.