Как исправить ошибку MS SQL «Arithmetic overflow error converting numeric to data type numeric» в запросах 1С?

← На главную

При формировании отчетов, заполнении документов или выполнении запросов в 1С при работе на MS SQL Server может возникать критическая ошибка: Microsoft SQL Server Native Client 10.0: Arithmetic overflow error converting numeric to data type numeric. Разберем причины возникновения этой проблемы и рассмотрим, как ее диагностировать и исправить.

Причины возникновения ошибки

Ошибка указывает на арифметическое переполнение при приведении типов данных numeric на стороне SQL Server. В результате математических операций с числовыми полями итоговый результат превысил максимальную допустимую разрядность (количество знаков до или после запятой), предусмотренную для данного вычисляемого поля.

Рассмотрим основные причины, которые приводят к переполнению:

Умножение и деление суммируемых полей в одном выражении: Формулы вида СУММА(Стоимость) * СУММА(Количество1) / СУММА(Количество2) заставляют SQL Server динамически рассчитывать точность промежуточного результата. При сложении точности операндов превышается предел в 38 знаков.
Прямое соединение таблиц остатков (декартово произведение): При выполнении ЛЕВОГО СОЕДИНЕНИЯ двух виртуальных таблиц остатков без предварительной группировки во временных таблицах происходит размножение (задвоение) строк. В результате функция СУММА() считывает размноженные данные и искусственно раздувает числа.
Деление на сверхмалые значения (повисшие остатки): Если в регистре остался мелкий «хвост» от прошлых списаний (например, 0.0000000001), условие равенства нулю не срабатывает. При делении на такое близкое к нулю число итоговый результат резко возрастает и вылезает за границы разрядности типа.

Шаг 1. Диагностика и локализация проблемных полей

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


ВЫБРАТЬ
    ОстаткиТоваров.Номенклатура КАК Номенклатура,
    СУММА(ОстаткиПартий.СтоимостьОстаток) * СУММА(ОстаткиТоваров.КоличествоОстаток) КАК ПромежуточнаяСумма,
    СУММА(ОстаткиПартий.КоличествоОстаток) КАК ДелительКоличество
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&МоментДокумента, Склад = &Склад) КАК ОстаткиТоваров
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&МоментДокумента, Склад = &ПартионныйСклад) КАК ОстаткиПартий
    ПО ОстаткиТоваров.Номенклатура = ОстаткиПартий.Номенклатура
СГРУППИРОВАТЬ ПО
    ОстаткиТоваров.Номенклатура

Выясним причину по полученной выборке:

Если поле ПромежуточнаяСумма приобретает аномально огромные значения, значит, происходит задвоение строк из-за неоптимального соединения таблиц остатков.
Если поле ДелительКоличество содержит сверхмалое значение (меньше 0.001), то деление на него приводит к арифметическому переполнению.

Шаг 2. Оптимизация запроса через временные таблицы

Чтобы исключить декартово произведение и некорректную работу агрегатных функций СУММА(), перепишем запрос. Разберем по шагам правильную структуру: сначала рассчитаем остатки во временных таблицах для каждого регистра по отдельности, а затем соединим готовые сгруппированные данные.


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

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

ВЫБРАТЬ
    ВТОстаткиТоваров.Номенклатура КАК Номенклатура,
    ВТОстаткиТоваров.КоличествоОстаток КАК Количество,
    ВЫБОР
        КОГДА ВТОстаткиПартий.КоличествоПартий ЕСТЬ NULL 
           ИЛИ (ВТОстаткиПартий.КоличествоПартий >= -0.0001 И ВТОстаткиПартий.КоличествоПартий <= 0.0001) ТОГДА 0
        ИНАЧЕ ВЫРАЗИТЬ(ВТОстаткиПартий.СтоимостьОстаток / ВТОстаткиПартий.КоличествоПартий * ВТОстаткиТоваров.КоличествоОстаток КАК ЧИСЛО(15, 2))
    КОНЕЦ КАК Сумма
ИЗ
    ВТОстаткиТоваров КАК ВТОстаткиТоваров
    ЛЕВОЕ СОЕДИНЕНИЕ ВТОстаткиПартий КАК ВТОстаткиПартий
    ПО ВТОстаткиТоваров.Номенклатура = ВТОстаткиПартий.Номенклатура

Шаг 3. Явное ограничение разрядности с помощью функции ВЫРАЗИТЬ

Посмотрим на применение функции ВЫРАЗИТЬ. Когда транслятор 1С генерирует SQL-запрос, ему необходимо явно указать целевой тип данных. Оборачивание вычисляемого выражения в функцию ВЫРАЗИТЬ(... КАК ЧИСЛО(15, 2)) гарантирует, что SQL Server выделит правильный буфер под результат и не выдаст ошибку переполнения.


ВЫРАЗИТЬ(СУММА(ОстаткиПартий.СтоимостьОстаток) * СУММА(ОстаткиТоваров.КоличествоОстаток) / СУММА(ОстаткиПартий.КоличествоОстаток) КАК ЧИСЛО(15, 2))

Шаг 4. Исправление повреждений итогов регистров

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

Откроем Конфигуратор в режиме администратора.
Перейдем в раздел Администрирование > Тестирование и исправление...
Установим флажок Пересчет итогов.
Запустим процесс кнопкой Выполнить.