Почему отбор СКД не применяется к части вложенного запроса с объединением и как это исправить?

← На главную

Разберем распространенную ситуацию при разработке отчетов в системе компоновки данных (СКД). Часто отчет строится на базе сложного набора данных — запроса, внутри которого используется вложенный запрос с конструкцией ОБЪЕДИНИТЬ ВСЕ из нескольких разных таблиц регистров. При наложении пользовательского отбора по общему полю (например, Инвойс) выясняется, что фильтр корректно отрабатывает только на первых частях объединения, а строки из последней ветки запроса выводятся без учета установленного отбора.

Проанализируем структуру такого запроса, выясним причины такого поведения платформы 1С и рассмотрим несколько надежных способов устранения проблемы.

Посмотрим на исходный пример запроса набора данных:


ВЫБРАТЬ
    ВложенныйЗапрос.Инвойс КАК Инвойс,
    ВложенныйЗапрос.Контрагент,
    ВложенныйЗапрос.Регистратор КАК Регистратор,
    ВложенныйЗапрос.СтоимостьЗакупки КАК СтоимостьЗакупки,
    ВложенныйЗапрос.СтоимостьПродажи КАК СтоимостьПродажи,
    ВложенныйЗапрос.СтоимостьЗакупкиЕвро,
    ВложенныйЗапрос.СтоимостьПродажиЕвро
ИЗ
    (ВЫБРАТЬ
        ЗакупкиОбороты.Инвойс КАК Инвойс,
        ЗакупкиОбороты.Контрагент КАК Контрагент,
        ЗакупкиОбороты.Регистратор КАК Регистратор,
        ЗакупкиОбороты.СтоимостьОборот КАК СтоимостьЗакупки,
        0 КАК СтоимостьПродажи,
        ЗакупкиОбороты.СтоимостьЕвроОборот КАК СтоимостьЗакупкиЕвро,
        0 КАК СтоимостьПродажиЕвро
    ИЗ
        РегистрНакопления.Закупки.Обороты(, , Регистратор, ) КАК ЗакупкиОбороты
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ПродажиОбороты.Инвойс,
        ПродажиОбороты.Контрагент,
        ПродажиОбороты.Регистратор,
        0,
        ПродажиОбороты.СтоимостьОборот,
        0,
        ПродажиОбороты.СтоимостьЕвроОборот
    ИЗ
        РегистрНакопления.Продажи.Обороты(, , Регистратор, ) КАК ПродажиОбороты
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ПартииТоваровНаСкладах.Регистратор.Инвойс,
        NULL,
        ПартииТоваровНаСкладах.Регистратор,
        0,
        0,
        0,
        0
    ИЗ
        РегистрНакопления.ПартииТоваровНаСкладах КАК ПартииТоваровНаСкладах
    ГДЕ
        ПартииТоваровНаСкладах.КодОперации = ЗНАЧЕНИЕ(Перечисление.КодыОперацийПартииТоваров.СписаниеНаЗатраты)) КАК ВложенныйЗапрос

Причины некорректной работы автозаполнения СКД

Рассмотрим подробнее, почему механизм компоновки данных дает сбой в подобных конструкциях:

Особенности «проталкивания» условий (Push-down): по умолчанию в СКД включен флаг Автозаполнение. Система пытается оптимизировать запрос и транслировать отбор пользователя как можно глубже в подзапросы. Для виртуальных таблиц оборотов (Закупки.Обороты и Продажи.Обороты) СКД автоматически подставляет отбор в параметры самой виртуальной таблицы. Для реальной таблицы ПартииТоваровНаСкладах условие должно попасть в секцию ГДЕ. Однако из-за сложного выражения парсер СКД не может корректно сопоставить поле верхнего уровня и выражение ветки объединения.
Разыменование составного ссылочного типа: поле Регистратор в регистрах накопления имеет составной тип. Конструкция ПартииТоваровНаСкладах.Регистратор.Инвойс генерирует множество неявных левых соединений (LEFT JOIN) со всеми возможными документами-регистраторами. Если у части документов реквизит отсутствует или возвращается NULL, автоматическое наложение условия работает непредсказуемо.
Отсутствие явных псевдонимов полей: во второй и третьей секциях объединения поля не имеют явных псевдонимов КАК Инвойс, что при автоматическом анализе схемы компоновки также затрудняет связывание полей.

Способ 1. Отключение автозаполнения и ручная настройка компоновки

Разберем по шагам решение, которое позволяет полностью контролировать структуру полей и условий:

Откроем схему компоновки данных на вкладке Наборы данных.
В окне редактирования запроса снимем флаг Автозаполнение.
Перейдем на вкладку Компоновка данных в нижней части конструктора запроса.
На закладке Поля вручную укажем доступные поля отчета (результирующие поля выборки).
На закладке Условия настроим доступные поля для фильтрации, связав поле отбора с нужными выражениями запроса.

При таком подходе платформа не пытается автоматически модифицировать вложенные ветки запроса, а применяет фильтр строго к результирующей выборке верхнего уровня.

Способ 2. Использование языка расширения СКД

Если мы хотим сохранить преимущества автоматической фильтрации на уровне СУБД, добавим явные инструкции расширения языка запросов СКД в фигурных скобках {ГДЕ ...} для каждой ветки вложенного запроса.

Посмотрим на пример модифицированного запроса:


