Columnstore Aggregate Pushdown doesn't work for float/real data types

I am having an issue with Aggregate Pushdown for float/real data types.
According to the documentation, Aggregate Pushdown is supported for "Any datatype

• Any datatype

• Aggregate operator must be on top of SCAN node or SCAN node with group by

It doesn't work no matter what I do. I tried making column nullable and not null. Grouping and w/o grouping.

We are running on the latest version of the SQL Server 2016 (SP1-CU3). I wonder anyone experienced the same? It seems like a bug to me. Am I missing anything?

If you experience the same issue, please upvote my SQL Server Feedback Request. Now I am faced with a choice to convert float columns to numeric. But operations with numeric types are slower in general. So I may gain in one place and lose in another. I have successfully tested numeric(15,12).

Here is a script illustrating the issue (please enable Actual Execution plan to see the issue):

```
DROP TABLE IF EXISTS dbo.TestTable;

CREATE TABLE dbo.TestTable
(
cKey INT NOT NULL
, cGroup INT NOT NULL
, cNumeric36_3 NUMERIC(36, 3) NULL
, cNumeric18_3 DECIMAL(18, 3) NULL
, cNumeric18_9 DECIMAL(18, 9) NULL
, cNumeric15_12 DECIMAL(15, 12) NULL
, cMoney MONEY NULL
, cFloat53 FLOAT(53) NULL
, cFloat53Less1 FLOAT(53) NULL
, cFloat24 FLOAT(24) NULL
, cReal REAL NULL
);

;WITH _Numbers0 AS (
SELECT TOP 3000 column_id FROM sys.all_columns
)
, _Numbers AS (
SELECT cKey = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM _Numbers0 a CROSS JOIN _Numbers0 b
)
, _Keys AS (
SELECT
cKey = n.cKey
, Divder10 = CONVERT(INT, FLOOR(CHECKSUM(NewId()) % 10))
, Divder100 = CONVERT(INT, FLOOR(CHECKSUM(NewId()) % 100))
, Divder10000 = CONVERT(INT, FLOOR(CHECKSUM(NewId()) % 10000))
FROM _Numbers n
)
, _Ran

Microsoft has updated the documentation to say that float is not supported*.

The documentation for aggregate pushdown was inaccurate in some places and it doesn't state all of the restrictions. In some testing that I did a week ago I also found that float types are not supported. In addition to that, numeric(10,0) is supported despite requiring nine bytes of storage. Here's the most accurate summary for supported data types that I'm aware of:


All date and time data types are supported except datetimeoffset. All exact numeric data types are supported if they are under 10 bytes.

If you want to troubleshoot more you may be able to uncover something with the query_execution_dynamic_push_down_statistics extended event.

In case it's of interest, you can read about some of the limitations I've found around aggregate pushdown in my blog post here. Paul White has also written about the details of Grouped Aggregate Pushdown.

* Float and real now appear to work with aggregate pushdown for COUNT and COUNT_BIG only in SQL Server 2017 CU 16 (and maybe earlier). Other previously-unsupported types like datetimeoffset and numeric with precision > 18 also work, so long as the data (in batch format) fits in 64 bits at runtime.

StackExchange Database Administrators Q#182296, answer score: 8