Sql-server – Columnstore Aggregate Pushdown doesn’t work for float/real data types

aggregatecolumnstoreperformancesql serversql-server-2016

I am having an issue with Aggregate Pushdown for float/real data types.
According to the documentation, Aggregate Pushdown is supported for "Any datatype <= 64 bits" or 8 bytes ():

  • Supported aggregate operators are MIN, MAX, SUM, COUNT, AVG
  • Any datatype <= 64 bits is supported. For example, bigint is supported as its size is 8 bytes but decimal (38,6) is not because its size is 17 bytes. Also, no string types are supported
  • 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
)
, _RandomValues AS 
(
    SELECT 
          cKey  
        , cGroup        = ABS(CHECKSUM(NewId())) % 100          
        , cNumeric36_3  = CONVERT(NUMERIC(36,3) , CHECKSUM(NewId())           )                         / CONVERT(NUMERIC(36,3) , NULLIF(Divder10000          , 0.00))
        , cNumeric18_3  = CONVERT(NUMERIC(18,3) , CHECKSUM(NewId())           )                         / CONVERT(NUMERIC(36,3) , NULLIF(Divder100            , 0.00))
        , cNumeric18_9  = CONVERT(NUMERIC(18,9) , CHECKSUM(NewId()) % 1000000 )                         / CONVERT(NUMERIC(36,3) , NULLIF(Divder10000          , 0.00))
        , cNumeric15_12 = CONVERT(NUMERIC(15,12), CHECKSUM(NewId()) % 100     )                         / CONVERT(NUMERIC(36,3) , NULLIF(Divder10000          , 0.00))
        , cMoney        = CONVERT(MONEY, CHECKSUM(NewId()))                                             / CONVERT(MONEY         , NULLIF(Divder10000          , 0.00))
        , cFloat53      = CONVERT(FLOAT, CHECKSUM(NewId())) * CONVERT(FLOAT, CHECKSUM(NewId()))         / CONVERT(FLOAT(53)     , NULLIF(Divder10000          , 0.00))
        , cFloat53Less1 = CONVERT(FLOAT, 1.00)                                                          / CONVERT(FLOAT(53)     , NULLIF(CHECKSUM(NewId())    , 0.00))
        , cFloat24      = CONVERT(FLOAT(24), CHECKSUM(NewId())) * CONVERT(FLOAT(24), CHECKSUM(NewId())) / CONVERT(FLOAT(24)     , NULLIF(Divder10000          , 0.00))
        , cReal         = CONVERT(REAL, CHECKSUM(NewId())) * CONVERT(REAL, CHECKSUM(NewId()))           / CONVERT(REAL          , NULLIF(Divder10000          , 0.00))
    FROM _Keys
)
INSERT INTO dbo.TestTable 
SELECT *       
FROM _RandomValues
GO

CHECKPOINT;
GO

CREATE CLUSTERED COLUMNSTORE INDEX IDXCC_dboTestTable
    ON dbo.TestTable WITH (MAXDOP = 4);
GO

SELECT COUNT(*) 
FROM dbo.TestTable tt
GO

SELECT MAX(tt.cNumeric36_3) FROM dbo.TestTable tt;
SELECT MAX(tt.cNumeric18_3) FROM dbo.TestTable tt;
SELECT MAX(tt.cNumeric18_9) FROM dbo.TestTable tt;
SELECT MAX(tt.cNumeric15_12) FROM dbo.TestTable tt;
SELECT MAX(tt.cMoney) FROM dbo.TestTable tt;
SELECT MAX(tt.cFloat53) FROM dbo.TestTable tt;
SELECT MAX(tt.cFloat53Less1) FROM dbo.TestTable tt;
SELECT MAX(tt.cFloat24) FROM dbo.TestTable tt;
SELECT MAX(tt.cReal) FROM dbo.TestTable tt;
GO

Best Answer

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.

Related Question