ВЫБРАТЬ
    ВложенныйЗапрос.Инвойс КАК Инвойс,
    ВложенныйЗапрос.Контрагент КАК Контрагент,
    ВложенныйЗапрос.Регистратор КАК Регистратор,
    ВложенныйЗапрос.СтоимостьЗакупки КАК СтоимостьЗакупки,
    ВложенныйЗапрос.СтоимостьПродажи КАК СтоимостьПродажи,
    ВложенныйЗапрос.СтоимостьЗакупкиЕвро КАК СтоимостьЗакупкиЕвро,
    ВложенныйЗапрос.СтоимостьПродажиЕвро КАК СтоимостьПродажиЕвро
ИЗ
    (ВЫБРАТЬ
        ЗакупкиОбороты.Инвойс КАК Инвойс,
        ЗакупкиОбороты.Контрагент КАК Контрагент,
        ЗакупкиОбороты.Регистратор КАК Регистратор,
        ЗакупкиОбороты.СтоимостьОборот КАК СтоимостьЗакупки,
        0 КАК СтоимостьПродажи,
        ЗакупкиОбороты.СтоимостьЕвроОборот КАК СтоимостьЗакупкиЕвро,
        0 КАК СтоимостьПродажиЕвро
    ИЗ
        РегистрНакопления.Закупки.Обороты(, , Регистратор, {Инвойс.* КАК Инвойс}) КАК ЗакупкиОбороты
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ПродажиОбороты.Инвойс КАК Инвойс,
        ПродажиОбороты.Контрагент КАК Контрагент,
        ПродажиОбороты.Регистратор КАК Регистратор,
        0,
        ПродажиОбороты.СтоимостьОборот,
        0,
        ПродажиОбороты.СтоимостьЕвроОборот
    ИЗ
        РегистрНакопления.Продажи.Обороты(, , Регистратор, {Инвойс.* КАК Инвойс}) КАК ПродажиОбороты
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ПартииТоваровНаСкладах.Регистратор.Инвойс КАК Инвойс,
        NULL,
        ПартииТоваровНаСкладах.Регистратор,
        0,
        0,
        0,
        0
    ИЗ
        РегистрНакопления.ПартииТоваровНаСкладах КАК ПартииТоваровНаСкладах
    ГДЕ
        ПартииТоваровНаСкладах.КодОперации = ЗНАЧЕНИЕ(Перечисление.КодыОперацийПартииТоваров.СписаниеНаЗатраты)
        {ГДЕ
            ПартииТоваровНаСкладах.Регистратор.Инвойс.* КАК Инвойс}) КАК ВложенныйЗапрос

В этом случае платформа точно знает, в какое место каждой ветки поместить условие пользователя при установке отбора по полю Инвойс.

Способ 3. Переход на временные таблицы и типизация ссылок

Использование вложенных запросов с ОБЪЕДИНИТЬ ВСЕ в качестве прямого источника данных СКД считается неоптимальной практикой. Более надежный и производительный архитектурный подход — использование пакета запросов с временными таблицами.

Разберем по шагам построение пакета:

Сначала собираем данные в промежуточную временную таблицу с явной типизацией ссылок через оператор ВЫРАЗИТЬ.
В финальном запросе пакета выбираем сгруппированные данные из временной таблицы.


ВЫБРАТЬ
    ЗакупкиОбороты.Инвойс КАК Инвойс,
    ЗакупкиОбороты.Контрагент КАК Контрагент,
    ЗакупкиОбороты.Регистратор КАК Регистратор,
    ЗакупкиОбороты.СтоимостьОборот КАК СтоимостьЗакупки,
    0 КАК СтоимостьПродажи,
    ЗакупкиОбороты.СтоимостьЕвроОборот КАК СтоимостьЗакупкиЕвро,
    0 КАК СтоимостьПродажиЕвро
ПОМЕСТИТЬ ВТ_ВсеДанные
ИЗ
    РегистрНакопления.Закупки.Обороты(, , Регистратор, ) КАК ЗакупкиОбороты

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ПродажиОбороты.Инвойс,
    ПродажиОбороты.Контрагент,
    ПродажиОбороты.Регистратор,
    0,
    ПродажиОбороты.СтоимостьОборот,
    0,
    ПродажиОбороты.СтоимостьЕвроОборот
ИЗ
    РегистрНакопления.Продажи.Обороты(, , Регистратор, ) КАК ПродажиОбороты

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВЫРАЗИТЬ(ПартииТоваровНаСкладах.Регистратор КАК Документ.ПоступлениеТоваровУслуг).Инвойс,
    NULL,
    ПартииТоваровНаСкладах.Регистратор,
    0,
    0,
    0,
    0
ИЗ
    РегистрНакопления.ПартииТоваровНаСкладах КАК ПартииТоваровНаСкладах
ГДЕ
    ПартииТоваровНаСкладах.КодОперации = ЗНАЧЕНИЕ(Перечисление.КодыОперацийПартииТоваров.СписаниеНаЗатраты)
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Инвойс КАК Инвойс,
    ВТ.Контрагент КАК Контрагент,
    ВТ.Регистратор КАК Регистратор,
    ВТ.СтоимостьЗакупки КАК СтоимостьЗакупки,
    ВТ.СтоимостьПродажи КАК СтоимостьПродажи,
    ВТ.СтоимостьЗакупкиЕвро КАК СтоимостьЗакупкиЕвро,
    ВТ.СтоимостьПродажиЕвро КАК СтоимостьПродажиЕвро
ИЗ
    ВТ_ВсеДанные КАК ВТ

При такой структуре СКД накладывает отбор на плоскую временную таблицу ВТ_ВсеДанные без необходимости сложного разбора вложенных конструкций, что гарантирует стопроцентное срабатывание условий по всем видам движений.