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