Разработчики при написании запросов в платформе 1С:Предприятие часто сталкиваются со странным поведением условий фильтрации. Классический пример: условие проверки вхождения В работает отлично, а вот его отрицание НЕ В (или NOT IN в терминах СУБД) внезапно возвращает абсолютно пустой результат. При этом, если дописать к условию тавтологию вида = ИСТИНА или = ЛОЖЬ, запрос начинает возвращать корректные данные.
В рамках этой статьи мы подробно проанализируем, почему возникает этот парадокс, разберем внутреннее устройство работы СУБД с троичной логикой, выясним, как транслятор запросов 1С преобразует наши условия в SQL-код, и рассмотрим наиболее эффективные методы решения этой проблемы.
Рассмотрим ситуацию, с которой столкнулся автор темы. В его коде использовалось три варианта написания одного и того же по логике условия в параметрах виртуальной таблицы остатков. Давайте проанализируем их.
Вариант №1 (Работает корректно):
РегистрНакопления.ДвижениеПродукции.Остатки(
,
ЗаписьСкладскогоЖурнала В
(ВЫБРАТЬ
ВтВпроцессе.ЗаписьСкладскогоЖурнала
ИЗ
ВтВпроцессе) = ЛОЖЬ)
Вариант №2 (Также работает корректно):
РегистрНакопления.ДвижениеПродукции.Остатки(
,
НЕ ЗаписьСкладскогоЖурнала В
(ВЫБРАТЬ
ВтВпроцессе.ЗаписьСкладскогоЖурнала
ИЗ
ВтВпроцессе) = ИСТИНА)
Вариант №3 (НЕ РАБОТАЕТ — выдает пустой результат):
РегистрНакопления.ДвижениеПродукции.Остатки(
,
НЕ ЗаписьСкладскогоЖурнала В
(ВЫБРАТЬ
ВтВпроцессе.ЗаписьСкладскогоЖурнала
ИЗ
ВтВпроцессе))
С точки зрения обычной человеческой логики все три варианта абсолютно эквивалентны. Отрицание логического выражения должно давать тот же результат, что и сравнение этого выражения с ЛОЖЬ или сравнение его отрицания с ИСТИНА. Однако СУБД выдает пустую выборку для третьего варианта. Почему так происходит? Выясним причину.
Для того чтобы локализовать проблему и исключить влияние специфики метаданных конфигурации, мы можем составить простой тестовый запрос. Создадим две временные таблицы. В первую поместим числа от 1 до 8. Во вторую поместим значения для сравнения, добавив туда одну строчку со значением NULL.
Рассмотрим следующий тестовый код:
ВЫБРАТЬ "1" КАК ЧС ПОМЕСТИТЬ ВТ ОБЪЕДИНИТЬ
ВЫБРАТЬ "2" ОБЪЕДИНИТЬ
ВЫБРАТЬ "3" ОБЪЕДИНИТЬ
ВЫБРАТЬ "4" ОБЪЕДИНИТЬ
ВЫБРАТЬ "5" ОБЪЕДИНИТЬ
ВЫБРАТЬ "6" ОБЪЕДИНИТЬ
ВЫБРАТЬ "8" ;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ NULL КАК ЧСНалл ПОМЕСТИТЬ ВТН ОБЪЕДИНИТЬ
ВЫБРАТЬ "8" ОБЪЕДИНИТЬ
ВЫБРАТЬ "1" ;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.ЧС КАК ЧисловоеПоле
ИЗ
ВТ КАК ВТ
ГДЕ
ВТ.ЧС НЕ В
(ВЫБРАТЬ
ВТН.ЧСНалл
ИЗ
ВТН КАК ВТН)
Запустив данный запрос в консоли запросов, мы увидим абсолютно пустую таблицу результатов. Но стоит нам убрать строку ВЫБРАТЬ NULL КАК ЧСНалл из временной таблицы ВТН, как запрос моментально отрабатывает корректно и возвращает все ожидаемые числа, кроме "1" и "8". Это доказывает, что корень проблемы кроется в наличии значения NULL в подзапросе.
Для того чтобы понять, почему СУБД ведет себя именно так, нам необходимо обратиться к стандарту языка SQL. В отличие от классической булевой логики, где существуют только два значения (TRUE и FALSE), в реляционных базах данных используется так называемая троичная логика (Three-Valued Logic, 3VL). В ней существует третье логическое состояние — UNKNOWN (Неизвестно).
Когда СУБД вычисляет условие сравнения с участием NULL, результатом всегда становится UNKNOWN. Важно запомнить: NULL — это не пустота и не ноль, это принципиальное отсутствие данных ("значение неизвестно"). Поэтому:
1 = NULL дает UNKNOWN.1 <> NULL также дает UNKNOWN.Теперь посмотрим, как СУБД под капотом разворачивает оператор отрицания вхождения NOT IN (который транслируется из 1С-овского НЕ В). Предположим, у нас есть поле Поле1, которое мы сравниваем со списком значений из подзапроса: {1, 8, NULL}.
Условие Поле1 NOT IN (1, 8, NULL) математически и логически эквивалентно следующей цепочке сравнений, объединенных логическим "И" (AND):
(Поле1 <> 1) AND (Поле1 <> 8) AND (Поле1 <> NULL)
Давайте подставим вместо Поле1 конкретное значение, например, число 3 (которого точно нет в подзапросе):
3 <> 1. Результат: TRUE.3 <> 8. Результат: TRUE.3 <> NULL. Результат: UNKNOWN.Теперь объединяем результаты по правилам троичной логики для оператора AND:
TRUE AND TRUE AND UNKNOWN => UNKNOWN.
Согласно спецификации SQL, секция фильтрации (будь то параметр виртуальной таблицы или классическое предложение WHERE) пропускает только те строки, для которых итоговое логическое условие вычисляется как строгое TRUE. Строки со значениями условий FALSE или UNKNOWN отбрасываются. Из-за этого СУБД отсеивает абсолютно все записи вашей таблицы!
Теперь проанализируем, почему конструкции со сравнением булевых значений возвращали корректные данные. Для этого заглянем "под капот" СУБД с помощью технологического журнала 1С или SQL Profiler и посмотрим на сформированный SQL-код.
Когда вы пишете условие НЕ ЗаписьСкладскогоЖурнала В (ВЫБРАТЬ ...) = ИСТИНА, транслятор платформы 1С понимает, что вы хотите сравнить результат выражения с булевой константой. Поскольку в самом SQL нет полноценного типа данных Boolean в явном виде для фильтров, 1С вынуждена генерировать условную конструкцию CASE WHEN:
WHERE NOT (
CASE
WHEN (T1.ЗаписьСкладскогоЖурнала IN (SELECT T2.Поле FROM ВтВпроцессе T2)) THEN 0x01
ELSE 0x00
END = 0x01
)
Давайте разберем, как СУБД вычисляет эту конструкцию для записи, которой нет в подзапросе (при наличии там NULL):
WHEN (T1.ЗаписьСкладскогоЖурнала IN (SELECT ...)).IN с присутствующим NULL возвращает нам UNKNOWN (так как значение может быть равно NULL, а может и не быть — это неизвестно).WHEN не равен TRUE (он равен UNKNOWN), процессор СУБД пропускает ветку THEN 0x01 и уходит в ветку по умолчанию — ELSE 0x00!CASE WHEN успешно возвращает значение 0x00 (Ложь).0x00 = 0x01 (Ложь = Истина), что дает результат FALSE.NOT переворачивает этот результат: NOT (FALSE) превращается в долгожданное TRUE!Благодаря этой сложной цепочке преобразований платформа 1С обходит ограничения SQL-логики, но делает это ценой усложнения запроса. Такой "костыльный" синтаксис запутает любого разработчика, который будет читать ваш код после вас, а также может негативно повлиять на построение плана запроса оптимизатором СУБД. Нам нужно красивое и системное решение.
Помимо троичной логики, существует еще одна неочевидная проблема, связанная с генерацией временных таблиц. Рассмотрим пример кода:
ВЫБРАТЬ NULL КАК Поле ПОМЕСТИТЬ Таблица2;
Если вы создаете временную таблицу таким образом, колонка Поле создается платформой без явного указания типа данных. При попытке выполнить соединение или проверку вхождения по такому полю, СУБД будет вынуждена динамически приводить типы, что крайне негативно сказывается на производительности и может приводить к ошибкам компиляции запроса в некоторых версиях СУБД.
Золотое правило разработки в 1С: Любые выбираемые NULL во временных таблицах должны быть строго типизированы с помощью функции ВЫРАЗИТЬ!
ВЫБРАТЬ ВЫРАЗИТЬ(NULL КАК Справочник.Номенклатура) КАК Поле ПОМЕСТИТЬ Таблица2;
Теперь, когда мы досконально разобрались в причинах некорректного поведения СУБД, рассмотрим два правильных и профессиональных способа решения этой задачи.
Если вам критически важно наложить отбор именно внутри параметров виртуальной таблицы (например, для оптимизации первоначальной выборки остатков), вам необходимо гарантировать, что подзапрос никогда не вернет строку со значением NULL. Для этого достаточно добавить условие ЕСТЬ НЕ NULL в подзапрос выборки.
Посмотрим на пример правильной реализации:
РегистрНакопления.ДвижениеПродукции.Остатки(
,
НЕ ЗаписьСкладскогоЖурнала В
(ВЫБРАТЬ
ВтВпроцессе.ЗаписьСкладскогоЖурнала
ИЗ
ВтВпроцессе КАК ВтВпроцессе
ГДЕ
ВтВпроцессе.ЗаписьСкладскогоЖурнала ЕСТЬ НЕ NULL))
В данном случае СУБД гарантированно получит чистый список значений без неопределенностей. Цепочка условий NOT IN развернется без участия NULL, и фильтр отработает абсолютно корректно, вернув нужные строки.
Использование конструкции НЕ В в запросах 1С (особенно по виртуальным таблицам на больших базах данных) считается неоптимальным подходом и относится к категории антипаттернов проектирования. СУБД зачастую не может эффективно сопоставить индексы при обработке NOT IN и переходит к полному сканированию таблиц (Table Scan / Index Scan).
Наиболее правильным, быстрым и надежным способом исключения данных является использование ЛЕВОГО СОЕДИНЕНИЯ с последующей проверкой соединенного поля на значение NULL. Давайте перепишем исходный запрос автора темы по стандартам разработки 1С.
Разберем это решение по шагам:
ВтВпроцессе по ключевому полю ЗаписьСкладскогоЖурнала.ГДЕ отсекаем те строки, для которых сопоставление произошло успешно. Нам нужны только те записи, у которых в правой таблице значение осталось равным NULL.Реализуем этот алгоритм в коде:
ВЫБРАТЬ
ДвижениеПродукцииОстатки.ЗаписьСкладскогоЖурнала КАК ЗаписьСкладскогоЖурнала,
ДвижениеПродукцииОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
РегистрНакопления.ДвижениеПродукции.Остатки КАК ДвижениеПродукцииОстатки
ЛЕВОЕ СОЕДИНЕНИЕ ВтВпроцессе КАК ВтВпроцессе
ПО ДвижениеПродукцииОстатки.ЗаписьСкладскогоЖурнала = ВтВпроцессе.ЗаписьСкладскогоЖурнала
ГДЕ
ВтВпроцессе.ЗаписьСкладскогоЖурнала ЕСТЬ NULL
Этот вариант обладает рядом неоспоримых преимуществ перед использованием конструкции НЕ В:
Hash Match или Merge Join.NULL в любой из таблиц. Логика соединения гарантирует предсказуемый и корректный результат в любых условиях.Таким образом, мы детально разобрали неочевидное поведение запросов 1С при работе с отрицанием вхождения. Понимание принципов работы СУБД и троичной логики позволяет не только быстро находить подобные ошибки в коде, но и проектировать надежные и быстродействующие информационные системы.