При работе с языком запросов 1С разработчики часто сталкиваются с необходимостью явного приведения типов. Особенно это актуально, когда в одну временную таблицу или в результат объединения помещаются данные из разных источников, и нам требуется жестко типизировать строковое поле определенной длиной. Для этих целей традиционно используется конструкция ВЫРАЗИТЬ.
Однако здесь нас подстерегает неприятный сюрприз: в некоторых случаях результат запроса в выраженном поле автоматически дополняется концевыми пробелами до максимальной указанной длины. Например, если мы преобразуем строку "Тест" к типу СТРОКА(100), на выходе мы рискуем получить строку длиной ровно 100 символов, где оставшиеся 96 позиций будут забиты пробелами. В этой статье мы подробно разберем, почему так происходит, рассмотрим подводные камни старых обходных путей и изучим современные изящные способы решения этой проблемы.
Если провести простой эксперимент в разных базах данных, можно заметить странную вещь. На одной платформе код возвращает строку без пробелов, а на другой — дополняет ее. Рассмотрим тестовый запрос:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВЫРАЗИТЬ(""тест"" КАК СТРОКА(100)) КАК Поле1";
Таб = Запрос.Выполнить().Выгрузить();
Сообщить(СтрДлина(Таб[0][0]));
В файловой базе данных или при работе с СУБД PostgreSQL этот код выведет в окно сообщений цифру 4. То есть строка осталась переменной длины и лишние пробелы к ней не добавились.
Однако если мы запустим этот же код в клиент-серверном варианте под управлением MS SQL Server (при условии, что режим совместимости конфигурации установлен в значение 8.3.10 или выше), система выведет число 100.
В чем причина такого поведения?
Все дело в особенностях трансляции языка запросов 1С в язык SQL для конкретной СУБД:
ВЫРАЗИТЬ(... КАК СТРОКА(N)) в тип данных фиксированной длины nchar(N). СУБД MS SQL Server на аппаратном уровне дополняет любые значения в полях типа nchar пробелами до максимально объявленной длины.varchar), которые не требуют дополнения пробелами.Таким образом, поведение платформы целиком и полностью зависит от используемой СУБД и режима совместимости базы данных.
Многие разработчики, пытаясь обойти проблему с концевыми пробелами в MS SQL Server, заменяют конструкцию ВЫРАЗИТЬ на функцию ПОДСТРОКА. С первого взгляда это кажется отличным решением:
ПОДСТРОКА(РасчетыСПокупателями.Содержание, 1, 100) КАК СодержаниеПроводки
Действительно, функция ПОДСТРОКА возвращает значение переменной длины и не забивает хвост строки пробелами. Но у этого приема есть скрытая и очень опасная угроза, о которой официально предупреждает технологическая платформа 1С.
Когда вы используете функцию ПОДСТРОКА для приведения строк неограниченной длины к ограниченной, MS SQL Server в определенных контекстах (например, во вложенных запросах или при объединениях) резервирует под это поле максимально возможный объем памяти, эквивалентный строке длиной 4000 символов.
Если такое поле затем участвует в операциях:
СГРУППИРОВАТЬ ПО)УПОРЯДОЧИТЬ ПО)ПО)то при наличии нескольких подобных полей в одном запросе суммарный размер записи для внутреннего сортировщика СУБД может легко превысить жесткий лимит MS SQL Server в 8000 байт. В этом случае ваш запрос аварийно завершится с критической ошибкой базы данных:
"Warning: The query processor could not produce a query plan..." или ошибкой о превышении размера строки таблицы SQL.
Именно поэтому использовать ПОДСТРОКА для явного ограничения длины полей, участвующих в дальнейших тяжелых операциях запроса, крайне не рекомендуется.
Если ваша конфигурация работает на версии платформы ниже 8.3.20, решить проблему "красиво" прямо внутри запроса без побочных эффектов для MS SQL Server не получится. В этом случае единственным надежным архитектурным решением является постобработка результата запроса во встроенном языке 1С.
Мы собираем данные запросом, типизируя поле через надежный ВЫРАЗИТЬ, выгружаем результат в таблицу значений и затем в цикле удаляем пробелы с помощью функции СокрЛП:
// Выполняем запрос с использованием ВЫРАЗИТЬ
ТаблицаРезультата = Запрос.Выполнить().Выгрузить();
// Обходим строки и удаляем концевые пробелы во встроенном языке
Для Каждого СтрокаРезультата Из ТаблицаРезультата Цикл
СтрокаРезультата.СодержаниеПроводки = СокрЛП(СтрокаРезультата.СодержаниеПроводки);
КонецЦикла;
Да, этот способ требует написания дополнительного кода и тратит небольшое количество процессорного времени на обход коллекции, но он гарантирует стабильность работы СУБД и корректность типов данных.
Начиная с версии платформы 8.3.20.1549, компания 1С сделала долгожданный подарок всем разработчикам. В язык запросов была добавлена полноценная поддержка строковых функций, которые ранее были доступны только во встроенном языке. Среди них появились функции сжатия пробелов:
СокрЛ() (удаляет пробелы слева)СокрП() (удаляет пробелы справа)СокрЛП() (удаляет пробелы с обеих сторон)Благодаря этому нововведению мы теперь можем избавиться от концевых пробелов непосредственно в самом теле запроса, сохранив при этом правильную типизацию поля и безопасность выполнения на MS SQL Server.
Давайте посмотрим, как будет выглядеть наше решение на современной платформе:
ВЫБРАТЬ
СокрЛП(ВЫРАЗИТЬ(ВЫБОР
КОГДА РасчетыСПокупателями.ЭтоАванс
ТОГДА &ТекстАванс
ИНАЧЕ &ТекстОплатаДолга
КОНЕЦ КАК СТРОКА(100))) КАК СодержаниеПроводки
Как это работает под капотом?
Конструкция ВЫРАЗИТЬ корректно определяет и ограничивает тип колонки до 100 символов, что важно для последующего помещения данных во временные таблицы или объединения. А обернутая вокруг нее функция СокрЛП убирает концевые пробелы, которые генерирует MS SQL Server. В результате в выборку попадает чистая строка переменной длины без лишнего "мусора".
При работе со строками в запросах 1С важно помнить о поведении СУБД MS SQL Server. Если вам нужно ограничить длину строки:
ПОДСТРОКА для полей, по которым планируется группировка, сортировка или соединение — это может привести к падению СУБД.ВЫРАЗИТЬ в запросе и последующий вызов СокрЛП() в коде при обходе выборки или таблицы значений.СокрЛП() непосредственно внутри текста запроса вокруг конструкции ВЫРАЗИТЬ.