Why does batch mode window aggregate yield arithmetic overflow?

12 votes

1 answer

299 views

sql-server sql-server-2016 window-functions columnstore batch-mode

                          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 8649 to prefer a parallel plan regardless of the cost threshold for parallelism
* Use trace flag 9453 to disable batch mode
* Use the COUNT aggregation function instead of the SUM function
* Remove the WHERE x.rank = 1 predicate

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 */) 


                        

Asked by Geoff Patterson (8447 rep)

Sep 26, 2018, 03:40 PM
Last activity: Nov 9, 2018, 11:22 PM

Why does batch mode window aggregate yield arithmetic overflow?

Related Questions