Как в запросе 1С отфильтровать пустые значения составного типа данных?

← На главную

При работе с запросами в платформе «1С:Предприятие» разработчики часто сталкиваются с ситуацией, когда стандартные условия фильтрации пустых значений не дают ожидаемого результата. Особенно остро эта проблема проявляется при работе со свойствами, имеющими составной тип данных. Ярким примером служит механизм дополнительных реквизитов и сведений, реализованный в Библиотеке Стандартных Подсистем (БСП). В этой статье мы подробно разберем, почему простые проверки на равенство пустой строке или НЕОПРЕДЕЛЕНО не работают для незаполненных свойств, как устроен составной тип данных "под капотом" платформы и СУБД, и как правильно составить производительный запрос с учетом всех нюансов.

Выясняем причину: почему стандартные проверки не работают

Рассмотрим стандартный сценарий. Мы хотим получить список дополнительных сведений для определенного объекта (например, серии номенклатуры), исключив при этом все незаполненные свойства. В регистре сведений ДополнительныеСведения ресурс Значение имеет составной тип данных, который может включать числа, строки, даты, булевы значения, а также ссылки на различные справочники конфигурации (включая Справочник.ДополнительныеЗначения).

Когда мы пытаемся отсечь незаполненные значения стандартными условиями:


И регДополнительныеСведения.Значение <> НЕОПРЕДЕЛЕНО
И регДополнительныеСведения.Значение <> ""
И регДополнительныеСведения.Значение <> 0

Мы замечаем, что свойство «Материал» (которое визуально не заполнено в пользовательском интерфейсе) все равно выводится в результат запроса. Почему так происходит?

Дело во внутреннем устройстве работы платформы с составными типами. Если для дополнительного реквизита назначен тип значения «Дополнительное значение» (который ссылается на подчиненный справочник дополнительных значений), то в базе данных незаполненное состояние этого реквизита сохраняется не как абстрактное пустое значение (НЕОПРЕДЕЛЕНО или NULL), а как пустая ссылка конкретного типа данных.

В нашем случае в ресурсе Значение хранится вполне конкретная пустая ссылка: ЗНАЧЕНИЕ(Справочник.ДополнительныеЗначения.ПустаяСсылка).

Так как пустая ссылка — это полноценный объект типа «СправочникСсылка», она успешно проходит все наши проверки:

Пустая ссылка не равна значению НЕОПРЕДЕЛЕНО (так как тип определен).
Пустая ссылка не равна пустой строке "".
Пустая ссылка не равна числу 0.

В результате СУБД считает это условие истинным и возвращает пустую строку свойства в выборку запроса.

Как определить тип пустого значения в запросе

Прежде чем писать условия отбора, нам необходимо точно узнать, какой тип данных возвращается для незаполненного свойства. Для этого мы можем воспользоваться встроенной функцией запроса ТИПЗНАЧЕНИЯ. Выполним диагностический запрос, чтобы проанализировать типы данных в нашей таблице:


ВЫБРАТЬ
    регДополнительныеСведения.Свойство.Заголовок КАК Свойство,
    ТИПЗНАЧЕНИЯ(регДополнительныеСведения.Значение) КАК ТипЗначения
ИЗ
    Справочник.СерияТоваров КАК спрСерияТоваров
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК регДополнительныеСведения
        ПО спрСерияТоваров.Ссылка = регДополнительныеСведения.Объект
ГДЕ
    спрСерияТоваров.Ссылка = &СерияТовара

Выполнив этот запрос, мы увидим, что для незаполненного свойства «Материал» колонка ТипЗначения вернет тип Справочник.ДополнительныеЗначения (или Справочник.ДополнительныеЗначенияИерархия). Теперь, когда истинная природа пустого значения нам известна, мы можем составить правильное условие отбора.

Способ 1. Точечное сравнение с пустыми ссылками (Рекомендуемый)

Самым простым и в то же время максимально производительным способом решения проблемы является прямое сравнение значения с пустой ссылкой конкретного справочника. Для этого в языке запросов используется конструкция ЗНАЧЕНИЕ():


И регДополнительныеСведения.Значение <> ЗНАЧЕНИЕ(Справочник.ДополнительныеЗначения.ПустаяСсылка)
И регДополнительныеСведения.Значение <> ЗНАЧЕНИЕ(Справочник.ДополнительныеЗначенияИерархия.ПустаяСсылка)

Преимущества этого подхода:

Высокая скорость выполнения на уровне СУБД. Запрос транслируется в SQL напрямую, сравнивая значения идентификаторов (UUID), состоящих из нулей.
Отсутствие избыточных соединений с физическими таблицами базы данных.

Способ 2. Использование функции ВЫРАЗИТЬ (Оптимальный компромисс)

Если мы хотим жестко ограничить составной тип данных до конкретного типа, чтобы избежать неопределенности при чтении полей, мы можем использовать функцию ВЫРАЗИТЬ. Это полезно, когда в рамках запроса нам требуется не просто отфильтровать пустые значения, но и обратиться к реквизитам этого значения:


