Turn off rounding in Spark SQL calculation with table columns

user_13113250 0
Viewed 150

I need to calculate using two columns using Spark SQL on Azure Databricks: Result = column1 * column2 but it always returns a result with rounding to 6 decimals, even I set or convert the columns with decimal(38,18).

%sql

drop table if exists test_calc_round;

create table test_calc_round (number decimal(38,18), factor decimal(38,18));

insert into test_calc_round values (0.123456789, 1),  (0.123456789, 0.1), (0.123456789, 0.01), (0.123456789, 0.001);


SELECT
  number, factor,  
  CASE factor 
        WHEN 1 then number * 1
        WHEN 0.1 then number * 0.1
        WHEN 0.01 then number * 0.01
        WHEN 0.001 then number * 0.001
    END AS EXPECTED_RESULT,
  number * factor AS calc1,  
  cast(number as decimal(38,18)) * cast(factor as decimal(38,18))  AS calc2,
  CAST(
      cast(number as decimal(38,18)) * 
      cast(factor as decimal(38,18)) 
      AS decimal(38,18)
      ) AS calc3
FROM test_calc_round
ORDER BY factor DESC;

The result of this query is:

+--------------------+--------------------+-------------------+--------+--------+--------------------+
|              number|              factor|    EXPECTED_RESULT|   calc1|   calc2|               calc3|
+--------------------+--------------------+-------------------+--------+--------+--------------------+
|0.123456789000000000|1.000000000000000000|0.12345678900000000|0.123457|0.123457|0.123457000000000000|
|0.123456789000000000|0.100000000000000000|0.01234567890000000|0.012346|0.012346|0.012346000000000000|
|0.123456789000000000|0.010000000000000000|0.00123456789000000|0.001235|0.001235|0.001235000000000000|
|0.123456789000000000|0.001000000000000000|0.00012345678900000|0.000123|0.000123|0.000123000000000000|
+--------------------+--------------------+-------------------+--------+--------+--------------------+

Please any suggestions to resolve this automatic rounding?

An important point, I need to run using Spark SQL and not pyspark for this calculation (project definition).

Thanks a lot.

azure-databricks apache-spark-sql
1 Answers

This behavior is caused as a prevention of data loss. If you multiply two decimals the precision and scale of the resulting decimal is calculated by the following rules:

Precision = p1+p2+1 
Scale = s1+s2

This would equal a precision of 77 in your result. However the maximum possible precision is 38 and in this case both decimals which are part of the multiplication are already at 38. The server cannot know that not the full 38 is being used so it has to expect possible data loss.

So to avoid that the defined behavior is to sacrifice data after the decimal point rather than in front as that information is deemed less important. By default it truncates to six digits after the decimal point like seen in your example.

Changing your initial table creation to a lower precision e.g.:

create table test_calc_round1 (number decimal(17,10), factor decimal(17,10));

avoids the loss of data after the decimal point.

user_121735800
Related