Почему изменяется количество строк и сумма при обращении к виртуальной таблице остатков в запросе 1С?

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

Анализ проблемы на практическом примере

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

ВЫБРАТЬ РАЗРЕШЕННЫЕ
    ВзаиморасчетыСКонтрагентамиОстатки.Контрагент КАК Контрагент,
    ВзаиморасчетыСКонтрагентамиОстатки.ДоговорКонтрагента КАК ДоговорКонтрагента,
    ВзаиморасчетыСКонтрагентамиОстатки.Сделка КАК Сделка,
    ВзаиморасчетыСКонтрагентамиОстатки.СуммаУпрОстаток КАК СуммаУпр,
    ВЫБОР
        КОГДА &ПереключательЗадолженность = 1
            ТОГДА ВзаиморасчетыСКонтрагентамиОстаткиНужногоТипа.СуммаВзаиморасчетовОстаток > 0
        ИНАЧЕ ИСТИНА
    КОНЕЦ КАК ПОЛЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕ
ИЗ
    РегистрНакопления.ВзаиморасчетыСКонтрагентами.Остатки() КАК ВзаиморасчетыСКонтрагентамиОстатки
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ
        РегистрНакопления.ВзаиморасчетыСКонтрагентами.Остатки() КАК ВзаиморасчетыСКонтрагентамиОстаткиНужногоТипа
        ПО ВзаиморасчетыСКонтрагентамиОстатки.ДоговорКонтрагента = ВзаиморасчетыСКонтрагентамиОстаткиНужногоТипа.ДоговорКонтрагента
        И ВзаиморасчетыСКонтрагентамиОстатки.Сделка = ВзаиморасчетыСКонтрагентамиОстаткиНужногоТипа.Сделка

Если закомментировать вычисляемое поле, в котором задействован ресурс СуммаВзаиморасчетовОстаток из второй таблицы, результат запроса возвращает большее количество строк и иную общую сумму по полю СуммаУпр. При этом параметр &ПереключательЗадолженность равен 0 (то есть ветка ТОГДА не должна была отсекать данные).

Причина: скрытая оптимизация виртуальных таблиц остатков платформой

Проанализируем, как платформа 1С транслирует запросы к виртуальным таблицам Остатки() в реальный SQL-запрос к СУБД:

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

Варианты корректного решения

Разберем способы устранения проблемы в зависимости от архитектуры вашего запроса.

Вариант 1. Отказ от самосоединения и получение всех данных из одного источника

Наиболее производительный и правильный подход — не соединять таблицу остатков саму с собой, а получать все необходимые измерения и ресурсы из одного экземпляра виртуальной таблицы, накладывая условия фильтрации в секции ГДЕ:

ВЫБРАТЬ РАЗРЕШЕННЫЕ
    ВзаиморасчетыОстатки.Контрагент КАК Контрагент,
    ВзаиморасчетыОстатки.ДоговорКонтрагента КАК ДоговорКонтрагента,
    ВзаиморасчетыОстатки.Сделка КАК Сделка,
    ВзаиморасчетыОстатки.СуммаУпрОстаток КАК СуммаУпр
ИЗ
    РегистрНакопления.ВзаиморасчетыСКонтрагентами.Остатки(&ДатаКон, ) КАК ВзаиморасчетыОстатки
ГДЕ
    ВЫБОР
        КОГДА &ПереключательЗадолженность = 1 
            ТОГДА ВзаиморасчетыОстатки.СуммаВзаиморасчетовОстаток > 0
        КОГДА &ПереключательЗадолженность = 2 
            ТОГДА ВзаиморасчетыОстатки.СуммаВзаиморасчетовОстаток < 0
        ИНАЧЕ ИСТИНА
    КОНЕЦ

Преимущества: СУБД выполняет одно обращение к таблице итогов, исключаются лишние соединения и непредсказуемое поведение оптимизатора 1С.

Вариант 2. Использование временных таблиц

Если структура запроса требует сложных связей или многократного использования среза остатков, рекомендуется сначала зафиксировать данные во временной таблице с помощью конструкции ПОМЕСТИТЬ:

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Контрагент КАК Контрагент,
    ВТ.ДоговорКонтрагента КАК ДоговорКонтрагента,
    ВТ.Сделка КАК Сделка,
    ВТ.СуммаУпр КАК СуммаУпр
ИЗ
    ВТ_Остатки КАК ВТ
ГДЕ
    ВЫБОР
        КОГДА &ПереключательЗадолженность = 1 
            ТОГДА ВТ.СуммаВзаиморасчетов > 0
        КОГДА &ПереключательЗадолженность = 2 
            ТОГДА ВТ.СуммаВзаиморасчетов < 0
        ИНАЧЕ ИСТИНА
    КОНЕЦ

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

Вариант 3. Принудительное включение ресурсов во вторую таблицу (быстрый фикс)

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

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

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