И ВЫРАЗИТЬ(регДополнительныеСведения.Значение КАК Справочник.ДополнительныеЗначения) <> ЗНАЧЕНИЕ(Справочник.ДополнительныеЗначения.ПустаяСсылка)

Как это работает: Функция ВЫРАЗИТЬ приводит поле составного типа к одиночному типу данных. Если в поле хранилось значение другого типа, функция вернет NULL, который также успешно отсечется при сравнении. При этом СУБД будет обращаться только к одной таблице справочника, что положительно сказывается на производительности.

Критический антипаттерн: почему нельзя использовать ".Ссылка ЕСТЬ NULL"

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


// ВНИМАНИЕ! ТАК ДЕЛАТЬ НЕ РЕКОМЕНДУЕТСЯ!
НЕ регДополнительныеСведения.Значение.Ссылка ЕСТЬ NULL

Давайте проанализируем, почему этот прием является опасным антипаттерном производительности. Поле Значение в регистре дополнительных сведений имеет очень широкий составной тип (десятки различных справочников и документов). Когда платформа 1С видит обращение через точку к реквизиту поля составного типа (в данном случае к свойству Ссылка), она вынуждена сгенерировать SQL-запрос, содержащий неявные левые соединения (LEFT JOIN) ко всем таблицам, типы которых входят в это составное поле.

В результате простой запрос превращается в монстра с десятками левых соединений в SQL-коде. Это приводит к:

Колоссальному росту нагрузки на процессор сервера СУБД.
Неоптимальному выбору плана выполнения запроса СУБД.
Возможному превышению лимита на количество соединений таблиц в SQL-запросе.
Существенному замедлению работы всей системы в целом.

Поэтому всегда избегайте обращения через точку к полям широкого составного типа в условиях запроса.

Ловушка ЛЕВОГО СОЕДИНЕНИЯ в исходном запросе

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


ИЗ
    Справочник.СерияТоваров КАК спрСерияТоваров
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК reгДополнительныеСведения
        ПО спрСерияТоваров.Ссылка = регДополнительныеСведения.Объект
ГДЕ
    спрСерияТоваров.Ссылка = &СерияТовара
    И регДополнительныеСведения.Значение <> НЕОПРЕДЕЛЕНО // Фильтрация по правой таблице!

Проанализируем ситуацию: Мы используем левое соединение, чтобы получить все серии товаров, независимо от того, заполнены ли для них дополнительные сведения. Однако в секции ГДЕ мы накладываем жесткие фильтры на поля присоединяемой (правой) таблицы регДополнительныеСведения.

На уровне СУБД наложение условий на правую таблицу в секции ГДЕ (WHERE) автоматически превращает левое соединение (LEFT JOIN) во внутреннее соединение (INNER JOIN). Если у какой-то серии товаров вообще не окажется ни одной записи в регистре ДополнительныеСведения, то в результате соединения поля правой таблицы заполнятся значениями NULL. Проверка NULL <> НЕОПРЕДЕЛЕНО в секции ГДЕ вернет ложь, и вся серия товара полностью исчезнет из результатов запроса! Это в корне нарушает логику левого соединения.

Как решить эту проблему: Все условия отбора, накладываемые на присоединяемую (правую) таблицу, необходимо переносить непосредственно в условие связи ПО (ON). В секции ГДЕ должны оставаться только отборы по основной (левой) таблице.

Собираем идеальный запрос по шагам

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

Посмотрим на итоговый вариант запроса:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	регДополнительныеСведения.Свойство.Заголовок КАК Свойство,
    |	регДополнительныеСведения.Значение КАК Значение
    |ИЗ
    |	Справочник.СерияТоваров КАК спрСерияТоваров
    |		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК регДополнительныеСведения
    |			ПО спрСерияТоваров.Ссылка = регДополнительныеСведения.Объект
    |				И регДополнительныеСведения.Значение <> НЕОПРЕДЕЛЕНО
    |				И регДополнительныеСведения.Значение <> """"
    |				И регДополнительныеСведения.Значение <> 0
    |				И регДополнительныеСведения.Значение <> ЗНАЧЕНИЕ(Справочник.ДополнительныеЗначения.ПустаяСсылка)
    |				И регДополнительныеСведения.Значение <> ЗНАЧЕНИЕ(Справочник.ДополнительныеЗначенияИерархия.ПустаяСсылка)
    |ГДЕ
    |	спрСерияТоваров.Ссылка = &СерияТовара";

Запрос.УстановитьПараметр("СерияТовара", ВыбраннаяСерия);
РезультатЗапроса = Запрос.Выполнить();

При таком подходе мы гарантируем, что:

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

Используйте эти рекомендации в своей повседневной практике разработки, и ваши запросы в 1С всегда будут работать быстро и возвращать корректные данные!