При написании сложных аналитических отчетов и выборок в системе «1С:Предприятие» разработчики часто сталкиваются с необходимостью фильтрации данных по незаполненным субконто или реквизитам. На первый взгляд, абсолютно прозрачные конструкции условий в секции ГДЕ начинают вести себя непредсказуемо: запросы возвращают пустой результат или, наоборот, выбирают лишние строки. Особенно много вопросов вызывают конструкции, совмещающие в себе логическое отрицание НЕ, проверку на равенство и функцию ЕСТЬNULL.
В этой статье мы подробно разберем, почему прямое сравнение с NULL через отрицание не работает, как функционирует трехзначная логика СУБД, в чем разница между NULL и НЕОПРЕДЕЛЕНО на уровне базы данных, а также проанализируем приоритеты операторов и вопросы производительности подобных запросов.
Рассмотрим практический пример запроса, который вызвал серьезные затруднения у автора темы на форуме. Разработчик столкнулся со следующей конструкцией в секции ГДЕ:
ГДЕ
ВЫБОР
КОГДА ХозрасчетныйОбороты.КорСчет В (&ФильтрСписокСчетов)
ТОГДА ВЫБОР
КОГДА &ДоговорКонтрагента = НЕОПРЕДЕЛЕНО
ТОГДА НЕ ЕСТЬNULL(ХозрасчетныйОбороты.КорСубконто1, НЕОПРЕДЕЛЕНО) = &Контрагент
ИНАЧЕ НЕ(ЕСТЬNULL(ХозрасчетныйОбороты.КорСубконто1, НЕОПРЕДЕЛЕНО) = &Контрагент
И ЕСТЬNULL(ХозрасчетныйОбороты.КорСубконто2, НЕОПРЕДЕЛЕНО) = &ДоговорКонтрагента)
КОНЕЦ
ИНАЧЕ ИСТИНА
КОНЕЦ
У любого программиста возникнет логичный вопрос: зачем здесь используется столь громоздкое выражение НЕ ЕСТЬNULL(..., НЕОПРЕДЕЛЕНО) = &Контрагент? Почему нельзя было написать гораздо проще, например:
НЕ ХозрасчетныйОбороты.КорСубконто1 = &Контрагент
Давайте разберем по шагам, к каким последствиям в выборке данных приведет такое упрощение и как на самом деле СУБД обрабатывает эти условия.
Главная причина кроется в том, как реляционные СУБД (такие как MS SQL Server, PostgreSQL, IBM DB2) работают с неопределенными значениями. В классической булевой логике существуют только два состояния: ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Однако в базах данных используется так называемая трехзначная логика (Three-Valued Logic, или 3VL).
В этой логике любое логическое выражение может принимать одно из трех значений:
Значение NULL в базах данных символизирует полное отсутствие данных как таковых. Оно не равно нулю, не равно пустой строке и даже не равно другому NULL. Из-за этого любое прямое сравнение любого значения с NULL (равенство, неравенство, больше или меньше) всегда дает результат UNKNOWN.
Проанализируем, что происходит, когда СУБД вычисляет упрощенное выражение НЕ (ХозрасчетныйОбороты.КорСубконто1 = &Контрагент) для строки, в которой у проводки отсутствует первое субконто (то есть значение поля равно NULL):
NULL = &Контрагент. Поскольку одна из сторон сравнения является NULL, результатом будет UNKNOWN.НЕ (UNKNOWN). По правилам трехзначной логики SQL, отрицание неизвестности также дает UNKNOWN.ГДЕ (WHERE) в SQL-запросах устроена так, что она пропускает в итоговый результат только те строки, для которых итоговое условие вернуло строго ИСТИНА. Строки со значениями условий ЛОЖЬ и UNKNOWN отбрасываются СУБД.В итоге проводки, у которых вообще нет первого субконто (значение NULL), будут полностью исключены из выборки! Но по бизнес-логике нашей задачи, если субконто отсутствует, оно заведомо не равно искомому контрагенту, а значит, отрицание равенства должно вернуть истину, и мы обязаны увидеть эти строки в отчете.
Использование функции ЕСТЬNULL спасает ситуацию, преобразуя физический NULL в системное значение НЕОПРЕДЕЛЕНО, сравнение которого с конкретной ссылкой контрагента дает четкую ЛОЖЬ, а последующее отрицание превращает ее в ИСТИНУ.
Давайте наглядно сопоставим, как будут вычисляться условия с использованием функции ЕСТЬNULL и без нее для различных значений в поле КорСубконто1 (при условии, что параметр &Контрагент равен «ООО Ромашка»):
НЕ ("ООО Ромашка" = "ООО Ромашка") -> НЕ (ИСТИНА) -> ЛОЖЬ (строка исключается, логика верна).НЕ (ЕСТЬNULL("ООО Ромашка", НЕОПРЕДЕЛЕНО) = "ООО Ромашка") -> НЕ ("ООО Ромашка" = "ООО Ромашка") -> ЛОЖЬ (строка исключается).НЕ ("ИП Иванов" = "ООО Ромашка") -> НЕ (ЛОЖЬ) -> ИСТИНА (строка выбирается, логика верна).НЕ (ЕСТЬNULL("ИП Иванов", НЕОПРЕДЕЛЕНО) = "ООО Ромашка") -> НЕ ("ИП Иванов" = "ООО Ромашка") -> ИСТИНА (строка выбирается).НЕ (NULL = "ООО Ромашка") -> НЕ (UNKNOWN) -> UNKNOWN (строка ошибочно отбрасывается!).НЕ (ЕСТЬNULL(NULL, НЕОПРЕДЕЛЕНО) = "ООО Ромашка") -> НЕ (НЕОПРЕДЕЛЕНО = "ООО Ромашка") -> НЕ (ЛОЖЬ) -> ИСТИНА (строка корректно выбирается).Еще один частый вопрос, возникающий у начинающих разработчиков при взгляде на конструкцию НЕ ЕСТЬNULL(...) = &Контрагент: почему здесь не возникает ошибка типов? Кажется, что оператор отрицания НЕ применяется непосредственно к результату функции ЕСТЬNULL (то есть к ссылке или значению НЕОПРЕДЕЛЕНО), что должно вызывать ошибку компиляции запроса.
Выясним причину, почему этот код работает корректно. Дело в строгих правилах приоритета операторов в языке запросов платформы 1С:
ЕСТЬNULL, ПРЕДСТАВЛЕНИЕ, РАЗНОСТЬДАТ).=, неравенство <>, проверку на вхождение в список В).НЕ, затем конъюнкция И, и в последнюю очередь дизъюнкция ИЛИ).Поскольку операция сравнения = имеет более высокий приоритет, чем логическое отрицание НЕ, транслятор запросов 1С преобразует написанное выражение следующим образом:
НЕ ( ЕСТЬNULL(ХозрасчетныйОбороты.КорСубконто1, НЕОПРЕДЕЛЕНО) = &Контрагент )
Для улучшения читаемости кода и во избежание двусмысленности мы настоятельно рекомендуем всегда явно расставлять скобки в подобных условиях, даже если платформа прекрасно справляется с этим самостоятельно.
Почему разработчик в качестве значения замены в функции ЕСТЬNULL выбрал именно системное значение НЕОПРЕДЕЛЕНО, а не пустую ссылку конкретного контрагента? Посмотрим на природу этих типов данных в платформе 1С:
NULL — это техническое понятие СУБД. Оно возникает, когда поле физически отсутствует в результирующей таблице базы данных (например, при левом соединении таблиц, когда для строки левой таблицы не нашлось соответствия в правой, или когда запрашивается субконто у счета, на котором субконто данного порядка не настроено в плане счетов).НЕОПРЕДЕЛЕНО — это специальный тип данных платформы 1С. Оно используется для обозначения пустого значения в полях составного типа данных. Поля Субконто на счетах учета всегда имеют составной тип, так как на разных счетах аналитикой могут выступать совершенно разные справочники (Контрагенты, Номенклатура, Договоры, Физические лица и т.д.).Если бы мы заменили NULL на пустую ссылку контрагента (ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка)), мы бы жестко привязали логику запроса к конкретному типу данных. Но на месте КорСубконто1 в зависимости от счета может находиться совсем другой тип данных (например, договор или склад). Использование универсального значения НЕОПРЕДЕЛЕНО семантически корректно приравнивает «отсутствующее» субконто к «пустому составному» значению, сохраняя гибкость метаданных.
Хотя исходная конструкция решает поставленную задачу с точки зрения логики, она содержит в себе критические архитектурные недостатки, которые могут привести к колоссальному падению производительности на реальных (особенно крупных) базах данных. Проанализируем эти проблемы:
Проблема №1: Отрицание НЕ в секции ГДЕ.
Использование любых отрицаний (НЕ, <>) делает условие «не-саргабельным» (Non-SARGable). Это означает, что СУБД не сможет использовать существующие индексы по полям субконто для быстрого поиска строк (Index Seek). Вместо этого СУБД будет вынуждена выполнять полное сканирование таблиц или индексов (Index Scan / Table Scan), что крайне медленно на больших объемах данных.
Проблема №2: Конструкции ВЫБОР КОГДА внутри фильтрации.
Динамический выбор условий заставляет оптимизатор СУБД строить универсальный (и из-за этого крайне неэффективный) план запроса. Оптимизатор не может заранее предугадать, какое именно условие будет проверяться, и строит «тяжелый» план выполнения, который будет неоптимальным при любых переданных параметрах.
Проблема №3: Неявные соединения составного поля КорСубконто.
Обращение к субконто напрямую через точку приводит к тому, что платформа 1С «под капотом» генерирует левые соединения со всеми таблицами, типы которых входят в состав субконто. В сочетании с конструкциями ВЫБОР и НЕ это создает огромную нагрузку на сервер СУБД.
Для ускорения работы базы данных и исключения взаимоблокировок мы рекомендуем переписать логику фильтрации. Рассмотрим наиболее эффективные способы оптимизации:
Вместо того чтобы заставлять СУБД динамически вычислять заполненность параметра &ДоговорКонтрагента прямо в запросе с помощью ВЫБОР КОГДА, гораздо эффективнее разделить этот запрос на уровне встроенного языка 1С в зависимости от переданных параметров:
// В зависимости от заполненности параметров формируем текст запроса динамически в коде
Если ЗначениеЗаполнено(ДоговорКонтрагента) Тогда
// Если договор передан, накладываем строгое условие на равенство
ТекстЗапроса = ТекстЗапроса + " ГДЕ (ХозрасчетныйОбороты.КорСубконто1 = &Контрагент И ХозрасчетныйОбороты.КорСубконто2 = &ДоговорКонтрагента)";
Иначе
ТекстЗапроса = ТекстЗапроса + " ГДЕ (ХозрасчетныйОбороты.КорСубконто1 = &Контрагент)";
КонецЕсли;
Самый правильный и быстрый способ отбора данных по субконто в 1С — это наложение фильтров непосредственно в параметрах виртуальной таблицы (например, в параметре Условие виртуальной таблицы Обороты), а не в секции ГДЕ. Это позволяет СУБД отсечь ненужные данные на самом раннем этапе построения выборки, используя подготовленные индексы виртуальных таблиц.
Подводя итог, можно сказать: детальное понимание трехзначной логики SQL и внутреннего устройства платформы 1С позволяет не только избегать досадных логических ошибок при обработке значений NULL, но и создавать быстрые, масштабируемые и качественные алгоритмы для высоконагруженных информационных систем.