The following query performs a windowed SUM over a columnstore table with 1500 total rows, each of which has the value 0 or 1, and it overflows the INT data type. Why is this happening?
SELECT a, p, s, v, m, n,
SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
SELECT a, p, s, v, m, n,
RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
FROM #t /* A columnstore table with 1,500 rows */
) x
WHERE x.rank = 1
--Msg 8115, Level 16, State 2, Line 1521
--Arithmetic overflow error converting expression to data type int.
Full script
See this file for a fully contained reproduction script.
Query plan
Here is an annotated estimated query plan (full XML on Paste the Plan).
Similar queries that execute successfully
If any of the following modifications are made, the error does not occur:
- Use trace flag
8649to prefer a parallel plan regardless of the cost threshold for parallelism - Use trace flag
9453to disable batch mode - Use the
COUNTaggregation function instead of theSUMfunction - Remove the
WHERE x.rank = 1predicate
For example, this query executes successfully:
SELECT a, p, s, v, m, n,
SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
SELECT a, p, s, v, m, n,
RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
FROM #t /* A columnstore table with 1,500 rows */
) x
WHERE x.rank = 1
OPTION (QUERYTRACEON 9453/* Disable batch mode */)
