Почему один и тот же запрос с временной таблицей в консоли и в СКД возвращает разные результаты?

При разработке отчетов на системе компоновки данных (СКД) разработчики периодически сталкиваются с ситуацией: пакетный запрос, который в обычной консоли запросов возвращает абсолютно корректные данные, в СКД выдает совершенно иной результат. Разберем типичный пример с выборкой отрицательных остатков, выясним глубинную причину такого поведения платформы 1С и рассмотрим надежные способы решения проблемы.

Анализ проблемы: как проявляется расхождение

Рассмотрим классическую задачу: необходимо найти номенклатурные позиции, по которым зафиксирован отрицательный остаток хотя бы на одном складе, а затем для найденного списка номенклатуры вывести остатки по всем складам компании. Для этого пишется пакетный запрос:

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

При запуске этого пакета в обычной консоли запросов результат предсказуем: первая временная таблица находит отрицательные остатки с учетом конкретного склада, а второй запрос выводит расшифровку. Однако при переносе этого же запроса в набор данных СКД итоговый отчет выводит только те позиции, у которых общий суммарный остаток по всем складам отрицательный. Если по номенклатуре на Складе №1 числится «-10», а на Складе №2 «+10», в СКД такая позиция исчезает из выборки, так как в целом по номенклатуре остаток равен нулю.

Почему СКД меняет логику выборки: механизм оптимизации

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

1. Особенности виртуальных таблиц остатков. Набор измерений, по которым СУБД рассчитывает итоги в виртуальных таблицах (Остатки, ОстаткиИОбороты), напрямую определяется полями, запрошенными в секции ВЫБРАТЬ, условиях связей или параметрах таблицы. Если измерение Склад исключается из выборки, СУБД сворачивает остатки только до уровня Номенклатура еще до применения условия из секции ГДЕ.
2. Транзитивная оптимизация временных таблиц в СКД. При компоновке макета СКД анализирует весь граф использования полей. Во втором запросе пакета из временной таблицы ТоварыСминусом запрашивается только поле Номенклатура. Поле Склад во втором запросе нигде не участвует. СКД считает поле Склад лишним и автоматически вырезает его из первого запроса при формировании временной таблицы.
3. Итоговый эффект. Первый запрос после оптимизации СКД превращается в выборку только одного поля Номенклатура и вычисляет остаток свернуто по всей базе без учета складов. Условие Товары.КоличествоКонечныйОстаток < 0 применяется уже к свернутому результату.

Важно отметить, что стандартные средства управления полями не помогают обойти эту оптимизацию:

1. Снятие флага «Автозаполнение» отключает только автоматическое добавление условий и полей компоновщика, но не выключает оптимизатор неиспользуемых полей временных таблиц.
2. Установка флажков «Обязательное» или указание роли «Измерение» на вкладке «Наборы данных» влияет только на результирующие поля отчета и игнорируется для промежуточных временных таблиц пакета.

Способы решения проблемы

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

Вариант 1. Фильтрация по кортежу полей (составному ключу)

Самый простой и лаконичный способ — явно связать оба измерения в условии отбора второго запроса. Когда поле Склад фигурирует во втором запросе, оптимизатор СКД не имеет права удалить его из временной таблицы:

ВЫБРАТЬ
    Товары.Склад КАК Склад,
    Товары.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ТоварыСминусом
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(
            ,
            КоличествоОстаток < 0) КАК Товары
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ТоварыНаСкладахОстатки.Склад КАК Склад,
    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(
            ,
            (Склад, Номенклатура) В
                (ВЫБРАТЬ
                    ВТ.Склад,
                    ВТ.Номенклатура
                ИЗ
                    ТоварыСминусом КАК ВТ)) КАК ТоварыНаСкладахОстатки

Вариант 2. Использование внутреннего соединения

Вместо передачи списка во входящие параметры виртуальной таблицы можно связать результирующую выборку с временной таблицей через ВНУТРЕННЕЕ СОЕДИНЕНИЕ по всем ключевым измерениям:

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

Вариант 3. Использование вложенного запроса вместо временной таблицы

Если логика отчета требует отобрать именно всю номенклатуру (по всем складам), у которой есть минус хотя бы на одном складе, можно отказаться от временной таблицы в пользу вложенного подзапроса в секции ГДЕ:

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

Вариант 4. Добавление фиктивного условия во второй запрос

Если необходимо использовать именно конструкцию с временной таблицей и фильтрацией только по номенклатуре, заставим оптимизатор сохранить поле Склад с помощью фиктивного условия:

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

Рекомендации по оптимизации производительности

Посмотрим на частую ошибку при получении среза данных: в исходных запросах нередко обращаются к таблице ОстаткиИОбороты, запрашивая только поле КоличествоКонечныйОстаток. Таблица ОстаткиИОбороты предназначена для получения оборотов и динамики за период, поэтому обращение к ней создает повышенную нагрузку на СУБД. Всегда, когда требуется только актуальный остаток (или остаток на конкретную дату), следует использовать виртуальную таблицу Остатки с фильтрацией по ресурсам в параметрах таблицы (УсловиеОтбора), а не в секции ГДЕ.