none
Aggregation Issue in Azure Data WareHouse RRS feed

  • Question

  • Hello Team,

    Recently we observed that Azure datawarehouse provides a  different value every time an aggregation query is executed for the same input dataset.  

    Eg:   select cast(sum(columnA) as decimal(30,15))  from #table

    --columnA is of type float

    So the result sets to differ randomly out of a set of values like:

    173288250.22000020742416381836
    173288250.22000023722648620605
    173288250.22000011801719665527
    173288250.22000017762184143066
    173288250.22000014781951904297

    So is there any specific reason for the difference in the output for the same input dataset?

    Response would be greatly appreciated.

    Monday, March 4, 2019 2:50 PM

Answers

  • Hello Mike,

    Thank you for the input but I found that 

    querying : select cast(sum([amountusd]) as decimal(15,7))  from [dbo].[PAAS]

     I was getting a constant value and hence found out that float datatype is precise to 15 significant digits only.

    Hence the above issue.

    Tuesday, April 2, 2019 9:19 AM

All replies

  • Hi Nandan,

    I see 5 rows. Did select cast(sum(columnA) as decimal(30,15))  from #table get executed 5 times and the result the first time was: 173288250.22000014781951904297

    And the second time it was run: 173288250.22000017762184143066

    And the thirst time it was run: 173288250.22000011801719665527

    The 4th: 173288250.22000023722648620605

    And 5th: 173288250.22000020742416381836.

    Trying to understand the behavior. Thanks!

    Monday, March 4, 2019 11:30 PM
    Moderator
  • Hello Mike,

    I executed the select query multiple times and every time the output result was out of the above 5 values randomly with no particular sequence meaning sometimes got the some values like 173288250.22000020742416381836. consequently for multiple executions but sometimes got different values out of the 5 for consequent executions.

    So wanted to know the cause behind the same and is there an alternative to avoid the above scenario and get a static value every time.

    Thanks

    Tuesday, March 5, 2019 3:08 AM
  • Curious, can you try the following: select cast(sum(columnA) as decimal(30,20))

    The decimal(30,15) from your example does not fit the the actual scale value exemplified in 173288250.22000020742416381836, which is specifically 20. The precision is 9. This would be expressed as (9,20). So, if you could try select cast(sum(columnA) as decimal(30,20))

    Reference:

    Precision, scale, and Length (Transact-SQL)

    Wednesday, March 6, 2019 11:59 PM
    Moderator
  • Hello Mike,

    Thank you for the help.

    But even after change in the query, we are getting the above scenario only where the output result set differs randomly.

    So is this an expected scenario in Azure data warehosue due to the Distribution logic?

    Thursday, March 7, 2019 7:23 AM
  • Thanks for trying this out and letting me know the results. To better investigate this issue, this should go to support. Do you have an Azure support plan? If you do, please open a support request via the Azure Portal to have this investigated further. If you do not have an active support plan, please send me your Subscription ID to AzCommunity at Microsoft.com and instructions will be returned to you to have a support request created. Please include this forum thread in your email as a reference. 

    Thanks,

    Mike

    Thursday, March 7, 2019 5:37 PM
    Moderator
  • Hello Mike,

    Thank you for the input but I found that 

    querying : select cast(sum([amountusd]) as decimal(15,7))  from [dbo].[PAAS]

     I was getting a constant value and hence found out that float datatype is precise to 15 significant digits only.

    Hence the above issue.

    Tuesday, April 2, 2019 9:19 AM