Почему запрос с остатками выдает разные данные в консоли запросов и в СКД?

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

Рассмотрим конкретный пример запроса, с которым возникла сложность:

ВЫБРАТЬ
    ТоварыНаСкладахОстатки.Склад КАК Склад,
    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    ТоварыНаСкладахОстатки.СерияНоменклатуры КАК Серия,
    ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) КАК Остаток,
    ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена,
    ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) * ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Стоимость,
    ЕСТЬNULL(ТоварыНаСкладахОстаткиСрез.КоличествоОстаток, 0) КАК ОстатокСрез,
    ЕСТЬNULL(ТоварыНаСкладахОстаткиСрез.КоличествоОстаток, 0) * ЕСТЬNULL(ЦеныНоменклатурыСрезПоследнихСрез.Цена, 0) КАК СтоимостьСрез
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(КОНЕЦПЕРИОДА(&Период, ДЕНЬ), СерияНоменклатуры.СрокГодности <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)) КАК ТоварыНаСкладахОстатки
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(КОНЕЦПЕРИОДА(&Период, ДЕНЬ), ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
        ПО ТоварыНаСкладахОстатки.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
            И ТоварыНаСкладахОстатки.ХарактеристикаНоменклатуры = ЦеныНоменклатурыСрезПоследних.ХарактеристикаНоменклатуры
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(КОНЕЦПЕРИОДА(&ПериодСрез, ДЕНЬ), СерияНоменклатуры.СрокГодности <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)) КАК ТоварыНаСкладахОстаткиСрез
        ПО ТоварыНаСкладахОстатки.Склад = ТоварыНаСкладахОстаткиСрез.Склад
            И ТоварыНаСкладахОстатки.Номенклатура = ТоварыНаСкладахОстаткиСрез.Номенклатура
            И ТоварыНаСкладахОстатки.ХарактеристикаНоменклатуры = ТоварыНаСкладахОстаткиСрез.ХарактеристикаНоменклатуры
            И ТоварыНаСкладахОстатки.СерияНоменклатуры = ТоварыНаСкладахОстаткиСрез.СерияНоменклатуры
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(КОНЕЦПЕРИОДА(&ПериодСрез, ДЕНЬ), ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследнихСрез
        ПО (ТоварыНаСкладахОстаткиСрез.Номенклатура = ЦеныНоменклатурыСрезПоследнихСрез.Номенклатура)
            И (ТоварыНаСкладахОстаткиСрез.ХарактеристикаНоменклатуры = ЦеныНоменклатурыСрезПоследнихСрез.ХарактеристикаНоменклатуры)

При тестировании в консоли запросов на определенные даты получаются верные значения: поле Остаток равно 40, а поле ОстатокСрез равно 0. Но как только этот же запрос запускается в СКД, оба поля принимают одинаковое значение — 40. Разберем по шагам, почему это происходит и как исправить поведение системы.

В чем причина различий: механизм автозаполнения СКД

Проанализируем ситуацию с точки зрения работы платформы 1С. В наборе данных-запросе по умолчанию включено свойство Автозаполнение. Когда СКД готовит итоговый запрос к выполнению, она модифицирует исходный текст:

1. Система анализирует виртуальные таблицы Остатки, СрезПоследних и СрезПервых.
2. Для таких таблиц СКД автоматически генерирует и регистрирует стандартный параметр Период.
3. Если в запросе хотя бы одна из таблиц явно использует параметр с именем &Период, компоновщик данных жестко связывает параметры всех однотипных виртуальных таблиц с этим стандартным системным параметром.
4. В результате пользовательский параметр &ПериодСрез игнорируется либо переопределяется значением параметра &Период. Реальный текст запроса, отправляемый в СУБД, обращается к виртуальным таблицам на одну и ту же дату, из-за чего остатки дублируются.

Увидеть сформированный текст запроса можно с помощью специализированных средств отладки (например, консоли компоновки данных), остановившись на этапе инициализации процессора компоновки.

Способ 1. Отказ от использования системного имени параметра (Рекомендуемый)

Самое простое и эффективное решение — исключить совпадение имен параметров запроса со стандартными именами СКД. Достаточно не использовать имя Период для параметров виртуальных таблиц, заменив их на уникальные имена, например, &ДатаОтчета и &ДатаСравнения.

Посмотрим на пример исправленного запроса:

ВЫБРАТЬ
    ТоварыНаСкладахОстатки.Склад КАК Склад,
    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    ТоварыНаСкладахОстатки.СерияНоменклатуры КАК Серия,
    ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) КАК Остаток,
    ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена,
    ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) * ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Стоимость,
    ЕСТЬNULL(ТоварыНаСкладахОстаткиСрез.КоличествоОстаток, 0) КАК ОстатокСрез,
    ЕСТЬNULL(ТоварыНаСкладахОстаткиСрез.КоличествоОстаток, 0) * ЕСТЬNULL(ЦеныНоменклатурыСрезПоследнихСрез.Цена, 0) КАК СтоимостьСрез
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(КОНЕЦПЕРИОДА(&ДатаОтчета, ДЕНЬ), СерияНоменклатуры.СрокГодности <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)) КАК ТоварыНаСкладахОстатки
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(КОНЕЦПЕРИОДА(&ДатаОтчета, ДЕНЬ), ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
        ПО ТоварыНаСкладахОстатки.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
            И ТоварыНаСкладахОстатки.ХарактеристикаНоменклатуры = ЦеныНоменклатурыСрезПоследних.ХарактеристикаНоменклатуры
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(КОНЕЦПЕРИОДА(&ДатаСравнения, ДЕНЬ), СерияНоменклатуры.СрокГодности <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)) КАК ТоварыНаСкладахОстаткиСрез
        ПО ТоварыНаСкладахОстатки.Склад = ТоварыНаСкладахОстаткиСрез.Склад
            И ТоварыНаСкладахОстатки.Номенклатура = ТоварыНаСкладахОстаткиСрез.Номенклатура
            И ТоварыНаСкладахОстатки.ХарактеристикаНоменклатуры = ТоварыНаСкладахОстаткиСрез.ХарактеристикаНоменклатуры
            И ТоварыНаСкладахОстатки.СерияНоменклатуры = ТоварыНаСкладахОстаткиСрез.СерияНоменклатуры
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(КОНЕЦПЕРИОДА(&ДатаСравнения, ДЕНЬ), ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследнихСрез
        ПО ТоварыНаСкладахОстаткиСрез.Номенклатура = ЦеныНоменклатурыСрезПоследнихСрез.Номенклатура
            И ТоварыНаСкладахОстаткиСрез.ХарактеристикаНоменклатуры = ЦеныНоменклатурыСрезПоследнихСрез.ХарактеристикаНоменклатуры

После такого переименования СКД создаст на вкладке «Параметры» два независимых поля: ДатаОтчета и ДатаСравнения, которые будут точно передаваться в соответствующие виртуальные таблицы без автоматической унификации дат.

Способ 2. Использование синтаксиса расширения языка СКД (фигурные скобки)

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

РегистрНакопления.ТоварыНаСкладах.Остатки({(&ДатаОтчета)}, ...)

Явное указание параметров в фигурных скобках (или настройка на вкладке «Компоновка данных» в конструкторе запроса) дает указание СКД применять именно эти параметры к данной виртуальной таблице, не пытаясь связать её с глобальным параметром периода.

Способ 3. Отключение автозаполнения в наборе данных

В свойствах набора данных-запроса можно снять флаг Автозаполнение. В этом случае компоновщик полностью прекращает модификацию текста запроса и подстановку стандартных параметров. Однако важно помнить: при отключении автозаполнения все доступные поля, отборы, сортировки и параметры придется описывать вручную с использованием синтаксиса расширения языка СКД в фигурных скобках {}.

Важные нюансы работы с датами и производительностью

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

1. Граница периода: Виртуальная таблица Остатки(&Период) рассчитывает срез на начало секунды переданной даты. Функция КОНЕЦПЕРИОДА(&Период, ДЕНЬ) возвращает время 23:59:59, что приведет к исключению движений, проведенных ровно в эту секунду. Чтобы включить все движения дня, к концу периода прибавляют 1 секунду либо передают в запрос параметр с типом Граница и видом ВидГраницы.Включая.
2. Оптимальность запроса: Левое соединение нескольких виртуальных таблиц остатков одного регистра является неоптимальной практикой. Если требуется получить остатки на две смежные даты (начало и конец интервала), правильнее использовать одну таблицу ОстаткиИОбороты и выбирать поля КоличествоНачальныйОстаток и КоличествоКонечныйОстаток, либо связать два набора данных через вкладку «Связи наборов данных» в СКД.