Search code examples
sqlsnowflake-cloud-data-platformdivide-by-zero

How to solve that snowflake force the very small result of integer division to zero

I'm writing a snowflake query that calculate 1/2940744 and get the result equals to 0 How to solve to get the actual calculation result?

Solution

  • From docs:

    Division

    When performing division:

    • The leading digits for the output is the sum of the leading digits of the numerator and the scale of the denominator.

    • Snowflake minimizes potential overflow in the output (due to chained division) and loss of scale by adding 6 digits to the scale of the numerator, up to a maximum threshold of 12 digits, unless the scale of the numerator is larger than 12, in which case the numerator scale is used as the output scale.

    In other words, assuming a division operation with numerator L1.S1 and denominator L2.S2, the maximum number of digits in the output are calculated as follows:

    Scale S = max(S1, min(S1 + 6, 12))

    If the result of the division operation exceeds the output scale, Snowflake rounds the output (rather than truncating the output).

    Returning to example:

    SELECT 1/2940744;
-- 0

DESC RESULT LAST_QUERY_ID();

    enter image description here

    The value 0.00000034005 was rounded to 0. In order to change the behaviour one of the arguments could be explicitly casted:

     SELECT 1::NUMBER(38,12)/2940744;
 -- 0.00000034005

 DESC RESULT LAST_QUERY_ID();
 -- 1::NUMBER(38,12)/2940744    NUMBER(38,12)