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

← На главную

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

В рамках этой статьи мы подробно разберем, почему возникает данная проблема на уровне СУБД и платформы 1С, как правильно типизировать данные «на берегу», как обойти ограничения с помощью функций языка запросов и как избавиться от сопутствующих ошибок, приводящих к получению пустого результата запроса.

Почему возникает ошибка сравнения полей неограниченной длины?

Для начала выясним причину поведения системы. Когда мы передаем таблицу значений во временную таблицу запроса, платформа 1С автоматически создает физическую временную таблицу в СУБД (Microsoft SQL Server, PostgreSQL, IBM DB2 или Oracle). Чтобы создать столбцы в этой таблице, системе необходимо сопоставить типы колонок из нашей таблицы значений с типами данных базы данных.

Если колонка таблицы значений была добавлена без жесткого указания типа (то есть имеет тип по умолчанию — строка неограниченной длины), платформа создает в СУБД поле типа NVARCHAR(MAX) или аналогичное СУБД-хранилище для больших текстовых блоков (LOB-объектов). СУБД накладывает жесткие ограничения на работу с такими полями:

Их невозможно индексировать.
Их категорически запрещено использовать в условиях связи таблиц (секция ПО оператора СОЕДИНЕНИЕ).
Их нельзя использовать в условиях фильтрации (секция ГДЕ) при операциях точного сравнения (=, <>).
Они не могут участвовать в операциях группировки (СГРУППИРОВАТЬ ПО) и упорядочивания (УПОРЯДОЧИТЬ ПО).

Попытка выполнить любое из этих действий приводит к генерации ошибки базой данных, которую платформа 1С транслирует разработчику.

Решение №1: Явное ограничение длины в запросе с помощью функции ВЫРАЗИТЬ()

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

Рассмотрим пример использования этой функции при соединении временной таблицы ВТТовары с таблицей регистра накопления по строковому полю:


ВЫБРАТЬ
    ВТТовары.НоменклатураСтрока КАК НоменклатураСтрока,
    СебестоимостьТоваров.Стоимость КАК Стоимость
ИЗ
    ВТТовары КАК ВТТовары
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
        ПО ВЫРАЗИТЬ(ВТТовары.НоменклатураСтрока КАК Строка(100)) = СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Наименование

При использовании функции ВЫРАЗИТЬ() важно помнить о следующих правилах:

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

Решение №2: Правильная типизация таблицы значений «на берегу»

Использование функций приведения типов непосредственно в запросе снижает его производительность, так как СУБД не всегда может эффективно построить план запроса и использовать индексы по вычисляемым полям. Гораздо более профессиональный и надежный подход — жестко типизировать колонки таблицы значений еще на этапе ее программного создания в коде 1С.

Проанализируем пример кода, в котором колонкам таблицы значений явным образом назначаются квалификаторы типов данных:


// Создаем таблицу значений
ТЗТовары = Новый ТаблицаЗначений;

// Описываем тип для строкового поля с жестким ограничением длины
КвалификаторыСтроки = Новый КвалификаторыСтроки(100);
ОписаниеСтроки = Новый ОписаниеТипов("Строка", , , КвалификаторыСтроки);
ТЗТовары.Колонки.Добавить("НоменклатураНаименование", ОписаниеСтроки);

// Описываем ссылочный тип данных для связи по уникальному идентификатору (ссылке)
ОписаниеСсылки = Новый ОписаниеТипов("СправочникСсылка.Номенклатура");
ТЗТовары.Колонки.Добавить("НоменклатураСсылка", ОписаниеСсылки);

Если таблица значений была получена нами из внешнего источника без жестких типов (например, загружена из файла Excel, CSV или получена в результате парсинга JSON), мы можем воспользоваться механизмом клонирования структуры таблицы с заменой типов. В типовых конфигурациях на базе Библиотеки Стандартных Подсистем (БСП) для этого реализована удобная функция:


// Пример использования функции БСП для приведения типов в ТЗ
ТаблицаСТипами = ОбщегоНазначения.СкопироватьТаблицуЗначенийСЗаменойТипов(ИсходнаяТаблица, ОписаниеТиповКолонок);

