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

6 votes
1 answer
564 views
sql-server performance sql-server-2016 aggregate columnstore
                          I am having an issue with Aggregate Pushdown for float/real data types. 
 According to the documentation , Aggregate Pushdown is supported for "Any datatype * 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

                        
Asked by Alexander Sharovarov (233 rep)
Jul 31, 2017, 09:27 PM
Last activity: Oct 3, 2024, 04:05 PM
Columnstore Aggregate Pushdown doesn't work for float/real data types

Related Questions
