Каждый разработчик 1С рано или поздно сталкивается с так называемыми «паранормальными» явлениями в Системе компоновки данных (СКД). Вроде бы запрос написан абсолютно корректно, в консоли запросов он возвращает полные и правильные данные, да и в самом отчете СКД при выводе общего списка все строки заполнены идеально. Но стоит пользователю наложить простейший отбор по одной конкретной строке (например, по торговой точке или партнеру), как часть связанных данных — например, цены, соглашения, адреса или телефоны — бесследно исчезает из результирующей строки. Выводится пустая ячейка или значение NULL.
В этой статье мы подробно разберем, почему СКД ведет себя подобным образом, детально проанализируем механизм оптимизации запросов при включенном автозаполнении, рассмотрим два эффективных способа решения этой проблемы на практическом примере и заглянем в другие «паранормальные зоны» СКД, связанные с виртуальными таблицами регистров.
Проанализируем ситуацию на основе реального примера. У нас есть пакетный запрос, состоящий из нескольких временных таблиц:
Справочник.Партнеры. Поле ссылки на партнера называется по умолчанию Ссылка.Ссылка.Справочник.СоглашенияСКлиентами. В этой таблице поле ссылки на само соглашение называется Ссылка, а поле ссылки на партнера — Партнер.ВтПартнерыСКонтактами) и таблицы соглашений (ВТАктуальныеСоглашения) по условию равенства партнеров: ВтПартнерыСКонтактами.Ссылка = ВТАктуальныеСоглашения.Partner.Когда мы формируем отчет без отборов, СКД выполняет этот пакет запросов последовательно, соединение отрабатывает корректно, и мы видим в отчете заполненный тип цены из соглашения.
Но что происходит, когда пользователь устанавливает отбор по полю Ссылка (которое в представлении отчета логично называется «Торговая точка» или «Партнер»)?
По умолчанию в СКД включена опция Автозаполнение. Пытаясь оптимизировать итоговый SQL-запрос для СУБД, СКД анализирует весь пакет запросов. Она видит, что пользователь наложил отбор на поле с именем Ссылка. СКД «сканирует» временные таблицы пакета и обнаруживает, что поле с именем Ссылка присутствует и в первой таблице (где это ссылка на партнера), и в третьей таблице (где это ссылка на соглашение!).
СКД ошибочно предполагает, что это одно и то же сущностное поле, и в целях оптимизации принудительно внедряет условие отбора на самый ранний этап формирования данных — во временные таблицы. В итоге в третий запрос пакета (в таблицу соглашений) автоматически добавляется условие вида:
ГДЕ СоглашенияСКлиентами.Ссылка = &ПараметрОтбораПартнер
Поскольку значением параметра &ПараметрОтбораПартнер является ссылка на конкретного партнера, а поле СоглашенияСКлиентами.Ссылка хранит ссылки на соглашения, это условие никогда не вернет истину. Ссылка на партнера физически не может быть равна ссылке на соглашение (это разные типы данных). В результате временная таблица ВТАктуальныеСоглашения оказывается абсолютно пустой. При финальном левом соединении по этому партнеру просто не находится ни одного соглашения, и колонка с типом цены возвращает пустоту (NULL).
Рассмотрим два надежных способа обойти эту особенность работы оптимизатора СКД.
Для того чтобы СКД не путала разнородные сущности во временных таблицах пакета запроса, мы должны отказаться от одинаковых имен полей. Каждому полю во всех временных таблицах необходимо дать уникальный и понятный псевдоним.
Выполним это переименование по шагам:
Ссылка справочника партнеров в Партнер или ТорговаяТочка.Ссылка соглашения переименуем в СоглашениеСсылка или просто Соглашение.Посмотрим на пример измененного, полностью работоспособного запроса:
ВЫБРАТЬ
Партнеры.Ссылка КАК Партнер,
Партнеры.БизнесРегион КАК БизнесРегион,
Партнеры.Наименование КАК Наименование,
ВЫБОР
КОГДА Партнеры.Родитель = ЗНАЧЕНИЕ(Справочник.Партнеры.ПустаяСсылка)
ТОГДА Партнеры.Ссылка
ИНАЧЕ Партнеры.Родитель
КОНЕЦ КАК Родитель,
Партнеры.Код КАК Код
ПОМЕСТИТЬ ВТПартнеры
ИЗ
Справочник.Партнеры КАК Партнеры
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТПартнеры.Партнер КАК Партнер,
ВТПартнеры.БизнесРегион КАК БизнесРегион,
ВТПартнеры.Наименование КАК Наименование,
ВТПартнеры.Код КАК Код,
ПартнерыКонтактнаяИнформация.Тип КАК Тип,
ПартнерыКонтактнаяИнформация.Вид КАК Вид,
ПартнерыКонтактнаяИнформация.Представление КАК Представление,
ПартнерыКонтактнаяИнформация.ЗначенияПолей КАК ЗначенияПолей,
ПартнерыКонтактнаяИнформация.Страна КАК Страна,
ПартнерыКонтактнаяИнформация.Регион КАК Регион,
ПартнерыКонтактнаяИнформация.Город КАК Город,
ПартнерыКонтактнаяИнформация.АдресЭП КАК АдресЭП,
ВТПартнеры.Родитель КАК Родитель
ПОМЕСТИТЬ ВтПартнерыСКонтактами
ИЗ
ВТПартнеры КАК ВТПартнеры
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Партнеры.КонтактнаяИнформация КАК ПартнерыКонтактнаяИнформация
ПО ВТПартнеры.Партнер = ПартнерыКонтактнаяИнформация.Ссылка
ГДЕ
ПартнерыКонтактнаяИнформация.Тип = ЗНАЧЕНИЕ(Перечисление.ТипыКонтактнойИнформации.Адрес)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СоглашенияСКлиентами.Ссылка КАК Соглашение,
СоглашенияСКлиентами.ВидЦен КАК ВидЦен,
СоглашенияСКлиентами.Партнер КАК Партнер
ПОМЕСТИТЬ ВТАктуальныеСоглашения
ИЗ
Справочник.СоглашенияСКлиентами КАК СоглашенияСКлиентами
ГДЕ
СоглашенияСКлиентами.Статус = ЗНАЧЕНИЕ(Перечисление.СтатусыСоглашенийСКлиентами.Действует)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВтПартнерыСКонтактами.Партнер КАК ТорговаяТочка,
ВтПартнерыСКонтактами.БизнесРегион КАК БизнесРегион,
ВтПартнерыСКонтактами.Наименование КАК Наименование,
ВтПартнерыСКонтактами.Код КАК Код,
ВтПартнерыСКонтактами.Тип КАК Тип,
ВтПартнерыСКонтактами.Вид КАК Вид,
ВтПартнерыСКонтактами.Представление КАК Представление,
ВтПартнерыСКонтактами.ЗначенияПолей КАК ЗначенияПолей,
ВтПартнерыСКонтактами.Страна КАК Страна,
ВтПартнерыСКонтактами.Регион КАК Регион,
ВтПартнерыСКонтактами.Город КАК Город,
ВтПартнерыСКонтактами.АдресЭП КАК АдресЭП,
ВТАктуальныеСоглашения.ВидЦен КАК ВидЦен,
ВТАктуальныеСоглашения.Соглашение КАК СсылкаНаСоглашение,
ВтПартнерыСКонтактами.Родитель КАК Родитель
ИЗ
ВтПартнерыСКонтактами КАК ВтПартнерыСКонтактами
ЛЕВОЕ СОЕДИНЕНИЕ ВТАктуальныеСоглашения КАК ВТАктуальныеСоглашения
ПО ВтПартнерыСКонтактами.Партнер = ВТАктуальныеСоглашения.Партнер
Теперь, когда пользователь наложит отбор по полю ТорговаяТочка, СКД четко поймет, что это поле соответствует полю Партнер в таблице ВТПартнеры и в таблице ВТАктуальныеСоглашения. Оптимизатор корректно наложит условия отбора по полям связи, не вызывая конфликта типов данных.
Если по каким-то причинам изменять имена полей в самом запросе нежелательно, мы можем явно объяснить системе компоновки данных, какое поле за что отвечает. Для этого используется механизм расширения языка запросов СКД, записываемый в фигурных скобках {}.
Для решения нашей проблемы перепишем третий запрос пакета с использованием конструкции {ГДЕ}:
ВЫБРАТЬ
СоглашенияСКлиентами.Ссылка КАК Ссылка,
СоглашенияСКлиентами.ВидЦен КАК ВидЦен,
СоглашенияСКлиентами.Партнер КАК Партнер
ПОМЕСТИТЬ ВТАктуальныеСоглашения
ИЗ
Справочник.СоглашенияСКлиентами КАК СоглашенияСКлиентами
ГДЕ
СоглашенияСКлиентами.Статус = ЗНАЧЕНИЕ(Перечисление.СтатусыСоглашенийСКлиентами.Действует)
{ГДЕ СоглашенияСКлиентами.Ссылка.* КАК СсылкаНаСоглашение,
СоглашенияСКлиентами.Партнер.* КАК Ссылка}
Разберем подробнее, что означает эта запись в фигурных скобках:
{ГДЕ ...} указывает СКД правила наложения необязательных отборов. Если пользователь не выбирает эти отборы в настройках отчета, то код внутри фигурных скобок полностью игнорируется при генерации SQL.СоглашенияСКлиентами.Ссылка.* КАК СсылкаНаСоглашение явно привязывает отборы по соглашению к новому псевдониму, отличному от «Ссылка».СоглашенияСКлиентами.Партнер.* КАК Ссылка дает СКД четкую команду: «Если пользователь устанавливает отбор по полю Ссылка (которое для него является торговой точкой или партнером), то внутри этой временной таблицы данный отбор нужно применить к полю Партнер».Настроить эти параметры можно как вручную в тексте запроса, так и в Конструкторе запроса СКД на вкладке «Компоновка данных» на закладке «Условия».
У начинающих разработчиков часто возникает вопрос: для чего нужны вкладки «Поля» и «Условия» в меню «Компоновка данных» конструктора запросов, и почему там тоже генерируются фигурные скобки с {ВЫБРАТЬ ...} и {ГДЕ ...}?
Эти вкладки служат для управления необязательными элементами запроса, которые СКД может динамически включать или исключать в зависимости от настроек отчета, сделанных пользователем. Это критически важно для оптимизации производительности тяжелых отчетов.
Вкладка «Поля» (конструкция {ВЫБРАТЬ ...}):
Позволяет объявить поля запроса необязательными для выбора. Если в вашем запросе рассчитываются сложные формулы или выполняются тяжелые соединения, но пользователь в настройках структуры отчета (на вкладке «Выбранные поля») не выбрал эти поля для вывода, то благодаря конструкции {ВЫБРАТЬ ...} СКД полностью исключит расчет этих полей из итогового SQL-запроса к СУБД. Это позволяет значительно сэкономить ресурсы сервера.
Вкладка «Условия» (конструкция {ГДЕ ...}):
Определяет места, куда СКД должна внедрить условия отбора, если пользователь задал их в настройках. Без этой конструкции СКД пытается разместить отборы автоматически (что часто приводит к ошибкам, рассмотренным выше). С помощью явного указания {ГДЕ} мы жестко контролируем поведение оптимизатора отборов.
В процессе разработки отчетов важно помнить, что оптимизатор СКД ведет себя специфически не только с временными таблицами, но и с виртуальными таблицами регистров (такими как Остатки, Обороты, ОстаткиИОбороты). Проанализируем основные подводные камни, с которыми можно столкнуться.
1. Автоматический проброс отборов в параметры виртуальных таблиц
Если пользователь устанавливает отбор, например, по номенклатуре или складу, СКД автоматически пытается перенести этот отбор непосредственно в параметры виртуальной таблицы (внутрь круглых скобок условий получения остатков или оборотов). Это повышает скорость выполнения запроса на уровне СУБД.
Однако, если виртуальная таблица соединяется через ЛЕВОЕ СОЕДИНЕНИЕ с другой таблицей, и отбор накладывается на поле этой левой таблицы, автоматический перенос отборов может превратить левое соединение во внутреннее (INNER JOIN). Строки основной таблицы, по которым не оказалось записей в регистре, просто исчезнут из отчета. Чтобы этого не происходило, необходимо использовать уникальные псевдонимы или отключать флаг «Автозаполнение» для набора данных.
2. Конфликт имен в нескольких виртуальных таблицах
Если ваш пакет запросов содержит две разные виртуальные таблицы (например, остатки по складам и обороты по продажам), и в обеих есть измерение Номенклатура, то пользовательский отбор по номенклатуре СКД автоматически применит к обеим таблицам. Если по логике отчета вам требовалось отфильтровать только продажи, а остатки показать без фильтра, автоматика СКД нарушит этот замысел. Решением здесь также является явное переопределение полей компоновки данных.
3. Специфика параметров периодов
СКД автоматически создает стандартные параметры для виртуальных таблиц (Период, НачалоПериода, КонецПериода). Если вы вручную добавите в запрос параметры со схожими именами (например, &ДатаНачала), на вкладке параметров схемы компоновки данных могут появиться дублирующие строки, что запутает пользователя. Рекомендуется использовать стандартные имена параметров СКД и связывать их с вашими пользовательскими настройками на вкладке «Параметры» схемы.
Для того чтобы ваши отчеты всегда работали предсказуемо и быстро, старайтесь придерживаться следующих правил:
Ссылка во всех временных таблицах пакета запросов. Переименовывайте его в соответствии со смыслом сущности (ПартнерСсылка, СоглашениеСсылка, ДоговорСсылка).ГДЕ.