Ошибка арифметического переполнения после изменения порядка запроса

У нас есть запрос, как показано ниже

SELECT p.propertyNumber as propertyNumber,
       ( SELECT COUNT(*)
         FROM #TEM
         WHERE pNumber = p.propertyNumber
           AND dsenderType = 0
           AND dType = 1
           AND reTime >= '2017-03-01 00:00'
           AND reTime <= '2017-04-01 00:00' ) AS temailCount,  
       ( SELECT COUNT(*)
         FROM #TEM
         WHERE pNumber = p.propertyNumber
           AND dsenderType = 1
           AND dType = 1
           AND dSendStatus = 1
           AND sentTime >= '2017-03-01 00:00'
           AND sentTime <= '2017-04-01 00:00' ) AS temailJobCount,  
       ( SELECT SUM( DATEDIFF( second, reTime, jEnddate ) )
         FROM #TEM
         WHERE pNumber = p.propertyNumber
           AND dsenderType = 0
           AND dType = 1
           AND ( jStatus = 2 OR jStatus = 4 )
           AND jEnddate >= '2017-03-01 00:00'
           AND jEnddate <= '2017-04-01 00:00' ) AS temailturnAroundTime
FROM property p
WHERE p.locationId = 6
GROUP BY propertyNumber

Он выдает ошибку «Арифметическая ошибка переполнения, преобразующая выражение в тип данных int». Но это работает, если мы изменим SUM( DATEDIFF( second, reTime, jEnddate ) ) на SUM( DATEDIFF( minute, reTime, jEnddate ) ) и самое большое число будет 1153447 минут, что не более 70000000 секунд.

Самое странное, что это работает, если мы изменим порядок запросов, поместив функцию SUM в качестве второго поля, например (также работает, если SUM является единственным полем)

SELECT p.propertyNumber AS propertyNumber,
       ( SELECT SUM( DATEDIFF( second, reTime, jEnddate ) )
         FROM #TEM
         WHERE pNumber = p.propertyNumber
           AND dsenderType = 0
           AND dType = 1
           AND ( jStatus = 2 OR jStatus = 4 )
           AND jEnddate >= '2017-03-01 00:00'
           AND jEnddate <= '2017-04-01 00:00' ) AS temailturnAroundTime, 
       ( SELECT ......) AS temailCount,
       ( SELECT ......) AS temailJobCount
FROM property p
WHERE p.locationId = 6
GROUP BY propertyNumber

Мы можем решить проблему, приведя результат DATEDIFF к bigint, но я все равно не могу понять, почему возникает ошибка. Может ли кто-нибудь дать мне ключ? Большое спасибо!


sql sql-server integer-arithmetic
person S Liu    schedule 06.04.2017    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Когда сервер считывает строку за строкой для вычисления SUM, он складывает значения int вместе во внутренней переменной int. Если при суммировании промежуточный результат превысит int емкости (2 147 483 647), сервер остановит вычисления и выдаст ту ошибку, которую вы видите.

Сервер остановит выполнение запроса, даже если последующие значения будут отрицательными, а общая сумма будет меньше 2 147 483 647.

По-видимому, в первой версии вашего запроса сервер сканирует строки в некотором порядке, в результате чего накопленный промежуточный результат превышает 2 147 483 647. Другие версии запроса могут использовать другой индекс для сканирования строк, следовательно, другой порядок суммирования значений, который никогда не превышает 2 147 483 647.

Это легко воспроизвести.

Я сделаю таблицу с кластеризованным индексом, добавлю несколько строк в другом порядке и посчитаю SUM. В этом простом запросе сервер будет сканировать таблицу, используя кластеризованный индекс, в порядке этого индекса.

CREATE TABLE [dbo].[T](
    [ID] [int] NOT NULL,
    [Value] [int] NOT NULL,
CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))

Тест 1

TRUNCATE TABLE [dbo].[T];

INSERT [dbo].[T] ([ID], [Value]) VALUES (1, 2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (2, -2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (3, 2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (4, -2147483647);

SELECT SUM([Value]) AS s
FROM [dbo].[T];

Результат

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

+---+
| s |
+---+
| 0 |
+---+

Тест 2

TRUNCATE TABLE [dbo].[T];

INSERT [dbo].[T] ([ID], [Value]) VALUES (1, 2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (2, 2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (3, -2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (4, -2147483647);

SELECT SUM([Value]) AS s
FROM [dbo].[T];

Результат

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 8115, Level 16, State 2, Line 8
Arithmetic overflow error converting expression to data type int.

Самый простой способ избежать этой проблемы — привести значение к bigint, в чем вы сами убедились:

TRUNCATE TABLE [dbo].[T];

INSERT [dbo].[T] ([ID], [Value]) VALUES (1, 2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (2, 2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (3, -2147483647);
INSERT [dbo].[T] ([ID], [Value]) VALUES (4, -2147483647);

SELECT SUM(CAST([Value] AS bigint)) AS s
FROM [dbo].[T];

Этот запрос возвращает 0 без ошибок арифметического переполнения.

Могут быть и другие случаи возникновения этой ошибки.

Сервер может сначала вычислить значение выражения DATEDIFF(second,reTime,jEnddate), а затем отфильтровать эту строку в соответствии с предложением WHERE.

Таким образом, если в таблице есть строка с большой разницей, даже если ее потом отфильтровать, это может повлиять на расчеты.

Движку не нужно сначала отфильтровывать строки и только потом вычислять выражение. Вы можете увидеть, что происходит, изучив фактический план выполнения.

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

Сделайте эту простую проверку:

select 
    MAX(DATEDIFF(minute,reTime,jEnddate)) AS MaxDiff,
    MIN(DATEDIFF(minute,reTime,jEnddate)) AS MinDiff
from #TEM

Обратите внимание, что в этом запросе нет WHERE. Вы хотите увидеть глобальную разницу MAX и MIN.

person Vladimir Baranov    schedule 06.04.2017
comment
Спасибо за Ваш ответ. Я проверил, и MaxDiff равен 6582081, MinDiff равен -75762, также получаю результаты при использовании второго устройства 394924854/-4545735. Может ли это быть причиной? - person S Liu; 06.04.2017
comment
@SLiu, я думаю, причина в том, что в некоторых версиях вашего запроса посредник SUM превышает возможности типа int. Я добавил пример в ответ. - person Vladimir Baranov; 06.04.2017
comment
О, это может быть причиной. Ваш пример все объясняет. Я меняю запрос на группировку по столбцу id с кластеризованным индексом, без ошибок. Спасибо большое :) - person S Liu; 06.04.2017
comment
@SLiu, движок может выбирать любой порядок сканирования строк. Вы не должны полагаться на это. Мой пример был для демонстрации ошибки переполнения. В сложном запросе и большой таблице порядок просмотра таблицы непредсказуем и нестабилен. Ваш измененный запрос может выполниться сегодня, а завтра завершиться ошибкой. Вы должны преобразовать свои значения int в bigint, чтобы гарантировать, что переполнения не произойдет. - person Vladimir Baranov; 07.04.2017
comment
Понял. Мы уже берем решение bigint cast. просто хочу сделать больше тестов, чтобы понять причину. - person S Liu; 07.04.2017