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