Почему возникает ошибка арифметического переполнения при использовании СУММА(1) в запросах 1С и как ее исправить?

← На главную

При разработке запросов для получения количества записей в больших таблицах и регистрах 1С мы иногда сталкиваемся с критической ошибкой базы данных. Сообщение об ошибке выглядит следующим образом: «Microsoft SQL Server Native Client 11.0: Ошибка арифметического переполнения при преобразовании numeric к типу данных numeric. SQLSTATE=22003, native=8115». Давайте разберем по шагам, почему возникает эта проблема, проанализируем механику работы СУБД и рассмотрим правильные подходы к подсчету количества строк.

Разберем причину возникновения ошибки

Часто программисты используют конструкцию вида СУММА(1) для подсчета строк в запросе. На первый взгляд это кажется логичным, но проблема кроется в том, как платформа 1С транслирует этот код в язык T-SQL при обращении к Microsoft SQL Server.

Когда мы пишем в запросе 1С СУММА(1), платформа неявно определяет разрядность для результата вычисления. В итоговом SQL-запросе формируется конструкция следующего вида:

CAST(SUM(1.0) AS NUMERIC(7, 0))

Проанализируем этот код. Тип данных NUMERIC(7, 0) означает, что результирующее число может состоять максимум из семи цифр. Максимальное значение, которое поместится в такую переменную — 9 999 999. Как только количество записей в вашем регистре (например, РегистрСведений.КлючиДоступаНаборовГруппДоступа) достигает 10 миллионов, базе данных требуется вернуть восьмизначное число. Оно физически не помещается в выделенный тип данных, и SQL Server экстренно прерывает операцию с ошибкой переполнения.

Дополнительные нюансы работы MS SQL: проблема с ISNULL

Рассмотрим еще одну интересную особенность, связанную с типами данных в SQL Server, которая также приводила к похожим ошибкам переполнения в старых версиях платформы 1С. В транслируемых запросах иногда встречается конструкция ISNULL(MAX(0.0), 1.0).

Выясним причину падения в этом случае. Функция ISNULL жестко приводит возвращаемый результат к типу данных своего первого аргумента:

Число 0.0 SQL Server расценивает как тип NUMERIC(1,1) (максимум одна цифра, и она же находится после запятой).
Число 1.0 воспринимается как NUMERIC(2,1) (всего две цифры, одна после запятой).

Если таблица пуста, MAX(0.0) возвращает NULL, и СУБД пытается вернуть второй аргумент — 1.0. При попытке конвертировать NUMERIC(2,1) в NUMERIC(1,1) происходит логическое переполнение, так как целая часть (единица) не может поместиться в тип, где все доступное место выделено только под одну цифру после запятой. Начиная с версии платформы 8.3.21 разработчики 1С учли этот нюанс и сделали обход данного ограничения на уровне платформы.

Решение 1: Правильный подсчет всех строк

Использование конструкции СУММА(1) для получения количества всех записей в таблице является антипаттерном. Для этих целей всегда используйте агрегатную функцию КОЛИЧЕСТВО().

Посмотрим на правильный пример запроса:


ВЫБРАТЬ
    КОЛИЧЕСТВО(*) КАК КоличествоЗаписей
ИЗ
    РегистрСведений.КлючиДоступаНаборовГруппДоступа КАК КлючиДоступаНаборовГруппДоступа

При использовании КОЛИЧЕСТВО(*) платформа генерирует на стороне СУБД функцию COUNT_BIG(*), которая возвращает значение с большим типом данных (до NUMERIC(12) или более) и гарантированно не приведет к переполнению даже на таблицах с миллиардами строк.

Решение 2: Подсчет количества строк с множественными условиями

Иногда нам требуется посчитать в одном запросе количество строк, удовлетворяющих разным условиям. Например, в скольких строках один реквизит равен определенному значению, а другой — не равен. В этом случае использовать КОЛИЧЕСТВО(*) напрямую не получится, и разработчики прибегают к конструкции СУММА(ВЫБОР КОГДА... ТОГДА 1 ИНАЧЕ 0 КОНЕЦ).

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

Рассмотрим пример безопасного запроса с условиями:


ВЫБРАТЬ
    СУММА(ВЫРАЗИТЬ(
        ВЫБОР 
            КОГДА Таблица.Реквизит1 = 2 ТОГДА 1 
            ИНАЧЕ 0 
        КОНЕЦ 
    КАК ЧИСЛО(30, 0))) КАК КоличествоУсловие1,
    
    СУММА(ВЫРАЗИТЬ(
        ВЫБОР 
            КОГДА Таблица.Реквизит2 <> 3 ТОГДА 1 
            ИНАЧЕ 0 
        КОНЕЦ 
    КАК ЧИСЛО(30, 0))) КАК КоличествоУсловие2
ИЗ
    Таблица КАК Таблица

Благодаря функции ВЫРАЗИТЬ(... КАК ЧИСЛО(30, 0)) мы заставляем транслятор 1С создать на уровне базы данных переменную достаточной размерности, которая с легкостью вместит любые объемы расчетов.

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

Мы подробно проанализировали причину возникновения ошибки SQLSTATE=22003 при работе с агрегатными функциями. Запомните два простых правила:

Для подсчета общего количества записей всегда применяйте функцию КОЛИЧЕСТВО(*). Это не только спасает от ошибок, но и работает значительно оптимальнее.
Если вам жизненно необходима функция СУММА() с константами или сложными расчетными показателями (включая деление и умножение), всегда оборачивайте результат в ВЫРАЗИТЬ() с явным указанием большой размерности (например, ЧИСЛО(30, 0) или ЧИСЛО(38, 0)).

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