Каждый разработчик на платформе «1С:Предприятие» рано или поздно сталкивается со странным поведением системы при работе с запросами. Одна из самых "взрывающих котелок" ситуаций — когда один запрос, использующий прямую фильтрацию по массиву, отрабатывает идеально, а аналогичный запрос с использованием конструкции В (ВЫБРАТЬ Ссылка ИЗ ВременнаяТаблица) возвращает абсолютно пустой результат (тот самый «кукиш»).
В этой статье мы подробно разберем, почему так происходит, заглянем «под капот» СУБД и платформы «1С», а также рассмотрим правильные и надежные способы решения этой проблемы.
Проанализируем ситуацию, с которой столкнулся автор темы. В его распоряжении есть временная таблица Склады, созданная из реального справочника баз данных, и внешняя таблица значений &ТоварыНаПеремещение, переданная в запрос в качестве параметра.
Рассмотрим первый вариант запроса, который не работает (возвращает пустую выборку):
ВЫБРАТЬ
Склады.Ссылка КАК Ссылка
ПОМЕСТИТЬ Склады
ИЗ
Справочник.Склады КАК Склады
ГДЕ
Склады.Ссылка В(&Склады)
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТоварыНаПеремещение.СкладОтправитель КАК СкладОтправитель,
ТоварыНаПеремещение.СкладПолучатель КАК СкладПолучатель,
ТоварыНаПеремещение.Характеристика КАК Характеристика,
ТоварыНаПеремещение.Количество
ИЗ
&ТоварыНаПеремещение КАК ТоварыНаПеремещение
ГДЕ
ТоварыНаПеремещение.Номенклатура = &Номенклатура
И ТоварыНаПеремещение.СкладОтправитель В
(ВЫБРАТЬ
Склады.Ссылка
ИЗ
Склады КАК Склады)
И ТоварыНаПеремещение.СкладПолучатель В
(ВЫБРАТЬ
Склады.Ссылка
ИЗ
Склады КАК Склады)
А теперь посмотрим на второй вариант запроса, который работает исправно и возвращает ожидаемые данные:
ВЫБРАТЬ
Склады.Ссылка КАК Ссылка
ПОМЕСТИТЬ Склады
ИЗ
Справочник.Склады КАК Склады
ГДЕ
Склады.Ссылка В(&Склады)
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТоварыНаПеремещение.СкладОтправитель КАК СкладОтправитель,
ТоварыНаПеремещение.СкладПолучатель КАК СкладПолучатель,
ТоварыНаПеремещение.Характеристика КАК Характеристика,
ТоварыНаПеремещение.Количество
ИЗ
&ТоварыНаПеремещение КАК ТоварыНаПеремещение
ГДЕ
ТоварыНаПеремещение.Номенклатура = &Номенклатура
И ТоварыНаПеремещение.СкладОтправитель В(&Склады)
И ТоварыНаПеремещение.СкладПолучатель В(&Склады)
Возникает логичный вопрос: почему замена конструкции В (ВЫБРАТЬ Ссылка ИЗ ВТ) на простую передачу массива параметров В (&Склады) кардинально меняет результат выполнения запроса?
Для того чтобы понять природу этой аномалии, нам нужно разобрать механизмы типизации и трансляции запросов платформой «1С» на уровень СУБД (например, MS SQL Server или PostgreSQL).
&ТоварыНаПеремещение в качестве параметра запроса напрямую, платформа «1С» вынуждена "на лету" создавать для нее системную временную таблицу в СУБД. Если колонки этой таблицы значений не были строго типизированы при ее создании в коде 1С (то есть не было задано жесткое ОписаниеТипов), то СУБД создает эти поля с максимально обобщенными или бинарными типами (например, sql_variant или нетипизированный binary).В (ВЫБРАТЬ ...). СУБД транслирует эту конструкцию как полусоединение (Semi-Join). При попытке сопоставить данные из нетипизированной системной таблицы (созданной для передачи нашей ТЗ-параметра) со строго типизированным полем Ссылка таблицы Склады (которое в SQL является уникальным идентификатором типа binary(16)), оптимизатор СУБД не может выполнить неявное приведение типов. Сравнение разнородных типов данных на уровне SQL всегда возвращает значение FALSE (ложь). В результате СУБД успешно выполняет запрос, но отсекает все строки, возвращая пустой результат.IN (@P1, @P2...), где типы параметров уже заранее известны и жестко типизированы. В этом случае неявное приведение отрабатывает корректно.JOIN, объединения UNION, группировки GROUP BY и вложенные подзапросы) категорически не рекомендуется. Прямая работа с таблицей-параметром без явного помещения ее во временную таблицу очень часто приводит к непредсказуемому поведению оптимизатора СУБД и к потере производительности.Чтобы раз и навсегда избавить себя от подобных "чудес" в запросах, рассмотрим три основных практических метода, которые гарантируют корректную работу СУБД.
Это самый надежный, стандартный и архитектурно верный способ. Перед тем как накладывать любые сложные условия фильтрации, соединять таблицы или делать выборки, мы должны явно перенести нашу таблицу-параметр в пространство временных таблиц пакета.
Проанализируем правильную структуру пакета запросов:
// ШАГ 1: Помещаем исходную ТЗ во временную таблицу.
// На этом этапе платформа создает полноценную индексированную временную таблицу.
ВЫБРАТЬ
Товары.СкладОтправитель КАК СкладОтправитель,
Товары.СкладПолучатель КАК СкладПолучатель,
Товары.Характеристика КАК Характеристика,
Товары.Количество КАК Количество,
Товары.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_ТоварыНаПеремещение
ИЗ
&ТоварыНаПеремещение КАК Товары
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Создаем временную таблицу со складами
ВЫБРАТЬ
Склады.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Склады
ИЗ
Справочник.Склады КАК Склады
ГДЕ
Склады.Ссылка В(&Склады)
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 3: Теперь мы можем безопасно делать любые вложенные запросы
// к двум полноценным временным таблицам СУБД. Типы данных сопоставятся идеально!
ВЫБРАТЬ
ТоварыПеремещение.СкладОтправитель КАК СкладОтправитель,
ТоварыПеремещение.СкладПолучатель КАК СкладПолучатель,
ТоварыПеремещение.Характеристика КАК Характеристика,
ТоварыПеремещение.Количество
ИЗ
ВТ_ТоварыНаПеремещение КАК ТоварыПеремещение
ГДЕ
ТоварыПеремещение.Номенклатура = &Номенклатура
И ТоварыПеремещение.СкладОтправитель В
(ВЫБРАТЬ
ВТ_Склады.Ссылка
ИЗ
ВТ_Склады КАК ВТ_Склады)
И ТоварыПеремещение.СкладПолучатель В
(ВЫБРАТЬ
ВТ_Склады.Ссылка
ИЗ
ВТ_Склады КАК ВТ_Склады)
Если таблица значений наполняется динамически из разных источников и у платформы могут возникнуть сомнения в типах колонок, мы можем принудительно указать типы данных прямо в запросе при помещении во временную таблицу. Для этого задействуем функцию ВЫРАЗИТЬ.
Рассмотрим, как это выглядит на практике:
ВЫБРАТЬ
ВЫРАЗИТЬ(Товары.СкладОтправитель КАК Справочник.Склады) КАК СкладОтправитель,
ВЫРАЗИТЬ(Товары.СкладПолучатель КАК Справочник.Склады) КАК СкладПолучатель,
Товары.Характеристика КАК Характеристика,
Товары.Количество КАК Количество
ПОМЕСТИТЬ ВТ_ТоварыСТипами
ИЗ
&ТоварыНаПеремещение КАК Товары
;
////////////////////////////////////////////////////////////////////////////////
// Дальнейший запрос к ВТ_ТоварыСТипами отработает безошибочно,
// так как поля СкладОтправитель и СкладПолучатель на уровне СУБД
// теперь имеют строгий тип уникального идентификатора Справочника.Склады.
Лучшая профилактика подобных ошибок — создание изначально качественных объектов в коде программы. Если вы формируете таблицу значений ТоварыНаПеремещение программно, никогда не добавляйте колонки "просто так". Всегда явно описывайте типы данных.
Посмотрим на пример правильного программного кода создания таблицы значений перед передачей в параметры запроса:
// Неправильно:
// ТаблицаТоваров.Колонки.Add("СкладОтправитель"); // Колонка не имеет типа!
// Правильно:
ТаблицаТоваров = Новый ТаблицаЗначений;
// Формируем описание типов для ссылки на справочник
МассивТиповСклад = Новый Массив;
МассивТиповСклад.Добавить(Тип("СправочникСсылка.Склады"));
ОписаниеТиповСклад = Новый ОписаниеТипов(МассивТиповСклад);
// Добавляем строго типизированные колонки
ТаблицаТоваров.Колонки.Добавить("СкладОтправитель", ОписаниеТиповСклад);
ТаблицаТоваров.Колонки.Добавить("СкладПолучатель", ОписаниеТиповСклад);
ТаблицаТоваров.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число"));
// Заполняем таблицу и передаем в запрос...
Запрос.УстановитьПараметр("ТоварыНаПеремещение", ТаблицаТоваров);
При таком подходе платформа «1С» при трансляции запроса сразу создаст временную таблицу СУБД с корректными типами колонок, что автоматически решит любые проблемы со сравнением типов данных.
Для того чтобы ваши запросы всегда работали предсказуемо и быстро, старайтесь придерживаться следующих правил:
ВЫРАЗИТЬ при помещении во временную таблицу.ОписаниеТипов для каждой колонки.