ANSI compliant DECIMAL precision and scale

Learn how to enable ANSI compliant error messages when incorrect values are used for DECIMAL precision and scale.

Written by saritha.shivakumar

Last published at: October 29th, 2022

Problem

You are trying to cast a value of one or greater as a DECIMAL using equal values for both precision and scale. A null value is returned instead of the expected value.

This sample code:

%sql

SELECT CAST (5.345 AS DECIMAL(20,20))

Returns:

Image showing a null value is returned.

Cause

The DECIMAL type (AWS | Azure | GCP) is declared as DECIMAL(precision, scale), where precision and scale are optional.

Precision represents the total number of digits in the value of the variable. This includes the whole number part and the fractional part.

The scale represents the number of fractional digits in the value of the variable. Put simply, this is the number of digits to the right of the decimal point.

For example, the number 123.45 has a precision of five (as there are five total digits) and a scale of two (as only two digits are on the right-hand side of the decimal point).

When the precision and scale are equal, it means the value is less than one, as all digits are used for the fractional part of the number. For example, DECIMAL(20, 20) defines a value with 20 digits and 20 digits to the right of the decimal point. All 20 digits are used to represent the fractional part of the number, with no digits used for the whole number.

If the precision in the value overflows the precision defined in the datatype declaration, null is returned instead of the fractional decimal value.

Solution

Set spark.sql.ansi.enabled to true in your cluster's Spark config (AWS | Azure | GCP). 

This enables Spark SQL ANSI compliance.

For more information, review the ANSI compliance documentation.

Delete

Info

You can also set this value at the notebook level using spark.conf.set("spark.sql.ansi.enabled", "True") in a Python cell if you don't have the ability to edit the cluster's Spark config.

Once ANSI compliance is enabled, passing incorrect precision and scale values returns an error indicating the correct value.

For example, this sample code:

%sql

SELECT CAST (5.345 AS DECIMAL(20,20))

Returns this error message:

Error in SQL statement: SparkArithmeticException: [CANNOT_CHANGE_DECIMAL_PRECISION] Decimal(expanded, 5.345, 4, 3) cannot be represented as Decimal(20, 20).


However, this sample code:

SELECT CAST (5.345 AS DECIMAL(4,3))

Returns the expected result:

Image showing the expected value is returned.