Разработчики отчетов на Системе компоновки данных (СКД) часто сталкиваются с загадочным поведением: запрос, который в обычной консоли запросов отрабатывает идеально и выводит все строки, в отчете СКД неожиданно «теряет» целые блоки данных. Особенно ярко эта проблема проявляется при использовании конструкции ОБЪЕДИНИТЬ ВСЕ (UNION ALL), когда данные трех веток объединения выводятся без проблем, а четвертая ветка возвращает пустой результат.
В этой статье мы подробно разберем, почему так происходит, как под капотом работает оптимизатор запросов СКД, почему добавление «ненужного» поля в выборку может внезапно «оживить» отчет и как правильно проектировать подобные запросы, чтобы избежать подобных проблем в будущем.
Рассмотрим классическую ситуацию. В СКД формируется временная таблица ОбъединениеРазделов, в которой через ОБЪЕДИНИТЬ ВСЕ собираются данные из четырех разных источников: материалы по поступлению, инвестиции, услуги и плановые показатели. Запрос выглядит следующим образом:
ВЫБРАТЬ
МатериалыПоступлениеИтоговая.Регистратор КАК Регистратор,
МатериалыПоступлениеИтоговая.Номенклатура КАК Номенклатура,
МатериалыПоступлениеИтоговая.Партия КАК Партия,
МатериалыПоступлениеИтоговая.Контрагент КАК Контрагент,
МатериалыПоступлениеИтоговая.СтатьяЗатрат КАК СтатьяЗатрат,
"Материалы" КАК Раздел,
"руб." КАК ВалютаДокумента,
МатериалыПоступлениеИтоговая.СуммаПоступило КАК СуммаПоступило,
МатериалыПоступлениеИтоговая.СуммаСНДСПоступило КАК СуммаСНДСПоступило,
МатериалыПоступлениеИтоговая.СуммаСписано КАК СуммаСписано,
МатериалыПоступлениеИтоговая.СуммаСНДССписано КАК СуммаСНДССписано,
МатериалыПоступлениеИтоговая.Организация КАК Организация,
МатериалыПоступлениеИтоговая.КоличествоСписано КАК КоличествоСписано,
МатериалыПоступлениеИтоговая.КоличествоПоступление КАК КоличествоПоступление,
0 КАК СуммаПлан,
0 КАК СуммаПланНДС,
0 КАК КоличествоПлан
ПОМЕСТИТЬ ОбъединениеРазделов
ИЗ
МатериалыПоступлениеИтоговая КАК МатериалыПоступлениеИтоговая
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ИнвестицииПоступление.Регистратор,
ИнвестицииПоступление.Номенклатура,
"Материалы не списаны",
ИнвестицииПоступление.Контрагент,
ИнвестицииПоступление.СтатьяИнвестиции,
"Инвестиции",
"руб.",
ИнвестицииПоступление.Сумма, 0, 0, 0,
ИнвестицииПоступление.Организация, 0,
ИнвестицииПоступление.Количество, 0, 0, 0
ИЗ
ИнвестицииПоступление КАК ИнвестицииПоступление
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
УслугиПоступление.Регистратор,
УслугиПоступление.Номенклатура,
"Услуги",
УслугиПоступление.Контрагент,
УслугиПоступление.СтатьяЗатрат,
"Услуги",
УслугиПоступление.ВалютаДокумента,
УслугиПоступление.Сумма, 0,
УслугиПоступление.Сумма, 0,
УслугиПоступление.Организация,
УслугиПоступление.Количество,
УслугиПоступление.Количество, 0, 0, 0
ИЗ
УслугиПоступление КАК УслугиПоступление
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
МатериалыПлан.Регистратор,
МатериалыПлан.Номенклатура,
"План",
МатериалыПлан.Контрагент,
"План",
"Плановый закуп",
МатериалыПлан.Регистратор.ВалютаДокумента,
0, 0, 0, 0,
МатериалыПлан.Организация, 0, 0,
МатериалыПлан.Сумма,
МатериалыПлан.СуммаСНДС,
МатериалыПлан.Количество
ИЗ
МатериалыПлан КАК МатериалыПлан
Проблема заключается в том, что ветка УслугиПоступление упорно не выводит данные в отчет. При этом в обычной консоли запросов вся выборка заполняется корректно. Более того, если в список выбираемых полей этой ветки добавить техническое поле, например УслугиПоступление.СуммаБУ, которое изначально не планировалось выводить в финальный отчет, данные по услугам начинают отображаться.
Давайте разберем внутреннее устройство временной таблицы УслугиПоступление, чтобы понять истоки проблемы:
ВЫБРАТЬ
ХозрасчетныйОборотыДтКт.Регистратор КАК Регистратор,
ХозрасчетныйОборотыДтКт.СуммаОборот КАК СуммаБУ,
ХозрасчетныйОборотыДтКт.СубконтоКт1 КАК Контрагент,
ПоступлениеТоваровУслугУслуги.Номенклатура КАК Номенклатура,
ВЫБОР
КОГДА ПоступлениеТоваровУслугУслуги.Ссылка.СуммаВключаетНДС
ТОГДА ПоступлениеТоваровУслугУслуги.Сумма
ИНАЧЕ ВЫБОР
КОГДА ПоступлениеТоваровУслугУслуги.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС0)
ИЛИ ПоступлениеТоваровУслугУслуги.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.БезНДС)
ТОГДА ПоступлениеТоваровУслугУслуги.Сумма
КОГДА ПоступлениеТоваровУслугУслуги.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС18)
ИЛИ ПоступлениеТоваровУслугУслуги.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС18_118)
ТОГДА ПоступлениеТоваровУслугУслуги.Сумма * 1.18
КОНЕЦ
КОНЕЦ КАК Сумма,
ПоступлениеТоваровУслугУслуги.Ссылка.ВалютаДокумента КАК ВалютаДокумента,
ХозрасчетныйОборотыДтКт.СубконтоДт2 КАК СтатьяЗатрат,
ХозрасчетныйОборотыДтКт.Организация КАК Организация,
ХозрасчетныйОборотыДтКт.КоличествоОборотДт КАК Количество
ПОМЕСТИТЬ УслугиПоступление
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(
&НачалоПериода, &КонецПериода, Регистратор,
СчетДт В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.ОсновноеПроизводство_))
ИЛИ СчетДт В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.ОбщехозяйственныеРасходы)), ,
СчетКт В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетыСПоставщикамиИПодрядчиками)), ,
) КАК ХозрасчетныйОборотыДтКт
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Документ.ПоступлениеТоваровУслуг.Услуги КАК ПоступлениеТоваровУслугУслуги
ПО
ХозрасчетныйОборотыДтКт.Регистратор = ПоступлениеТоваровУслугУслуги.Ссылка
Для того чтобы понять причину происходящего, нам нужно заглянуть под капот СКД. В отличие от стандартной работы с СУБД, когда платформа отправляет запрос «как есть», Система компоновки данных выполняет динамическую генерацию и оптимизацию текста запроса перед его выполнением.
Оптимизатор СКД анализирует структуру настроек отчета (выбранные поля, группировки, отборы) и пытается максимально облегчить итоговый SQL-запрос. Для этого применяется механизм исключения неиспользуемых таблиц.
УслугиПоступление используется ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN) между регистром бухгалтерии и табличной частью документа. Если в итоговом запросе СКД выбирает только поля из табличной части (например, Номенклатура, Сумма, ВалютаДокумента) и игнорирует специфические поля регистра (такие как СуммаОборот КАК СуммаБУ), оптимизатор СКД может «выбросить» обращение к регистру бухгалтерии из этой ветки.
Поскольку внутреннее соединение физически отсекает строки, не соответствующие условию связи, его исключение ломает логику выборки. СКД пытается оптимизировать этот узел, но из-за сложной логики объединения в итоговом SQL-запросе получается пустая выборка или неверный левый стык.УслугиПоступление.СуммаБУ в выборку, оптимизатор СКД «понимает», что данные из регистра бухгалтерии действительно нужны в результирующем наборе. Исключение таблицы регистра становится невозможным. Соединение отрабатывает в штатном режиме, и данные появляются в отчете.Регистратор в регистрах 1С:Предприятия всегда имеет составной тип данных. В то же время поле Ссылка табличной части документа имеет фиксированный тип (например, ДокументСсылка.ПоступлениеТоваровУслуг). Когда СУБД выполняет внутреннее соединение по полям с несовпадающими типами, оптимизатору СКД крайне тяжело адекватно проанализировать эту связь. Платформа начинает генерировать громоздкий SQL-код с кучей неявных соединений (LEFT JOIN) ко всем таблицам документов, входящих в составной тип регистратора, что часто приводит к логическим ошибкам при наложении системных отборов СКД (например, по периоду).Рассмотрим несколько эффективных способов решения этой проблемы, начиная от изменения логики запроса и заканчивая точечными настройками СКД.
Самый надежный и архитектурно правильный способ — перепроектировать запрос так, чтобы избежать соединения виртуальных таблиц с физическими табличными частями документов. Соединение табличной части документа с регистром бухгалтерии по полю Регистратор = Ссылка — это крайне «тяжелая» операция для СУБД, которая к тому же плохо поддается оптимизации.
Посмотрим, как можно переписать формирование временной таблицы УслугиПоступление, заменив табличную часть документа на левое соединение с регистром накопления (например, НДСПредъявленный.Остатки) или получая все данные напрямую из регистра бухгалтерии:
ВЫБРАТЬ
ХозрасчетныйОборотыДтКт.Регистратор КАК Регистратор,
ВЫБОР
КОГДА НДСПредъявленныйОстатки.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС0)
ИЛИ НДСПредъявленныйОстатки.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.БезНДС)
ТОГДА ХозрасчетныйОборотыДтКт.СуммаОборот
КОГДА НДСПредъявленныйОстатки.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС18)
ИЛИ НДСПредъявленныйОстатки.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС18_118)
ТОГДА ХозрасчетныйОборотыДтКт.СуммаОборот * 1.18
ИНАЧЕ ХозрасчетныйОборотыДтКт.СуммаОборот
КОНЕЦ КАК Сумма,
ХозрасчетныйОборотыДтКт.СубконтоКт1 КАК Контрагент,
ХозрасчетныйОборотыДтКт.СубконтоДт1 КАК Номенклатура,
ЕСТЬNULL(ХозрасчетныйОборотыДтКт.ВалютаКт, "руб.") КАК ВалютаДокумента,
ХозрасчетныйОборотыДтКт.СубконтоДт2 КАК СтатьяЗатрат,
ХозрасчетныйОборотыДтКт.Организация КАК Организация,
ЕСТЬNULL(ХозрасчетныйОборотыДтКт.КоличествоОборотДт, 0) КАК Количество,
НДСПредъявленныйОстатки.СтавкаНДС КАК СтавкаНДС
ПОМЕСТИТЬ УслугиПоступление
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(
&НачалоПериода, &КонецПериода, Регистратор,
СчетДт В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.ОсновноеПроизводство_))
ИЛИ СчетДт В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.ОбщехозяйственныеРасходы)), ,
СчетКт В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетыСПоставщикамиИПодрядчиками)), ,
) КАК ХозрасчетныйОборотыДтКт
ЛЕВОЕ СОЕДИНЕНИЕ
РегистрНакопления.НДСПредъявленный.Остатки КАК НДСПредъявленныйОстатки
ПО
ХозрасчетныйОборотыДтКт.Регистратор = НДСПредъявленныйОстатки.СчетФактура
Почему это работает лучше? Левое соединение с регистром накопления по четко типизированному полю СчетФактура (которое, в отличие от регистратора, имеет гораздо более узкий набор типов) не ломает логику оптимизатора СКД. Даже если оптимизатор решит исключить таблицу остатков НДС из запроса, основная таблица регистра бухгалтерии гарантированно останется в выборке, и строки не пропадут.
Если отказаться от соединения с табличной частью документа невозможно, крайне важно помочь оптимизатору СКД правильно сопоставить типы данных на уровне СУБД. Для этого в условии связи обязательно нужно использовать конструкцию ВЫРАЗИТЬ().
Проанализируем пример явного приведения типов:
ВЫБРАТЬ
ХозрасчетныйОборотыДтКт.Регистратор КАК Регистратор,
...
ПОМЕСТИТЬ УслугиПоступление
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(...) КАК ХозрасчетныйОборотыДтКт
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Документ.ПоступлениеТоваровУслуг.Услуги КАК ПоступлениеТоваровУслугУслуги
ПО
ВЫРАЗИТЬ(ХозрасчетныйОборотыДтКт.Регистратор КАК Документ.ПоступлениеТоваровУслуг) = ПоступлениеТоваровУслугУслуги.Ссылка
В чем польза этого подхода? Оператор ВЫРАЗИТЬ принудительно ограничивает составной тип поля Регистратор до одного конкретного типа документа. Платформа 1С прекращает строить избыточные внутренние SQL-соединения со всеми возможными документами-регистраторами, благодаря чему оптимизатор СКД корректно интерпретирует логику связи и не исключает таблицы из цепочки выполнения запроса.
Если мы хотим гарантировать, что СКД ни при каких обстоятельствах не исключит определенное поле или таблицу из итогового SQL-запроса (даже если пользователь отключил отображение этого поля в настройках отчета), мы можем воспользоваться встроенными механизмами контроля метаданных СКД.
Выполним следующие шаги для настройки обязательности поля:
Регистратор или ключевое поле связи СуммаБУ).Установка этого флага жестко указывает оптимизатору СКД: данное поле должно присутствовать в SQL-запросе всегда, вне зависимости от выбранных пользователем группировок и колонок. Это полностью предотвратит нежелательное вырезание связанных таблиц.
Для минимизации подобных ошибок при разработке сложных многоветочных отчетов рекомендуем придерживаться следующих правил:
Субконто (которые также являются составными) всегда используйте приведение типов: ВЫРАЗИТЬ(ХозрасчетныйОборотыДтКт.СубконтоДт1 КАК Справочник.Номенклатура).Соблюдение этих простых рекомендаций убережет вас от трудноуловимых ошибок при компоновке данных и сделает ваши отчеты производительными и надежными.