1

I'm getting this error but only when grouping by specific columns:

Arithmetic overflow error converting expression to data type int.

And I can't wrap my head around why. This is the query causing it (the sum-function is the culprit):

SELECT  a.AtgardAvvattningId, 
        a.ObjektId,
        sum(p.SlutLopLangd - p.StartLopLangd) As TotalLangd
    FROM AtgardAvvattning a 
        INNER JOIN Objekt o ON o.ObjektId = a.ObjektId 
        INNER JOIN Position p ON p.AvvattningAtgardId = a.AtgardAvvattningId 
        INNER JOIN Vna v ON v.PositionId = p.PositionId 
    WHERE v.OID IN (...) 
    GROUP BY a.AtgardAvvattningId, a.ObjektId, o.AtgardsDatum
    ORDER BY a.ObjektId

p.SlutLopLangd and p.StartLopLangd are both int columns. If I convert the values to bigints before sumation it works:

    sum(CONVERT(bigint, p.SlutLopLangd - p.StartLopLangd)) As TotalLangd

Giving this result:

AtgardAvvattningId ObjektId TotalLangd
DC9... 9B2... 25684
ECD... 9B2... 25700
3D0... 9B2... 170005
959... 9B2... 170005
BEC... 214... 11814
C31... 214... 11815

As you can see, no sum is even near the limit for int. The wierd thing is if I include the positionId in the group by clause like this it doesn't raise an error:

SELECT  a.AtgardAvvattningId, 
        a.ObjektId,
        sum(p.SlutLopLangd - p.StartLopLangd) As TotalLangd
    FROM AtgardAvvattning a 
        INNER JOIN Objekt o ON o.ObjektId = a.ObjektId 
        INNER JOIN Position p ON p.AvvattningAtgardId = a.AtgardAvvattningId 
        INNER JOIN Vna v ON v.PositionId = p.PositionId 
    WHERE v.OID IN (...) 
    GROUP BY a.AtgardAvvattningId, a.ObjektId, o.AtgardsDatum, p.PositionId
    ORDER BY a.ObjektId

In this case it's a 1-to-1 relationship between AtgardAvvattning and Position. This query gives the exact same result as above.

Why is it raising an Arithmetic overflow in the first place when the values are so small? And why does it work in the second? What's different? I know it's probably hard to give an answer without data and table structures but any hint would be helpful.

Update:

When removing the group by completly with this query:

    SELECT  a.AtgardAvvattningId, 
            a.ObjektId,
            p.PositionId,
            v.VnaId,
            p.StartLopLangd,
            p.SlutLopLangd,
            p.SlutLopLangd - p.StartLopLangd as Subtraction
        FROM AtgardAvvattning a 
            INNER JOIN Objekt o ON o.ObjektId = a.ObjektId 
            INNER JOIN Position p ON p.AvvattningAtgardId = a.AtgardAvvattningId 
            INNER JOIN Vna v WITH (NOLOCK) ON v.PositionId = p.PositionId 
        WHERE v.OID IN (...) 
        ORDER BY a.ObjektId

The result is not many rows at all:

AtgardAvvattningId ObjektId PositionId VnaId StartLopLangd SlutLopLangd Subtraction
DC96... 9B2... 473... 1345183 168501 174922 6421
ECD4... 9B2... 07E... 1252649 74602 81027 6425
ECD4... 9B2... 07E... 1252651 74602 81027 6425
ECD4... 9B2... 07E... 1252652 74602 81027 6425
ECD4... 9B2... 07E... 1252650 74602 81027 6425
DC96... 9B2... 473... 1345180 168501 174922 6421
DC96... 9B2... 473... 1345181 168501 174922 6421
DC96... 9B2... 473... 1345182 168501 174922 6421
3D08... 9BC... F18... 1374284 199000 233001 34001
3D08... 9BC... F18... 1374283 199000 233001 34001
9590... 9BC... A2D... 1374285 16591 50592 34001
9590... 9BC... A2D... 1374286 16591 50592 34001
9590... 9BC... A2D... 1374287 16591 50592 34001
9590... 9BC... A2D... 1374289 16591 50592 34001
9590... 9BC... A2D... 1374288 16591 50592 34001
3D08... 9BC... F18... 1374281 199000 233001 34001
3D08... 9BC... F18... 1374280 199000 233001 34001
3D08... 9BC... F18... 1374282 199000 233001 34001
C31B... 214... B20... 1349999 32756 44571 11815
BEC3... 214... F21... 1349998 205022 216836 11814

And however you sum the rows it should be hard to reach the int overflow limit.

Improve this question
6
  • 1
    A DISTINCT with a GROUP BY is always a sign of a flaw in your query. A GROUP BY already causes your data to be returned in distinct sets, so if you are getting duplicates, it likely means your GROUP BY is wrong. Otherwise the DISTINCT is redundant and unneeded overhead. Commented Jan 20, 2022 at 14:52
  • @Larnu I have now removed the DISTINCT from the query, still the same results and not really relevant to the question. Commented Jan 20, 2022 at 14:56
  • It is relevant to your SQL though, @Johan . DISTINCT and GROUP BY should rarely (read never) mix. Commented Jan 20, 2022 at 15:00
  • Double-check that the columns in the expression are Int. Does the query also work if your CONVERT uses Int instead of BigInt? That the result returns a number well below the limits of the int data type could be hiding the issue. You could have one row whose expression return value is out of range, but when summed together they are not. Try running a MAX(CONVERT(bigint, p.SlutLopLangd - p.StartLopLangd)) Commented Jan 20, 2022 at 15:01
  • 1
    Long and short of it is that SQL Server does not take into account possible errors happening when building a query plan. So a calculation could for example be calculated over values that are later filtered out anyway. Therefore you must code defensively and ensure that errors cannot happen even on data that would logically be filtered out. For example, you should cast values to bigint before summing them, you should null out 0 before using it as a divisor, you should use TRY_CONVERT instead of CONVERT Commented Jan 21, 2022 at 0:51

1 Answer 1

Reset to default
6

The final value doesn't actually matter. What is likely happening, is that at some point in your SUM you are going over the maximum value (2,147,483,647) or minimum value (-2,147,483,648) for an int and getting the error.

Take this example:

SELECT SUM(V.I)
FROM (VALUES(2147483646),
            (2),
            (-2006543543))V(I);

This will likely generate the same error:

Arithmetic overflow error converting expression to data type int.

The result of the SUM however, would be 140,940,105 (well below the maximum). This is because if 2147483646 and 2 are summed first, then you get 2147483648, which is larger than the maximum value of an int. If you CAST/CONVERT the value first, you don't get the error:

SELECT SUM(CONVERT(bigint,V.I))
FROM (VALUES(2147483646),
            (2),
            (-2006543543))V(I);
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Yea, that's what I suspected. But what I dont understand is why does it calculate differently when adding positionId to the group by when there's a 1-to-1 relationship. It should be the exact same numbers to sum
But not in the same groups, @Johan . If, for example, the value 2 in the above was part of a different group, then you wouldn't get the error, despite the same numbers are being SUMed; they're just in different "boxes".
@Johan any change in the query text can lead to a different plan (and potentially different estimates for which and how many rows will flow into and out of any operator, which operators are used, where implicit conversions may happen, which calculations are performed before or after any filtering, etc). Similar question yesterday, about string lengths vs. numeric range.
Yea, this has to be the explanation. But I still don't understand how it manages to reach the overflow limit with so few rows and so low numbers (I updated the question with the results without the group by). However the rows flow I don't see how it's possiple, but clearly it is somehow.
Your sample data doesn't replicate the error, @Johan . db<>fiddle
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.