При разработке сложных отчетов и обработок в среде «1С:Предприятие» разработчики нередко сталкиваются со специфическим поведением платформы и систем управления базами данных (СУБД). Особенно остро это проявляется при выполнении ресурсоемких пакетных запросов, задействующих механизмы временных таблиц, левых соединений, агрегатных функций и последующего расчета итогов.
В этой статье мы подробно разберем природу возникновения критической ошибки СУБД, связанной с превышением максимальной точности числовых типов данных (более 38 знаков), проанализируем влияние платформенных багов на трансляцию запросов в T-SQL, а также рассмотрим особенности использования зарезервированных системных слов (таких как Тип) в качестве псевдонимов полей.
При работе базы данных в клиент-серверном варианте (особенно под управлением MS SQL Server) при выполнении запроса может возникнуть аварийное завершение работы со следующим системным сообщением:
«Ошибка СУБД: Microsoft SQL Server Native Client: Столбец или параметр: указанная точность столбца 44 (или другое число более 38) превышает максимальную допустимую точность 38. HRESULT=80040E14, SQLSrvr: SQLSTATE=42000».
Выясним причину этой ошибки. В СУБД Microsoft SQL Server существует жесткое ограничение на максимальную точность (число значащих цифр) для типов данных decimal и numeric. Она не может превышать 38 знаков. Если транслятор запросов платформы 1С пытается передать в СУБД или создать во временной таблице поле, расчетная разрядность которого превышает этот лимит, SQL Server аварийно прерывает выполнение транзакции.
Каким образом платформа 1С умудряется сгенерировать тип данных с разрядностью, например, 44 знака? Рассмотрим этот механизм подробнее. При трансляции запроса из языка 1С в T-SQL платформа автоматически рассчитывает потенциальную разрядность и точность для результатов вычислений. Это необходимо для предотвращения арифметического переполнения. При этом действуют следующие правила:
СУММА платформа закладывает значительный «запас» под возможное накопление итогов по всей выборке.ЕСТЬNULL с вложенными операциями или константами разряды операндов могут складываться.ЕСТЬNULL, затем по этому полю строится левое соединение, выполняется группировка СГРУППИРОВАТЬ ПО, а в финале рассчитываются ИТОГИ ПО) расчетные разрядности накапливаются на каждом шаге.Ситуация усугублялась наличием внутреннего бага в платформе 1С версии 8.3.12 (в частности, в релизах 8.3.12.1412 и 8.3.12.1440). В этих версиях оптимизатор запросов некорректно суммировал разрядность полей при построении итогов по виртуальным таблицам остатков и временным таблицам, что приводило к автоматическому созданию в MS SQL Server полей с недопустимым типом вроде numeric(44, 2).
Если обновить платформу «1С:Предприятие» в текущий момент нет возможности, проблему можно решить непосредственно в тексте запроса. Для этого мы должны принудительно указать СУБД требуемую точность числа, запретив платформе заниматься автоматическим расчетом разрядности.
Инструментом для этого служит функция языка запросов ВЫРАЗИТЬ. Синтаксис конструкции выглядит следующим образом:
ВЫРАЗИТЬ(<Выражение> КАК ЧИСЛО(Длина, Точность))
Рассмотрим, как правильно применить эту конструкцию на практике. Проанализируем фрагмент запроса, в котором вычисляется сумма остатков:
ВЫБОР
КОГДА БланкиСтрогойОтчетностиОстатки.КоличествоОстаток ЕСТЬ NULL
ТОГДА ЛОЖЬ
ИНАЧЕ ИСТИНА
КОНЕЦ КАК ЕстьОстаток,
СУММА(ЕСТЬNULL(БланкиСтрогойОтчетностиОстатки.КоличествоОстаток, 0)) КАК КоличествоОстаток
В данном случае расчет разрядности поля КоличествоОстаток может выйти из-под контроля СУБД. Чтобы гарантировать корректное выполнение, обернем суммируемое выражение в функцию ВЫРАЗИТЬ, ограничив его, например, 15 знаками, из которых 2 знака отведем под дробную часть:
СУММА(ВЫРАЗИТЬ(ЕСТЬNULL(БланкиСтрогойОтчетностиОстатки.КоличествоОстаток, 0) КАК ЧИСЛО(15, 2))) КАК КоличествоОстаток
Благодаря этому приему платформа 1С жестко транслирует тип поля в SQL Server как numeric(15, 2). Автоматический расчет разрядности прекращается, и критическая ошибка СУБД полностью нивелируется.
Поскольку корень проблемы кроется в ошибке транслятора запросов самой платформы 1С, наиболее надежным и стратегически правильным решением является системное устранение бага:
ВЫРАЗИТЬ во всех запросах.Разберем еще один нюанс, поднятый в обсуждении. Почему слово Тип выделяется в редакторе запросов синим цветом? Синим цветом встроенный текстовый редактор 1С подсвечивает зарезервированные системные слова, операторы и функции.
Слово Тип является встроенной функцией языка запросов 1С (она применяется для определения типа данных, например, ТИП(Справочник.Номенклатура)). Использование зарезервированных слов в качестве псевдонимов полей (например, СписокНоменклатуры.Тип КАК Тип) крайне нежелательно по следующим причинам:
ИТОГИ ПО по такому полю.Чтобы избежать подобных конфликтов, рекомендуется либо переименовывать псевдоним поля на стадии выбора (например, КАК ТипНоменклатуры или КАК Тип1), либо использовать квадратные скобки для экранирования псевдонима: КАК [Тип].
Посмотрим на примере, как изменился финальный пакет запроса после переименования поля:
ВЫБРАТЬ
ТЗИтоги.ФизическоеЛицо КАК ФизическоеЛицо,
ТЗИтоги.Номенклатура КАК Номенклатура,
ТЗИтоги.Характеристика КАК Характеристика,
ТЗИтоги.Тип КАК Тип1, // Изменили псевдоним поля во избежание конфликтов
ТЗИтоги.Серия КАК Серия,
ТЗИтоги.Номер КАК Номер,
ТЗИтоги.ЕстьОстаток КАК ЕстьОстаток,
ТЗИтоги.КоличествоОстаток КАК КоличествоОстаток
ИЗ
ТЗИтоги КАК ТЗИтоги
УПОРЯДОЧИТЬ ПО
ТЗИтоги.Серия,
ТЗИтоги.Номер
ИТОГИ ПО
Тип1 // Корректный расчет итогов по новому имени поля
В ходе обсуждения участники предложили альтернативный вариант запроса, который не только решает проблему с расчетом разрядности, но и оптимизирует структуру вычислений, убирая лишние группировки и вложенные циклы.
Проанализируем исходный вариант запроса. В нем во временной таблице ТаблЧастьБСО сначала подготавливались данные, затем во второй временной таблице производилось левое соединение с остатками регистра и одновременно с этим — группировка СГРУППИРОВАТЬ ПО с расчетом суммы. Группировка осуществлялась по большому количеству полей, включая условную конструкцию ВЫБОР КОГДА..., что существенно замедляло работу СУБД и приводило к накоплению разрядности.
Рассмотрим более изящное и стабильное решение, предложенное в сообщении 10. Мы можем разделить логику получения данных и логику их представления:
ТаблЧастьБСО на основе переданной таблицы значений (параметра &ТаблицаНабор).ЕСТЬNULL. Помещаем этот промежуточный результат во временную таблицу ВТ без выполнения тяжелых группировок.ВТ и строим по ним многомерные ИТОГИ ПО.Посмотрим на реализацию этого оптимизированного алгоритма:
ВЫБРАТЬ
ЗНАЧЕНИЕ(Справочник.ФизическиеЛица.Putasylka) КАК ФизическоеЛицо,
СписокНоменклатуры.Номенклатура КАК Номенклатура,
СписокНоменклатуры.Характеристика КАК Характеристика,
СписокНоменклатуры.Тип КАК Тип1, // Избегаем зарезервированного имени
СписокНоменклатуры.Серия КАК Серия,
СписокНоменклатуры.Номер КАК Номер
ПОМЕСТИТЬ ТаблЧастьБСО
ИЗ
&ТаблицаНабор КАК СписокНоменклатуры
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТаблЧастьБСО.ФизическоеЛицо КАК ФизическоеЛицо,
ТаблЧастьБСО.Номенклатура КАК Номенклатура,
ТаблЧастьБСО.Характеристика КАК Характеристика,
ТаблЧастьБСО.Тип1 КАК Тип1,
ТаблЧастьБСО.Серия КАК Серия,
ТаблЧастьБСО.Номер КАК Номер,
ЕСТЬNULL(БланкиСтрогойОтчетностиОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток,
ВЫБОР
КОГДА ЕСТЬNULL(БланкиСтрогойОтчетностиОстатки.КоличествоОстаток, 0) = 0
ТОГДА ЛОЖЬ
ИНАЧЕ ИСТИНА
КОНЕЦ КАК ЕстьОстаток
ПОМЕСТИТЬ ВТ
ИЗ
ТаблЧастьБСО КАК ТаблЧастьБСО
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.БланкиСтрогойОтчетности.Остатки(
&ГраницаДокумента,
Склад = &Склад
И (ФизическоеЛицо, Номенклатура, Характеристика, Тип, Серия) В
(ВЫБРАТЬ РАЗЛИЧНЫЕ
ТаблЧастьБСО.ФизическоеЛицо,
ТаблЧастьБСО.Номенклатура,
ТаблЧастьБСО.Характеристика,
ТаблЧастьБСО.Тип1,
ТаблЧастьБСО.Серия
ИЗ
ТаблЧастьБСО КАК ТаблЧастьБСО)) КАК БланкиСтрогойОтчетностиОстатки
ПО ТаблЧастьБСО.ФизическоеЛицо = БланкиСтрогойОтчетностиОстатки.ФизическоеЛицо
И ТаблЧастьБСО.Номенклатура = БланкиСтрогойОтчетностиОстатки.Номенклатура
И ТаблЧастьБСО.Характеристика = БланкиСтрогойОтчетностиОстатки.Характеристика
И ТаблЧастьБСО.Тип1 = БланкиСтрогойОтчетностиОстатки.Тип
И ТаблЧастьБСО.Серия = БланкиСтрогойОтчетностиОстатки.Серия
И ТаблЧастьБСО.Номер = БланкиСтрогойОтчетностиОстатки.Номер
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.ФизическоеЛицо КАК ФизическоеЛицо,
ВТ.Номенклатура КАК Номенклатура,
ВТ.Характеристика КАК Характеристика,
ВТ.Тип1 КАК Тип1,
ВТ.Серия КАК Серия,
ВТ.Номер КАК Номер,
ВТ.КоличествоОстаток КАК КоличествоОстаток,
ВТ.ЕстьОстаток КАК ЕстьОстаток
ИЗ
ВТ КАК ВТ
ИТОГИ ПО
Тип1
Такой подход делает структуру запроса более "плоской" и понятной для оптимизатора СУБД. Нагрузка на сервер снижается, а вероятность столкнуться с багом автоматического пересчета разрядности сводится к минимуму. Сочетая оптимизацию структуры запроса с явным приведением типов через ВЫРАЗИТЬ, вы гарантируете стабильную и быструю работу вашей конфигурации на любых релизах платформы и СУБД.