Почему результат одного и того же запроса отличается в файловом и клиент-серверном режимах 1С?

← На главную

Нередко разработчики сталкиваются с ситуацией, когда запрос, идеально работающий в файловой информационной базе, при переносе на клиент-серверный вариант (например, под управлением PostgreSQL или Microsoft SQL Server) начинает возвращать дублирующиеся либо пустые строки. Разберем эту проблему на практическом примере, выясним причины такого поведения платформы и СУБД, а также рассмотрим правильные способы исправления запроса.

Анализ проблемного запроса

Рассмотрим исходный запрос, предназначенный для получения остатков номенклатуры, резервов и заказов в конфигурации «Управление торговлей»:


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

В файловой базе такой запрос возвращает ровно одну строку с рассчитанными суммами. Однако в клиент-серверной базе появляются лишние строки с пустым значением номенклатуры. Проанализируем, почему возникает такое поведение.

Выясним причину расхождений

Разница в поведении обусловлена несколькими фундаментальными факторами взаимодействия встроенного языка запросов 1С и реляционных СУБД:

Разрыв цепочки в последовательных полных соединениях (FULL JOIN). В реляционной алгебре внешние соединения не обладают свойством ассоциативности, если связывание идет строго по ключу первой таблицы. Обратим внимание на условие: ПО ТоварыНаСкладахОстатки.Номенклатура = ЗаказыПокупателейОстатки.Номенклатура. Если на чистых складах (без брака) остатка нет, то поле ТоварыНаСкладахОстатки.Номенклатура принимает значение NULL. Последующие таблицы пытаются соединиться по условию NULL = Заказы...Номенклатура, что возвращает значение Ложь. В итоге записи последующих таблиц не объединяются с предыдущими, а порождают новые строки с NULL в первой таблице.
Различия файлового движка и внешних СУБД. Встроенная файловая СУБД 1С самостоятельно обрабатывает цепочки объединений и нередко неявно «схлопывает» строки при совпадении условий. Внешние СУБД (в особенности PostgreSQL) выполняют строгую реляционную трансляцию SDBL-запроса, что при некорректно выстроенных соединениях приводит к расщеплению групп и появлению пустых строк.
Выборка поля, содержащего NULL, в группировке. В полях ВЫБРАТЬ и СГРУППИРОВАТЬ ПО указано поле ТоварыНаСкладахОстатки.Номенклатура. При полном соединении это поле обязано вычисляться через функцию ЕСТЬNULL() относительно всех соединяемых таблиц либо заменяться входным параметром. В противном случае результирующая номенклатура возвращает пустую ссылку.
Декартово произведение из-за неуказанных измерений. Вызов РегистрНакопления.ТоварыНаСкладах.Остатки(...) без указания конкретного списка измерений возвращает данные в разрезе всех имеющихся измерений регистра (склады, серии, характеристики). При наличии остатков на разных складах соединение «многие-ко-многим» по одной лишь номенклатуре приводит к дублированию строк и искажению сумм.

Решение 1. Использование конструкции «ОБЪЕДИНИТЬ ВСЕ» (рекомендуемый подход)

Стандарты разработки 1С запрещают прямое соединение нескольких виртуальных таблиц. Наиболее эффективный и надежный способ обойти проблемы внешних соединений — собрать данные из всех регистров через объединение с фиктивными колонками и последующей группировкой. Посмотрим на реализацию:


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

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

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

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

    ВЫБРАТЬ
        0,
        0,
        ЗаказыПок.КоличествоОстаток,
        0
    ИЗ
        РегистрНакопления.ЗаказыПокупателей.Остатки(&КонДата, Номенклатура = &Номенклатура) КАК ЗаказыПок

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

    ВЫБРАТЬ
        0,
        0,
        0,
        ЗаказыПост.КоличествоОстаток
    ИЗ
        РегистрНакопления.ЗаказыПоставщикам.Остатки(&КонДата, Номенклатура = &Номенклатура) КАК ЗаказыПост) КАК Вложенный

Такой запрос гарантированно отработает одинаково быстро и корректно как в файловой базе, так и на любых серверах СУБД, исключая декартово перемножение и появление пустых строк.

Решение 2. Оптимизация через однократное чтение и агрегацию с условием

Заметим, что обращаться к таблице РегистрНакопления.ТоварыНаСкладах.Остатки дважды нет необходимости. Мы можем прочитать таблицу один раз, передав измерение Склад, и разделить остатки с помощью условной конструкции ВЫБОР КОГДА. Это снизит нагрузку на серверную СУБД в два раза.

Решение 3. Построение цепочки ЛЕВЫХ СОЕДИНЕНИЙ от опорной таблицы

Если по архитектурным соображениям требуется сохранить структуру соединений, разберем по шагам, как сделать это правильно:

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

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