При разработке сложных отчетов на Системе компоновки данных (СКД), особенно в конфигурациях типа УНФ или ERP, программисты часто сталкиваются с парадоксальной ситуацией. В отчете выводятся данные, где поле типа Булево наглядно содержит значение Ложь, условное оформление для таких строк отрабатывает корректно, но стоит установить отбор в пользовательских настройках "Поле Равно Ложь" — и отчет становится пустым. При этом отбор по значению Истина работает безупречно.
Рассмотрим детально, почему это происходит, проанализируем внутреннюю логику работы 1С и найдем несколько способов решения этой проблемы.
Проанализируем ситуацию на примере запроса, который собирает данные по дополнительным реквизитам заказов. В таких задачах часто используется левое соединение с таблицей дополнительных реквизитов. Если реквизит у документа не заполнен, система возвращает NULL, который мы предусмотрительно заменяем на Ложь с помощью функции ЕСТЬNULL.
Посмотрим на пример типичного запроса, вызывающего проблему:
ВЫБРАТЬ
МАКСИМУМ(ВЫБОР
КОГДА ЗаказПокупателяДополнительныеРеквизиты.Свойство = &ОригиналДоговора
ТОГДА ЗаказПокупателяДополнительныеРеквизиты.Значение
ИНАЧЕ NULL
КОНЕЦ) КАК ОригиналДоговора,
ВложенныйЗапрос.ЗаказСсылка
ПОМЕСТИТЬ ВТДанные
ИЗ
(ВЫБРАТЬ
ЗаказыПокупателей.ЗаказПокупателя КАК ЗаказСсылка
ИЗ
РегистрНакопления.ЗаказыПокупателей КАК ЗаказыПокупателей
ГДЕ
ТИПЗНАЧЕНИЯ(ЗаказыПокупателей.Регистратор) = ТИП(Документ.ЗаказПокупателя)
СГРУППИРОВАТЬ ПО
ЗаказыПокупателей.ЗаказПокупателя) КАК ВложенныйЗапрос
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказПокупателя.ДополнительныеРеквизиты КАК ЗаказПокупателяДополнительныеРеквизиты
ПО ВложенныйЗапрос.ЗаказСсылка = ЗаказПокупателяДополнительныеРеквизиты.Ссылка
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.ЗаказСсылка;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЕСТЬNULL(ВТДанные.ОригиналДоговора, ЛОЖЬ) КАК ОригиналДоговора,
ВТДанные.ЗаказСсылка
ИЗ
ВТДанные КАК ВТДанные
В данном случае, если у заказа нет записи в табличной части ДополнительныеРеквизиты для свойства "Оригинал договора", поле в ВТДанные будет содержать NULL. В финальной выборке ЕСТЬNULL превратит его в ЛОЖЬ. Казалось бы, логика верна, но именно здесь кроется ловушка оптимизатора СКД.
Выясним причину такого поведения. Query Transformer (преобразователь запросов) в СКД старается максимально ускорить выполнение отчета. Для этого он пытается пробросить пользовательские отборы как можно "глубже" в текст SQL-запроса, чтобы отсечь ненужные строки на ранних этапах.
Когда вы устанавливаете отбор ОригиналДоговора = Ложь, СКД может попытаться применить его непосредственно к исходной таблице дополнительных реквизитов. Но в этой таблице физически нет записей со значением Ложь для тех документов, где этот реквизит просто не заполнен (там нет записи вообще). Для СУБД условие "значение равно Ложь" и "записи не существует (NULL)" — это принципиально разные вещи.
В результате оптимизации условие ГДЕ Значение = ЛОЖЬ отсекает все строки, где значение было NULL, и в итоговую выборку не попадает ничего. При отборе же Значение = ИСТИНА всё работает, так как записи со значением "Истина" в базе присутствуют физически.
Рассмотрим самое простое и быстрое решение, которое часто помогает в подобных случаях. Как показывает практика, СКД иначе обрабатывает вложенные запросы по сравнению с временными таблицами (ВТ).
При использовании вложенных запросов компоновщик чаще накладывает фильтр на уже готовый результат выборки, после того как отработала функция ЕСТЬNULL. Если заменить ПОМЕСТИТЬ ВТДанные на обычный вложенный запрос в секции ИЗ, отбор по значению Ложь начнет работать корректно, так как он будет применяться к полю, в котором NULL уже заменен на ЛОЖЬ.
Разберем ситуацию с составными типами данных. Поле Значение в дополнительных реквизитах имеет составной тип (Число, Строка, Булево, Ссылки). СКД иногда испытывает трудности с определением типа поля после агрегатных функций вроде МАКСИМУМ.
Чтобы "помочь" системе, проанализируем возможность применения функции ВЫРАЗИТЬ. Попробуйте изменить описание поля в запросе следующим образом:
ВЫБРАТЬ
ВЫРАЗИТЬ(ЕСТЬNULL(ВТДанные.ОригиналДоговора, ЛОЖЬ) КАК БУЛЕВО) КАК ОригиналДоговора
Жесткая типизация колонки дает понять оптимизатору, что это поле всегда является булевым. Это часто заставляет СКД строить более корректный SQL-запрос, правильно обрабатывающий сравнение с FALSE.
Если временные таблицы необходимы для производительности и вы не хотите от них отказываться, можно принудительно указать СКД, где именно накладывать отбор. Для этого используются фигурные скобки {}.
В основном запросе, где вы выбираете данные из временной таблицы, можно явно указать место для условий:
ВЫБРАТЬ
ЕСТЬNULL(ВТДанные.ОригиналДоговора, ЛОЖЬ) КАК ОригиналДоговора,
ВТДанные.ЗаказСсылка
ИЗ
ВТДанные КАК ВТДанные
{ГДЕ
(ЕСТЬNULL(ВТДанные.ОригиналДоговора, ЛОЖЬ)) КАК ОригиналДоговора}
Используя конструкцию {ГДЕ...}, мы указываем компоновщику, что отбор должен накладываться именно на этот участок кода, учитывая функцию ЕСТЬNULL, а не "проваливаться" внутрь временной таблицы к исходным данным.
Посмотрим на альтернативный подход. Мы можем вытягивать из запроса "чистые" данные (включая NULL), а трансформацию в Ложь перенести на уровень настроек СКД.
NULL).ОригиналДоговораВычисляемый.ЕСТЬNULL(ОригиналДоговора, Ложь).Поскольку вычисляемые поля обрабатываются системой уже после выполнения основного запроса к базе данных, проблема оптимизации на уровне SQL исчезает полностью. Отбор по вычисляемому полю всегда работает так, как ожидает пользователь.
Проанализировав проблему, мы выяснили, что корень зла кроется в попытке СКД оптимизировать запрос к разреженным данным (где отсутствие записи подменяется значением). Чтобы ваш отчет работал стабильно, рекомендуем придерживаться следующих правил:
ВЫРАЗИТЬ(... КАК БУЛЕВО) для полей, полученных из составных типов данных.Ложь по-прежнему не работает, используйте механизм Вычисляемых полей — это самый надежный, хоть и чуть менее производительный способ.Следуя этим методикам, вы сможете создавать надежные отчеты, которые корректно фильтруют данные независимо от того, насколько сложная структура запроса лежит в их основе.