После такой подготовки СУБД создаст временную таблицу с четко определенными типами полей (например, NVARCHAR(100) вместо NVARCHAR(MAX)), что позволит свободно использовать их в секции ПО без вызова ошибок.

Решение №3: Избегаем сравнения несовместимых типов данных

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

Наиболее распространенная ошибка — это попытка сравнить строковое значение (например, наименование номенклатуры из текстового файла) со ссылочным типом данных в базе данных. Пример некорректного соединения:


// ВТТовары.Номенклатура содержит строковое значение "Шоколад Алёнка"
// СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура содержит ссылку СправочникСсылка.Номенклатура
ПО ВЫРАЗИТЬ(ВТТовары.Номенклатура КАК Строка(150)) = СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура

Хотя СУБД больше не выдает ошибку неограниченной длины, это выражение всегда будет принимать значение ЛОЖЬ, поскольку тип Строка физически никогда не равен типу СправочникСсылка. Чтобы соединение сработало корректно, мы обязаны сравнивать сопоставимые сущности:

Либо сравнивать строку со строкой (наименование или код элемента):


ПО ВЫРАЗИТЬ(ВТТовары.НоменклатураСтрока КАК Строка(150)) = СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура.Наименование

Либо предварительно найти в коде ссылки на элементы справочника по наименованию/коду, записать их в колонку таблицы значений и связать ссылки напрямую:
```
ПО ВТТовары.НоменклатураСсылка = СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура
```

Решение №4: Борьба с паразитными строками NULL из-за предложений ИТОГИ

Еще одной неочевидной причиной пустого результата при соединении таблиц может быть наличие пустых строк или значений NULL во временной таблице. На форуме была описана ситуация, когда в результат выгрузки исходной таблицы первой строкой попадала пустая запись с NULL.

Выясним причину этой аномалии. Такое поведение характерно для случаев, когда исходная таблица значений формируется путем выгрузки результатов другого запроса, в тексте которого присутствовало ключевое слово ИТОГИ. При стандартной выгрузке такого результата функцией РезультатЗапроса.Выгрузить() в результирующую таблицу включаются не только детальные записи, но и строки групповых и общих итогов. В строке общих итогов детальные поля (включая ссылки и строки для поиска) будут содержать значения NULL.

Для исключения этой проблемы мы должны использовать правильный метод обхода результата запроса, минуя итоговые группировки. Рассмотрим правильный шаблон получения чистых детальных записей:


// Осуществляем прямой обход только детальных записей запроса
ВыборкаДетали = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.Прямой);

// Создаем чистую таблицу для выгрузки с типизированными колонками
ЧистаяТЗ = Новый ТаблицаЗначений;
ЧистаяТЗ.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ЧистаяТЗ.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число"));

Пока ВыборкаДетали.Следующий() Цикл
    // Заполняем только реальными данными, исключая строки итогов
    НоваяСтрока = ЧистаяТЗ.Добавить();
    ЗаполнитьЗначенияСвойств(НоваяСтрока, ВыборкаДетали);
КонецЦикла;

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

Если по каким-то причинам очистить таблицу данных на этапе выгрузки невозможно, мы можем обработать NULL в самом запросе, применив функцию ЕСТЬNULL() или добавив жесткий отбор в секции ГДЕ:


ВЫБРАТЬ
    ВТТовары.Номенклатура КАК Номенклатура,
    СебестоимостьТоваров.Стоимость КАК Стоимость
ИЗ
    ВТТовары КАК ВТТовары
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
        ПО ВТТовары.Номенклатура = СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура
ГДЕ
    НЕ ВТТовары.Номенклатура ЕСТЬ NULL

Подведем итоги

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

Всегда типизируйте колонки создаваемых программно таблиц значений. Избегайте использования типов строк неограниченной длины.
Если типы изменить нельзя, используйте конструкцию ВЫРАЗИТЬ(Поле КАК Строка(Х)) в запросе с ограничением длины не более 1024 символов.
Никогда не сравнивайте разные типы данных (строки со ссылками). Приводите сравниваемые значения к единому знаменателю.
Контролируйте состав выгружаемой таблицы значений — очищайте её от итоговых записей со значениями NULL еще до передачи в параметры запроса.