SQL Server SUM in subquery causing arithmetic overflow when the SUM is less than a 32-bit int

I have a database that has Manifest's which contain multiple Inventory items. Each inventory item has a integer NetWeight.

I have the following query:

SELECT 
    ID,
    (SELECT SUM(NetWeight) FROM Inventory WHERE Inventory.ManifestID = Manifest.ID)
FROM Manifest

which is throwing an exception: Arithmetic overflow error converting expression to data type int. and I do not understand why.

Recently, the Inventory table has exceeded the size of an int if you SUM(NetWeight) with no filter.

However, I know and have verified that there are no Manifest's with a value greater than 100,000 for that subquery.

If I append WHERE ID > 0 to the end of the query, it runs fine. All Manifest IDs are greater than 0, so this is theoretically equivalent to having no filter at all.

I know I can just convert it to a bigint and continue with my day, but I want to understand why SQL is overflowing.

Is it internally summing the entire Inventory table for some reason?

Any insight would be greatly appreciated.

Estimated query plan: https://www.brentozar.com/pastetheplan/?id=BJETnFUpa

The top branch of the execution plan selects all 1,098,300 rows from Inventory and performs

SELECT SUM(NetWeight), 
       ManifestID
FROM Inventory
GROUP BY ManifestID

The output of this (estimated 6 groups) is merge joined onto Manifest (with a right outer join meaning that all rows from Manifest are preserved).

Manifest has a cardinality of 5 rows.

Presumably there exists a ManifestID in Inventory which is causing this overflow for a group but is unmatched in Manifest so is eliminated by the join.

Maybe rows where ManifestID is NULL?

You could try changing the query to

SELECT 
    ID,
    (SELECT SUM(NetWeight) FROM Inventory WHERE Inventory.ManifestID = Manifest.ID AND Inventory.ManifestID IS NOT NULL)
FROM Manifest

to get these rows eliminated earlier in the plan (Fiddle).

You might also evaluate an index on

Inventory(ManifestID) INCLUDE (NetWeight) WHERE (ManifestID IS NOT NULL)

to avoid reading these rows entirely and provide a useful covering index for the aggregation by ManifestID.

StackExchange Database Administrators Q#337534, answer score: 4